️ 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 core/base_model.py), which implements standardized ActiveRecord operations, soft delete, relationship safely-handling helpers, and database query wrappers.

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 core.base_model import BaseModel

class User(BaseModel):
    __tablename__ = "users"
    
    # 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=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())

Model Custom Configurations

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

_delete_logic = True: If True (default), calling delete() performs a soft delete (updates active field to 0). Set to False to perform hard deletion.
_active_field = "active": The name of the column storing the soft delete state (default "active").
_primary_key = "id": The name of the primary key field (default "id").

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. Class Methods (ActiveRecord)

from app.connections import connection

# 1. Fetch all active records (filtered by active=1 automatically)
users = User.get_all()

# 2. Fetch specific columns to optimize memory (returns list of dicts)
user_emails = User.get_all(select="id,email")

# 3. Filter by fields and limit
recent_users = User.get_all(limit=10, active=1)

# Get a session for modifying operations
db = connection.get_session()

# 4. Fetch a single record by primary key (filtered by active=1 automatically)
user = User.get_by_id(db, 1)

# 5. Insert a new record
new_user = User.insert(db, {
    "name": "Alex",
    "email": "alex@example.com",
    "password": "hashed_password"
})
db.commit() # Save changes to database

# 6. Update a record by ID
User.update(db, 1, {"name": "Alex Updated"})

# 7. Delete a record (performs soft delete by default)
User.delete(db, 1)

2. Async Class Methods (Deduplicated)

Lila provides non-blocking async counterparts for get_all() and get_by_id() to prevent blocking the event loop in concurrent ASGI routes. These async methods utilize **automatic query deduplication (Single-flight)**.

# Fetch all users asynchronously (non-blocking)
# Concurrent calls will share a single DB query!
users = await User.get_all_async(limit=10)

# Fetch a user by primary key asynchronously (non-blocking)
# Concurrent calls for the same ID share a single query!
user = await User.get_by_id_async(user_id=1)

Writing Custom Async Model Methods

You can easily create your own custom async query methods by defining a standard synchronous query method, and then wrapping it inside an async method using the inherited cls.run_async() helper.

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

    @classmethod
    def get_by_email(cls, db: Session, email: str, active: int = 1):
        """Standard synchronous query method."""
        return db.query(cls).filter(cls.email == email, cls.active == active).first()

    @classmethod
    async def get_by_email_async(cls, email: str, active: int = 1):
        """Non-blocking, query-deduplicated version."""
        # 1. Define a unique fingerprint to collapse concurrent requests
        cache_key = f"{cls.__tablename__}:get_by_email:{email}:{active}"
        
        # 2. Define the execution wrapper that opens/closes the database session
        def _fetch():
            db = connection.get_session()
            try:
                return cls.get_by_email(db, email, active)
            finally:
                db.close()
                
        # 3. run_async takes care of deduplication, thread pool delegation, and resolution!
        return await cls.run_async(cache_key, _fetch)

3. Relationship Helpers

In standard SQLAlchemy, accessing relationships (like user.posts) after the database session has closed raises a DetachedInstanceError. Lila solves this by offering relationship helpers on BaseModel that manage their own session lifecycle and immediately expunge (detach) the retrieved records from the session. This makes the returned models safe to pass and access anywhere — inside **Routes**, **Services**, or even directly in **Jinja2 Templates** without session issues.

A. One-to-One / Many-to-One (`get_related`)

Fetches a single related object. By default, Lila checks the current model for a foreign key column named {target_model}_id. If that is not found, it checks for id_{target_model}.

# Looks for 'profile_id' (or 'id_profile') on the user instance
profile = user.get_related(Profile)

# If your foreign key column is named differently, pass it explicitly:
profile = user.get_related(Profile, foreign_key_field="custom_profile_fk")

B. One-to-Many (`get_related_many`)

Fetches a list of related objects. By default, Lila looks for a foreign key column referencing this model on the target model named {current_model}_id. If not found, it checks for id_{current_model} on the target model.

# Inside User model, this looks for 'user_id' (or 'id_user') on the Post model:
posts = user.get_related_many(Post)

# If the foreign key on the Post model is different (e.g. 'author_id'):
posts = user.get_related_many(Post, foreign_key_field="author_id")

# You can also set a limit on the number of returned records (default is 1000)
recent_posts = user.get_related_many(Post, limit=10)

C. Usage in Routes / Jinja2 Templates

Because these helpers decouple the returned models from the active database session, you can load and pass them directly to your template context:

# In app/routes/routes.py
@router.get("/profile")
async def user_profile(request: Request):
    db = connection.get_session()
    user = User.get_by_id(db, id=1) # Fetch active user
    
    # Safely fetch relationships without keeping the session open
    profile = user.get_related(Profile)
    posts = user.get_related_many(Post, limit=5)
    
    return render(request, "pages/profile", {
        "user": user,
        "profile": profile,
        "posts": posts
    })

3. Performance Raw SQL Fallbacks (Non-ORM)

If you need raw performance without ORM overhead, use these raw SQL wrappers:

# Fetches active rows directly using connection.query (returns list of dicts)
raw_users = User.get_all_without_orm(select="id, name", limit=5)

# Fetches single active row directly (returns dict)
raw_user = User.get_by_id_without_orm(id=1, select="name, email")

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