SQLite Cheat Sheet

The sqlite3 command line shell

sudo apt install sqlite3            # Debian, Ubuntu
sudo dnf install sqlite             # Fedora, RHEL
sqlite3 --version

macOS ships sqlite3; Homebrew has the current release

sqlite3 --version                   # the system copy, usually a few releases behind
brew install sqlite
$(brew --prefix sqlite)/bin/sqlite3 --version

Windows: winget, or the zip from sqlite.org

winget install SQLite.SQLite
sqlite3 --version
# or unzip sqlite-tools-win-x64-3530400.zip and put sqlite3.exe on the PATH

Python already includes SQLite

python3 -c "import sqlite3; print(sqlite3.sqlite_version)"
python3 -m sqlite3 app.db           # a small interactive shell, Python 3.12+

A database is one file; opening a missing file creates it

sqlite3 shop.db                     # opens or creates shop.db
sqlite3                             # a transient in-memory database
sqlite3 :memory:
sqlite3 -readonly shop.db
sqlite3 shop.db "SELECT count(*) FROM orders;"      # run one statement and exit

URI filenames: read only, immutable, shared memory

sqlite3 "file:shop.db?mode=ro"
sqlite3 "file:shop.db?immutable=1"                   # no locking at all: only for files nothing can change
sqlite3 "file:memdb1?mode=memory&cache=shared"       # an in-memory database several connections can share
ATTACH 'file:archive.db?mode=ro' AS archive;

Check what you are running

SELECT sqlite_version(), sqlite_source_id();
PRAGMA compile_options;

The dot-commands you use every day

.help                    -- every dot-command
.open shop.db            -- open another database file
.databases               -- attached databases and their files
.tables                  -- tables and views
.tables ord%             -- matching a LIKE pattern
.schema orders           -- the CREATE statements
.indexes orders          -- indexes (3.53: the pattern matches index names)
.quit                    -- or .exit, or Ctrl+D

Output modes

.mode table              -- ASCII boxes
.mode box                -- Unicode boxes, the interactive default from 3.53
.mode markdown
.mode json
.mode csv
.mode line               -- one column per line, for wide rows
.headers on
SELECT id, name, country FROM customers LIMIT 2;

Timing, changes and query statistics

.timer on                -- or .timer once (3.53+) for the next statement only
.changes on              -- rows changed by each statement
.eqp on                  -- print the query plan before each result
.stats on

Send output to a file, run a script

.once report.csv         -- the next result only
.output all.txt          -- every result until .output stdout
.read schema.sql         -- run the statements in a file
.excel                   -- the next result opens in your spreadsheet program
.www                     -- the next result opens in your web browser (3.47+)

Scripts from the command line

sqlite3 shop.db < schema.sql
sqlite3 -bail shop.db < migrate.sql          # stop at the first error
sqlite3 -csv -header shop.db "SELECT * FROM orders" > orders.csv
sqlite3 -json shop.db "SELECT * FROM products LIMIT 2"
sqlite3 shop.db migrate.sql                  # 3.53: *.sql arguments that are files are run as scripts

Named parameters inside the shell

.parameter init
.parameter set :country 'RO'
SELECT name FROM customers WHERE country = :country;
.parameter list

A startup file

-- ~/.sqliterc runs every time the interactive shell starts
.mode box
.headers on
.timer on
.nullvalue NULL
PRAGMA foreign_keys = ON;

Built-in helpers worth knowing

.expert                  -- then a query: suggests indexes for it
.lint fkey-indexes       -- foreign keys without an index on the child column
.dbinfo                  -- page size, encoding, schema version
.recover                 -- salvage what it can from a damaged file
.sha3sum                 -- a hash of the content, to compare two databases
.shell ls -la            -- run a shell command

Show every shell setting

.show

Five storage classes; the column type is only a preference

CREATE TABLE t (a INTEGER, b TEXT, c);
INSERT INTO t VALUES ('42', 42, x'00ff');
SELECT a, typeof(a), b, typeof(b), typeof(c) FROM t;

How a declared type becomes an affinity

The "STRING" trap

CREATE TABLE codes (code STRING);
INSERT INTO codes VALUES ('0123');
SELECT code, typeof(code) FROM codes;

Enforce the declared types

CREATE TABLE catalog (
  id INTEGER PRIMARY KEY,
  sku TEXT NOT NULL UNIQUE,
  price_cents INTEGER NOT NULL,
  attrs ANY
) STRICT;
INSERT INTO catalog (sku, price_cents) VALUES ('OAK-1', 'cheap');

STRICT and WITHOUT ROWID together

CREATE TABLE tags (post_id INTEGER, tag TEXT, PRIMARY KEY (post_id, tag)) STRICT, WITHOUT ROWID;

No boolean, date or decimal types: pick a convention

TRUE, FALSE and comparisons

SELECT TRUE, FALSE, 1 = TRUE, 'yes' = TRUE;

Casting and checking types

SELECT CAST('12.5kg' AS REAL), CAST('abc' AS INTEGER), CAST(12.9 AS INTEGER);
SELECT typeof(1), typeof(1.0), typeof('1'), typeof(NULL), typeof(x'01');

Count values by storage class

SELECT typeof(price_cents) AS type, count(*) FROM products GROUP BY 1;
SELECT typeof(created_at) AS type, count(*) FROM orders GROUP BY 1;

A typical table

CREATE TABLE IF NOT EXISTS orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers (id) ON DELETE CASCADE,
  status TEXT NOT NULL DEFAULT 'new' CHECK (status IN ('new', 'paid', 'shipped', 'refunded')),
  total_cents INTEGER NOT NULL DEFAULT 0 CHECK (total_cents >= 0),
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
) STRICT;
CREATE INDEX orders_customer ON orders (customer_id, created_at);

INTEGER PRIMARY KEY is the rowid

CREATE TABLE a (id INTEGER PRIMARY KEY, v TEXT);      -- id is an alias for the rowid: no extra index
CREATE TABLE b (id INT PRIMARY KEY, v TEXT);          -- INT is not INTEGER: a separate unique index
CREATE TABLE c (id INTEGER PRIMARY KEY AUTOINCREMENT, v TEXT);
INSERT INTO a (v) VALUES ('x') RETURNING id, rowid;

Generated columns

CREATE TABLE people (
  first TEXT NOT NULL,
  last TEXT NOT NULL,
  full_name TEXT GENERATED ALWAYS AS (first || ' ' || last) VIRTUAL,
  email TEXT,
  email_domain TEXT AS (substr(email, instr(email, '@') + 1)) STORED
);
CREATE INDEX people_domain ON people (email_domain);

