SQLite: JSON, Full-Text Search, WAL & Tooling

JSON Support

-- Store and query JSON (SQLite 3.38+ has json_* functions built-in)
CREATE TABLE products (
  id   INTEGER PRIMARY KEY,
  data TEXT  -- JSON stored as TEXT
);

INSERT INTO products (data) VALUES ('{"name": "Widget", "price": 9.99, "tags": ["sale", "new"]}');

-- Extract values
SELECT json_extract(data, '$.name') AS name,
       json_extract(data, '$.price') AS price
FROM products;

-- Filter by JSON field
SELECT * FROM products
WHERE json_extract(data, '$.price') < 20;

-- JSON array functions
SELECT json_each.value FROM products, json_each(data, '$.tags')
WHERE products.id = 1;

-- Update JSON field
UPDATE products
SET data = json_set(data, '$.price', 7.99)
WHERE id = 1;

Full-Text Search (FTS5)

-- FTS5 virtual table for full-text search
CREATE VIRTUAL TABLE docs_fts USING fts5(
  title, body,
  content='docs',        -- external content table
  content_rowid='id'
);

-- Populate FTS index
INSERT INTO docs_fts(rowid, title, body)
  SELECT id, title, body FROM docs;

-- Search
SELECT rowid, title, rank
FROM docs_fts
WHERE docs_fts MATCH 'sqlite AND performance'
ORDER BY rank;

-- Highlight matches
SELECT highlight(docs_fts, 1, '<b>', '</b>') AS body_highlighted
FROM docs_fts
WHERE docs_fts MATCH 'sqlite';

-- Snippet
SELECT snippet(docs_fts, 1, '<b>', '</b>', '...', 10) AS excerpt
FROM docs_fts WHERE docs_fts MATCH 'performance';

WAL Mode & Concurrency

WAL (Write-Ahead Logging) is the recommended journal mode for most applications. It allows concurrent reads during a write — a major improvement over the default DELETE journal mode.

• WAL: readers never block writers; writers never block readers — only one writer at a time

• Default (DELETE): writer blocks all readers and writers

• WAL checkpoint: WAL file is periodically merged back into the main DB file (auto or manual)

• WAL creates two extra files: db.db-wal and db.db-shm — keep them together with the .db file

• SQLite on NFS/network shares: avoid WAL — use DELETE journal mode

• Connection timeout: set busy_timeout pragma to retry instead of immediately erroring

PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;   -- wait up to 5s if DB is locked (instead of SQLITE_BUSY error)

-- Manual WAL checkpoint
PRAGMA wal_checkpoint(TRUNCATE);

Using SQLite in Node.js

// better-sqlite3 (synchronous, fastest)
import Database from 'better-sqlite3'

const db = new Database('app.db')
db.pragma('journal_mode = WAL')
db.pragma('foreign_keys = ON')

// Prepared statements (auto-cached)
const getUser = db.prepare('SELECT * FROM users WHERE id = ?')
const user = getUser.get(1)

// Transactions (synchronous)
const insertMany = db.transaction((users) => {
  for (const user of users) {
    db.prepare('INSERT INTO users (email) VALUES (?)').run(user.email)
  }
})
insertMany(usersArray)

// Drizzle ORM with SQLite
import { drizzle } from 'drizzle-orm/better-sqlite3'
const drizzleDb = drizzle(db)

// Turso (SQLite at the edge — libSQL protocol)
import { createClient } from '@libsql/client'
const turso = createClient({ url: process.env.TURSO_URL!, authToken: process.env.TURSO_TOKEN })
await turso.execute('SELECT * FROM users WHERE id = ?', [1])

Tooling

• DB Browser for SQLite (sqlitebrowser): free GUI for browsing and editing .db files

• sqlite3 CLI: built into macOS/Linux — sqlite3 app.db ".tables"

• Drizzle ORM: first-class SQLite support with better-sqlite3 or libSQL

• Prisma: SQLite adapter — good for local dev, limited for production

• Turso: SQLite-compatible cloud DB with global replication (libSQL fork)

• Cloudflare D1: serverless SQLite — 5GB free, runs at edge workers

• LiteFS: SQLite replication across multiple nodes via FUSE filesystem