Sqlite

SQLite Embedded Database AI Skill Guide

Overview

SQLite is an embedded SQL engine in a single file (plus optional WAL sidecars). It shines for local apps, tests, edge devices, and low-to-moderate concurrency services. Agents should enable WAL for readers-alongside-a-writer, set busy timeouts, and treat the database file as the backup unit - copying while a writer is active without backup APIs risks corruption.

Application (sqlite3 / better-sqlite3 / sqlx)
        |
        v
database.db  (+ database.db-wal + database.db-shm in WAL mode)

When to use

• Local development databases and unit/integration tests
• Single-node apps where ops simplicity beats client/server DB
• Shipping desktop/mobile/edge data stores
• Prototyping schema before graduating to MySQL/Postgres

Operational directives

1. Prefer WAL mode for multi-reader workloads: PRAGMA journal_mode=WAL;.
2. Set PRAGMA busy_timeout=5000; (or app equivalent) to reduce flaky locks.
3. Always back up with sqlite3 .backup or the backup API - not naive cp under write load.
4. Use migrations with a schema version table; avoid ad-hoc ALTER chaos.
5. Do not assume multi-writer scalability - one writer at a time is the model.

Concrete examples

Create schema

PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;

CREATE TABLE notes (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_notes_updated ON notes(updated_at);

CLI migration + backup

sqlite3 app.db < migrations/001_init.sql
sqlite3 app.db "PRAGMA integrity_check;"
sqlite3 app.db ".backup 'app-backup.db'"
sqlite3 app.db ".schema"

Concurrent read pattern (conceptual)

Writer: BEGIN IMMEDIATE; ... COMMIT;
Readers: can proceed in WAL while writer holds write lock (with caveats)

Explain query plan

EXPLAIN QUERY PLAN
SELECT * FROM notes WHERE updated_at > '2026-01-01' ORDER BY updated_at DESC;

Decision table

Best practices

1. Keep foreign keys on; many drivers disable them by default.
2. Store timestamps in UTC ISO-8601 text or integer Unix time consistently.
3. Vacuum only with understanding of locks and file rewrite cost.
4. Document file path permissions - SQLite needs write access for journal/WAL.

Limitations

• Limited concurrent writes; easy to hit SQLITE_BUSY under load.
• Network access requires your app - there is no native multi-host server mode.
• Some SQL dialect differences vs MySQL/Postgres (types are flexible/affinity-based).

Related skills

• mysql - when graduating beyond embedded
• docker - optional; often unnecessary for SQLite apps
• makefile-automation - make db-migrate / make db-backup targets
• trivy - not DB-specific, but scan apps bundling SQLite libs