Cheatsheet SQLite

sqlite3 my.db
sqlite3 :memory:
sqlite3 my.db ".tables"
sqlite3 my.db ".schema"

sqlite3 file.db opens or creates the database. :memory: creates a temporary DB in RAM. The file is created automatically if it does not exist.

-- Export to CSV
.mode csv
.output output.csv
SELECT * FROM users;
.output stdout

-- Export to JSON
.mode json
.output data.json
SELECT * FROM users;
.output stdout

.output file redirects the output to a file. .output stdout returns to the terminal. Combine with .mode csv or .mode json for exporting.

-- List tables
.tables

-- Structure of a table
.schema users

-- Column info
PRAGMA table_info(users);

-- File size
PRAGMA page_count;
PRAGMA page_size;

PRAGMA table_info() shows columns, types and constraints. page_count × page_size gives the total size. .schema shows the creation SQL.

.tables          -- list tables
.schema table    -- view table DDL
.headers on      -- show column names
.mode column     -- column format
.mode csv        -- CSV format
.mode json       -- JSON format
.quit            -- exit

dot commands are internal to the CLI (they are not SQL). .tables lists tables, .schema shows the DDL. .headers on is essential for readability.

-- Binary backup (safe with the DB in use)
sqlite3 my.db ".backup backup.db"

-- Full SQL dump
sqlite3 my.db .dump > dump.sql

-- Restore from dump
sqlite3 new.db < dump.sql

-- Restore from backup
sqlite3 my.db ".restore backup.db"

.backup makes a safe copy even with the DB in use. .dump generates re-executable SQL. .restore restores from a binary backup.

.mode table      -- table with borders (3.33+)
.mode column     -- aligned columns
.mode csv        -- comma separated
.mode json       -- array of JSON objects
.mode markdown   -- Markdown format
.mode html       -- HTML table
.mode box        -- box with borders

.mode sets the output format. table and box are the most readable. csv and json are ideal for exporting data to other tools.

-- Via shell
sqlite3 my.db < script.sql

-- Inside the CLI
.read script.sql
.read /absolute/path/setup.sql

-- Single command via flag
sqlite3 my.db "SELECT COUNT(*) FROM users;"

.read file.sql runs a script inside the CLI. Via shell, redirect with <. The flag with an SQL string runs a single command and exits.

.mode csv
.import data.csv users

-- With header (first line = columns):
.import --csv --skip 1 data.csv users

-- Verify:
SELECT COUNT(*) FROM users;

.import loads data from a CSV into a table. With --skip 1 it ignores the header line. The table must exist beforehand with compatible columns.

-- Put in ~/.sqliterc
.headers on
.mode table
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;

The .sqliterc file is loaded automatically at startup. Enables headers, foreign_keys and WAL by default. Saves repetition in every session.

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  email TEXT UNIQUE,
  age INTEGER DEFAULT 0,
  created_at TEXT DEFAULT (datetime('now'))
);

INTEGER PRIMARY KEY AUTOINCREMENT creates an auto-incrementing ID. NOT NULL requires a value. UNIQUE prevents duplicates. DEFAULT sets a default value.

DROP TABLE IF EXISTS logs;

DROP TABLE users;

-- Check if it exists first:
SELECT name FROM sqlite_master
WHERE type = 'table' AND name = 'logs';

DROP TABLE removes the table and all its data. IF EXISTS avoids an error if it does not exist. sqlite_master is SQLite's internal catalog.

-- All tables
SELECT name FROM sqlite_master
WHERE type = 'table' ORDER BY name;

-- DDL of an object
SELECT sql FROM sqlite_master
WHERE name = 'users';

-- Types: table, index, view, trigger
SELECT type, name FROM sqlite_master;

sqlite_master is the internal catalog. It contains the DDL (sql) of all objects. type can be table, index, view or trigger.

CREATE TABLE IF NOT EXISTS logs (
  id INTEGER PRIMARY KEY,
  message TEXT NOT NULL,
  level TEXT CHECK(level IN ('info', 'warn', 'error')),
  created_at TEXT DEFAULT (datetime('now'))
);

IF NOT EXISTS avoids an error if the table already exists. Essential in migration and setup scripts. CHECK validates the allowed values in the column.

PRAGMA foreign_keys = ON;

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  total REAL,
  FOREIGN KEY (user_id)
    REFERENCES users(id)
    ON DELETE CASCADE
);

PRAGMA foreign_keys = ON is mandatory — they are disabled by default. ON DELETE CASCADE deletes children automatically. Options: CASCADE, SET NULL, RESTRICT.

-- Recommended (implicit rowid):
CREATE TABLE notes (
  id INTEGER PRIMARY KEY,  -- auto-increments
  text TEXT
);

-- With AUTOINCREMENT (avoids id reuse):
CREATE TABLE invoices (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  total REAL
);

-- Difference:
--   INTEGER PRIMARY KEY may reuse
--   the highest id after DELETE
--   AUTOINCREMENT never reuses
--   (but it is slower)

INTEGER PRIMARY KEY is an alias for the rowid and auto-increments automatically. AUTOINCREMENT guarantees deleted ids are never reused, but has overhead — use it only if you really need it (e.g. invoices).

ALTER TABLE users ADD COLUMN phone TEXT;

