Sqlite Ops

---
name: sqlite-ops
description: "Patterns for SQLite databases in Python projects - state management, caching, and async operations. Triggers on: sqlite, sqlite3, aiosqlite, local database, database schema, migration, wal mode."
compatibility: "Requires Python 3.8+ with sqlite3 (standard library) or aiosqlite for async."
allowed-tools: "Read Write Bash"
---

# SQLite Operations

Patterns for SQLite databases in Python projects.

## Quick Connection

```python
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

```python
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:

```python
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

```bash
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