WITHOUT ROWID tables

CREATE TABLE settings (key TEXT PRIMARY KEY, value TEXT) WITHOUT ROWID;

Conflict clauses on constraints

CREATE TABLE emails (address TEXT UNIQUE ON CONFLICT IGNORE);
INSERT INTO emails VALUES ('a@x.com'), ('a@x.com');     -- second row silently skipped
SELECT count(*) FROM emails;

Rename and add columns

ALTER TABLE orders RENAME TO sales;
ALTER TABLE sales RENAME COLUMN total_cents TO amount_cents;
ALTER TABLE sales ADD COLUMN note TEXT;
ALTER TABLE sales ADD COLUMN channel TEXT NOT NULL DEFAULT 'web';

Drop a column

ALTER TABLE sales DROP COLUMN note;

Set or drop NOT NULL, add or drop a CHECK

ALTER TABLE sales ALTER COLUMN channel SET NOT NULL;
ALTER TABLE sales ALTER COLUMN note DROP NOT NULL;
ALTER TABLE sales ADD CONSTRAINT amount_positive CHECK (amount_cents >= 0);
ALTER TABLE sales DROP CONSTRAINT amount_positive;

Anything else: the 12 step rebuild

PRAGMA foreign_keys = OFF;
BEGIN;
CREATE TABLE sales_new (id INTEGER PRIMARY KEY, amount_cents INTEGER NOT NULL, created_at TEXT NOT NULL) STRICT;
INSERT INTO sales_new (id, amount_cents, created_at) SELECT id, amount_cents, created_at FROM sales;
DROP TABLE sales;
ALTER TABLE sales_new RENAME TO sales;
-- recreate indexes, triggers and views here
PRAGMA foreign_key_check;
COMMIT;
PRAGMA foreign_keys = ON;

CREATE TABLE ... AS SELECT

CREATE TABLE ro_customers AS SELECT id, name, email FROM customers WHERE country = 'RO';
SELECT count(*) FROM ro_customers;

A temporary table for the current connection

CREATE TEMP TABLE big_orders AS SELECT id, total_cents FROM orders WHERE total_cents > 30000;
SELECT count(*) FROM big_orders;

A good default block for applications

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -65536;          -- 64 MiB
PRAGMA temp_store = MEMORY;

Read a setting

PRAGMA journal_mode;
PRAGMA foreign_keys;
SELECT * FROM pragma_busy_timeout;

What each common setting does

Integrity and foreign key checks

PRAGMA integrity_check;          -- full check, reads every page
PRAGMA quick_check;              -- skips index content checks, much faster
PRAGMA foreign_key_check;        -- rows that break a foreign key

Keep planner statistics fresh

PRAGMA optimize;                 -- before closing a connection, or every few hours
PRAGMA optimize = 0x10002;       -- right after opening a long lived connection
PRAGMA analysis_limit = 400;

Schema versioning for migrations

PRAGMA user_version;
PRAGMA user_version = 7;

Columns, indexes and foreign keys of a table

PRAGMA table_info(orders);
PRAGMA index_list(orders);
PRAGMA foreign_key_list(orders);
SELECT name, type, "notnull", pk FROM pragma_table_info('orders');

Everything in the schema

SELECT type, name, tbl_name FROM sqlite_schema ORDER BY type, name;
SELECT name FROM pragma_table_list WHERE type = 'table' AND schema = 'main';

Foreign keys of a table

SELECT "table", "from", "to", on_delete FROM pragma_foreign_key_list('order_items');

Index columns, including hidden ones

SELECT * FROM pragma_index_xinfo('orders_customer_created');

The basic shape

SELECT id, name, country
FROM customers
WHERE country IN ('RO', 'DE') AND created_at >= '2026-01-01'
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;

LIKE is case insensitive for ASCII; GLOB is case sensitive with shell wildcards

SELECT name FROM customers WHERE name LIKE 'an%';          -- matches Ana
SELECT sku FROM products WHERE sku GLOB '[A-Z][A-Z][A-Z]-*';
SELECT name FROM customers WHERE email LIKE '%\_%' ESCAPE '\';

NULL handling

SELECT name FROM customers WHERE phone IS NULL;
SELECT coalesce(phone, email, 'none') FROM customers;
SELECT nullif(discount, 0) FROM orders;
SELECT a IS b FROM t;              -- NULL-safe equality: NULL IS NULL is 1
SELECT * FROM t ORDER BY score DESC NULLS LAST;

CASE, iif() and if()

SELECT sku,
  CASE WHEN price_cents >= 10000 THEN 'premium' WHEN price_cents >= 3000 THEN 'standard' ELSE 'budget' END AS tier,
  iif(price_cents > 5000, 'yes', 'no') AS free_shipping
FROM products;
SELECT sku, if(price_cents > 10000, 'ships free') FROM products;   -- two argument form: NULL when false

Keyset pagination

SELECT id, name, created_at FROM customers
WHERE (created_at, id) < (?1, ?2)                 -- the last row of the previous page
ORDER BY created_at DESC, id DESC
LIMIT 20;

DISTINCT and duplicates

SELECT DISTINCT country FROM customers ORDER BY 1;
SELECT email, count(*) FROM customers GROUP BY email HAVING count(*) > 1;

A random row

SELECT * FROM products ORDER BY random() LIMIT 1;                    -- fine for small tables
SELECT * FROM products WHERE rowid >= (abs(random()) % (SELECT max(rowid) FROM products)) + 1 LIMIT 1;

Case insensitive comparisons with COLLATE NOCASE

SELECT * FROM customers WHERE email = 'ANA@EXAMPLE.COM' COLLATE NOCASE;
CREATE INDEX customers_email_nocase ON customers (email COLLATE NOCASE);

Top N

SELECT sku, price_cents / 100.0 AS price FROM products ORDER BY price_cents DESC LIMIT 3;

BETWEEN, IN and row values

SELECT count(*) FROM orders WHERE total_cents BETWEEN 10000 AND 30000;
SELECT count(*) FROM order_items WHERE (order_id, product_id) IN (VALUES (4, 8), (8, 4), (1, 3));

INNER and LEFT JOIN

SELECT o.id, c.name, o.status
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid';