ALTER TABLE users
  ADD COLUMN active INTEGER DEFAULT 1 NOT NULL;

-- SQLite does not support DROP COLUMN before 3.35
-- SQLite 3.35+:
ALTER TABLE users DROP COLUMN phone;

ALTER TABLE ADD COLUMN adds a column at the end. New columns need a DEFAULT if they are NOT NULL. DROP COLUMN only since version 3.35.

CREATE TABLE config (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL
) WITHOUT ROWID;

-- More efficient when the PK is TEXT
-- and the table is small

WITHOUT ROWID removes the internal rowid and uses the PK the the cluster. Faster for small tables with a non-integer PK. Does not support AUTOINCREMENT.

-- Validate values in the table itself:
CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  price REAL CHECK (price >= 0),
  stock INTEGER DEFAULT 0
    CHECK (stock >= 0),
  status TEXT CHECK (status IN ('active', 'inactive'))
);

-- Violation → error on INSERT/UPDATE:
INSERT INTO products (name, price)
VALUES ('X', -5);  -- fails!

-- CHECK is always validated
-- by SQLite

CHECK constraints validate values directly in the column or table definition. Violations cause an error on INSERT/UPDATE. Useful to guarantee invariants (positive prices, valid statuses). SQLite always validates them.

-- Rename column (3.25+)
ALTER TABLE users
  RENAME COLUMN name TO full_name;

-- Rename table
ALTER TABLE users RENAME TO customers;

RENAME COLUMN changes the name of a column (version 3.25+). RENAME TO changes the table name. Indexes and triggers are updated automatically.

CREATE TEMP TABLE results (
  id INTEGER PRIMARY KEY,
  value REAL
);

-- Exists only in the current session
-- Deleted when the connection closes
-- Does not appear in .tables of another session

CREATE TEMP TABLE creates a table visible only in the current connection. Useful for intermediate calculations. It is deleted automatically when the database is closed.

INSERT INTO users (name, email, age)
VALUES ('Anna', 'ana@mail.com', 30);

-- Get the last inserted ID:
SELECT last_insert_rowid();

INSERT INTO ... VALUES inserts one row. last_insert_rowid() returns the generated rowid. Always specify columns explicitly for clarity.

DELETE FROM users WHERE id = 5;

-- Delete all records:
DELETE FROM logs;

-- Reset the AUTOINCREMENT:
DELETE FROM table_name;
DELETE FROM sqlite_sequence WHERE name = 'table_name';

DELETE FROM ... WHERE removes rows. Without WHERE, it deletes everything (but keeps the table). sqlite_sequence stores the AUTOINCREMENT counter.

-- Return data from the inserted row
INSERT INTO users (name, email)
VALUES ('Anna', 'ana@mail.com')
RETURNING id, name;

-- Return on update
UPDATE products SET price = price * 1.1
WHERE category = 'tech'
RETURNING id, name, price;

-- Return on delete
DELETE FROM logs WHERE created < '2024-01-01'
RETURNING id;

RETURNING (version 3.35+) returns the affected rows directly. Avoids an extra SELECT after INSERT/UPDATE/DELETE. Supports RETURNING *.

INSERT INTO users (name, email, age) VALUES
  ('John', 'j@mail.com', 25),
  ('Mary', 'm@mail.com', 30),
  ('Peter', 'p@mail.com', 35);

-- Insert from a SELECT:
INSERT INTO active_users
SELECT * FROM users WHERE active = 1;

A single INSERT with multiple VALUES is faster than several inserts. INSERT INTO ... SELECT copies data from another table or query.

-- SQLite 3.24+
INSERT INTO config (key, value)
VALUES ('theme', 'dark')
ON CONFLICT(key) DO UPDATE
SET value = excluded.value;

-- Ignore if it already exists:
INSERT OR IGNORE INTO users (id, name)
VALUES (1, 'Anna');

ON CONFLICT DO UPDATE performs an upsert — inserts or updates. excluded.value references the value that would have been inserted. INSERT OR IGNORE simply ignores conflicts.

-- Several rows in one INSERT:
INSERT INTO products (name, price)
VALUES
  ('Keyboard', 45.00),
  ('Mouse', 25.50),
  ('Monitor', 180.00);

-- One transaction = much faster
-- than 3 separate INSERTs

-- INSERT from a SELECT:
INSERT INTO products_archive (name, price)
SELECT name, price FROM products
WHERE discontinued = 1;

An INSERT with several rows (VALUES (...), (...)) is much faster than separate INSERTs (a single transaction). INSERT ... SELECT copies data from another table. Ideal for bulk-populating the DB.

SELECT * FROM users;

SELECT name, email FROM users
WHERE age > 18
ORDER BY name ASC
LIMIT 10 OFFSET 0;

SELECT DISTINCT city FROM users;

SELECT queries data. WHERE filters, ORDER BY sorts, LIMIT/OFFSET paginates. DISTINCT removes duplicates. Avoid * in production.

SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

SELECT u.name, COUNT(o.id) AS total_orders
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

INNER JOIN returns only rows with a match. LEFT JOIN includes all from the left even without a match. SQLite also supports CROSS JOIN and RIGHT JOIN (3.39+).

-- Replaces on key conflict:
INSERT OR REPLACE INTO defs (key, value)
VALUES ('version', '2.0');

