SQLite 3 cheatsheet
SQLite 3: Pragmas and maintenance
7 common SQLite 3 tasks.
Enforce foreign keys
Foreign key checks are off by default. Turn them on for each connection.
PRAGMA foreign_keys = ON;
Turn on write-ahead logging
Let readers and a writer work at the same time. The setting persists in the file.
PRAGMA journal_mode = WAL;
Wait when the database is locked
Retry for up to 5 seconds instead of failing with SQLITE_BUSY.
PRAGMA busy_timeout = 5000;
Check for corruption
Verify the database file's integrity. It prints ok when everything is fine.
PRAGMA integrity_check;
Update query planner statistics
Gather statistics so the planner can choose better indexes.
ANALYZE;
See a query plan
Show whether a query uses an index.
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = '[email protected]';