SELECT c.name, count(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;

RIGHT and FULL OUTER JOIN

SELECT c.name, o.id
FROM orders o
RIGHT JOIN customers c ON c.id = o.customer_id;

SELECT a.sku, b.sku FROM warehouse_a a FULL OUTER JOIN warehouse_b b ON b.sku = a.sku;

USING, NATURAL and CROSS JOIN

SELECT * FROM order_items JOIN order_items_archive USING (order_id, product_id);   -- only when both tables have the columns
SELECT * FROM sizes CROSS JOIN colors;                        -- every combination

Rows with, or without, a match

SELECT name FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'refunded');
SELECT name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Self join

SELECT e.name, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

UPDATE FROM

UPDATE orders
SET total_cents = t.total
FROM (SELECT order_id, sum(qty * price_cents) AS total FROM order_items GROUP BY order_id) AS t
WHERE t.order_id = orders.id;

DELETE with a join condition

DELETE FROM order_items
WHERE order_id IN (SELECT id FROM orders WHERE status = 'refunded');

Join four tables

SELECT o.id, c.name, p.sku, i.qty
FROM order_items i
JOIN orders o ON o.id = i.order_id
JOIN customers c ON c.id = o.customer_id
JOIN products p ON p.id = i.product_id
ORDER BY o.id, p.sku
LIMIT 4;

Rows with no match: LEFT JOIN ... IS NULL

SELECT p.sku FROM products p
LEFT JOIN order_items i ON i.product_id = p.id
WHERE i.order_id IS NULL;

GROUP BY and HAVING

SELECT status, count(*) AS n, sum(total_cents) / 100.0 AS revenue
FROM orders
GROUP BY status
HAVING count(*) > 5
ORDER BY revenue DESC;

sum() versus total()

SELECT sum(x), total(x) FROM (SELECT NULL AS x);
SELECT sum(9223372036854775807), total(9223372036854775807);

Bare columns with min() and max()

SELECT customer_id, max(created_at), id FROM orders GROUP BY customer_id;

Join values into a string

SELECT customer_id, group_concat(status, ', ') FROM orders GROUP BY customer_id;
SELECT customer_id, string_agg(status, ', ' ORDER BY created_at) FROM orders GROUP BY customer_id;

FILTER on one aggregate

SELECT
  count(*) AS all_orders,
  count(*) FILTER (WHERE status = 'paid') AS paid,
  sum(total_cents) FILTER (WHERE status <> 'refunded') AS kept_cents
FROM orders;

Aggregate rows into JSON

SELECT c.name, json_group_array(json_object('id', o.id, 'status', o.status)) AS orders
FROM customers c JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;

Median and percentiles

SELECT median(total_cents), percentile(total_cents, 90), percentile_cont(total_cents, 0.9) FROM orders;

Revenue per month

SELECT strftime('%Y-%m', created_at) AS month, count(*) AS orders, sum(total_cents) / 100.0 AS revenue
FROM orders WHERE status IN ('paid', 'shipped')
GROUP BY month ORDER BY month DESC LIMIT 3;

count(*), count(column) and count(DISTINCT)

SELECT count(*), count(DISTINCT customer_id), count(DISTINCT status) FROM orders;

Scalar, IN and derived tables

SELECT name, (SELECT count(*) FROM orders o WHERE o.customer_id = c.id) AS orders FROM customers c;
SELECT * FROM products WHERE id IN (SELECT product_id FROM order_items WHERE qty >= 3);
SELECT avg(n) FROM (SELECT customer_id, count(*) AS n FROM orders GROUP BY customer_id);

WITH: name a subquery

WITH paid AS (
  SELECT customer_id, sum(total_cents) AS cents FROM orders WHERE status = 'paid' GROUP BY customer_id
)
SELECT c.name, p.cents / 100.0 AS spent
FROM paid p JOIN customers c ON c.id = p.customer_id
ORDER BY spent DESC LIMIT 5;

A number series

WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 5)
SELECT i FROM n;

Every day in a range, for reports with no gaps

WITH RECURSIVE d(day) AS (
  SELECT date('2026-09-01')
  UNION ALL SELECT date(day, '+1 day') FROM d WHERE day < '2026-09-30'
)
SELECT d.day, count(o.id) AS orders
FROM d LEFT JOIN orders o ON date(o.created_at) = d.day
GROUP BY d.day;

Walk a tree

WITH RECURSIVE chain(id, name, manager_id, depth) AS (
  SELECT id, name, manager_id, 0 FROM employees WHERE id = 7
  UNION ALL
  SELECT e.id, e.name, e.manager_id, chain.depth + 1 FROM employees e JOIN chain ON e.id = chain.manager_id
)
SELECT * FROM chain;

Control whether a CTE is materialized

WITH big AS MATERIALIZED (SELECT * FROM events WHERE type = 'click')
SELECT count(*) FROM big WHERE at > '2026-09-01';
WITH small AS NOT MATERIALIZED (SELECT * FROM products) SELECT * FROM small WHERE id = 3;

Customers who spent more than the average

WITH spend AS (SELECT customer_id, sum(total_cents) AS cents FROM orders GROUP BY customer_id)
SELECT c.name, s.cents / 100.0 AS spent
FROM spend s JOIN customers c ON c.id = s.customer_id
WHERE s.cents > (SELECT avg(cents) FROM spend)
ORDER BY s.cents DESC;

Update from an aggregate

UPDATE orders SET total_cents = (
  SELECT coalesce(sum(qty * price_cents), 0) FROM order_items WHERE order_id = orders.id
);
SELECT changes();

row_number, rank and dense_rank

SELECT customer_id, id, total_cents,
  row_number() OVER (PARTITION BY customer_id ORDER BY total_cents DESC) AS rn
FROM orders;

Latest row per group

SELECT * FROM (
  SELECT o.*, row_number() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
  FROM orders o
) WHERE rn = 1;

Running total and moving average

SELECT day, amount,
  sum(amount) OVER (ORDER BY day) AS running,
  avg(amount) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg7
FROM daily_sales;

Compare with the previous row

SELECT day, amount,
  amount - lag(amount) OVER (ORDER BY day) AS change,
  lead(day) OVER (ORDER BY day) AS next_day
FROM daily_sales;

Named windows and frame exclusion

SELECT day, amount,
  avg(amount) OVER w AS avg_neighbours
FROM daily_sales
WINDOW w AS (ORDER BY day ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW);

Share of total

SELECT status, count(*) AS n, round(100.0 * count(*) / sum(count(*)) OVER (), 1) AS pct
FROM orders GROUP BY status;

