Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
A direct command skips the review prompt. Inspect the source before running it.
["May add better-sqlite3 or @libsql/client dependencies","May create .sqlite or .db files in project","May modify build configuration for native module compilation"]
SQLite Domain Skill
Purpose
SQLite is the most deployed database engine in the world. This skill covers using SQLite as a serious application database -- not just a toy. Modern SQLite with WAL mode, proper pragmas, and tools like Turso/libSQL makes it viable for production web apps, CLI tools, edge deployments, and embedded systems.
When to Use SQLite
Use Case
SQLite?
Why
CLI tools, desktop apps
Yes
Zero config, single file, no server
Edge/serverless (Cloudflare D1, Turso)
Yes
Low latency, embedded replicas
Read-heavy web apps (< 1000 req/s writes)
Yes
WAL mode handles concurrent reads well
Single-server deployments
Yes
Simpler than PostgreSQL if writes are moderate
Write-heavy, multi-server
No
Single-writer limitation; use PostgreSQL
Complex transactions across services
No
No network protocol; use PostgreSQL
Full-text search (simple)
Yes
Built-in FTS5 is excellent
Prototyping before PostgreSQL
Yes
Drizzle makes migration between DBs straightforward
Key Concepts
Critical Pragmas (Always Set These)
importDatabasefrom'better-sqlite3';
functioncreateDatabase(path: string): Database.Database {
const db = newDatabase(path);
// WAL mode: concurrent reads + writes, massive performance boost
db.pragma('journal_mode = WAL');
// Normal sync: safe with WAL, 10x faster than FULL
db.pragma();
db.();
db.();
db.();
db.();
db.();
db;
}
'synchronous = NORMAL'
// Store temp tables in memory
pragma
'temp_store = MEMORY'
// 64MB mmap for faster reads on large databases
pragma
'mmap_size = 67108864'
// 20MB page cache (default is 2MB)
pragma
'cache_size = -20000'
// Enable foreign keys (OFF by default!)
pragma
'foreign_keys = ON'
// Busy timeout: wait 5s for locks instead of failing immediately
pragma
'busy_timeout = 5000'
return
WAL Mode Explained
Default (DELETE journal):
Write -> Lock entire DB -> Write -> Unlock
Reads block during writes. Writes block during reads.
WAL (Write-Ahead Log):
Write -> Append to WAL file -> Readers see snapshot
Multiple concurrent readers. One writer. No blocking between readers and writer.
Checkpointing:
WAL file periodically merged back into main DB.
Auto-checkpoint at 1000 pages (default). Manual: PRAGMA wal_checkpoint(TRUNCATE);
Patterns
1. better-sqlite3 (Synchronous, Node.js)
importDatabasefrom'better-sqlite3';
const db = createDatabase('./app.db');
// Prepared statements -- ALWAYS use these for repeated queriesconst getUser = db.prepare('SELECT * FROM users WHERE id = ?');
const insertUser = db.prepare(
'INSERT INTO users (id, name, email, created_at) VALUES (?, ?, ?, ?)'
);
// Single rowconst user = getUser.get('user_123');
// Multiple rowsconst allUsers = db.prepare('SELECT * FROM users WHERE active = ?').all(1);
// Insert
insertUser.run('user_456', 'Alice', 'alice@example.com', Date.now());
// Transactions -- 50-100x faster for bulk operationsconst insertMany = db.transaction((users: Array<{ id: string; name: string; email: string }>) => {
for (const u of users) {
insertUser.run(u.id, u.name, u.email, Date.now());
}
});
insertMany([
{ id: '1', name: 'Alice', email: 'a@test.com' },
{ id: '2', name: 'Bob', email: 'b@test.com' },
// ... thousands of rows, still fast
]);
// Cleanup on shutdown
process.on('SIGTERM', () => {
db.pragma('wal_checkpoint(TRUNCATE)');
db.close();
});
2. libSQL / Turso (Remote + Embedded Replicas)
import { createClient } from'@libsql/client';
// Local development -- file-based, no server neededconst localDb = createClient({
url: 'file:./local.db',
});
// Production -- remote Turso databaseconst remoteDb = createClient({
url: process.env.TURSO_DATABASE_URL!,
authToken: process.env.TURSO_AUTH_TOKEN!,
});
// Embedded replica -- local read, remote write (best of both worlds)const replicaDb = createClient({
url: 'file:./replica.db',
syncUrl: process.env.TURSO_DATABASE_URL!,
authToken: process.env.TURSO_AUTH_TOKEN!,
syncInterval: 60, // Sync every 60 seconds
});
// Queriesconst result = await replicaDb.execute({
sql: 'SELECT * FROM products WHERE category = ? AND price < ?',
args: ['electronics', 500],
});
// Batch operations (single round-trip)const batchResult = await remoteDb.batch([
{ sql: 'INSERT INTO orders (user_id, total) VALUES (?, ?)', args: ['u1', 99.99] },
{ sql: 'UPDATE inventory SET stock = stock - 1 WHERE product_id = ?', args: ['p1'] },
], 'write'); // 'write' = transactional batch// Manual sync for embedded replicasawait replicaDb.sync();
-- Create FTS virtual tableCREATE VIRTUAL TABLE posts_fts USING fts5(
title,
content,
content='posts',
content_rowid='id',
tokenize='porter unicode61'
);
-- Triggers to keep FTS in syncCREATETRIGGER posts_ai AFTER INSERTON posts BEGININSERT INTO posts_fts(rowid, title, content)
VALUES (new.id, new.title, new.content);
END;
CREATETRIGGER posts_ad AFTER DELETEON posts BEGININSERT INTO posts_fts(posts_fts, rowid, title, content)
VALUES ('delete', old.id, old.title, old.content);
END;
CREATETRIGGER posts_au AFTER UPDATEON posts BEGININSERT INTO posts_fts(posts_fts, rowid, title, content)
VALUES ('delete', old.id, old.title, old.content);
INSERT INTO posts_fts(rowid, title, content)
VALUES (new.id, new.title, new.content);
END;
// Search with rankingconst results = db.prepare(`
SELECT posts.*, rank
FROM posts_fts
JOIN posts ON posts.id = posts_fts.rowid
WHERE posts_fts MATCH ?
ORDER BY rank
LIMIT 20
`).all('typescript AND database');
// Snippet extractionconst snippets = db.prepare(`
SELECT
posts.id,
posts.title,
snippet(posts_fts, 1, '<mark>', '</mark>', '...', 32) as content_snippet,
rank
FROM posts_fts
JOIN posts ON posts.id = posts_fts.rowid
WHERE posts_fts MATCH ?
ORDER BY rank
LIMIT 10
`).all('sqlite performance');
6. JSON Support
// SQLite has built-in JSON functionsconst db = newDatabase('./app.db');
// Store JSON
db.prepare(`
CREATE TABLE IF NOT EXISTS events (
id INTEGER PRIMARY KEY,
type TEXT NOT NULL,
payload TEXT NOT NULL, -- JSON stored as TEXT
created_at INTEGER DEFAULT (unixepoch())
)
`).run();
// Query JSON fieldsconst clickEvents = db.prepare(`
SELECT id, json_extract(payload, '$.page') as page,
json_extract(payload, '$.x') as x,
json_extract(payload, '$.y') as y
FROM events
WHERE type = 'click'
AND json_extract(payload, '$.page') = ?
`).all('/dashboard');
// JSON aggregationconst stats = db.prepare(`
SELECT type,
COUNT(*) as count,
json_group_array(json_extract(payload, '$.page')) as pages
FROM events
GROUP BY type
`).all();
Deployment Patterns
Turso (Multi-Region, Edge)
Architecture:
Primary DB (one region) <- All writes
Embedded Replicas (each edge server) <- Local reads, sync periodically
Benefits:
- Reads are local (< 1ms)
- Writes go to primary (may have latency)
- Automatic sync keeps replicas fresh
- Works offline, syncs when connected
Setup:
turso db create myapp --group default
turso db tokens create myapp
turso db replicas create myapp --region nrt # Tokyo
turso db replicas create myapp --region fra # Frankfurt