-- Silently ignores the conflict:
INSERT OR IGNORE INTO defs (key, value)
VALUES ('version', '1.0');

-- Other variants:
INSERT OR ABORT ...   -- default (error)
INSERT OR ROLLBACK ...
INSERT OR FAIL ...

-- REPLACE deletes the old row
-- and inserts a new one (id changes!)

INSERT OR REPLACE deletes the conflicting row and inserts a new one (the id may change). INSERT OR IGNORE silently discards. For upserts that keep the id, prefer ON CONFLICT DO UPDATE.

UPDATE users
SET age = 31, email = 'new@mail.com'
WHERE name = 'Anna';

-- Update all (no WHERE):
UPDATE products SET price = price * 1.1;

-- With a subquery:
UPDATE users SET active = 0
WHERE id IN (SELECT id FROM banned);

UPDATE ... SET ... WHERE modifies rows. Without WHERE, it updates all rows. Use subqueries for complex conditions. Returns the number of affected rows.

-- In the WHERE
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- In the FROM (derived table)
SELECT category, average FROM (
  SELECT category, AVG(price) AS average
  FROM products GROUP BY category
) WHERE average > 50;

Subqueries are queries inside queries. In the WHERE they compare against aggregated results. In the FROM they work the temporary tables. SQLite supports correlated subqueries.

INTEGER   -- integers (1, 2, 3, 4, 6 or 8 bytes)
REAL      -- 64-bit float (IEEE 754)
TEXT      -- strings (UTF-8, UTF-16)
BLOB      -- binary data (no conversion)
NUMERIC   -- decimal, boolean, date

-- SQLite uses dynamic typing:
-- a column accepts any type

SQLite has 5 storage classes (affinities). Typing is dynamic — an INTEGER column can store text. The affinity only suggests a preferred conversion.

SELECT datetime('now');
SELECT date('now');
SELECT time('now');

-- Formatting
SELECT strftime('%d/%m/%Y %H:%M', 'now');

-- Date arithmetic
SELECT datetime('now', '-7 days');
SELECT datetime('now', '+1 month', 'start of month');

SQLite has no native DATE type — it uses TEXT (ISO-8601), REAL (Julian) or INTEGER (Unix). Functions: date(), time(), datetime(), strftime().

SELECT CAST('42' AS INTEGER);
SELECT CAST(3.14 AS INTEGER);   -- 3
SELECT CAST(100 AS REAL);       -- 100.0
SELECT CAST(1 AS TEXT);         -- '1'

-- typeof() shows the storage class
SELECT typeof(42);       -- 'integer'
SELECT typeof(3.14);     -- 'real'
SELECT typeof('text');   -- 'text'
SELECT typeof(NULL);     -- 'null'

CAST converts between types explicitly. typeof() returns the actual storage class of the value. Useful for debugging and validating imported data.

-- Alias for rowid (recommended):
CREATE TABLE t (
  id INTEGER PRIMARY KEY
);

-- With explicit autoincrement:
CREATE TABLE t (
  id INTEGER PRIMARY KEY AUTOINCREMENT
);

-- rowid is always present (except WITHOUT ROWID)

INTEGER PRIMARY KEY is an alias for the internal rowid. AUTOINCREMENT prevents reuse of deleted IDs (slower). Without it, SQLite may reuse the highest ID.

-- Days between two dates
SELECT julianday('2025-12-25') - julianday('now');

-- Age in years
SELECT (julianday('now') - julianday('1990-05-15')) / 365.25;

-- Days in the current month
SELECT strftime('%d', 'now', 'start of month', '+1 month', '-1 day');

julianday() converts dates to a decimal number of days. The difference gives days. Divide by 365.25 for approximate years. strftime extracts components.

-- SQLite does not enforce rigid types;
-- it uses "affinity" (preference):
CREATE TABLE test (
  a INTEGER,   -- INTEGER affinity
  b TEXT,      -- TEXT affinity
  c NUMERIC    -- NUMERIC affinity
);

-- You can store any value:
INSERT INTO test VALUES ('123', 456, 'abc');

-- '123' is converted to 123 (int)
-- 456 is stored the text '456'
-- 'abc' stays the text

-- STRICT (3.37+) enforces types:
CREATE TABLE strict_table (x INTEGER) STRICT;

SQLite uses type affinity: columns have a type preference but accept any value (converting when possible). CREATE TABLE ... STRICT (3.37+) enforces rigid types like in other DBMSs.

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  code TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  price REAL NOT NULL CHECK(price >= 0),
  stock INTEGER DEFAULT 0
);

NOT NULL requires a value. UNIQUE prevents duplicates (accepts multiple NULLs). CHECK validates conditions. DEFAULT sets the value when omitted.

-- SQLite has no native BOOLEAN
-- Use INTEGER: 0 = false, 1 = true
SELECT * FROM users WHERE active = 1;

-- NULL handling
SELECT COALESCE(email, 'no email');
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;
SELECT IFNULL(phone, 'N/A');

SQLite uses INTEGER 0/1 for booleans. IS NULL / IS NOT NULL checks for nulls (do not use = NULL). COALESCE() returns the first non-null.

-- SQLite has no native DATE type.
-- Options:

-- 1. TEXT in ISO-8601 (recommended):
INSERT INTO events (date)
VALUES ('2024-06-15 14:30:00');