Running total per customer

SELECT customer_id, id, total_cents,
  sum(total_cents) OVER (PARTITION BY customer_id ORDER BY created_at) AS running
FROM orders WHERE customer_id = 1;

Rank products by revenue

SELECT p.sku, sum(i.qty * i.price_cents) / 100.0 AS revenue,
  rank() OVER (ORDER BY sum(i.qty * i.price_cents) DESC) AS rnk
FROM order_items i JOIN products p ON p.id = i.product_id
GROUP BY p.sku LIMIT 5;

Quartiles with ntile()

SELECT quartile, min(total_cents), max(total_cents), count(*)
FROM (SELECT total_cents, ntile(4) OVER (ORDER BY total_cents) AS quartile FROM orders)
GROUP BY quartile;

The everyday string functions

SELECT length('Ștefan'), octet_length('Ștefan'), upper('abc'), substr('SQLite', 1, 3),
  instr('a@b.com', '@'), replace('a-b-c', '-', '/'), trim('  x  '), 'a' || 'b';

Formatting and concatenation

SELECT format('%s ordered %d items for %.2f', 'Ana', 3, 59.5);
SELECT concat('a', NULL, 'b'), concat_ws('-', '2026', '09', '29');

Characters, escapes and Unicode

SELECT char(72, 105), unicode('A'), unistr('café'), quote('it''s');

Blobs and hex

SELECT hex('SQLite'), unhex('53514C697465'), lower(hex(randomblob(16)));

Rounding and integer division

SELECT 7 / 2, 7 / 2.0, 7 % 3, round(2.5), round(3.14159, 2), abs(-4), max(3, 9, 5);

Math functions

SELECT sqrt(2), pow(2, 10), ln(10), log10(1000), log2(8), floor(-1.5), ceil(1.2), pi();

After a write

INSERT INTO customers (name, email, country, created_at) VALUES ('Tom', 'tom@example.com', 'UK', datetime('now'));
SELECT last_insert_rowid(), changes(), total_changes();

Types and literals

SELECT typeof(3.0), typeof('3'), quote(x'00ff'), quote(NULL), coalesce(NULL, 'default');

Split an email and pad columns

SELECT email, substr(email, instr(email, '@') + 1) AS domain FROM customers LIMIT 3;
SELECT format('%-7s %8.2f', sku, price_cents / 100.0) FROM products LIMIT 3;

Insert rows and get the new ids back

INSERT INTO customers (name, email, country, created_at)
VALUES ('Ana Pop', 'ana@example.org', 'RO', datetime('now')),
       ('Radu Ionescu', 'radu@example.org', 'RO', datetime('now'))
RETURNING id, name;

Insert from a query

INSERT INTO archive_orders (id, customer_id, status, created_at)
SELECT id, customer_id, status, created_at FROM orders WHERE created_at < '2026-01-01';

Insert or update

INSERT INTO page_views (page, day, views) VALUES ('/pricing', '2026-09-29', 1)
ON CONFLICT (page, day) DO UPDATE SET views = views + 1;

INSERT INTO products (sku, name, price_cents) VALUES ('OAK-1', 'Oak table', 21900)
ON CONFLICT (sku) DO UPDATE SET name = excluded.name, price_cents = excluded.price_cents
WHERE excluded.price_cents <> products.price_cents;

Skip duplicates

INSERT INTO tags (name) VALUES ('oak') ON CONFLICT DO NOTHING;
INSERT OR IGNORE INTO tags (name) VALUES ('oak');

Upsert from a SELECT needs a WHERE

INSERT INTO stock (sku, qty)
SELECT sku, sum(qty) FROM deliveries WHERE true GROUP BY sku
ON CONFLICT (sku) DO UPDATE SET qty = stock.qty + excluded.qty;

REPLACE deletes, then inserts

REPLACE INTO settings (key, value) VALUES ('theme', 'dark');

Update and delete with RETURNING

UPDATE orders SET status = 'shipped' WHERE id = 42 RETURNING id, status;
DELETE FROM sessions WHERE expires_at < unixepoch() RETURNING id;

Conflict resolution on UPDATE

UPDATE OR IGNORE customers SET email = lower(email);
UPDATE OR ROLLBACK accounts SET balance = balance - 100 WHERE id = 1;

Empty a table

DELETE FROM logs;           -- SQLite has no TRUNCATE; without WHERE it uses a fast truncate path
VACUUM;                     -- give the space back to the file system

Fast bulk inserts

BEGIN;
INSERT INTO events (at, type) VALUES (?, ?);     -- one prepared statement, executed many times
COMMIT;

Upsert and see the result

INSERT INTO products (sku, name, price_cents) VALUES ('OAK-1', 'Oak table', 21900)
ON CONFLICT (sku) DO UPDATE SET price_cents = excluded.price_cents
RETURNING id, sku, price_cents;

Delete and return what went

DELETE FROM order_items WHERE product_id = 10 RETURNING order_id, qty;

Current date and time, in UTC

SELECT date('now'), time('now'), datetime('now'), unixepoch(), julianday('now');

Modifiers shift and truncate

SELECT date('now', 'start of month');
SELECT date('now', 'start of month', '+1 month', '-1 day');       -- last day of this month
SELECT datetime('now', '-7 days');
SELECT date('now', '-6 days', 'weekday 1');                        -- Monday of this week
SELECT datetime('2026-09-29 14:05', '+90 minutes');

Unix time in and out

SELECT datetime(1790690712, 'unixepoch');
SELECT datetime(1790690712123 / 1000.0, 'unixepoch', 'subsec');        -- milliseconds (3.42+)
SELECT unixepoch('2026-09-29 14:05:12');
SELECT datetime(1790690712, 'auto');                                   -- detects unix or julian day

strftime()

SELECT strftime('%Y-%m', created_at) AS month, count(*) FROM orders GROUP BY month;
SELECT strftime('%d.%m.%Y %H:%M', 'now');
SELECT strftime('%w', 'now'), strftime('%j', 'now'), strftime('%s', 'now');

ISO weeks

SELECT strftime('%G-W%V', '2026-09-29');

Format codes

Differences between dates

SELECT julianday('2026-09-29') - julianday('2026-01-01') AS days;
SELECT (unixepoch('2026-09-29 18:00') - unixepoch('2026-09-29 09:30')) / 3600.0 AS hours;
SELECT timediff('2026-09-29', '2025-06-15');

