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.
90
Does it follow best practices?
Optimize this skill
If you maintain this skill, you can automatically optimize it using the tessl CLI to improve its score:
npx tessl skill review --optimize ./path/to/skillValidation for skill structure
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 connContext Manager Pattern
from contextlib import contextmanager
@contextmanager
def db_transaction(conn: sqlite3.Connection):
try:
yield conn
conn.commit()
except Exception:
conn.rollback()
raiseWAL 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 spaceWhen 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
- Repository
- NeverSight/skills_feed
- Commit
5c15b3d
- Last updated
- Created
Is this your skill?
If you maintain this skill, you can claim it as your own. Once claimed, you can manage eval scenarios, bundle related skills, attach documentation or rules, and ensure cross-agent compatibility.