-- 2. INTEGER the Unix timestamp:
INSERT INTO events (date)
VALUES (strftime('%s', 'now'));

-- Date functions work with TEXT:
SELECT date('now');                  -- today
SELECT datetime('now', '+7 days');   -- +7 days
SELECT strftime('%d/%m/%Y', date)
FROM events;

SQLite has no native DATE type — store dates the TEXT in ISO-8601 format (recommended, sorts correctly) or INTEGER (Unix timestamp). The date(), datetime() and strftime() functions operate on these formats.

-- All have TEXT affinity in SQLite:
CREATE TABLE people (
  name TEXT,           -- recommended
  email VARCHAR(255),  -- limit is NOT enforced
  country CHAR(2)      -- accepts any length
);

-- SQLite does NOT truncate or reject
-- strings longer than VARCHAR(255)

-- Convention: always use TEXT

In SQLite, TEXT, VARCHAR(n) and CHAR(n) all have TEXT affinity — the declared size is not enforced (no truncation or error). Convention: use plain TEXT. The max length is only validated if you add a CHECK.

CREATE TABLE files (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  data BLOB,
  type TEXT
);

-- Insert a hex literal
INSERT INTO files (name, data)
VALUES ('icon', x'89504E47');

-- Size
SELECT length(data) FROM files;

BLOB stores binary data (images, PDFs). Hex literals use the x'hex' prefix. length() returns the size in bytes. For large files, prefer the filesystem.

SELECT COUNT(*) FROM users;
SELECT SUM(total) FROM orders;
SELECT AVG(age) FROM users;
SELECT MIN(price), MAX(price) FROM products;
SELECT GROUP_CONCAT(name, ', ') FROM users;
SELECT TOTAL(price) FROM items;  -- 0.0 if empty

Aggregates: COUNT, SUM, AVG, MIN, MAX. GROUP_CONCAT concatenates values. TOTAL() is like SUM() but returns 0.0 instead of NULL.

SELECT name, salary,
  RANK() OVER (ORDER BY salary DESC) AS ranking,
  SUM(salary) OVER (
    PARTITION BY department
  ) AS dept_total,
  LAG(name) OVER (ORDER BY salary) AS previous
FROM employees;

Window functions (3.25+) compute over a set without collapsing rows. RANK(), ROW_NUMBER(), LAG(), LEAD(). PARTITION BY defines the groups.

-- First non-null value
SELECT COALESCE(phone, mobile, 'no contact')
FROM users;

-- NULLIF: returns NULL if equal
SELECT NULLIF(price, 0);  -- NULL if price = 0

-- Avoid division by zero:
SELECT total / NULLIF(qty, 0) AS average
FROM sales;

COALESCE() returns the first non-NULL argument. NULLIF(a, b) returns NULL if a = b. Useful to avoid division by zero and set fallbacks.

SELECT category, COUNT(*) AS total,
       AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING total > 5
ORDER BY total DESC;

GROUP BY groups rows by column. HAVING filters groups (like WHERE but for aggregates). WHERE filters before grouping, HAVING after.

WITH sales_2024 AS (
  SELECT user_id, SUM(total) AS total
  FROM orders
  WHERE strftime('%Y', date) = '2024'
  GROUP BY user_id
)
SELECT u.name, s.total
FROM users u
JOIN sales_2024 s ON u.id = s.user_id
ORDER BY s.total DESC;

WITH (CTE) creates named temporary queries. Improves readability of complex queries. You can chain multiple CTEs separated by commas. They are not materialized.

SELECT upper('ana');        -- 'ANA'
SELECT lower('ANA');        -- 'ana'
SELECT length('hey');       -- 3
SELECT trim('  hi  ');      -- 'hi'
SELECT substr('SQLite', 1, 3);  -- 'SQL'
SELECT replace('a-b', '-', '+'); -- 'a+b'
SELECT instr('hello', 'e');  -- 2 (position)

-- Concatenate:
SELECT 'Hello' || ' ' || 'World';

-- LIKE is case-insensitive (ASCII):
SELECT * FROM users
WHERE name LIKE 'ana%';

String functions: upper/lower/length/trim, substr, replace, instr (position). Concatenation with ||. LIKE is case-insensitive for ASCII by default.

SELECT UPPER(name), LOWER(email);
SELECT LENGTH(description);
SELECT TRIM('  text  ');
SELECT REPLACE(name, 'a', '@');
SELECT SUBSTR(name, 1, 3);
SELECT INSTR(email, '@');
SELECT printf('Hello %s, you are %d years old', name, age)
FROM users;

UPPER/LOWER change capitalization. LENGTH counts characters. SUBSTR extracts a part. INSTR finds a position. printf() formats strings with placeholders.

WITH RECURSIVE counter(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 10
)
SELECT n FROM counter;

-- Hierarchy (tree):
WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 0 AS level
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, t.level + 1
  FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;

WITH RECURSIVE allows CTEs that reference themselves. Ideal for hierarchies, series and graphs. Terminates when the recursive part returns zero rows.

-- Current date/time:
SELECT date('now');            -- 2024-06-15
SELECT time('now');            -- 14:30:00
SELECT datetime('now');        -- both
SELECT strftime('%s', 'now');  -- Unix epoch