Local time

SELECT datetime('now', 'localtime');
SELECT datetime('2026-09-29 11:00:00', 'utc');     -- treat the value as local time and convert to UTC

Month arithmetic at the end of a month

SELECT date('2026-01-31', '+1 month');
SELECT date('2026-01-31', '+1 month', 'floor');

The last 30 days, and a weekday histogram

SELECT count(*) FROM orders WHERE created_at >= datetime('2026-09-29', '-30 days');
SELECT strftime('%w', created_at) AS weekday, count(*) FROM orders GROUP BY weekday;

-> and ->>

SELECT '{"a": {"b": [1, 2, 3]}}' -> '$.a' AS obj,
       '{"a": {"b": [1, 2, 3]}}' ->> '$.a.b[1]' AS val,
       '{"a": {"b": [1, 2, 3]}}' ->> '$.a.b[#-1]' AS last;

Filter on a JSON field

SELECT sku, tags FROM products WHERE tags ->> '$[0]' = 'wood';
SELECT sku FROM products WHERE EXISTS (SELECT 1 FROM json_each(tags) WHERE value = 'kitchen');

Expand an array or object into rows

SELECT p.sku, t.value AS tag
FROM products p, json_each(p.tags) t;
SELECT key, value, type FROM json_each('{"a": 1, "b": [2], "c": null}');

Build JSON

SELECT json_object('id', id, 'sku', sku, 'price', price_cents / 100.0) FROM products LIMIT 1;
SELECT json_array(1, 'two', NULL), json('{ "a" : 1 }');

Change JSON

SELECT json_set('{"a":1}', '$.b', 2, '$.a', 10);       -- create or replace
SELECT json_insert('{"a":1}', '$.a', 99, '$.c', 3);     -- only create
SELECT json_replace('{"a":1}', '$.a', 5, '$.z', 0);     -- only replace
SELECT json_remove('{"a":1,"b":2}', '$.b');
SELECT json_patch('{"a":1,"b":{"c":2}}', '{"b":{"c":null,"d":4}}');

Update a JSON column in place

UPDATE products SET tags = json_insert(tags, '$[#]', 'sale') WHERE sku = 'OAK-1';
SELECT tags FROM products WHERE sku = 'OAK-1';

Validate and inspect

SELECT json_valid('{"a":1}'), json_valid('{a:1}'), json_type('[1,2]'), json_array_length('[1,2,3]');
SELECT json_error_position('{"a":1 "b":2}');
SELECT json_valid('{"a":1,}'), json_valid('{"a":1,}', 2);

JSONB: the binary form

CREATE TABLE docs (id INTEGER PRIMARY KEY, body BLOB CHECK (json_valid(body, 8)));
INSERT INTO docs (body) VALUES (jsonb('{"type":"invoice","total":120}'));
SELECT body ->> '$.type', json(body) FROM docs;

Index a JSON field

CREATE INDEX docs_type ON docs (body ->> '$.type');
SELECT id FROM docs WHERE body ->> '$.type' = 'invoice';

Newer JSON functions

SELECT json_pretty('{"a":[1,2]}');
SELECT json_array_insert('[1,3]', '$[1]', 2);

Count values inside JSON arrays

SELECT value AS tag, count(*) AS products
FROM products, json_each(products.tags)
GROUP BY tag ORDER BY products DESC, tag LIMIT 4;

One JSON object from many rows

SELECT json_group_object(sku, price_cents) FROM products WHERE price_cents > 10000;

Group writes into one transaction

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

The three kinds of BEGIN

Savepoints: nested transactions

BEGIN;
INSERT INTO orders (customer_id, status, created_at) VALUES (1, 'new', datetime('now'));
SAVEPOINT items;
INSERT INTO order_items (order_id, product_id, qty, price_cents) VALUES (last_insert_rowid(), 3, 1, 4500);
ROLLBACK TO items;          -- undo only the part after the savepoint
RELEASE items;
COMMIT;

database is locked

PRAGMA busy_timeout = 5000;

Who can do what at the same time

Read your own snapshot

BEGIN;
SELECT count(*) FROM orders;       -- the read starts a snapshot
SELECT sum(total_cents) FROM orders;  -- same snapshot, even if others commit meanwhile
COMMIT;

ROLLBACK undoes everything since BEGIN

BEGIN;
DELETE FROM order_items;
SELECT count(*) FROM order_items;
ROLLBACK;
SELECT count(*) FROM order_items;

Create, fill and search

CREATE VIRTUAL TABLE docs_fts USING fts5(title, body);
INSERT INTO docs_fts (title, body) VALUES ('Oak tables', 'How to oil an oak dining table'), ('Chairs', 'Ash and oak chairs compared');
SELECT title FROM docs_fts WHERE docs_fts MATCH 'oak' ORDER BY rank;

Query syntax

SELECT title FROM docs_fts WHERE docs_fts MATCH 'oak AND table';
SELECT title FROM docs_fts WHERE docs_fts MATCH '"dining table"';        -- phrase
SELECT title FROM docs_fts WHERE docs_fts MATCH 'tab*';                  -- prefix
SELECT title FROM docs_fts WHERE docs_fts MATCH 'title:oak';             -- one column
SELECT title FROM docs_fts WHERE docs_fts MATCH 'NEAR(oak table, 3)';
SELECT title FROM docs_fts WHERE docs_fts MATCH 'oak NOT chairs';

Highlight and snippet

SELECT highlight(docs_fts, 1, '<b>', '</b>') FROM docs_fts WHERE docs_fts MATCH 'oak';
SELECT snippet(docs_fts, 1, '[', ']', '...', 8) FROM docs_fts WHERE docs_fts MATCH 'dining';

Tokenizers

CREATE VIRTUAL TABLE t1 USING fts5(body, tokenize = 'porter unicode61 remove_diacritics 2');
CREATE VIRTUAL TABLE t2 USING fts5(body, tokenize = 'trigram');        -- substring search, LIKE '%x%' style

An external content table kept in sync by triggers

CREATE VIRTUAL TABLE articles_fts USING fts5(title, body, content='articles', content_rowid='id');
CREATE TRIGGER articles_ai AFTER INSERT ON articles BEGIN
  INSERT INTO articles_fts (rowid, title, body) VALUES (new.id, new.title, new.body);
END;
CREATE TRIGGER articles_ad AFTER DELETE ON articles BEGIN
  INSERT INTO articles_fts (articles_fts, rowid, title, body) VALUES ('delete', old.id, old.title, old.body);
