SQLite Cheat Sheet
A quick reference for SQLite's CLI, SQL syntax, type affinity, pragmas, and backup commands for embedded, file-based databases.
1 PageBeginnerMar 2, 2026
CLI Basics
Working with the sqlite3 shell.
bash
sqlite3 mydb.db.tables # list tables.schema users # show CREATE TABLE for users.headers on # show column headers.mode column # aligned column output.quit
SQL Basics
Creating and querying tables.
sql
CREATE TABLE users ( id INTEGER PRIMARY KEY AUTOINCREMENT, email TEXT NOT NULL UNIQUE, created_at TEXT DEFAULT CURRENT_TIMESTAMP);INSERT INTO users (email) VALUES ('[email protected]');SELECT * FROM users WHERE email = '[email protected]';PRAGMA table_info(users);
Type Affinity & Pragmas
Column types and common pragmas.
- INTEGER- whole number; INTEGER PRIMARY KEY aliases the internal rowid
- TEXT- UTF-8, UTF-16BE, or UTF-16LE string
- REAL- 8-byte floating point number
- BLOB- raw binary data, stored exactly as input
- PRAGMA foreign_keys = ON- enables FK constraint enforcement (off by default)
- PRAGMA journal_mode = WAL- enables write-ahead logging for better concurrency
Backup & Attach
Copying and combining databases.
sql
.backup main backup.dbATTACH DATABASE 'other.db' AS other;SELECT * FROM other.users;VACUUM; -- rebuilds the file, reclaiming free space
Window Functions
Ranking and running totals introduced in SQLite 3.25+.
sql
SELECT name, region, revenue, ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) AS rn, SUM(revenue) OVER (ORDER BY revenue DESC ROWS UNBOUNDED PRECEDING) AS running_totalFROM sales;-- Top 2 rows per regionSELECT * FROM ( SELECT s.*, RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS r FROM sales s) WHERE r <= 2;
Recursive CTEs
Generating series or traversing trees with WITH RECURSIVE.
sql
WITH RECURSIVE counter(n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM counter WHERE n < 10)SELECT n FROM counter;WITH RECURSIVE tree AS ( SELECT id, name, parent_id, 0 AS depth FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.name, c.parent_id, tree.depth + 1 FROM categories c JOIN tree ON c.parent_id = tree.id)SELECT * FROM tree ORDER BY depth;
Full-Text Search (FTS5)
Building and querying a full-text index as a virtual table.
sql
CREATE VIRTUAL TABLE docs_fts USING fts5(title, body, content='docs', content_rowid='id');INSERT INTO docs_fts(rowid, title, body) SELECT id, title, body FROM docs;SELECT title, snippet(docs_fts, 1, '<b>', '</b>', '...', 8) AS excerptFROM docs_ftsWHERE docs_fts MATCH 'sqlite AND (index OR performance)'ORDER BY rank;
JSON1 Functions & UPSERT
Querying JSON columns and atomic insert-or-update with ON CONFLICT.
sql
SELECT json_extract(payload, '$.user.email') AS emailFROM eventsWHERE json_extract(payload, '$.type') = 'signup';INSERT INTO counters (key, value) VALUES ('visits', 1)ON CONFLICT(key) DO UPDATE SET value = value + 1;
Triggers
Enforcing invariants and maintaining derived data automatically.
sql
CREATE TRIGGER trg_users_updated_atAFTER UPDATE ON usersFOR EACH ROWBEGIN UPDATE users SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;END;CREATE TRIGGER trg_prevent_negative_balanceBEFORE UPDATE OF balance ON accountsWHEN NEW.balance < 0BEGIN SELECT RAISE(ABORT, 'balance cannot go negative');END;
Advanced PRAGMAs
Operational pragmas beyond the basics for tuning and integrity checks.
- PRAGMA optimize- run before closing a long-lived connection; updates query planner statistics cheaply
- PRAGMA integrity_check- scans the whole database file for corruption; returns 'ok' or a list of errors
- PRAGMA foreign_key_check- reports rows that violate foreign key constraints (useful since FKs aren't retroactively checked)
- PRAGMA busy_timeout = 5000- makes a connection retry for 5s instead of immediately returning SQLITE_BUSY on lock contention
- PRAGMA synchronous = NORMAL- trades a small durability window for a large write speed gain; safe with WAL mode
- PRAGMA wal_checkpoint(TRUNCATE)- manually flushes the WAL file back into the main database and truncates it
- PRAGMA case_sensitive_like = ON- makes LIKE respect case instead of the default ASCII case-insensitive behavior
Pro Tip
SQLite uses dynamic type affinity rather than strict typing — any column can normally store any value type unless the table is declared STRICT (available in SQLite 3.37+).
Was this cheat sheet helpful?
Explore Topics
#SQLite#SQLiteCheatSheet#Database#Beginner#CLIBasics#SQLBasics#TypeAffinityPragmas#BackupAttach#Databases#CommandLine#CheatSheet#SkillVeris
