Skip to content

aiskillstore/marketplace / skills/0xdarkmatter/sqlite-ops

sqlite-ops

Verified

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.

FileKindSize
SKILL.mdskill md2.3 KB
assets/.gitkeepother0 B
references/async-patterns.mdmarkdown8.4 KB
references/migration-patterns.mdmarkdown8.8 KB
references/schema-patterns.mdmarkdown5.5 KB
scripts/.gitkeepother0 B
skill-report.jsonconfig54.0 KB
---
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."
license: MIT
compatibility: "Requires Python 3.8+ with sqlite3 (standard library) or aiosqlite for async."
allowed-tools: "Read Write Bash"
metadata:
  author: claude-mods
---

# 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
deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall