name:: sqlite-python-best-practices
description:: SQLite best practices for Python -- connection setup, WAL mode, foreign keys PRAGMA (must be set per connection!), context managers for transactions, parameterized queries, row_factory, executemany, connection management, indexes on FKs, and money as INTEGER cents. Use when building or reviewing any Python app with SQLite, when you see sqlite3 imports, or when setting up a new local database in a FastAPI/Flask project.
keywords:: sqlite python, sqlite3, python database, WAL mode, row factory, context manager, sqlite transactions, sqlite performance, python sqlite connection, parameterized queries, sqlite pragmas python, executemany, foreign keys pragma, busy timeout
license:: MIT

SQLite Best Practices for Python

Name: tessl-labs/sqlite-python-best-practices
Rating: 92.8 (1 reviews)
Author: tessl-labs

Production-quality SQLite patterns for Python applications.

Connection Setup (MUST DO for every connection)

SQLite requires PRAGMAs to be set per connection -- they are not persistent database settings. Every function that creates a connection MUST set these:

import sqlite3
import os

DB_PATH = os.getenv("DATABASE_PATH", "data.db")

def get_connection() -> sqlite3.Connection:
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row           # Dict-like access to rows
    conn.execute("PRAGMA journal_mode=WAL")  # Concurrent reads during writes
    conn.execute("PRAGMA foreign_keys=ON")   # Enforce FK constraints (OFF by default!)
    conn.execute("PRAGMA busy_timeout=5000") # Wait 5s on lock instead of SQLITE_BUSY
    conn.execute("PRAGMA synchronous=NORMAL")# Safe with WAL, better performance
    return conn

Critical: foreign_keys=ON must be set per connection

SQLite ignores all foreign key constraints by default. This means:

ON DELETE CASCADE silently does nothing
Invalid foreign key references are accepted without error
You MUST execute PRAGMA foreign_keys=ON on every new connection

Why each pragma matters

Pragma	Default	Set to	Why
`journal_mode`	DELETE	WAL	Allows concurrent reads while writing
`foreign_keys`	OFF	ON	Without this, FK constraints and CASCADE are ignored
`busy_timeout`	0	5000	Prevents immediate SQLITE_BUSY errors on contention
`synchronous`	FULL	NORMAL	Safe with WAL, avoids unnecessary fsync

Context Managers for Transactions (ALWAYS use)

Always use with conn: for write operations. This auto-commits on success and auto-rolls-back on exception:

def create_order(conn: sqlite3.Connection, customer_name: str, items: list[dict]) -> dict:
    with conn:  # BEGIN ... COMMIT on success, ROLLBACK on exception
        cursor = conn.execute(
            "INSERT INTO orders (customer_name, total_cents, status) VALUES (?, ?, ?)",
            (customer_name, calculate_total(items), "received")
        )
        order_id = cursor.lastrowid

        conn.executemany(
            "INSERT INTO order_items (order_id, menu_item_id, quantity, price_cents) VALUES (?, ?, ?, ?)",
            [(order_id, i["menu_item_id"], i["quantity"], i["price_cents"]) for i in items]
        )

    return get_order_by_id(conn, order_id)

Do NOT:

# BAD -- manual commit, no rollback on error, connection leaked
conn = sqlite3.connect("data.db")
conn.execute("INSERT INTO ...")
conn.commit()

Connection lifecycle pattern:

def handle_request():
    conn = get_connection()
    try:
        with conn:
            # ... write operations ...
        return result
    finally:
        conn.close()

Parameterized Queries (ALWAYS use ? placeholders)

# GOOD -- parameterized with ? placeholders
conn.execute("SELECT * FROM orders WHERE id = ?", (order_id,))
conn.execute("SELECT * FROM items WHERE name LIKE ?", (f"%{search}%",))

# BAD -- SQL injection vulnerability
conn.execute(f"SELECT * FROM orders WHERE id = {order_id}")
conn.execute("SELECT * FROM items WHERE name LIKE '%{}%'".format(search))

Row Factory for Dict-Like Access

conn.row_factory = sqlite3.Row
row = conn.execute("SELECT * FROM orders WHERE id = ?", (1,)).fetchone()
row["customer_name"]  # Access by column name
dict(row)             # Convert to plain dict

Returning lists of dicts:

def get_all_items(conn: sqlite3.Connection) -> list[dict]:
    rows = conn.execute("SELECT * FROM items ORDER BY name").fetchall()
    return [dict(row) for row in rows]

executemany for Bulk Operations

When inserting or updating multiple rows, use executemany instead of looping:

# GOOD -- single call, efficient
conn.executemany(
    "INSERT INTO order_items (order_id, product_id, quantity) VALUES (?, ?, ?)",
    [(order_id, item["id"], item["qty"]) for item in items]
)

# BAD -- N round-trips
for item in items:
    conn.execute("INSERT INTO order_items ...")

Schema Best Practices

Money as INTEGER (cents)

# Store in cents to avoid floating-point errors
total_cents = int(price_dollars * 100)
# Display
display_price = f"${total_cents / 100:.2f}"

price_cents INTEGER NOT NULL  -- NOT REAL

Dates as ISO TEXT

created_at TEXT DEFAULT (datetime('now')) NOT NULL

from datetime import datetime
created = datetime.fromisoformat(row["created_at"])

Indexes on foreign key columns

SQLite does not auto-create indexes on foreign key columns (unlike PostgreSQL):

CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_movements_product_id ON movements(product_id);

CHECK constraints for enums

status TEXT NOT NULL DEFAULT 'received'
    CHECK (status IN ('received', 'preparing', 'ready', 'cancelled'))

ON DELETE CASCADE (requires foreign_keys=ON!)

CREATE TABLE order_items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    -- ...
);
-- Remember: CASCADE only works when PRAGMA foreign_keys=ON

Thread Safety

SQLite connections are not thread-safe by default. For multi-threaded servers (FastAPI, Flask with threads):

# Option 1: New connection per request (recommended)
def get_db():
    conn = get_connection()
    try:
        yield conn
    finally:
        conn.close()

# Option 2: Allow cross-thread (less safe)
conn = sqlite3.connect(DB_PATH, check_same_thread=False)

Migrations Pattern

def init_db():
    conn = get_connection()
    try:
        conn.execute("""
            CREATE TABLE IF NOT EXISTS _migrations (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL UNIQUE,
                applied_at TEXT DEFAULT (datetime('now'))
            )
        """)

        migrations = [
            ("001_initial", """
                CREATE TABLE products (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT NOT NULL,
                    price_cents INTEGER NOT NULL,
                    created_at TEXT DEFAULT (datetime('now')) NOT NULL
                );
                CREATE INDEX idx_products_name ON products(name);
            """),
        ]

        for name, sql in migrations:
            if not conn.execute("SELECT 1 FROM _migrations WHERE name = ?", (name,)).fetchone():
                conn.executescript(sql)
                conn.execute("INSERT INTO _migrations (name) VALUES (?)", (name,))
                conn.commit()
    finally:
        conn.close()

Checklist

When creating or reviewing Python + SQLite code, verify:

Verifiers

python-sqlite-setup -- Configure SQLite connection with WAL mode and foreign keys in Python

evals

skills

sqlite-python-best-practices

SKILL.md

verifiers

tile.json

tessl-labs/sqlite-python-best-practices

SKILL.md.css-3qkkll{font-size:var(--chakra-font-sizes-sm);font-weight:var(--chakra-font-weights-normal);color:var(--chakra-colors-gray-300);}skills/sqlite-python-best-practices/