Connections to Database

To use connections, you need to import the Database class from lila.core.database. With that you can connect to your database, which can be SQLite, MySLQ, PostgreSQL or whatever you want to configure.

Below we leave you the example of how to connect. The connection will close automatically after being used, so you can use it as in this example in the variable connection

            
from lila.core.database import Database

#SQLite

#Example connection to a sqlite database 
config = {"type":"sqlite","database":"test"} #test.db
connection = Database(config=config)
connection.connect()

#MySql

#Example connection to a mysql database 
config = {"type":"mysql","host":"127.0.0.1","user":"root","password":"password","database":"db_test","auto_commit":False}
connection = Database(config=config)
connection.connect()
mysql_connection = connection
                

                
                

Transaction Context Manager

Lila provides a built-in context manager to safely run database operations. It automatically commits the session on success, or rolls back on exception, closing the session automatically.

# Automatically opens a session, commits at the end, and handles rollbacks/cleanup!
with connection.transaction() as db:
    new_user = User(email="test@example.com", name="Lila")
    db.add(new_user)
          

Connection Pool Configuration

Lila's Database class configures connection pooling for MySQL and PostgreSQL by default, with pool_size=20 and max_overflow=40. These settings ensure there are enough connections available for concurrent async database executions.

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",
    "auto_commit": False,
    "pool_size": 30,       # Maximum number of persistent connections
    "max_overflow": 60,    # Extra temporary connections under peak loads
}

Async Database Queries (Non-Blocking & Deduplicated)

Lila routes are asynchronous (async def) running on ASGI. Standard synchronous database queries block the event loop, which drastically reduces throughput under concurrent traffic.

To prevent blocking, Lila provides non-blocking async variants for model queries with **automatic query deduplication (Single-flight)**.

Use the _async variants inside your routes:

# ✅ Non-blocking & Deduplicated (Recommended for SELECT in route handlers)
@router.get("/products")
async def list_products(request: Request):
    items = await Product.get_all_async(limit=100)
    return JSONResponse(items)

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

# ⚠️ Standard writes (must use the synchronous method inside a session context)
@router.post("/products")
async def create_product(request: Request):
    db = connection.get_session()
    Product.insert(db, request.state.data.dict())
    db.commit()
    db.close()
    return JSONResponse({"success": True})

Non-Blocking Raw SQL Queries

If you run custom raw queries using the Database.query wrapper, Lila provides a corresponding non-blocking query_async method. Like the ORM variants, it also automatically deduplicates concurrent SELECT queries!

# Non-blocking & Deduplicated raw SELECT query
sql = "SELECT id, name FROM products WHERE price > :price LIMIT 10"
products = await connection.query_async(
    sql,
    params={"price": 10.5},
    return_rows=True
)

Hybrid Queue-Based Asynchronous Writes

Under heavy concurrent load, or when explicitly requested, database write queries (INSERT, UPDATE, DELETE) can be routed automatically to a Redis task queue. This prevents connection pool starvation and database lock conflicts.

The system executes writes synchronously if the queue size is 15 or less, and automatically enqueues writes once it passes the threshold. You can also bypass this routing logic using the background parameter:

• background=True: Force the query to execute asynchronously via the Redis queue. Returns a queue response payload immediately: {"success": true, "queued": true, "job_id": "..."}.
• background=False: Force the query to run synchronously against the database, bypassing the queue threshold entirely (useful for critical actions).

# Force a raw SQL INSERT to run in the background
result = await connection.query_async(
    query="INSERT INTO products(name, price) VALUES(:name, :price)",
    params={"name": "Gaming Chair", "price": 299.99},
    background=True
)

# Force an ORM model insertion to run in the background
result = await Product.insert_async(
    db_session,
    {"name": "Gaming Keyboard", "price": 89.99},
    background=True
)