SQLite Cheat Sheet
Shell
// open a database and inspect it
sqlite3 app.db
sqlite3 app.db ".tables"
sqlite3 app.db ".schema books"
sqlite3 -header -column app.db "SELECT * FROM books LIMIT 5;"
sqlite3 app.db ".dump" > backup.sql
Tables
CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, created TEXT DEFAULT (date('now')));
CREATE TABLE s (x INTEGER, y TEXT) STRICT;
ALTER TABLE t ADD COLUMN email TEXT;
DROP TABLE IF EXISTS t;
Queries
SELECT * FROM t WHERE name LIKE 'a%' ORDER BY id DESC LIMIT 10 OFFSET 20;
SELECT a.x, b.y FROM a LEFT JOIN b ON b.a_id = a.id;
SELECT g, COUNT(*) FROM t GROUP BY g HAVING COUNT(*) > 1;
SELECT title || ' - ' || author FROM books;
Writes
INSERT INTO t (name) VALUES ('x') RETURNING id;
INSERT INTO t (id, n) VALUES (1, 1) ON CONFLICT(id) DO UPDATE SET n = n + 1;
INSERT OR IGNORE INTO t (name) VALUES ('x');
UPDATE t SET n = 0 WHERE id = 1; DELETE FROM t WHERE id = 1;
BEGIN; COMMIT; ROLLBACK;
Dates and JSON
SELECT date('now'), datetime('now', 'localtime'), date(d, '+7 days'), strftime('%Y-%m', d);
SELECT julianday(b) - julianday(a);
SELECT json_extract(data, '$.user'), data ->> '$.type' FROM events;
Pragmas and performance
PRAGMA foreign_keys = ON; PRAGMA journal_mode = WAL; PRAGMA busy_timeout = 5000;
PRAGMA table_info(books); PRAGMA integrity_check;
CREATE INDEX idx_t_name ON t (name); EXPLAIN QUERY PLAN SELECT * FROM t WHERE name = 'x';
VACUUM; ANALYZE;
Full SQLite documentation