️ Models (SQLAlchemy & BaseModel)

Models define the structure of data in the application, featuring inherited CRUD actions.

Lila uses SQLAlchemy as the default ORM. Instead of writing raw queries or manually handling connections for basic CRUD, models in Lila inherit from BaseModel (defined in lila/core/base_model.py), which implements standardized ActiveRecord operations, soft delete, single-flight read caching and partial-column serialization.

Using BaseModel

The BaseModel class serves as the foundation. All models should inherit from BaseModel to gain automatic CRUD methods without boilerplate code. Key identifiers like primary keys are optimized with index=True for faster querying.

Example: User Model

Here is how you define a User model. Notice that you don't write any class query methods, yet they are fully available:

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

class User(BaseModel):
    __tablename__ = "users"

    # Never include these columns in to_dict()/JSON/session payloads.
    __private_fields__ = ("password", "token")

    # Primary key is indexed for database 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())

Model Custom Configurations

You can configure the behavior of BaseModel by setting specific class-level fields:

Memory Optimization with __slots__

For custom python classes (like DTOs, service containers, or auxiliary objects) that you instantiate frequently, you can define __slots__ to instruct Python to avoid creating a dynamic __dict__ dictionary for each instance. This can reduce RAM usage by up to 60-70% for these helper objects:

class ProductDTO:
    __slots__ = ['id', 'name', 'price']  # Disables dynamic __dict__ to save RAM
    def __init__(self, id, name, price):
        self.id = id
        self.name = name
        self.price = price
Warning: Do NOT add __slots__ to your SQLAlchemy / BaseModel subclasses. SQLAlchemy relies on dynamic class attributes which conflict with __slots__ definitions.

Built-in CRUD Methods

Once a class inherits from BaseModel, the following methods are readily available:

1. Sync Methods (outside an event loop)

These helpers are safe in scripts, CLI commands or sync code. Inside an async route use the _async variants instead — the sync ones raise RuntimeError("Use the *_async variants inside an event loop.").

# 1. Fetch active records (active=1 is applied automatically when the column exists)
users = User.get_all()

# 2. Load only some columns (returns a list of dicts)
user_emails = User.get_all(select="id,email")

# 3. Filter by fields, order and paginate
# (filters/order_by/select accept table columns only; an unknown filter raises ValueError)
recent_users = User.get_all(limit=10, offset=0, order_by="name", active=1)

# 4. Fetch one record by primary key
user = User.get_by_id(1)

# 5. Insert (returns a dict; only loaded columns)
new_user = User.insert(name="Alex", email="alex@example.com", password="hashed")

# or pass a model instance
new_user = User.insert(User(name="Alex", email="alex@example.com", password="hashed"))

# 6. Update by ID (returns True/False)
User.update(1, {"name": "Alex Updated"})
User.update(1, name="Alex Updated")   # kwargs form

# 7. Delete by ID (soft delete when `active` exists)
User.delete(1)

# Counting is async-only:
# total = await User.count_async()
v1 note: User.insert(db, params) is a User-specific shim kept by some scaffolds. It is not the generic BaseModel signature — the framework API is insert_async(instance=None, **values) / insert(**values).

2. Async Methods (inside routes)

Async CRUD methods use the project connection, return plain dicts and deduplicate concurrent reads (single-flight) with a short MODEL_CACHE_TTL (5 s by default).

# Fetch all users asynchronously (non-blocking).
# Concurrent identical calls share a single DB query.
users = await User.get_all_async(limit=10, order_by="name", desc=False)

# Partial columns: `select` and `select_cols` are accepted.
cards = await User.get_all_async(select="id,name", limit=50)

# Fetch by primary key (returns dict or None)
user = await User.get_by_id_async(1)
user = await User.get_by_id_async(1, select="id,email")

# Insert / update / delete
created = await User.insert_async(name="Alex", email="alex@example.com", password="hashed")
ok = await User.update_async(created["id"], name="Alex Updated")
ok = await User.update_async(created["id"], {"name": "Alex Updated"})
deleted = await User.delete_async(created["id"])   # soft delete if `active` exists