-- Modifiers:
SELECT date('now', '+1 month');
SELECT date('now', 'start of month');
SELECT date('now', 'weekday 0');  -- Sunday

-- Extract parts:
SELECT strftime('%Y', date) AS year,
       strftime('%m', date) AS month
FROM events;

-- Difference in days:
SELECT julianday('2024-12-31')
     - julianday('now');

Date functions: date(), time(), datetime() with modifiers (+1 month, start of month). strftime() extracts/formats parts. julianday() computes differences in days.

SELECT ABS(-5);          -- 5
SELECT ROUND(3.14159, 2); -- 3.14
SELECT RANDOM();          -- random integer
SELECT MAX(1, 5, 3);     -- 5 (scalar)
SELECT MIN(1, 5, 3);     -- 1 (scalar)

-- SQLite 3.35+ (math functions):
SELECT log(100), sqrt(16), pow(2, 10);
SELECT ceil(3.2), floor(3.8), pi();

ABS, ROUND, RANDOM are native. MAX/MIN with multiple arguments are scalar (not aggregates). The log, sqrt, pow functions require compiling with math (3.35+).

SELECT name,
  CASE
    WHEN age < 18 THEN 'minor'
    WHEN age BETWEEN 18 AND 65 THEN 'adult'
    ELSE 'senior'
  END AS bracket
FROM users;

-- Shortcut (SQLite 3.32+):
SELECT IIF(active = 1, 'Yes', 'No') FROM users;

CASE WHEN is a multi-branch conditional. IIF() is a simplified ternary (condition, true, false). Both work in SELECT, WHERE and ORDER BY.

CREATE INDEX idx_users_email
  ON users(email);

CREATE UNIQUE INDEX idx_code
  ON products(code);

CREATE INDEX idx_comp
  ON orders(user_id, created_at);

CREATE INDEX speeds up searches and sorting. UNIQUE prevents duplicates. Composite indexes follow the column order — the first is the most important for filters.

EXPLAIN QUERY PLAN
SELECT * FROM users
WHERE email = 'ana@mail.com';

-- Expected result:
-- SEARCH USING INDEX idx_users_email (email=?)

-- Bad result:
-- SCAN users  (full table scan!)

EXPLAIN QUERY PLAN shows how SQLite executes the query. SEARCH USING INDEX = good. SCAN = full table scan (bad). Essential for optimizing slow queries.

-- Index that covers the whole query
CREATE INDEX idx_covering
  ON orders(user_id, total, date);

-- This query does not need to access the table:
SELECT total, date FROM orders
WHERE user_id = 42;

-- EXPLAIN shows: USING COVERING INDEX

A covering index contains all the columns the query needs. SQLite answers using only the index without reading the table. Faster for frequent queries with specific columns.

-- Only indexes active users
CREATE INDEX idx_active_email
  ON users(email)
  WHERE active = 1;

-- Smaller and faster than a full index
-- Only used if the query includes WHERE active = 1

Partial indexes (WHERE) index only a subset of rows. Smaller, faster to maintain. SQLite only uses them if the query has a compatible condition.

CREATE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE active = 1;

SELECT * FROM active_users;

-- Drop
DROP VIEW IF EXISTS active_users;

CREATE VIEW saves a query the a virtual table. It does not store data — it runs the SELECT on every access. Simplifies complex queries and controls column access.

-- Index on multiple columns:
CREATE INDEX idx_orders_customer_date
ON orders (customer_id, date);

-- The ORDER of the columns matters:
-- the index works for:
--   WHERE customer_id = ?
--   WHERE customer_id = ? AND date > ?
--   ORDER BY customer_id, date

-- BUT it does NOT work for:
--   WHERE date > ?  (alone)

-- Rule: equality first,
-- range/sorting after

Composite indexes (multiple columns) follow the prefix rule: they work for filters on the first column, or first + second. Order matters — equality columns first, range/sorting after. An index (a, b) does not help with WHERE b = ?.

-- Index a computed value
CREATE INDEX idx_lower_email
  ON users(lower(email));

-- The query must use the same expression:
SELECT * FROM users
WHERE lower(email) = 'ana@mail.com';

Expression indexes index the result of a function. The query must use exactly the same expression for the index to be used. Useful for case-insensitive searches.

CREATE TRIGGER update_timestamp
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  UPDATE users
    SET updated_at = datetime('now')
    WHERE id = NEW.id;
END;

-- Drop trigger
DROP TRIGGER IF EXISTS update_timestamp;

CREATE TRIGGER runs SQL automatically on events. AFTER UPDATE, BEFORE INSERT, AFTER DELETE. NEW.column and OLD.column access the values.

-- Rebuild indexes (after many
-- changes or a collation change):
REINDEX;                      -- all
REINDEX idx_customers_email;  -- just one

-- Check DB integrity:
PRAGMA integrity_check;

-- Analyze for the optimizer:
ANALYZE;

-- Remove an unnecessary index:
DROP INDEX IF EXISTS idx_old;

-- Indexes cost on writes —
-- remove the ones that are not used

REINDEX rebuilds indexes (useful after collation changes). ANALYZE helps the optimizer choose better. DROP INDEX removes unused indexes — every index has a write cost, so keep only the necessary ones.

-- List indexes of a table
PRAGMA index_list('users');

-- Details of an index
PRAGMA index_info('idx_users_email');

