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

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