SQLite is a C library that reads and writes a self-contained SQL database directly from a single file on disk. No daemon, no port, no authentication handshake. It is the most widely deployed database engine in the world — present in every Android phone, every iOS device, every Chrome and Firefox tab, every macOS install. You almost certainly have a thousand SQLite databases running inside ten feet of you right now.
Traditional databases like PostgreSQL or MySQL run as separate server processes. Your application talks to them over a network socket. SQLite has no server. The library is linked directly into your application, and the database is a regular file on the filesystem.
This changes everything about how you use it. No connection pool tuning. No network latency. No authentication. Just sqlite3_open() and you're talking to disk.
Shell$ sqlite3 my_database.db SQLite version 3.45.0 2024-01-15 Enter ".help" for usage hints. sqlite>
Shell$ sqlite3 :memory: sqlite> CREATE TABLE t(x); sqlite> INSERT INTO t VALUES (1),(2); sqlite> SELECT sum(x) FROM t; 3
ATTACH DATABASE 'archive.db' AS archive; SELECT * FROM archive.orders WHERE year = 2023; DETACH DATABASE archive;
.tables -- list all tables .schema users -- show CREATE statement .indexes -- list all indexes .dump -- export entire DB as SQL .read seed.sql -- execute a SQL file .mode column -- pretty column output .headers on -- show column names
SQLite uses dynamic typing — the type of a value is stored alongside the value, not enforced by the column. A column declared INTEGER can hold text if you ask it to. This is a feature, not a bug: it lets SQLite store schemas from any other database engine without alteration.
When you declare a column as VARCHAR(255), SQLite doesn't enforce length. It assigns the column an affinity — a hint about how to coerce values. The five affinities are TEXT, NUMERIC, INTEGER, REAL, and BLOB (none).
This is why SQLite can read a PostgreSQL dump without schema changes: VARCHAR(100), DECIMAL(10,2), BOOLEAN all map cleanly to one of the five affinities.
-- Contains "INT" → INTEGER affinity CREATE TABLE t1(x INT, y BIGINT, z TINYINT); -- Contains "CHAR","CLOB","TEXT" → TEXT affinity CREATE TABLE t2(a VARCHAR(100), b TEXT, c CLOB); -- Contains "BLOB" or no type → BLOB (no affinity) CREATE TABLE t3(data BLOB, anything); -- Contains "REAL","FLOA","DOUB" → REAL affinity CREATE TABLE t4(price REAL, rate DOUBLE); -- Anything else → NUMERIC affinity CREATE TABLE t5(amount DECIMAL(10,2), flag BOOLEAN);
CREATE TABLE users( id INTEGER PRIMARY KEY, is_active INTEGER DEFAULT 1, -- 0=false, 1=true email_verified INTEGER DEFAULT 0 ); SELECT * FROM users WHERE is_active; -- truthy SELECT * FROM users WHERE NOT email_verified; -- falsy
Schema statements create the structure — tables, columns, constraints, indexes. They are infrequent operations, but they define every other query you'll write. Get them right.
A table is a named collection of rows, each row having the same set of columns. Columns have names, types, and optional constraints: NOT NULL, UNIQUE, DEFAULT, CHECK, PRIMARY KEY, FOREIGN KEY.
SQLite stores the schema in a special table called sqlite_schema (also accessible as sqlite_master). Querying it shows the CREATE statements for everything in the database.
CREATE TABLE orders ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER NOT NULL, total REAL NOT NULL CHECK (total >= 0), status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','paid','shipped')), created_at TEXT NOT NULL DEFAULT (datetime('now')), UNIQUE(user_id, created_at), FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE );
rowid column. Adding WITHOUT ROWID removes it — useful for tables where the primary key is the only lookup, saving disk and reads.CREATE TABLE kv_store ( key TEXT PRIMARY KEY, value BLOB NOT NULL ) WITHOUT ROWID; -- Lookup by primary key is now a single B-tree search -- instead of two (rowid → PK → data).
-- Supported operations: ALTER TABLE users ADD COLUMN email_verified INTEGER DEFAULT 0; ALTER TABLE users RENAME COLUMN email TO email_address; ALTER TABLE users RENAME TO accounts; ALTER TABLE users DROP COLUMN legacy_field; -- 3.35.0+ -- To change a column type, recreate: BEGIN; ALTER TABLE users RENAME TO users_old; CREATE TABLE users ( /* new definition */ ); INSERT INTO users SELECT * FROM users_old; DROP TABLE users_old; COMMIT;
DROP TABLE IF EXISTS orders; DROP TABLE IF EXISTS orders RESTRICT; -- error if FK references DROP TABLE IF EXISTS orders CASCADE; -- drop dependent FKs
The four operations you'll perform a billion times. SQLite supports the standard SQL syntax with a few thoughtful additions — most notably INSERT ... ON CONFLICT ... DO UPDATE, the SQLite-native upsert.
INSERT adds rows. SELECT reads them. UPDATE modifies existing rows in place. DELETE removes them. All four can be parameterized with ? placeholders to prevent SQL injection — use them always, even for "internal" queries.
Every mutation runs inside an implicit transaction. If you batch many writes, wrap them in BEGIN ... COMMIT — a thousand inserts in one transaction is roughly 50× faster than a thousand separate inserts.
INSERT INTO users (name, email, age) VALUES ('Ada Lovelace', 'ada@example.com', 36);
INSERT INTO users (name, email) VALUES ('Grace Hopper', 'grace@example.com'), ('Alan Turing', 'alan@example.com'), ('Margaret Hamilton', 'margaret@example.com');
INSERT INTO users (name, email, age) VALUES (:name, :email, :age); -- bind :name='Linus', :email='linus@linux.org', :age=54
excluded table refers to the row that was being inserted.INSERT INTO users (id, name, login_count) VALUES (1, 'Ada', 1) ON CONFLICT(id) DO UPDATE SET name = excluded.name, login_count = users.login_count + 1 WHERE users.name != excluded.name;
SELECT id, name, email FROM users WHERE age >= 18; SELECT * FROM users; -- avoid * in production SELECT DISTINCT country FROM users ORDER BY country;
UPDATE users SET last_login = datetime('now'), login_count = login_count + 1 WHERE id = 42 RETURNING id, name, login_count;
WHERE is mandatory in any real code; without it, every row goes.DELETE FROM orders WHERE status = 'cancelled' AND created_at < datetime('now', '-90 days') RETURNING id, user_id;
BEGIN TRANSACTION; INSERT INTO accounts (id, balance) VALUES (1, 100); UPDATE accounts SET balance = balance - 50 WHERE id = 1; INSERT INTO ledger (account_id, delta) VALUES (1, -50); COMMIT; -- or ROLLBACK to undo all of the above
SELECT is the workhorse. The clauses execute in a specific order — FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT — and understanding that order resolves most "why doesn't this work" confusion.
SQL reads top-to-bottom but executes inside-out. The FROM/JOIN runs first, then WHERE filters, then GROUP BY collapses, then HAVING filters the groups, then SELECT picks columns, then ORDER BY sorts, then LIMIT cuts.
This is why you can't use a column alias defined in SELECT inside WHERE — the alias doesn't exist yet when WHERE runs. You can use it in ORDER BY, which runs later.
SELECT * FROM products WHERE price < 100 AND stock > 0 AND category != 'discontinued' AND sku <> 'N/A'; -- <> is the same as !=
SELECT * FROM users WHERE country IN ('JP', 'KR', 'TW') AND age BETWEEN 25 AND 45 -- inclusive AND name LIKE 'A%' -- starts with A AND email GLOB '*@example.*'; -- case-sensitive
WHERE x = NULL matches nothing — use IS NULL. NULL compared to anything, including itself, yields NULL (treated as false).SELECT * FROM users WHERE deleted_at IS NULL; SELECT * FROM users WHERE deleted_at IS NOT NULL; SELECT COALESCE(nickname, name, 'anonymous') AS display; SELECT NULLIF(status, 'draft') FROM posts; -- NULL if 'draft'
SELECT * FROM products ORDER BY category ASC, -- ascending (default) price DESC, -- highest first name ASC NULLS LAST; -- NULLs go last -- Order by expression SELECT * FROM orders ORDER BY ABS(total - 100) LIMIT 5;
WHERE id > last_seen_id ORDER BY id LIMIT 20.-- Page 3 (rows 41-60) SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 40; -- Keyset pagination (faster on large tables) SELECT * FROM orders WHERE id > :last_seen_id ORDER BY id LIMIT 20;
SELECT name, CASE WHEN age < 18 THEN 'minor' WHEN age < 65 THEN 'adult' ELSE 'senior' END AS category, CASE WHEN status = 'active' THEN 1 ELSE 0 END AS is_active FROM users;
SELECT DISTINCT country FROM users ORDER BY country; -- DISTINCT on multiple columns SELECT DISTINCT country, city FROM users; -- DISTINCT inside aggregate SELECT COUNT(DISTINCT country) FROM users;
JOIN combines rows from two tables based on a condition. SQLite supports INNER, LEFT, CROSS, and (with a workaround) RIGHT and FULL OUTER. The mental model: for each row in the left table, find matching rows in the right table, emit a row for each match.
INNER JOIN — keep only rows that match in both tables.
LEFT JOIN — keep every row from the left table; fill NULLs on the right when no match.
CROSS JOIN — Cartesian product. Every left row paired with every right row. Use sparingly.
RIGHT / FULL OUTER — supported since SQLite 3.39 (2022). Older versions emulate RIGHT JOIN by swapping tables in a LEFT JOIN.
SELECT u.name, o.id AS order_id, o.total FROM users u INNER JOIN orders o ON o.user_id = u.id WHERE o.status = 'paid' ORDER BY o.id DESC;
-- Users with no orders SELECT u.name FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE o.id IS NULL;
SELECT u.name, o.id AS order_id, p.name AS product_name, oi.qty FROM users u JOIN orders o ON o.user_id = u.id JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id WHERE u.country = 'JP';
SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;
-- All possible size × color combinations SELECT s.name AS size, c.name AS color FROM sizes s CROSS JOIN colors c;
SELECT u.name, o.id AS order_id FROM users u FULL OUTER JOIN orders o ON o.user_id = u.id;
Aggregate functions collapse groups of rows into single values. Pair them with GROUP BY to compute per-category stats — counts, sums, averages, minimums, maximums, and concatenated strings.
COUNT(*) counts every row including NULLs. COUNT(column) counts only non-NULL values. The distinction matters when joining — a LEFT JOIN with no match produces NULL, and COUNT(column) will skip it.
SELECT country, COUNT(*) AS user_count, AVG(age) AS avg_age, MIN(created_at) AS first_user, MAX(created_at) AS latest_user FROM users GROUP BY country ORDER BY user_count DESC;
SELECT user_id, COUNT(*) AS order_count, SUM(total) AS spent FROM orders WHERE status = 'paid' GROUP BY user_id HAVING COUNT(*) >= 5 AND SUM(total) > 1000 ORDER BY spent DESC;
SELECT department, GROUP_CONCAT(name, ', ') AS employees FROM staff GROUP BY department; -- Use DISTINCT to avoid duplicates SELECT GROUP_CONCAT(DISTINCT country, '|') FROM users;
SELECT SUM(price) FROM orders WHERE id = -1; -- NULL SELECT TOTAL(price) FROM orders WHERE id = -1; -- 0.0
SELECT strftime('%Y-%m', created_at) AS month, country, COUNT(*) AS orders, AVG(total) AS avg_order FROM orders GROUP BY month, country ORDER BY month DESC, country;
A subquery is a SELECT inside another statement. A CTE (Common Table Expression, the WITH clause) is a named subquery declared at the top — it reads better and can reference itself recursively.
Subqueries nest inside parentheses — they're concise for one-off filters. CTEs live at the top of the query with a name — they shine for multi-step transformations and self-references.
Performance is generally identical between the two. Pick based on readability.
SELECT * FROM orders WHERE total > (SELECT AVG(total) FROM orders);
SELECT name FROM users WHERE id IN (SELECT DISTINCT user_id FROM orders WHERE status = 'paid');
SELECT u.name, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count FROM users u;
WITH paying_users AS ( SELECT DISTINCT user_id FROM orders WHERE status = 'paid' ), top_spenders AS ( SELECT user_id, SUM(total) AS total FROM orders WHERE status = 'paid' GROUP BY user_id HAVING SUM(total) > 500 ) SELECT u.name, t.total FROM top_spenders t JOIN users u ON u.id = t.user_id ORDER BY t.total DESC;
WITH RECURSIVE descendants(id, name, depth) AS ( SELECT id, name, 0 FROM categories WHERE id = 1 -- anchor UNION ALL SELECT c.id, c.name, d.depth + 1 FROM categories c JOIN descendants d ON c.parent_id = d.id ) SELECT * FROM descendants ORDER BY depth;
WITH RECURSIVE seq(n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 100 ) SELECT * FROM seq; -- Or use the built-in (3.35+) SELECT * FROM generate_series(1, 100);
SELECT user_id, created_at, total, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS nth_order, SUM(total) OVER (PARTITION BY user_id) AS lifetime_value, LAG(total) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_total FROM orders ORDER BY user_id, created_at;
An index is a separate B-tree structure that lets SQLite find rows by a column value without scanning the entire table. Indexes make reads fast and writes slightly slower. Choose them based on the queries you actually run.
Index columns that appear in WHERE clauses, JOIN conditions, ORDER BY, and GROUP BY. Don't index columns with low cardinality (boolean, status with 3 values) — the index won't help.
Use EXPLAIN QUERY PLAN before and after adding an index to verify it's actually used.
CREATE INDEX idx_users_email ON users(email); CREATE UNIQUE INDEX idx_users_email ON users(email); -- enforce uniqueness
CREATE INDEX idx_orders_user_status ON orders(user_id, status, created_at); -- Uses the index SELECT * FROM orders WHERE user_id = 5 AND status = 'paid'; -- Also uses the index (leftmost prefix) SELECT * FROM orders WHERE user_id = 5; -- Cannot use the index SELECT * FROM orders WHERE status = 'paid';
CREATE INDEX idx_active_users_email ON users(email) WHERE deleted_at IS NULL; -- Smaller index, faster lookups for active users
CREATE INDEX idx_users_email_lower ON users(LOWER(email)); -- Uses the index SELECT * FROM users WHERE LOWER(email) = 'ada@example.com';
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 5; -- Without index: -- SCAN orders -- With index on user_id: -- SEARCH orders USING INDEX idx_orders_user_id (user_id=?)
DROP INDEX IF EXISTS idx_users_email;
A view is a named SELECT stored in the schema. Querying a view runs its underlying SELECT. Views don't store data — they're a convenience for packaging complex queries behind a simple name.
Views hide complexity, enforce consistent access patterns, and let you change the underlying schema without breaking every query. The trade-off: there's no materialized view in SQLite (no cached result), so a complex view runs every time.
CREATE VIEW active_users AS SELECT id, name, email, last_login FROM users WHERE deleted_at IS NULL AND is_active = 1; -- Use it like a table SELECT * FROM active_users WHERE last_login > datetime('now', '-7 days');
CREATE VIEW order_summary AS SELECT o.id AS order_id, u.name AS customer, o.total, o.status, COUNT(oi.id) AS item_count FROM orders o JOIN users u ON u.id = o.user_id LEFT JOIN order_items oi ON oi.order_id = o.id GROUP BY o.id; SELECT * FROM order_summary WHERE status = 'paid';
DROP VIEW IF EXISTS active_users; DROP VIEW IF EXISTS order_summary;
A trigger is SQL that runs automatically when rows are inserted, updated, or deleted. Use them for audit logs, derived columns, and enforcing constraints too complex for CHECK.
Every trigger has: a name, a timing (BEFORE or AFTER), an event (INSERT/UPDATE/DELETE), a target table, and a body. Inside the body, the special tables NEW and OLD give access to row values — NEW for the incoming row, OLD for the row being replaced or deleted.
CREATE TRIGGER trg_orders_audit AFTER INSERT ON orders FOR EACH ROW BEGIN INSERT INTO audit_log (table_name, row_id, action, at) VALUES ('orders', NEW.id, 'INSERT', datetime('now')); END;
CREATE TRIGGER trg_users_normalize_email BEFORE UPDATE ON users FOR EACH ROW WHEN NEW.email != OLD.email BEGIN UPDATE users SET email = LOWER(NEW.email), email_verified = 0 WHERE id = NEW.id; END;
CREATE TRIGGER trg_users_cleanup AFTER DELETE ON users FOR EACH ROW BEGIN DELETE FROM orders WHERE user_id = OLD.id; DELETE FROM sessions WHERE user_id = OLD.id; DELETE FROM preferences WHERE user_id = OLD.id; END;
DROP TRIGGER IF EXISTS trg_orders_audit;
PRAGMA is SQLite's configuration dialect — a SQL-like command for querying and modifying database behavior. Foreign keys, journal mode, page size, schema introspection — all live behind PRAGMAs.
Most PRAGMAs are runtime settings — they apply to the current connection and reset on reconnect. A few (page_size, journal_mode) persist in the file. Always set foreign_keys=ON at connection start — it's OFF by default for backward compatibility.
-- At the start of every connection: PRAGMA foreign_keys = ON; -- Verify PRAGMA foreign_keys; -- returns 1
PRAGMA table_info(users); -- cid | name | type | notnull | dflt_value | pk -- 0 | id | INTEGER | 1 | NULL | 1 -- 1 | email | TEXT | 1 | NULL | 0 PRAGMA foreign_key_list(orders); PRAGMA index_list(users); PRAGMA index_info(idx_users_email);
PRAGMA journal_mode = WAL; -- Modes: DELETE (default) | WAL | TRUNCATE | MEMORY | OFF -- Checkpoint the WAL periodically PRAGMA wal_checkpoint;
PRAGMA integrity_check; -- full check, returns 'ok' if healthy PRAGMA quick_check; -- faster, less thorough PRAGMA foreign_key_check; -- find FK violations
PRAGMA synchronous = NORMAL; -- recommended with WAL -- 0 = OFF (risky) | 1 = NORMAL | 2 = FULL (default) | 3 = EXTRA
PRAGMA cache_size = -64000; -- 64MB page cache (negative = KB) PRAGMA temp_store = MEMORY; -- temp tables in RAM PRAGMA mmap_size = 268435456; -- 256MB memory-mapped I/O
PRAGMA stats; -- table sizes and index info PRAGMA compile_options; -- features compiled in PRAGMA database_list; -- attached databases
SQLite ships with a small but complete function library. The dates are the trickiest — SQLite stores them as TEXT in ISO-8601 by convention, and provides strftime for formatting.
Custom functions can be registered from C, Python (via create_function), and most other language bindings. This is one of SQLite's superpowers — domain logic can live inside the database.
SELECT LENGTH(name), -- character count LOWER(email), -- lowercase UPPER(country), -- uppercase SUBSTR(sku, 1, 3), -- first 3 chars TRIM(name), -- strip whitespace REPLACE(phone, '-', ''), -- remove dashes INSTR(email, '@'), -- position of '@' PRINTF('%05d', id); -- zero-pad SELECT || -- concatenation operator first_name || ' ' || last_name AS full_name FROM users;
SELECT ABS(-42), -- 42 ROUND(3.14159, 2), -- 3.14 RANDOM(), -- random integer MAX(1, 2, 3), -- 3 (scalar form) MIN(1, 2, 3), -- 1 CAST('42' AS INTEGER); -- type conversion
-- Current time SELECT datetime('now'); -- 2024-01-15 14:32:00 SELECT datetime('now', '+1 day'); -- tomorrow SELECT datetime('now', '-7 days', 'utc'); -- last week UTC -- Format with strftime SELECT strftime('%Y-%m-%d', created_at) AS day FROM orders; SELECT strftime('%H:%M', login_at) AS hour FROM sessions; SELECT strftime('%W', created_at) AS week_number; -- Difference in days SELECT CAST( (julianday('now') - julianday(created_at)) AS INTEGER ) AS days_since_signup FROM users;
-- COALESCE: first non-NULL value SELECT COALESCE(nickname, name, 'anonymous'); -- NULLIF: NULL if equal, else first arg SELECT NULLIF(status, 'draft'); -- NULL for drafts -- IIF: inline if (3.32+) SELECT IIF(age >= 18, 'adult', 'minor');
SELECT json_extract(payload, '$.user.name'), -- get field json_array_length('[1,2,3]'), -- 3 json_type(payload, '$.address'), -- 'object' json_insert(payload, '$.user.age', 36), -- add if missing json_replace(payload, '$.user.age', 37), -- replace if exists json_set(payload, '$.user.age', 37), -- upsert json_remove(payload, '$.temp'); -- delete field -- JSON in WHERE SELECT * FROM events WHERE json_extract(payload, '$.type') = 'click';
SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank, DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS in_dept_rank, AVG(salary) OVER (PARTITION BY dept) AS dept_avg, FIRST_VALUE(name) OVER (PARTITION BY dept ORDER BY salary DESC) AS top_earner FROM employees;
The things you learn after years of using SQLite in production. Read these once and remember them.
? or named parameters. This is non-negotiable — SQL injection is the #1 cause of database breaches and it's fully preventable.-- Bad — SQL injection vulnerable SELECT * FROM users WHERE email = '-- user input here'; -- Good — parameterized SELECT * FROM users WHERE email = ?;
BEGIN; INSERT INTO logs (msg) VALUES ('event 1'); INSERT INTO logs (msg) VALUES ('event 2'); -- ... 998 more COMMIT;
VACUUM; -- rebuilds entire DB PRAGMA auto_vacuum = INCREMENTAL; -- alternative: incremental PRAGMA incremental_vacuum(100); -- free 100 pages
.dump exports SQL text. For a live one, use the Online Backup API (accessible from most language bindings) to copy without locking readers out.-- CLI backup sqlite3 mydb.db .dump > backup.sql sqlite3 backup.db < backup.sql -- Or copy at file level (only when DB is closed) cp mydb.db mydb.backup.db cp mydb.db-wal mydb.backup.db-wal -- if WAL mode
-- Import CSV sqlite3 mydb.db .mode csv .import data.csv my_table -- Export CSV .mode csv .headers on .output products.csv SELECT * FROM products; .output stdout
YYYY-MM-DD HH:MM:SS) sorts correctly as text, is human-readable, and works with all SQLite date functions natively. Don't store Unix timestamps — you'll regret it when debugging.CREATE TABLE events ( id INTEGER PRIMARY KEY, created_at TEXT DEFAULT (datetime('now')) ); -- 2024-01-15 14:32:00 — sorts, parses, formats easily
sqlite3 CLI is a full REPL. Set up column mode once and your life improves immediately.-- ~/.sqliterc — runs every time the CLI starts .mode column .headers on .nullvalue ∅ .prompt > >> PRAGMA foreign_keys = ON;
-- Use SQLite for: -- · Mobile and desktop apps -- · Single-server web apps (low write concurrency) -- · Embedded systems -- · Testing and prototyping -- · Data exchange format (.db files are portable) -- Consider PostgreSQL for: -- · High-write-concurrency web services -- · Multi-tenant systems with strict security -- · Data warehouses