END;
CREATE TRIGGER articles_au AFTER UPDATE ON articles BEGIN
  INSERT INTO articles_fts (articles_fts, rowid, title, body) VALUES ('delete', old.id, old.title, old.body);
  INSERT INTO articles_fts (rowid, title, body) VALUES (new.id, new.title, new.body);
END;
INSERT INTO articles_fts (articles_fts) VALUES ('rebuild');

Single column and composite indexes

CREATE INDEX orders_customer ON orders (customer_id);
CREATE INDEX orders_status_created ON orders (status, created_at DESC);
CREATE UNIQUE INDEX customers_email ON customers (email);
DROP INDEX IF EXISTS orders_customer;

Equality columns first, then the sort, then ranges

-- WHERE customer_id = ? AND status = ? AND created_at > ? ORDER BY created_at
CREATE INDEX orders_lookup ON orders (customer_id, status, created_at);

Covering index: answer from the index alone

CREATE INDEX orders_cover ON orders (customer_id, status, total_cents);
EXPLAIN QUERY PLAN SELECT status, total_cents FROM orders WHERE customer_id = 3;

Partial index

CREATE INDEX orders_open ON orders (created_at) WHERE status IN ('new', 'paid');
SELECT id FROM orders WHERE status IN ('new', 'paid') AND created_at > '2026-09-01';

Expression index

CREATE INDEX customers_email_lower ON customers (lower(email));
SELECT id FROM customers WHERE lower(email) = 'ana@example.com';
CREATE INDEX orders_day ON orders (date(created_at));

List and inspect indexes

SELECT name, tbl_name, sql FROM sqlite_schema WHERE type = 'index' AND sql IS NOT NULL;
PRAGMA index_list(orders);
PRAGMA index_info(orders_status_created);

Statistics for the planner

ANALYZE;
PRAGMA optimize;
SELECT * FROM sqlite_stat1 LIMIT 3;

Rebuild indexes

REINDEX;                             -- every index
REINDEX orders;                      -- the indexes of one table
REINDEX EXPRESSIONS;                 -- expression indexes only (3.53+)

Plan with and without a usable index

EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 3 ORDER BY created_at;

EXPLAIN QUERY PLAN

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE status = 'paid' ORDER BY created_at DESC;

What the plan lines mean

The fix for the plan above

CREATE INDEX orders_status_created ON orders (status, created_at);
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE status = 'paid' ORDER BY created_at DESC;

Force or forbid an index

SELECT * FROM orders INDEXED BY orders_status_created WHERE status = 'paid';
SELECT * FROM orders NOT INDEXED WHERE status = 'paid';

Hints about selectivity

SELECT * FROM orders WHERE likely(status = 'shipped') AND customer_id = 3;
SELECT * FROM events WHERE unlikely(type = 'error');
SELECT * FROM t WHERE +a = 5;         -- the unary + stops SQLite from using an index on a

Let the shell suggest an index

.expert
SELECT * FROM orders WHERE customer_id = 3 AND status = 'paid' ORDER BY created_at;

Which table drives the join

EXPLAIN QUERY PLAN
SELECT c.name, o.id FROM customers c JOIN orders o ON o.customer_id = c.id WHERE c.country = 'RO';

Turn on WAL

PRAGMA journal_mode = WAL;

The files you will see

Checkpoints

PRAGMA wal_checkpoint(PASSIVE);      -- copy what it can without waiting
PRAGMA wal_checkpoint(TRUNCATE);     -- copy everything, then empty the -wal file
PRAGMA wal_autocheckpoint = 1000;    -- pages; the default
PRAGMA journal_size_limit = 67108864;

What SQLite handles well

One writer, many readers in application code

-- open one read-write connection for writes, used by one thread or a queue
-- open a pool of read only connections (file:app.db?mode=ro) for reads
-- every write transaction: BEGIN IMMEDIATE ... COMMIT, kept short

Leave WAL mode

PRAGMA journal_mode = DELETE;

How big, and how much is free

SELECT page_count * page_size AS bytes, freelist_count * page_size AS free_bytes
FROM pragma_page_count, pragma_page_size, pragma_freelist_count;

Shrink and defragment

VACUUM;
VACUUM INTO 'compact-copy.db';

Reclaim space gradually

PRAGMA auto_vacuum = INCREMENTAL;    -- takes effect for a new database, or after one VACUUM
VACUUM;
PRAGMA incremental_vacuum(500);      -- give back up to 500 free pages now

Which tables and indexes use the space

SELECT name, sum(pgsize) / 1024 AS kib FROM dbstat GROUP BY name ORDER BY kib DESC LIMIT 5;

Page size

PRAGMA page_size;              -- 4096 by default
PRAGMA page_size = 8192;       -- only before the first table is created, or before VACUUM
VACUUM;

Rows per table in one query

SELECT 'customers' AS tbl, count(*) FROM customers
UNION ALL SELECT 'orders', count(*) FROM orders
UNION ALL SELECT 'order_items', count(*) FROM order_items
UNION ALL SELECT 'products', count(*) FROM products;

A saved query

CREATE VIEW paid_orders AS
SELECT o.id, c.name, o.total_cents, o.created_at
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid';
SELECT * FROM paid_orders ORDER BY created_at DESC LIMIT 5;

Temporary views and tables

CREATE TEMP VIEW recent AS SELECT * FROM orders WHERE created_at > date('now', '-7 days');
CREATE TEMP TABLE scratch AS SELECT id FROM orders WHERE status = 'new';

Keep updated_at current

CREATE TRIGGER products_touch AFTER UPDATE ON products
WHEN old.updated_at IS new.updated_at
BEGIN
  UPDATE products SET updated_at = datetime('now') WHERE id = new.id;
END;

Audit log

CREATE TABLE audit (at TEXT DEFAULT CURRENT_TIMESTAMP, tbl TEXT, row_id INTEGER, action TEXT, old_value TEXT);
CREATE TRIGGER orders_audit AFTER UPDATE OF status ON orders
BEGIN
  INSERT INTO audit (tbl, row_id, action, old_value) VALUES ('orders', old.id, 'status', old.status);
END;

Reject bad writes with RAISE

CREATE TRIGGER orders_no_backwards BEFORE UPDATE OF status ON orders
WHEN old.status = 'shipped' AND new.status = 'new'
BEGIN
  SELECT RAISE(ABORT, 'a shipped order cannot go back to new');
