sqlite-ops
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.
When & Why to Use This Skill
This Claude skill provides comprehensive patterns and best practices for implementing SQLite databases in Python projects. It covers essential operations including state management, caching, asynchronous database access with aiosqlite, and schema migrations. By implementing optimized configurations like WAL mode and context managers, it helps developers build robust, high-performance local storage solutions for applications and MCP servers.
Use Cases
- Case 1: Implementing local state and configuration storage for desktop or CLI applications to ensure data persistence.
- Case 2: Building a high-performance caching layer to reduce API latency and minimize external database load.
- Case 3: Developing persistent storage for MCP (Model Context Protocol) servers to maintain context and state across sessions.
- Case 4: Managing database schema migrations and versioning for evolving Python projects using structured migration patterns.
- Case 5: Optimizing concurrent database access in web applications by enabling Write-Ahead Logging (WAL) mode for better read/write performance.
| 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
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