SQLite: Core SQL, Data Types & Pragmas

SQLite is a serverless, file-based relational database embedded directly in the application. No separate server process — the entire database is a single .db file. Used in mobile apps, browsers, Electron apps, edge computing, and embedded systems.

When to Use SQLite

• Embedded / local-first apps: mobile (iOS, Android), desktop (Electron), IoT devices

• Development and testing: replace PostgreSQL locally — faster setup, no Docker needed

• Read-heavy apps with low concurrency: SQLite handles thousands of reads/second, but concurrent writes are serialized

• Edge computing: Cloudflare D1, Turso, LiteFS — SQLite at the edge

• NOT for: high-concurrency write workloads, multi-server setups, or datasets >1TB

Data Types (Type Affinity)

SQLite uses dynamic typing with 5 storage classes. Column types are suggestions, not enforced — any column can store any type.

-- SQLite storage classes:
-- NULL, INTEGER, REAL, TEXT, BLOB

-- Type affinity (column type → storage class):
-- INTEGER affinity:  INT, INTEGER, TINYINT, BIGINT, BOOLEAN
-- REAL affinity:     REAL, FLOAT, DOUBLE, DECIMAL
-- TEXT affinity:     TEXT, VARCHAR, CHAR, CLOB, DATE, DATETIME
-- BLOB affinity:     BLOB, none
-- NUMERIC affinity:  NUMERIC, DECIMAL (stores as INT if possible, else REAL, else TEXT)

-- Dates stored as TEXT (ISO 8601), REAL (Julian day), or INTEGER (Unix time)
CREATE TABLE events (
  id      INTEGER PRIMARY KEY AUTOINCREMENT,
  name    TEXT NOT NULL,
  date    TEXT NOT NULL DEFAULT (date('now')),   -- '2026-05-07'
  ts      INTEGER DEFAULT (unixepoch()),          -- Unix timestamp
  price   REAL,
  data    BLOB
);

Essential SQL

-- Create and insert
CREATE TABLE users (
  id       INTEGER PRIMARY KEY,
  email    TEXT UNIQUE NOT NULL,
  name     TEXT,
  created  TEXT DEFAULT (datetime('now'))
);

INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice');
INSERT OR REPLACE INTO users (id, email, name) VALUES (1, 'alice@example.com', 'Alice Updated');
INSERT OR IGNORE INTO users (email, name) VALUES ('alice@example.com', 'Duplicate');

-- Query
SELECT * FROM users WHERE email LIKE '%@example.com' ORDER BY created DESC LIMIT 10;

-- Update and delete
UPDATE users SET name = 'Bob' WHERE id = 2;
DELETE FROM users WHERE created < date('now', '-1 year');

-- Upsert (SQLite 3.24+)
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT(email) DO UPDATE SET name = excluded.name;

Pragmas

Pragmas are SQLite-specific configuration commands. Run them after opening the connection.

-- Performance (run on every connection open)
PRAGMA journal_mode = WAL;       -- Write-Ahead Logging: better concurrency, faster writes
PRAGMA synchronous = NORMAL;     -- Safe + fast (vs FULL which is slower, OFF which is unsafe)
PRAGMA cache_size = -64000;      -- 64MB page cache (negative = KB)
PRAGMA temp_store = MEMORY;      -- Temp tables in memory
PRAGMA mmap_size = 268435456;    -- 256MB memory-mapped I/O

-- Foreign keys (disabled by default!)
PRAGMA foreign_keys = ON;

-- Info
PRAGMA table_info(users);         -- columns: cid, name, type, notnull, dflt_value, pk
PRAGMA index_list(users);         -- indexes on a table
PRAGMA integrity_check;           -- check for corruption
PRAGMA page_count;                -- database size in pages
PRAGMA page_size;                 -- bytes per page (default 4096)

Indexes & Performance

-- Create indexes for frequent query columns
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_events_date ON events(date DESC);
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Partial index (only index subset of rows)
CREATE INDEX idx_active_users ON users(email) WHERE active = 1;

-- Covering index (include extra columns to avoid table lookup)
CREATE INDEX idx_users_name_email ON users(name, email);

-- Explain query plan
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'alice@example.com';
-- Should show: SEARCH users USING INDEX (not SCAN)

-- VACUUM: reclaim space after deletes, defragment
VACUUM;
-- Auto-vacuum
PRAGMA auto_vacuum = INCREMENTAL;

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