️ 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.

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.

from core.base_model import BaseModel
from sqlalchemy import Column, Integer, String, TIMESTAMP, func

class User(BaseModel):
    __tablename__ = 'users'
    
    # 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=50), 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 will automatically load it to register it in the database. Simply run:

lila-migrations migrate

Running Migrations

To execute migrations, use the following command in your terminal:

# Basic migration
lila-migrations 

# Refresh all tables (drop and recreate)
lila-migrations --refresh
                                    

Command Options

• migrate: Run the database migrations
• --refresh: Optional flag to drop and recreate all tables