sqlite

SQLite patterns with better-sqlite3, libSQL, Turso local dev, WAL mode, migrations, embedded database use cases, and Drizzle ORM integration

SKILL.md

GitHub

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)

import Database from 'better-sqlite3';

function createDatabase(path: string): Database.Database {
  const db = new Database(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('synchronous = NORMAL');

  // Store temp tables in memory
  db.pragma('temp_store = MEMORY');

  // 64MB mmap for faster reads on large databases
  db.pragma('mmap_size = 67108864');

  // 20MB page cache (default is 2MB)
  db.pragma('cache_size = -20000');

  // Enable foreign keys (OFF by default!)
  db.pragma('foreign_keys = ON');

  // Busy timeout: wait 5s for locks instead of failing immediately
  db.pragma('busy_timeout = 5000');

  return db;
}

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)

import Database from 'better-sqlite3';

const db = createDatabase('./app.db');

// Prepared statements -- ALWAYS use these for repeated queries
const getUser = db.prepare('SELECT * FROM users WHERE id = ?');
const insertUser = db.prepare(
  'INSERT INTO users (id, name, email, created_at) VALUES (?, ?, ?, ?)'
);

// Single row
const user = getUser.get('user_123');

// Multiple rows
const 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 operations
const 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 needed
const localDb = createClient({
  url: 'file:./local.db',
});

// Production -- remote Turso database
const 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
});

// Queries
const 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 replicas
await replicaDb.sync();

3. Drizzle ORM + SQLite

// schema.ts
import { sqliteTable, text, integer, real, index } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  role: text('role', { enum: ['admin', 'user', 'moderator'] }).default('user'),
  createdAt: integer('created_at', { mode: 'timestamp' }).$defaultFn(() => new Date()),
}, (table) => [
  index('users_email_idx').on(table.email),
]);

export const posts = sqliteTable('posts', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  title: text('title').notNull(),
  content: text('content'),
  authorId: text('author_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  publishedAt: integer('published_at', { mode: 'timestamp' }),
  views: integer('views').default(0),
});

export const tags = sqliteTable('tags', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  name: text('name').notNull().unique(),
});

export const postTags = sqliteTable('post_tags', {
  postId: integer('post_id').notNull().references(() => posts.id, { onDelete: 'cascade' }),
  tagId: integer('tag_id').notNull().references(() => tags.id, { onDelete: 'cascade' }),
}, (table) => [
  index('post_tags_post_idx').on(table.postId),
]);

// db.ts -- better-sqlite3 driver
import { drizzle } from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';
import * as schema from './schema';

const sqlite = new Database('./app.db');
sqlite.pragma('journal_mode = WAL');
sqlite.pragma('synchronous = NORMAL');
sqlite.pragma('foreign_keys = ON');
sqlite.pragma('busy_timeout = 5000');

export const db = drizzle(sqlite, { schema });

// db.ts -- libSQL driver (for Turso)
import { drizzle } from 'drizzle-orm/libsql';
import { createClient } from '@libsql/client';
import * as schema from './schema';

const client = createClient({
  url: process.env.DATABASE_URL!,
  authToken: process.env.DATABASE_AUTH_TOKEN,
});

export const db = drizzle(client, { schema });

// queries.ts
import { eq, like, desc, sql, and, gt } from 'drizzle-orm';
import { db } from './db';
import { users, posts, tags, postTags } from './schema';

// Basic queries
const user = await db.select().from(users).where(eq(users.id, 'u1')).get();
const recentPosts = await db
  .select()
  .from(posts)
  .where(gt(posts.publishedAt, new Date('2025-01-01')))
  .orderBy(desc(posts.publishedAt))
  .limit(10)
  .all();

// Join
const postsWithAuthors = await db
  .select({
    postTitle: posts.title,
    authorName: users.name,
    views: posts.views,
  })
  .from(posts)
  .innerJoin(users, eq(posts.authorId, users.id))
  .orderBy(desc(posts.views))
  .all();

// Insert
await db.insert(users).values({
  id: 'u2',
  name: 'Bob',
  email: 'bob@example.com',
});

// Upsert (INSERT OR REPLACE)
await db.insert(users)
  .values({ id: 'u2', name: 'Bob Updated', email: 'bob@example.com' })
  .onConflictDoUpdate({
    target: users.id,
    set: { name: 'Bob Updated' },
  });

// Transaction
await db.transaction(async (tx) => {
  const [post] = await tx.insert(posts)
    .values({ title: 'New Post', authorId: 'u1', content: '...' })
    .returning();

  await tx.insert(postTags).values([
    { postId: post.id, tagId: 1 },
    { postId: post.id, tagId: 3 },
  ]);
});

4. Migrations

// drizzle.config.ts
import type { Config } from 'drizzle-kit';

