home / skills / 0xdarkmatter / claude-mods / sqlite-ops

sqlite-ops skill

safe

This skill streamlines SQLite usage in Python projects by guiding connection handling, WAL mode, and simple migration patterns for reliable local data.

npx playbooks add skill 0xdarkmatter/claude-mods --skill sqlite-ops

Review the files below or copy the command above to add this skill to your agents.

Files (6)

SKILL.md

2.2 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."
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

Overview

This skill provides practical patterns for using SQLite in Python projects, focusing on state management, caching, and async operations. It collects connection patterns, transaction helpers, WAL recommendations, and common gotchas to make local database use reliable and concurrent-safe. The guidance targets small-to-medium datasets, local state, and caching layers.

How this skill works

It inspects project usage of sqlite, sqlite3, and aiosqlite and suggests idiomatic code patterns for connections, transactions, and async CRUD. The skill recommends PRAGMA settings (WAL, foreign_keys), a context-manager transaction pattern, and operational CLI commands for schema inspection and export. It also points to table design patterns and migration approaches for state, cache, and event tables.

When to use it

Local state or configuration storage for desktop or server apps
A lightweight caching layer to reduce upstream calls
Event logging or queue persistence for small-to-medium workloads
MCP or single-node server persistence with moderate concurrency needs
Development, testing, and migration scaffolding for schema evolution

Best practices

Open connections with check_same_thread=False and set row_factory for dict-like rows
Enable PRAGMA journal_mode=WAL for concurrent reads during writes
Wrap operations in a context-manager transaction that commits or rollbacks on error
Enable foreign key enforcement with PRAGMA foreign_keys=ON
Use indexes and EXPLAIN QUERY PLAN to diagnose slow queries; VACUUM to reclaim space
For async code, adopt aiosqlite patterns and batch operations to reduce contention

Example use cases

A desktop app storing user preferences and recent activity in a local SQLite file
A web service using SQLite as a cache layer for expensive API responses
An event-sourcing microservice that appends events to an ordered table and consumes them
A small game server persisting player state and leaderboards with WAL enabled
A migration tool that applies versioned schema changes and handles JSON fields

FAQ

How do I fix "database is locked" errors?

Enable WAL mode (PRAGMA journal_mode=WAL) to allow concurrent reads during writes, and ensure transactions are short. Use batching and avoid long-running write transactions.

Should I use SQLite in a multi-threaded app?

Yes for small-to-medium loads: open connections with check_same_thread=False, keep transactions short, and prefer WAL. For high write concurrency, consider a client-server database instead.