-- Drop index
DROP INDEX IF EXISTS idx_users_email;

-- Rebuild all indexes
REINDEX;

PRAGMA index_list() shows the table's indexes. PRAGMA index_info() shows the index columns. REINDEX rebuilds all — useful after many deletes.

CREATE TRIGGER audit_update
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (table_name, id, field, old_value, new_value)
  VALUES ('products', NEW.id, 'price', OLD.price, NEW.price);
END;

Audit triggers record changes in a log table. OLD.value is the value before, NEW.value after. Useful for price history and status changes.

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100
  WHERE id = 1;
UPDATE accounts SET balance = balance + 100
  WHERE id = 2;

COMMIT;
-- or ROLLBACK; to undo everything

BEGIN starts, COMMIT confirms, ROLLBACK undoes. All operations between BEGIN and COMMIT are atomic — either all succeed or none are applied.

-- Compact the DB file
VACUUM;

-- VACUUM on a specific schema (3.27+):
VACUUM main;

-- Auto-vacuum (configure before creating tables):
PRAGMA auto_vacuum = FULL;
PRAGMA auto_vacuum = INCREMENTAL;

VACUUM rebuilds the file, reclaiming space from deleted data. auto_vacuum = FULL frees pages automatically. Requires exclusivity — do not use in active production.

PRAGMA synchronous = FULL;    -- safest (default)
PRAGMA synchronous = NORMAL;  -- good trade-off
PRAGMA synchronous = OFF;     -- fastest (risky!)

-- Recommended combination:
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;

synchronous controls how many fsync() calls SQLite makes. FULL is the safest. NORMAL with WAL is safe and faster. OFF can corrupt on power failure.

BEGIN DEFERRED;   -- default (lock only when needed)
BEGIN IMMEDIATE;  -- immediate write lock
BEGIN EXCLUSIVE;  -- full lock (nobody reads or writes)

-- Recommended for concurrent apps:
BEGIN IMMEDIATE;

DEFERRED (default) only acquires the lock on the first write. IMMEDIATE prevents deadlocks under concurrency. EXCLUSIVE locks everything — rarely needed.

-- Check DB integrity
PRAGMA integrity_check;
-- "ok" if everything is fine

-- Check orphaned foreign keys
PRAGMA foreign_key_check;

-- Optimize index statistics
PRAGMA optimize;

integrity_check validates the internal structure. foreign_key_check finds broken references. optimize updates statistics for the query planner. Run periodically.

-- Nested transactions:
SAVEPOINT point1;

INSERT INTO logs (msg) VALUES ('a');

SAVEPOINT point2;
INSERT INTO logs (msg) VALUES ('b');

-- Undo only up to point2:
ROLLBACK TO point2;

-- Confirm everything:
RELEASE point1;

-- Useful for "partially undoing"
-- without aborting the whole transaction

SAVEPOINT allows nested transactions. ROLLBACK TO name undoes up to that point without aborting the rest; RELEASE name confirms. Ideal for partially undoing a set of operations.

PRAGMA journal_mode = WAL;

-- Advantages:
-- Reads do not block writes
-- Writes do not block reads
-- Better performance under concurrency

PRAGMA wal_autocheckpoint = 1000;
PRAGMA synchronous = NORMAL;

WAL (Write-Ahead Logging) allows reads concurrent with writes. Faster than the DELETE mode (default) in most scenarios. Persistent across connections.

-- Wait up to 5s if the DB is locked
PRAGMA busy_timeout = 5000;

-- Check locking status
PRAGMA lock_status;

-- In code (Python example):
-- conn.execute("PRAGMA busy_timeout = 5000")

busy_timeout defines how long to wait when the DB is locked by another connection. Without it, it returns SQLITE_BUSY immediately. Essential in multi-threaded apps.

-- SQLite locks the DB during
-- writes. If another connection tries
-- to write, it gets "database is locked".

-- Wait up to 5s instead of failing right away:
PRAGMA busy_timeout = 5000;

-- With WAL, reads and writes
-- can happen simultaneously:
PRAGMA journal_mode = WAL;

-- Best practices:
--   1 connection for writing
--   busy_timeout always on
--   short transactions

SQLite allows only one writer at a time. busy_timeout makes connections wait (instead of a "database is locked" error). With WAL, reads and writes run concurrently. Keep transactions short and always use busy_timeout.

SAVEPOINT step1;
INSERT INTO t VALUES (1, 'a');

SAVEPOINT step2;
INSERT INTO t VALUES (2, 'b');

ROLLBACK TO step2;  -- undoes only step2
RELEASE step1;      -- confirms step1

SAVEPOINT creates partial restore points inside a transaction. ROLLBACK TO undoes up to the savepoint. RELEASE confirms. Useful for multi-step operations.

PRAGMA journal_mode = DELETE;   -- default (deletes journal)
PRAGMA journal_mode = WAL;      -- write-ahead log
PRAGMA journal_mode = MEMORY;   -- journal in RAM
PRAGMA journal_mode = OFF;      -- no journal (risky!)

-- See current mode:
PRAGMA journal_mode;

The journal mode controls how SQLite guarantees atomicity. DELETE is the default. WAL is the recommended one. OFF is dangerous — corruption on crash.

-- Extract a value
SELECT json_extract(data, '$.name') FROM configs;
SELECT data->>'$.email' FROM users;  -- 3.38+