END;

A writable view

CREATE TRIGGER paid_orders_delete INSTEAD OF DELETE ON paid_orders
BEGIN
  UPDATE orders SET status = 'refunded' WHERE id = old.id;
END;

A summary view

CREATE VIEW customer_totals AS
SELECT c.id, c.name, count(o.id) AS orders, coalesce(sum(o.total_cents), 0) AS cents
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;
SELECT name, orders, cents / 100.0 AS spent FROM customer_totals ORDER BY cents DESC LIMIT 3;

Keep a total in step with its items

CREATE TRIGGER items_total AFTER INSERT ON order_items
BEGIN
  UPDATE orders SET total_cents = total_cents + new.qty * new.price_cents WHERE id = new.order_id;
END;
INSERT INTO order_items VALUES (2, 9, 2, 900);
SELECT total_cents FROM orders WHERE id = 2;

List views and triggers

SELECT type, name, tbl_name FROM sqlite_schema WHERE type IN ('view', 'trigger');

Query several database files at once

ATTACH DATABASE 'archive.db' AS archive;
SELECT count(*) FROM archive.orders;
SELECT * FROM main.orders UNION ALL SELECT * FROM archive.orders;
DETACH DATABASE archive;
PRAGMA database_list;

Copy a table between files

ATTACH 'copy.db' AS copy;
CREATE TABLE copy.customers AS SELECT * FROM main.customers;

Schema names

SELECT * FROM main.sqlite_schema;
SELECT * FROM temp.sqlite_schema;
ATTACH ':memory:' AS mem;
CREATE TABLE mem.lookup (k TEXT PRIMARY KEY, v TEXT);

An in-memory database next to the main file

ATTACH ':memory:' AS scratch;
CREATE TABLE scratch.new_ids AS SELECT id FROM orders WHERE status = 'new';
SELECT count(*) FROM scratch.new_ids;

Online backup while the database is in use

sqlite3 shop.db ".backup backup/shop-2026-09-29.db"
sqlite3 backup/shop-2026-09-29.db "PRAGMA integrity_check"

A compacted copy with VACUUM INTO

VACUUM INTO '/backups/shop-2026-09-29.db';

Never copy the file with cp while it is open

# wrong while the app is running: the -wal file may hold recent commits
cp shop.db shop-copy.db
# right: let SQLite make the copy
sqlite3 shop.db ".backup shop-copy.db"

.dump to SQL text

sqlite3 shop.db .dump > shop.sql
sqlite3 shop.db ".dump orders customers" > two-tables.sql
sqlite3 restored.db < shop.sql

sqlite3_rsync: copy only the changed pages

sqlite3_rsync shop.db backup@db-replica:/srv/sqlite/shop.db

Continuous replication with Litestream

litestream replicate shop.db s3://my-bucket/shop
litestream restore -o shop.db s3://my-bucket/shop

Salvage a damaged database

sqlite3 broken.db ".recover" | sqlite3 recovered.db
sqlite3 recovered.db "PRAGMA integrity_check"

Restore a backup into an open shell

.restore backup/shop-2026-09-29.db

Import a CSV file

.import --csv --skip 1 customers.csv customers
.import --csv new_table.csv products_staging          -- table created from the header row
SELECT count(*) FROM customers;

Import JSON

INSERT INTO products (sku, name, price_cents)
SELECT value ->> '$.sku', value ->> '$.name', value ->> '$.price_cents'
FROM json_each(readfile('products.json'));

CSV to INSERT without the shell

-- paste the CSV into the toolbox above and press "CSV to INSERT":
-- it infers INTEGER, REAL and TEXT columns and writes CREATE TABLE ... STRICT plus the INSERTs

Export a query

sqlite3 -csv -header shop.db "SELECT * FROM orders WHERE status = 'paid'" > paid.csv
sqlite3 -json shop.db "SELECT id, name FROM customers" > customers.json
sqlite3 -markdown shop.db "SELECT status, count(*) FROM orders GROUP BY 1"

From inside the shell

.headers on
.mode csv
.once paid.csv
SELECT * FROM orders WHERE status = 'paid';
.excel
SELECT * FROM customers;

Build the JSON in SQL

SELECT json_group_array(json_object('id', id, 'name', name, 'country', country)) FROM customers;

One JSON object per line

SELECT json_object('id', id, 'sku', sku, 'price', price_cents / 100.0) FROM products LIMIT 3;

Bind values, never build SQL text

-- placeholders: ? ?1 :name @name $name
SELECT * FROM customers WHERE email = ?;
SELECT * FROM customers WHERE email = :email AND country = :country;

The same in Python

cur.execute("SELECT * FROM customers WHERE email = ?", (email,))          # right
cur.execute(f"SELECT * FROM customers WHERE email = '{email}'")          # SQL injection

IN lists with a variable number of values

SELECT * FROM orders WHERE id IN (SELECT value FROM json_each(?));   -- bind '[1, 5, 9]'

Opening a database file from someone else

PRAGMA trusted_schema = OFF;
PRAGMA cell_size_check = ON;
PRAGMA mmap_size = 0;
PRAGMA quick_check;

Extensions are off unless you turn them on

con = sqlite3.connect("app.db")
con.execute("SELECT load_extension('./vec0')")

Encryption at rest

File permissions and deleted data

chmod 600 shop.db
PRAGMA secure_delete = ON;

Check identifiers against an allowlist

SORTABLE = {"created_at", "total_cents", "status"}
column = request.args.get("sort", "created_at")
if column not in SORTABLE:
    column = "created_at"
rows = con.execute(f"SELECT id FROM orders ORDER BY {column} LIMIT ?", (20,)).fetchall()

The default limits

Check the limits of your build

PRAGMA compile_options;
SELECT * FROM pragma_compile_options WHERE compile_options LIKE 'MAX_%';

Options that change what SQL works

Turn off the double quoted string fallback

-- in the shell, from 3.41 double quoted strings are rejected by default:
SELECT * FROM customers WHERE name = "Ana Pop";

From the shell and from code

.load ./vec0
SELECT vec_version();

Popular extensions

Vector search with sqlite-vec

CREATE VIRTUAL TABLE vec_docs USING vec0(embedding float[384]);
INSERT INTO vec_docs (rowid, embedding) VALUES (1, ?);        -- bind a JSON array or a float32 blob
SELECT rowid, distance FROM vec_docs WHERE embedding MATCH ? ORDER BY distance LIMIT 5;

