Connections to Database

To use connections, import the Database class from lila.core.database. With that you can connect to SQLite, MySQL or PostgreSQL. The scaffold stores the project instance in app/connections.py as connection.

Without an explicit config, Database() reads DB_TYPE/DB_NAME (and host/user/password) from .env the first time it connects. SQLite defaults to async with WAL mode.

app/connections.py

            
from lila.core.database import Database

# SQLite (async, WAL). "test" -> test.sqlite next to the project.
config = {"type": "sqlite", "database": "test", "is_async": True}
connection = Database(config=config)
connection.connect()

# MySQL (async needs aiomysql: pip install "lila-framework[mysql]")
config = {
    "type": "mysql",
    "host": "127.0.0.1",
    "port": 3306,
    "user": "root",
    "password": "password",
    "database": "db_test",
    "is_async": True,
}
mysql_connection = Database(config=config)
mysql_connection.connect()

                
                

Transaction Context Manager

Lila provides context managers that open a session, commit on success and roll back on exception. Async databases require async with connection.transaction(); use with connection.transaction_sync() only with a sync database (is_async=False).

Using connection.transaction()

from sqlalchemy import select
from app.connections import connection

# Async: opens a session, commits at the end, rolls back on exception.
async with connection.transaction() as session:
    session.add(User(email="test@example.com", name="Lila"))
    await session.flush()

    result = await session.execute(select(User).limit(5))
    rows = [row.to_dict() for row in result.scalars().all()]
          
# Sync database only (is_async=False)
with connection.transaction_sync() as session:
    session.add(User(email="test@example.com", name="Lila"))

Connection Pool Configuration

Lila's Database class configures connection pooling for MySQL and PostgreSQL, with pool_size=10 and max_overflow=20 by default. SQLite uses pool_size=5 and max_overflow=10.

You can adjust these values in app/connections.py:

config = {
    "type": "mysql",
    "host": "127.0.0.1",
    "port": 3306,
    "user": "root",
    "password": "password",
    "database": "db_name",
    "is_async": True,
    "pool_size": 30,       # Maximum number of persistent connections
    "max_overflow": 60,    # Extra temporary connections under peak load
}

Async Database Queries

Lila routes are asynchronous (async def) running on ASGI. Synchronous database helpers block the event loop, so inside routes use the _async variants. Model reads additionally use single-flight deduplication and a short MODEL_CACHE_TTL (5 s).

How deduplication works: if N concurrent requests trigger the exact same model query, Lila runs one database query and shares the result. The cache key includes the model, columns, filters and pagination; entries expire after MODEL_CACHE_TTL seconds and are invalidated on every write to that model.

Use the _async variants inside your routes:

# Non-blocking reads (recommended in route handlers)
@router.get("/products")
async def list_products(request: Request):
    items = await Product.get_all_async(limit=100)
    return items                      # list -> JSON automatically

@router.get("/products/{id:int}")
async def get_product(request: Request, id: int):
    product = await Product.get_by_id_async(id)
    return product or {}

# Writes also have async variants; do not pass a session.
@router.post("/products", csrf=True)
async def create_product(request: Request):
    created = await Product.insert_async(
        name="Gaming Chair",
        price=299.99,
    )
    return {"success": True, "id": created.get("id")}

Raw SQL Queries

Raw SQL goes through query_async (inside a loop) or query (outside). There is no deduplication for raw SQL: each call executes. Passing return_rows=True returns a list of dicts, return_row=True returns one dict, and writes are committed automatically.

# Raw SELECT returning a list of dicts
sql = "SELECT id, name FROM products WHERE price > :price LIMIT 10"
products = await connection.query_async(
    sql,
    params={"price": 10.5},
    return_rows=True,
)

# Single row (or None)
product = await connection.query_async(
    "SELECT id, name FROM products WHERE id = :id",
    params={"id": 1},
    return_row=True,
)

Migrations Add Missing Columns

lila migrate creates missing tables and adds missing columns to existing tables (ALTER TABLE ... ADD COLUMN). Nullable columns and columns with a server default can be added automatically; a NOT NULL column without a default is skipped with a warning, so add it manually or give it a server_default.

from app.connections import connection

report = await connection.migrate_async()
# {"tables": [...], "added_columns": ["products.sku", ...]}

# Destructive: drop every table first. CLI --refresh asks for confirmation unless --yes.
await connection.drop_all_async(use_base=True)

Background Work

Database writes are always executed by the database call itself — there is no background=True parameter on query_async or on the model API. For out-of-band work use BackgroundTask after a response, or the Redis task worker (lila worker).

from lila.core.mailer import Mailer

@router.post("/orders")
async def create_order(request: Request):
    created = await Order.insert_async(total=10)
    # Mailer.send_background() returns a Starlette BackgroundTask.
    task = Mailer.send_background(
        to="buyer@example.com",
        subject="Receipt",
        body_html=f"<p>Order {created['id']}</p>",
    )
    return JSONResponse({"success": True, "id": created["id"]}, background=task)