-- Create JSON
SELECT json_object('id', id, 'name', name) FROM users;
SELECT json_group_array(name) FROM users;

-- Validate
SELECT json_valid('{"a":1}');  -- 1

SQLite has native JSON support (3.38+). json_extract() or the ->> operator extracts values. json_object() creates JSON. json_group_array() aggregates into an array.

PRAGMA cache_size = -64000;   -- 64 MB cache
PRAGMA temp_store = MEMORY;   -- temp in RAM
PRAGMA mmap_size = 268435456; -- 256 MB mmap
PRAGMA page_size = 4096;      -- page size

-- Check settings:
PRAGMA cache_size;
PRAGMA compile_options;

Negative cache_size = KB of memory. temp_store = MEMORY avoids disk for temporary tables. mmap_size uses memory-mapped I/O. Tune according to available RAM.

INSERT INTO stats (day, visits, uniques)
VALUES ('2025-01-15', 100, 80)
ON CONFLICT(day) DO UPDATE SET
  visits = visits + excluded.visits,
  uniques = uniques + excluded.uniques;

-- DO NOTHING (ignore the conflict):
INSERT INTO users (email) VALUES ('a@b.com')
ON CONFLICT(email) DO NOTHING;

ON CONFLICT DO UPDATE accumulates values with excluded.column. Ideal for counters and daily statistics. DO NOTHING silently ignores conflicts.

-- Set a value
SELECT json_set('{"a":1}', '$.b', 2);
-- {"a":1,"b":2}

-- Remove a key
SELECT json_remove('{"a":1,"b":2}', '$.b');

-- Type of a value
SELECT json_type('{"a":[1,2]}', '$.a');  -- "array"

-- Each element of an array
SELECT value FROM json_each('[1,2,3]');

json_set() adds/changes keys. json_remove() deletes. json_type() returns the type. json_each() is a table-valued function that iterates JSON arrays.

-- Generate a series of numbers (3.38+)
SELECT value FROM generate_series(1, 10);

-- With a step
SELECT value FROM generate_series(0, 100, 5);

-- Use in a JOIN
SELECT d.name, s.value AS day
FROM departments d
CROSS JOIN generate_series(1, 7) AS s;

generate_series() is a table-valued function that generates sequences. Useful for period reports, gap filling and tests. Returns a value column.

-- Automatically computed columns
-- (SQLite 3.31+):
CREATE TABLE items (
  price REAL,
  qty INTEGER,
  total REAL GENERATED ALWAYS
    AS (price * qty) STORED
);

-- VIRTUAL (computed on read):
--   AS (...) VIRTUAL
-- STORED (saved on disk):
--   AS (...) STORED

INSERT INTO items (price, qty) VALUES (10, 3);
SELECT total FROM items;  -- 30

-- You cannot write to them

Generated columns (SQLite 3.31+) compute the value from other columns. STORED saves to disk (can be indexed); VIRTUAL computes on read. They cannot be written directly — only read.

CREATE VIRTUAL TABLE docs USING fts5(
  title, body
);

INSERT INTO docs VALUES
  ('SQLite Guide', 'Embedded database...'),
  ('SQL Tutorial', 'Queries and filters...');

SELECT * FROM docs WHERE docs MATCH 'sqlite AND database';
SELECT * FROM docs WHERE docs MATCH 'title:guide';

FTS5 is full-text search. MATCH performs the search with boolean operators (AND, OR, NOT). Supports per-column search with column:term.

ATTACH DATABASE 'other.db' AS second;

-- Query across databases:
SELECT * FROM main.users
UNION ALL
SELECT * FROM second.users;

-- Copy data:
INSERT INTO main.archive
SELECT * FROM second.logs;

DETACH DATABASE second;

ATTACH links another DB to the current session. Reference with name.table. main is the primary DB. Allows JOINs and copies between DBs. Maximum 10 DBs at once.

-- Simple CTE:
WITH totals AS (
  SELECT customer_id, SUM(total) AS total
  FROM orders GROUP BY customer_id
)
SELECT c.name, t.total
FROM totals t JOIN customers c ON c.id = t.customer_id;

-- RECURSIVE CTE (hierarchies):
WITH RECURSIVE subs AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, s.level + 1
  FROM employees e JOIN subs s ON e.manager_id = s.id
)
SELECT * FROM subs;

CTE (WITH) creates named temporary tables. WITH RECURSIVE allows recursion — ideal for hierarchies (org charts, parent/child categories, trees). More readable than nested subqueries.

-- Ranking by relevance
SELECT title, rank FROM docs
WHERE docs MATCH 'sqlite'
ORDER BY rank;

-- Snippet with highlighting
SELECT snippet(docs, 1, '<b>', '</b>', '...', 32)
FROM docs WHERE docs MATCH 'database';

-- BM25 (custom ranking)
SELECT bm25(docs, 5.0, 1.0) FROM docs
WHERE docs MATCH 'sqlite';

rank sorts by relevance (BM25). snippet() extracts an excerpt with highlighting. bm25() lets you weight columns — the first argument gives more weight to the title.

-- SQLite limits:
-- Max columns per table: 2000
-- Max DB size: 281 TB
-- Max TEXT/BLOB length: 1 GB
-- Max JOIN depth: 1000
-- Max variables per query: 32766