export default {
  schema: './src/db/schema.ts',
  out: './drizzle',
  dialect: 'sqlite',
  dbCredentials: {
    url: './app.db',
  },
} satisfies Config;

# Generate migration from schema changes
npx drizzle-kit generate

# Apply migrations
npx drizzle-kit migrate

# Open Drizzle Studio (GUI)
npx drizzle-kit studio

// Programmatic migration (for production)
import { migrate } from 'drizzle-orm/better-sqlite3/migrator';
import { db } from './db';

migrate(db, { migrationsFolder: './drizzle' });

5. Full-Text Search with FTS5

-- Create FTS virtual table
CREATE VIRTUAL TABLE posts_fts USING fts5(
  title,
  content,
  content='posts',
  content_rowid='id',
  tokenize='porter unicode61'
);

-- Triggers to keep FTS in sync
CREATE TRIGGER posts_ai AFTER INSERT ON posts BEGIN
  INSERT INTO posts_fts(rowid, title, content)
  VALUES (new.id, new.title, new.content);
END;

CREATE TRIGGER posts_ad AFTER DELETE ON posts BEGIN
  INSERT INTO posts_fts(posts_fts, rowid, title, content)
  VALUES ('delete', old.id, old.title, old.content);
END;

CREATE TRIGGER posts_au AFTER UPDATE ON posts BEGIN
  INSERT 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 ranking
const 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 extraction
const 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 functions
const db = new Database('./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 fields
const 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 aggregation
const 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

Cloudflare D1

// wrangler.toml
// [[d1_databases]]
// binding = "DB"
// database_name = "myapp"
// database_id = "xxx"

export default {
  async fetch(request: Request, env: Env) {
    const { results } = await env.DB.prepare(
      'SELECT * FROM users WHERE id = ?'
    ).bind(userId).all();
    return Response.json(results);
  },
};

Fly.io LiteFS (Replicated SQLite)

Architecture:
  Primary node: reads + writes
  Replica nodes: reads only (replicated via FUSE filesystem)
  Automatic failover with consul lease

Benefits:
  - True SQLite file, all tools work
  - FUSE-based replication (transparent to app)
  - Sub-millisecond reads

Best Practices

  1. Always enable WAL mode -- the single biggest performance win for concurrent access
  2. Use prepared statements -- 2-5x faster than raw SQL strings; prevents SQL injection
  3. Wrap bulk inserts in transactions -- inserting 1000 rows: 50ms with transaction, 5000ms without
  4. Set busy_timeout -- prevents SQLITE_BUSY errors under concurrent writes
  5. Enable foreign_keys pragma -- it is OFF by default, which silently ignores FK constraints
  6. Use INTEGER PRIMARY KEY for rowid alias -- avoids extra index, fastest possible lookups
  7. Avoid AUTOINCREMENT unless required -- plain INTEGER PRIMARY KEY auto-generates IDs faster
  8. Checkpoint WAL before backup -- PRAGMA wal_checkpoint(TRUNCATE) ensures clean backup
  9. Use .wal and .shm awareness -- three files (.db, .db-wal, .db-shm) must travel together
  10. Profile with EXPLAIN QUERY PLAN -- check that indexes are being used

Common Pitfalls

Pitfall Impact Fix
Not enabling WAL mode All reads blocked during writes PRAGMA journal_mode = WAL at connection open
Missing foreign_keys = ON FK constraints silently ignored Set pragma on every new connection
Bulk inserts without transaction 100x slower performance Wrap in db.transaction()
Using LIKE for search Full table scan Use FTS5 for text search, indexes for prefix match
Storing dates as strings Cannot sort/compare correctly Use INTEGER (unix timestamp) or TEXT (ISO 8601) consistently
Not handling SQLITE_BUSY Crashes under concurrent writes Set busy_timeout pragma (5000ms+)
Deploying .db without .wal/.shm Data loss or corruption Always copy all three files together
Using SQLite across NFS/network drives Silent corruption risk Only use local filesystems
No connection pooling awareness better-sqlite3 is synchronous, blocks event loop Use worker threads for heavy queries
Forgetting vacuum Database file grows indefinitely Schedule periodic VACUUM or use auto_vacuum = INCREMENTAL

Похожие навыки: development

update-docs-vercel-next-js

This skill should be used when the user asks to "update documentation for my changes", "check docs for this PR", "what d...

Автор: vercel
137,316

docstring-pytorch-pytorch

Write docstrings for PyTorch functions and methods following PyTorch conventions. Use when writing or updating docstring...

Автор: pytorch
96,875

at-dispatch-v2

Convert PyTorch AT_DISPATCH macros to AT_DISPATCH_V2 format in ATen C++ code. Use when porting AT_DISPATCH_ALL_TYPES_AND...

dispatch
Автор: pytorch
95,362