sqlite
π―SkillInstallation
npx skills add g1joshi/agent-skills --skill sqliteRun it in a terminal; it asks which AI coding tools to install it for (Claude Code, Codex, Cursor and more).
Uses skills.sh Β· Node.js 22.20+
After installing
Ask for related work and the agent uses it on its own, or call it yourself.
/sqliteInstall by hand
Copy the whole skill folder from GitHub to the place below. Copying only SKILL.md can leave out files the skill uses.
Open the skill folder β- This project
.claude/skills/sqlite/ - All projects
~/.claude/skills/sqlite/
These paths are for Claude Code. For other tools, the command above puts it in the right place.
SKILL.md
View on GitHub βExpert SQLite embedded database assistance covering WAL mode, PRAGMA tuning, full-text search (FTS5), and concurrency. Use when building desktop/mobile apps, local databases, or high-performance embedded systems.
SQLite
SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. It reads and writes directly to ordinary disk files.
When to Use
- Embedded Mobile & Desktop Client Applications: The standard embedded database engine for iOS, Android, macOS, Windows, and Linux apps.
- Edge Computing & Local Cache: Running fast relational databases inside Cloudflare D1, Turso, or local container storage.
- Zero-Configuration Local Development: Powering unit tests and local prototypes without spinning up external database servers.
- High-Read Single-Server Web Applications: Serving millions of read queries per day with SQLite Write-Ahead-Logging (WAL) mode enabled.
Quick Start
-- Enable WAL mode for concurrency
PRAGMA journal_mode=WAL;
-- Create table
CREATE TABLE contacts (
contact_id INTEGER PRIMARY KEY,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
phone TEXT NOT NULL UNIQUE
);
Core Concepts
Single-File Serverless Engine
SQLite runs directly in the host application's memory space, reading and writing to a single cross-platform disk file:
[ Application Process (Python / Go / Node / Swift) ] ββDirect In-Memory Accessβββ [ database.sqlite (Single File) ]
Write-Ahead Logging (WAL Mode)
Enables concurrent readers while a writer commits changes simultaneously:
-- Essential production pragmas for web apps
PRAGMA journal_mode = WAL; -- Concurrent reads while writing
PRAGMA synchronous = NORMAL; -- 2-3x write acceleration with durable safety
PRAGMA busy_timeout = 5000; -- Wait up to 5s on busy locks before erroring
PRAGMA foreign_keys = ON; -- Enforce relational foreign key constraints
PRAGMA cache_size = -64000; -- Allocate 64MB memory page cache
Full-Text Search with FTS5
Built-in full-text search engine with BM25 relevancy ranking:
CREATE VIRTUAL TABLE document_search USING fts5(title, body);
INSERT INTO document_search (title, body) VALUES
('Distributed Systems', 'Consensus protocols and Raft algorithms in modern software.');
-- Fast full-text search
SELECT title, bm25(document_search) AS rank
FROM document_search
WHERE document_search MATCH 'Raft OR Consensus'
ORDER BY rank;
Common Patterns
WAL Mode and Concurrency Performance PRAGMAs
Problem: Default rollback journal locks entire database during writes, causing database is locked errors during concurrent reads.
Solution: Enable Write-Ahead Logging (WAL) and memory optimizations at connection initialization:
-- Enable concurrent readers and writer
PRAGMA journal_mode = WAL;
-- Optimize disk sync frequency for higher write throughput
PRAGMA synchronous = NORMAL;
-- Cache pages in memory (e.g. 64MB)
PRAGMA cache_size = -64000;
-- Store temporary tables and indexes in memory
PRAGMA temp_store = MEMORY;
-- Enable foreign key constraint checking
PRAGMA foreign_keys = ON;
Best Practices
Do:
- Always Enable WAL Mode in Production: Run
PRAGMA journal_mode = WAL;to prevent writers from blocking readers. - Set a
busy_timeout: ConfigurePRAGMA busy_timeout = 5000;to avoidSQLITE_BUSYerrors during concurrent transactions. - Enable Foreign Keys Explicitly: SQLite disables foreign keys by default; execute
PRAGMA foreign_keys = ON;on every connection. - Use Prepared Statements: Eliminate SQL injection and optimize query plan reuse.
Don't:
- Run multiple concurrent write transactions: SQLite supports only one writer at a time; queue writes or serialize them.
- Use SQLite over Network Filesystems (NFS/SMB): Network file locking bugs can corrupt SQLite database files.
- Store multi-gigabyte binary files directly: Store media in filesystem directories or object storage; store file paths in SQLite.
Troubleshooting
| Error | Cause | Solution |
|---|---|---|
database is locked (SQLITE_BUSY) | Another connection holds a write lock or transaction open too long. | Enable WAL mode and set busy timeout: PRAGMA busy_timeout = 5000;. |
database disk image is malformed (SQLITE_CORRUPT) | Power loss during non-sync write, hardware fault, or concurrent file access. | Restore from backup, or dump readable rows using .recover command in sqlite3 CLI. |
FOREIGN KEY constraint failed | Inserted row references non-existent parent foreign key. | Verify parent row exists before inserting child row. |
References
More from this repository10
A template skill providing a starter framework for creating new agent skills with structured sections for triggers, quick start examples, and capability descriptions.
Skill
Skill
Skill
Skill
Skill
Skill
Skill
Skill
Skill