PRAGMA compile_options;
PRAGMA max_page_count;

SQLite is generous with limits: DB up to 281 TB, text up to 1 GB, 2000 columns. PRAGMA compile_options shows compiled features. Enough for 99% of cases.

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

Ideal configuration for applications: WAL for concurrency, NORMAL for performance, foreign_keys ON for integrity, busy_timeout to avoid SQLITE_BUSY errors.

-- SQLite has limited ALTER TABLE
-- For complex changes:

-- 1. Create a new table
CREATE TABLE users_new (...);

-- 2. Copy the data
INSERT INTO users_new SELECT ... FROM users;

-- 3. Replace
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

SQLite's ALTER TABLE is limited (only ADD/RENAME/DROP COLUMN). For complex changes (changing types, constraints), use the 4-step pattern: create, copy, drop, rename.

-- See executed SQL
PRAGMA vdbe_listing = ON;

-- Count I/O operations
PRAGMA cache_spill = OFF;

-- Actual DB size
SELECT page_count * page_size AS bytes
FROM pragma_page_count(), pragma_page_size();

-- Free space
PRAGMA freelist_count;

freelist_count shows free pages (space recoverable with VACUUM). page_count × page_size gives the total size. vdbe_listing shows bytecode for advanced debugging.

-- SLOW: 1000 transactions
INSERT INTO t VALUES (1, 'a');
INSERT INTO t VALUES (2, 'b');
-- ...

-- FAST: 1 transaction
BEGIN;
INSERT INTO t VALUES (1, 'a');
INSERT INTO t VALUES (2, 'b');
-- ... (1000 inserts)
COMMIT;

Without a transaction, each INSERT does an individual fsync (very slow). Wrapping in BEGIN/COMMIT groups into a single fsync. A difference of 100x or more in bulk inserts.

-- 1. See the execution plan
EXPLAIN QUERY PLAN SELECT ...;

-- 2. Measure time
.timer on
SELECT ...;

-- 3. Check used indexes
PRAGMA index_list('table_name');

-- 4. Statistics
ANALYZE;
PRAGMA optimize;

EXPLAIN QUERY PLAN shows whether it uses an index or a scan. .timer on measures execution time. ANALYZE collects statistics for the query planner. PRAGMA optimize updates them.

-- VACUUM: rewrites the DB, frees
-- space from deleted rows:
VACUUM;

-- (the .db file shrinks)
-- Run outside a transaction,
-- requires free disk space

-- ANALYZE: collects statistics
-- for the query optimizer:
ANALYZE;

-- Auto-vacuum (continuous alternative):
PRAGMA auto_vacuum = FULL;
-- (must be set before
--  creating tables)

VACUUM rewrites the file and reclaims space from deleted data (the .db shrinks). ANALYZE collects statistics so the optimizer picks better indexes. auto_vacuum = FULL does continuous cleanup (set before creating tables).

-- In Python:
cursor.execute(
  "SELECT * FROM users WHERE age > ?", (18,)
)

-- In PHP (PDO):
$stmt = $pdo->prepare(
  "SELECT * FROM users WHERE city = ?"
);
$stmt->execute(['Lisbon']);

Prepared statements with ? placeholders prevent SQL injection and are faster on repeated executions. Never concatenate SQL strings with user input.

-- Native SQLite has NO encryption
-- Options:
-- • SQLCipher (fork with AES-256)
-- • SEE (official paid extension)
-- • wxSQLite3 (open-source extension)

-- Best practices:
-- • File permissions: chmod 600 db.db
-- • Do not store secrets in plain text
-- • Using WAL (the -wal file also has data!)

SQLite does not encrypt natively. SQLCipher is the most popular solution (AES-256). Careful: in WAL mode, the -wal and -shm files also contain data.

-- Safe backup with the DB in use:
sqlite3 my.db ".backup backup.db"

-- Do NOT copy the file directly
-- with the DB open (may corrupt)

-- SQL dump (portable):
sqlite3 my.db .dump > schema.sql

-- Restore from the dump:
sqlite3 new.db < schema.sql

-- .backup = binary, safe online
-- .dump = text, for migrations

Never copy the .db file with the database open (it may corrupt). Use .backup (safe online binary copy) or .dump (text SQL, portable across versions). Restore the dump with sqlite3 new.db < file.sql.

-- ✅ Good for:
-- Mobile / desktop apps
-- Prototypes and tests
-- DBs up to ~1 TB with 1 writer
-- Embedded / IoT systems
-- Local cache / settings

-- ❌ Bad for:
-- Multiple concurrent writers
-- Network access (NFS/SMB)
-- High availability / failover

SQLite is ideal for local apps, prototypes and embedded. A single file, zero configuration. It is not suitable for multiple writers over a network or systems needing automatic failover.

-- 1. Use WAL
PRAGMA journal_mode = WAL;

-- 2. Generous timeout
PRAGMA busy_timeout = 10000;

-- 3. Short transactions
BEGIN IMMEDIATE;
-- quick operations
COMMIT;

-- 4. One connection for writing
-- Multiple for reading (WAL allows it)

SQLite allows one writer at a time. WAL allows reads concurrent with writes. Keep transactions short. Use BEGIN IMMEDIATE to avoid deadlocks. One write connection, several read ones.