R*Tree for ranges and bounding boxes

CREATE VIRTUAL TABLE places_idx USING rtree(id, min_lat, max_lat, min_lon, max_lon);
SELECT id FROM places_idx WHERE min_lat >= 46.7 AND max_lat <= 46.8 AND min_lon >= 23.5 AND max_lon <= 23.7;

Table-valued functions

SELECT value FROM generate_series(1, 10, 3);           -- shell and builds with the series extension
SELECT * FROM pragma_table_info('orders');

The built-in sqlite3 module

import sqlite3

con = sqlite3.connect("app.db", timeout=5, autocommit=False)
con.execute("PRAGMA journal_mode = WAL")
con.execute("PRAGMA foreign_keys = ON")
con.row_factory = sqlite3.Row

rows = con.execute("SELECT id, name FROM customers WHERE country = ?", ("RO",)).fetchall()
with con:
    con.execute("UPDATE orders SET status = ? WHERE id = ?", ("shipped", 42))
print(rows[0]["name"])

better-sqlite3, synchronous and fast

import Database from 'better-sqlite3';

const db = new Database('app.db', { timeout: 5000 });
db.pragma('journal_mode = WAL');
db.pragma('foreign_keys = ON');

const byCountry = db.prepare('SELECT id, name FROM customers WHERE country = ?');
const rows = byCountry.all('RO');
const addMany = db.transaction((list) => { for (const c of list) insert.run(c); });

database/sql with the pure Go driver

import (
	"database/sql"
	_ "modernc.org/sqlite"
)

db, err := sql.Open("sqlite", "file:app.db?_pragma=busy_timeout(5000)&_pragma=journal_mode(WAL)&_pragma=foreign_keys(1)")
if err != nil { log.Fatal(err) }
db.SetMaxOpenConns(1)

var name string
err = db.QueryRow("SELECT name FROM customers WHERE id = ?", 1).Scan(&name)

rusqlite

use rusqlite::{params, Connection};

let conn = Connection::open("app.db")?;
conn.pragma_update(None, "journal_mode", "WAL")?;
conn.execute("INSERT INTO customers (name, email, country, created_at) VALUES (?1, ?2, ?3, datetime('now'))",
    params!["Ana", "ana@example.org", "RO"])?;
let name: String = conn.query_row("SELECT name FROM customers WHERE id = ?1", [1], |r| r.get(0))?;

PDO

$pdo = new PDO('sqlite:' . __DIR__ . '/app.db', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$pdo->exec('PRAGMA journal_mode = WAL');
$stmt = $pdo->prepare('SELECT id, name FROM customers WHERE country = :country');
$stmt->execute(['country' => 'RO']);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

What to do on every connection

C#, Java and mobile

// C#: Microsoft.Data.Sqlite
using var con = new SqliteConnection("Data Source=app.db;Foreign Keys=True;Default Timeout=5");
// Java: xerial sqlite-jdbc
Connection c = DriverManager.getConnection("jdbc:sqlite:app.db");
// Android: Room (on top of SQLite); iOS: GRDB or SQLite.swift; Flutter: sqflite or drift

SQLite in the browser

import sqlite3InitModule from '@sqlite.org/sqlite-wasm';
const sqlite3 = await sqlite3InitModule();
const db = new sqlite3.oo1.OpfsDb('/app.db');          // persistent, in a Worker, via OPFS
db.exec({ sql: 'SELECT sqlite_version()', rowMode: 'array', callback: (row) => console.log(row) });

Python: many rows at once, and dict rows

con.executemany("INSERT INTO tags (name) VALUES (?)", [("oak",), ("ash",), ("elm",)])
con.row_factory = lambda cur, row: {d[0]: v for d, v in zip(cur.description, row)}
print(con.execute("SELECT id, name FROM tags").fetchone())

Python: an in-memory copy and back

mem = sqlite3.connect(":memory:")
con.backup(mem)                          # copy the file into memory
data = mem.serialize()                   # the whole database as bytes (3.11+)

Go: a write transaction

tx, err := db.BeginTx(ctx, nil)
if err != nil { return err }
defer tx.Rollback()
if _, err := tx.ExecContext(ctx, "UPDATE orders SET status = ? WHERE id = ?", "shipped", 42); err != nil { return err }
return tx.Commit()

The codes you will see in practice

Constraint messages

PRAGMA foreign_keys = ON;
INSERT INTO customers (name, email, country, created_at) VALUES ('X', 'ana@example.com', 'RO', '2026-09-29');
INSERT INTO orders (customer_id, status, created_at) VALUES (999, 'new', '2026-09-29');
INSERT INTO orders (customer_id, status, created_at) VALUES (1, 'lost', '2026-09-29');

What the error text means

A new empty database instead of yours

sqlite3 "$(pwd)/data/shop.db" ".tables"
SELECT file FROM pragma_database_list WHERE name = 'main';

Extended error codes in Python

try:
    con.execute("INSERT INTO customers (email) VALUES (?)", ("ana@example.com",))
except sqlite3.IntegrityError as e:
    if e.sqlite_errorname == "SQLITE_CONSTRAINT_UNIQUE":
        print("email already registered")

Node.js: catch a constraint error

try {
  db.prepare('INSERT INTO customers (name, email, country, created_at) VALUES (?, ?, ?, ?)').run(name, email, 'RO', now);
} catch (err) {
  if (err.code === 'SQLITE_CONSTRAINT_UNIQUE') return res.status(409).json({ error: 'email already registered' });
  throw err;
}

Retry on SQLITE_BUSY, briefly

for attempt in range(5):
    try:
        with con:
            con.execute("UPDATE counters SET n = n + 1 WHERE id = 1")
        break
    except sqlite3.OperationalError as e:
        if "locked" not in str(e): raise
        time.sleep(0.05 * 2 ** attempt)

Recent releases, as of September 29, 2026

Older features you can count on from 3.37

Which version am I on, in each place

sqlite3 --version
python3 -c "import sqlite3; print(sqlite3.sqlite_version)"
node -e "console.log(require('better-sqlite3')(':memory:').prepare('select sqlite_version()').pluck().get())"
SELECT sqlite_version();

When SQLite fits and when it does not

Syntax differences you will hit

Ask the build what it includes

SELECT sqlite_compileoption_used('ENABLE_FTS5'), sqlite_compileoption_used('ENABLE_MATH_FUNCTIONS');
SELECT count(*) FROM pragma_function_list WHERE name = 'jsonb';