sqlite-ops_skill

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