0xDarkMatter/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