SQLite cheatsheet

Command	Description
`sqlite3 app.db`	Open database interactively
`sqlite3 app.db "SELECT 1"`	Run single query
`.tables`	List tables
`.schema table_name`	Show CREATE statement
`.databases`	List attached databases
`.quit`	Exit

PRAGMA	Description
`PRAGMA journal_mode;`	Current journal mode (want WAL)
`PRAGMA integrity_check;`	Verify database integrity
`PRAGMA foreign_keys;`	Check if FK enforcement is on
`PRAGMA page_count; page_size;`	Size estimation
`PRAGMA wal_checkpoint(TRUNCATE);`	Checkpoint WAL into main db
`PRAGMA quick_check;`	Faster integrity scan

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -64000;

Command	Description
`sqlite3 app.db ".backup backup.db"`	Online consistent backup
`sqlite3 app.db "VACUUM INTO 'backup.db';"`	Copy to new file (vacuumed)
`litestream replicate /path/app.db`	Stream changes to S3 (Litestream)
`litestream restore -o app.db s3://bucket/db`	Restore from Litestream

Command	Description
`ls -la app.db app.db-wal app.db-shm`	Database and WAL sidecars
`file app.db`	Confirm SQLite format
`lsof app.db`	Which processes hold the file open
`fuser -v app.db`	Processes using the database file

SELECT name FROM sqlite_master WHERE type='table';
SELECT COUNT(*) FROM table_name;
VACUUM;  -- rebuild db, reclaim space (exclusive lock)

Practice scenarios

Hands-on SQLite scenarios on live Linux VMs: sqlite