Sqlite Ops

SQLite Operations

Patterns for SQLite databases in Python projects.

Quick Connection

import sqlite3

def get_connection(db_path: str) -> sqlite3.Connection:
    conn = sqlite3.connect(db_path, check_same_thread=False)
    conn.row_factory = sqlite3.Row  # Dict-like access
    conn.execute("PRAGMA journal_mode=WAL")  # Better concurrency
    conn.execute("PRAGMA foreign_keys=ON")
    return conn

Context Manager Pattern

from contextlib import contextmanager

@contextmanager
def db_transaction(conn: sqlite3.Connection):
    try:
        yield conn
        conn.commit()
    except Exception:
        conn.rollback()
        raise

WAL Mode

Enable for concurrent read/write:

conn.execute("PRAGMA journal_mode=WAL")

| Mode | Reads | Writes | Best For |

|------|-------|--------|----------|

| DELETE (default) | Blocked during write | Single | Simple scripts |

| WAL | Concurrent | Single | Web apps, MCP servers |

Common Gotchas

| Issue | Solution |

|-------|----------|

| "database is locked" | Use WAL mode |

| Slow queries | Add indexes, check EXPLAIN QUERY PLAN |

| Thread safety | Use check_same_thread=False |

| FK not enforced | Run PRAGMA foreign_keys=ON |

CLI Quick Reference

sqlite3 mydb.sqlite    # Open database
.tables                # Show tables
.schema items          # Show schema
.headers on && .mode csv && .output data.csv  # Export CSV
VACUUM;                # Reclaim space

When to Use

• Local state/config storage
• Caching layer
• Event logging
• MCP server persistence
• Small to medium datasets

Additional Resources

For detailed patterns, load:

• ./references/schema-patterns.md - State, cache, event, queue table designs
• ./references/async-patterns.md - aiosqlite CRUD, batching, connection pools
• ./references/migration-patterns.md - Version migrations, JSON handling