# Count with filters
total = await User.count_async()

Writing Custom Async Model Methods

Use the same transaction and deduplication helpers the framework uses. run_deduplicated() collapses concurrent requests for the same cache key; connection.transaction() commits and rolls back for you.

from sqlalchemy import select
from lila.core.base_model import BaseModel, run_deduplicated
from lila.core.database import db as connection

class User(BaseModel):
    # ... columns ...

    @classmethod
    async def get_by_email_async(cls, email: str, active: int = 1):
        """Custom async query with single-flight deduplication."""
        cache_key = f"{cls.__tablename__}:get_by_email:{email}:{active}"

        async def _fetch():
            async with connection.transaction() as session:
                stmt = (
                    select(cls)
                    .where(cls.email == email, cls.active == active)
                    .limit(1)
                )
                result = await session.execute(stmt)
                row = result.scalars().first()
                return row.to_dict() if row else None

        return await run_deduplicated(cache_key, _fetch)

3. to_dict() and Loaded Columns

to_dict() returns only loaded columns and always skips __private_fields__. Because CRUD methods return dicts (never detached ORM instances), there is no lazy-loading/DetachedInstanceError problem and no get_related* helper: query the related table explicitly.

# Related rows: an explicit query is the supported pattern.
posts = await Post.get_all_async(user_id=1, limit=5)
profiles = await Profile.get_all_async(user_id=1, limit=1)
profile = profiles[0] if profiles else None

# get_by_id_async returns a dict with loaded columns only.
user = await User.get_by_id_async(1, select="id,name")

# On a model instance, to_dict() skips unloaded columns and __private_fields__.
instance = User(name="Alex", email="alex@example.com", password="hashed")
instance.to_dict()   # {'name': 'Alex', 'email': 'alex@example.com'}

4. Raw SQL Fallback (non-ORM)

If you need raw performance without the ORM, use the connection's query helpers. Use query_async inside async code and query outside the event loop.

from lila.core.database import db as connection

# Returns a list of dicts
rows = await connection.query_async(
    "SELECT id, name FROM users WHERE active = :active LIMIT :limit",
    params={"active": 1, "limit": 5},
    return_rows=True,
)

# Returns a single dict (or None)
row = await connection.query_async(
    "SELECT name, email FROM users WHERE id = :id",
    params={"id": 1},
    return_row=True,
)

# Writing: the statement is committed automatically and the result object is returned.
await connection.query_async("UPDATE users SET active = 0 WHERE id = :id", params={"id": 1})

For details on SQLAlchemy, visit the official documentation: SQLAlchemy Documentation.

5. Filters, ordering and pagination

Filters use column__operator=value. Unknown columns or operators raise ValueError (a typo never returns every row).

Filters
await Product.get_all_async(price__gte=10, price__lt=100, order_by="price", desc=True)
await Product.get_all_async(id__in=[1, 2, 3])
await Product.get_all_async(name__contains="coffee")      # case-insensitive
await Product.get_all_async(category_id__isnull=True)
await Product.count_async(status__ne="archived")

page = await Product.paginate_async(page=2, per_page=20, order_by="name", price__gte=10)
# {"items": [...], "page": 2, "per_page": 20, "total": 134, "pages": 7,
#  "has_prev": True, "has_next": True, "prev_page": 1, "next_page": 3}

Operators: ne, gt, gte, lt, lte, in, not_in, like, ilike, contains, startswith, endswith, isnull. per_page is capped by max_per_page=100. In a template: {{ pagination(page) }} renders the links (keeps the other query parameters).

Reads use SQLAlchemy Core and return dicts directly (no ORM objects, ≈1.8× faster for large lists) and never select __private_fields__. Results are copies, so mutating them never changes the read cache. On SQLite, writes of one process are queued behind a lock (single writer) and reads stay concurrent.