PRAGMA Statements

Code

-- Foreign keys (off by default)
PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;            -- show current value

-- WAL mode for better concurrency
PRAGMA journal_mode = WAL;
PRAGMA wal_autocheckpoint = 1000;

-- Performance tuning
PRAGMA synchronous = NORMAL;    -- FULL | NORMAL | OFF
PRAGMA cache_size = -20000;     -- 20MB negative = kibibytes
PRAGMA temp_store = MEMORY;
PRAGMA mmap_size = 268435456;   -- 256MB memory-mapped I/O

-- Introspection
PRAGMA table_info(users);
PRAGMA index_list(posts);
PRAGMA database_list;
PRAGMA integrity_check;

Explanation

PRAGMA statements configure SQLite at runtime, with foreign_keys and journal_mode being the most impactful. WAL mode allows concurrent readers and a single writer, dramatically improving throughput. synchronous=NORMAL is safe in WAL mode and far faster than the default FULL, trading durability for speed.

More SQLite Snippets