️ Migrations

In Lila Framework, database migrations can be managed using SQLAlchemy and Lila's configuration to make migrations as easy as possible. The framework now supports command-line migrations through Typer, providing a more intuitive and flexible way to manage your database schema.


Migration Methods

There are two main ways to define database tables:

1. Using Table

This method manually defines the table structure using SQLAlchemy's Table object.

app/models/tables.py

from sqlalchemy import Table, Column, Integer, String, TIMESTAMP
from lila.core.database import Base  # Import Base

table_users = Table(
    'users', Base.metadata, # IMPORTANT: Use Base.metadata
    Column('id', Integer, primary_key=True, autoincrement=True),
    Column('name', String(length=50), nullable=False),
    Column('email', String(length=50), unique=True),
    Column('password', String(length=150), nullable=False),
    Column('token', String(length=150), nullable=False),
    Column('active', Integer, default=1, nullable=False),
    Column('created_at', TIMESTAMP),
)
                                    

2. Using Models (Recommended)

This approach defines database tables as Python classes that inherit from BaseModel. This is the recommended method as it provides standardized CRUD, soft delete, and ORM capabilities. Key columns, especially primary keys (like id), are declared with index=True. Adding indexes to these columns enables the database engine to search and access records exponentially faster.

app/models/user.py
from lila.core.base_model import BaseModel
from sqlalchemy import Column, Integer, String, TIMESTAMP, func

class User(BaseModel):
    __tablename__ = 'users'

    __private_fields__ = ("password", "token")

    # Primary key with index=True to speed up lookup performance
    id = Column(Integer, primary_key=True, autoincrement=True, index=True)
    name = Column(String(length=50), nullable=False)
    email = Column(String(length=80), unique=True)
    password = Column(String(length=150), nullable=False)
    token = Column(String(length=150), nullable=True)
    active = Column(Integer, nullable=False, default=1)
    created_at = Column(TIMESTAMP, nullable=False, server_default=func.now())
Run Migrations

Once your model is defined in app/models/, Lila imports every model module so SQLAlchemy registers the table. Then run:

Terminal
lila migrate

Running Migrations

lila migrate creates missing tables and adds missing columns to existing tables (nullable columns or columns with a default). A NOT NULL column without a default is skipped with a warning. --refresh drops every table first and asks for confirmation unless --yes is passed.

Terminal Command

# Create tables + add missing columns (alias: lila-migrations)
lila migrate

# Drop and recreate everything (interactive confirmation)
lila migrate --refresh

# Destructive, no prompt:
lila migrate --refresh --yes
                                    

Command Options

Plan, renames and history

lila migrate --plan
$ lila migrate --plan
📋 6 difference(s):
  ✏️ rename_column      products.name                  nme → name  [safe]
  ➕ add_column         products.stock                 INTEGER  [safe]
  ⚡ create_index       products                       ix_products_name (name)  [safe]
  ⚠️ alter_type         products.price                 TEXT → INTEGER  [manual]
  ⚠️ add_column_manual  products.code                  NOT NULL without default ...  [manual]
  🗑️ drop_column        products.legacy                in the database but not in the model  [needs --drop-columns]
Renaming a column
class Product(BaseModel):
    __tablename__ = "products"
    __renamed_columns__ = {"name": "nme"}      # new name -> old name: data is kept
    name = Column(String(120), index=True)
    ...

From Python


from app.connections import connection

report = await connection.migrate_async()
# {"tables": ["users", "admins", ...], "added_columns": ["users.token", ...]}

# Destructive: drops every table registered in Base.metadata.
await connection.drop_all_async(use_base=True)
          

Note: models are imported from app/models/*.py (and lila.core.admin for the admins table). If a module fails to import, its table may be missing — the command prints the failing modules and exits with a non-zero status.