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