SQLite cheat sheet

-- Each .db file is one database. No server.
-- Dynamic typing: column types are *hints* — SQLite stores actual values as one of:
--   NULL, INTEGER, REAL, TEXT, BLOB.
-- INTEGER PRIMARY KEY is an alias for the rowid (no separate column).

CREATE TABLE users (
  id    INTEGER PRIMARY KEY AUTOINCREMENT,
  email TEXT NOT NULL UNIQUE,
  data  ANY                         -- type names are hints; STRICT tables (3.37+) enforce them
);

-- Strict tables (3.37+) — actually enforce declared types.
CREATE TABLE strict_users (id INTEGER, email TEXT) STRICT;

.databases                    -- list attached databases
.tables                       -- list tables in current db
.tables pattern               -- tables matching LIKE pattern
.schema table_name            -- CREATE TABLE statement
.indexes                      -- list indexes
.indexes table_name           -- indexes for a table
.headers on                   -- show column names in output
.mode column                  -- aligned columns (also: csv, tabs, json, html, line)
.timer on                     -- show query times
.read script.sql              -- execute SQL from file
.dump                         -- export all tables as SQL
.backup file.db               -- copy current db
.import file.csv table_name   -- bulk load CSV
.changes on                   -- show row counts after DML
.quit                         -- exit

-- Get help.
.help

-- List tables.
SELECT name FROM sqlite_master WHERE type = 'table';
SELECT name FROM sqlite_schema WHERE type = 'table';   -- alias from 3.33+

-- Get a table's columns.
PRAGMA table_info('orders');                            -- name, type, notnull, dflt_value, pk

-- Get a table's foreign keys.
PRAGMA foreign_key_list('orders');

-- Get a table's indexes.
PRAGMA index_list('orders');
PRAGMA index_info('idx_name');

-- Get the original CREATE statement.
SELECT sql FROM sqlite_master WHERE type = 'table' AND name = 'orders';

-- SQLite has no DATE / TIMESTAMP types — stores as TEXT, INTEGER, or REAL.
-- Use ISO 8601 text by default: '2026-04-26' or '2026-04-26 14:30:00'.

date('now')                              -- '2026-04-26' (UTC)
datetime('now')                          -- '2026-04-26 14:30:00' (UTC)
datetime('now', 'localtime')             -- session-local
strftime('%Y-%m-%d', 'now')              -- format

-- Modifiers ("date math" via the second argument).
date('now', '+7 days')
date('now', '-1 month')
date('now', 'start of month')             -- first of month
date('now', 'start of month', '+1 month', '-1 day')   -- last of month
date('now', 'weekday 1')                  -- next Monday

-- Difference in days.
julianday(end) - julianday(start)         -- as REAL

-- Extract a part.
strftime('%Y', d)                         -- year as text
CAST(strftime('%Y', d) AS INTEGER)        -- year as integer

json_extract(payload, '$.user_id')        -- scalar
json_extract(payload, '$.items[0]')       -- array index

-- Path operators (3.38+).
payload -> '$.user_id'                    -- returns JSON
payload ->> '$.user_id'                   -- returns scalar text

-- Modify.
json_set(payload, '$.user.plan', 'pro')
json_remove(payload, '$.tmp_field')

-- Build.
json_object('id', id, 'email', email)
json_array(1, 2, 3)
json_group_array(name)                    -- aggregate to array
json_group_object(key, value)             -- aggregate to object

-- Iterate.
SELECT j.value FROM events, json_each(payload, '$.items') AS j;

-- Open another .db file alongside the current connection.
ATTACH DATABASE 'archive.db' AS archive;

-- Now you can query across files.
SELECT * FROM main.orders
UNION ALL
SELECT * FROM archive.orders;

-- Detach when done.
DETACH DATABASE archive;

-- Upsert.
INSERT INTO users (id, email) VALUES (1, 'a@x.com')
ON CONFLICT (id) DO UPDATE SET email = excluded.email;

-- INSERT OR IGNORE / REPLACE.
INSERT OR IGNORE INTO users (id, email) VALUES (1, 'a@x.com');
INSERT OR REPLACE INTO users (id, email) VALUES (1, 'a@x.com');

-- LIMIT / OFFSET.
SELECT * FROM users LIMIT 20 OFFSET 40;

-- Window functions (3.25+).
SELECT id, total,
       SUM(total) OVER (ORDER BY id) AS running_total
FROM orders;

-- RETURNING (3.35+).
INSERT INTO users (email) VALUES ('a@x.com') RETURNING *;
DELETE FROM users WHERE id = 1 RETURNING email;

-- Foreign keys are OFF by default in SQLite!
PRAGMA foreign_keys = ON;                 -- enable per connection

-- Other useful pragmas.
PRAGMA journal_mode = WAL;                -- write-ahead log; better concurrency
PRAGMA synchronous = NORMAL;              -- balance durability and speed
PRAGMA temp_store = MEMORY;               -- temp tables in RAM
PRAGMA cache_size = -64000;               -- 64 MB cache
PRAGMA integrity_check;                   -- verify db is not corrupt
PRAGMA quick_check;                       -- faster integrity check