Skip to content
Tutorials Center
My learning

Topics

Lesson 33 of 33 ยท Practice and Review

SQLite Cheat Sheet

A one-page SQLite cheat sheet: sqlite3 shell commands, storage classes, upsert, RETURNING, date and string functions, JSON, windows, pragmas and common patterns.

This page squeezes the SQLite course into one scrollable reference: the shell commands, types, statements, functions and pragmas you reach for most often. Keep it open while you work through the exercises, while you write code against a database file, or before an interview.

Each section links to the lesson that explains the topic properly. The snippets use the SQLite that runs in this site's editors (version 3.49), so the statements marked with an editor really run. Shell commands, pragmas that need a file, and extensions such as FTS5 are shown as read-only code blocks.

Five things to remember

Values have types Columns only have affinity.
Foreign keys are off Turn them on per connection.
Dates are text ISO-8601 sorts correctly.
One writer Keep transactions short.

Most SQLite surprises come from these four.

1 / 2

The sqlite3 shell

Start the shell with sqlite3 mydata.db (a new file is created on first write) or sqlite3 alone for a temporary in-memory database. Dot-commands are for the shell itself and need no semicolon. See Running SQLite: Browser and the sqlite3 Shell.

TEXT
.help                  list all dot-commands
.open file.db          open or create a database file
.tables                list tables (and views)
.schema [table]        show the CREATE statements
.headers on            show column names
.mode column           aligned columns   (also: table, box, csv, json, line, list)
.import data.csv t     load a CSV file into table t
.output out.csv        send results to a file (".output" alone restores the screen)
.read script.sql       run a file of SQL
.backup copy.db        copy the live database to another file
.dump                  print the whole database as SQL text
.timer on              show how long each statement takes
.quit                  leave the shell

Types and affinity

Storage class Holds typeof()
NULL missing value null
INTEGER 1 to 8 bytes, signed integer
REAL 8-byte IEEE float real
TEXT UTF-8 (or UTF-16) string text
BLOB raw bytes blob

The declared type picks an affinity by its name: contains INT is INTEGER; CHAR, CLOB or TEXT is TEXT; BLOB or no type is BLOB; REAL, FLOA or DOUB is REAL; everything else is NUMERIC. Affinity converts values when it can do so without loss and never rejects anything, unless the table is STRICT. Sorting across classes puts NULL first, then numbers, then text, then blobs. See Dynamic Typing and Type Affinity.

Tables and constraints

SQL
CREATE TABLE IF NOT EXISTS users (
  id       INTEGER PRIMARY KEY,              -- alias for rowid
  email    TEXT    NOT NULL UNIQUE,
  age      INTEGER CHECK (age BETWEEN 0 AND 150),
  active   INTEGER NOT NULL DEFAULT 1,       -- booleans are 0/1
  created  TEXT    NOT NULL DEFAULT (datetime('now')),
  team_id  INTEGER REFERENCES teams (id) ON DELETE SET NULL
) STRICT;                                    -- optional: enforce column types
Option Meaning
INTEGER PRIMARY KEY the column is the rowid (exact spelling matters)
AUTOINCREMENT never reuse ids (needs INTEGER PRIMARY KEY)
STRICT allowed types: INT, INTEGER, REAL, TEXT, BLOB, ANY
WITHOUT ROWID store the table keyed by its primary key
GENERATED ALWAYS AS (expr) [VIRTUAL|STORED] computed column

Useful pieces: last_insert_rowid() (the id of the last insert), changes() (rows changed by the last statement). See Tables, rowid and INTEGER PRIMARY KEY and Constraints and STRICT Tables.

Changing a table

SQL
ALTER TABLE users ADD COLUMN nickname TEXT;
ALTER TABLE users RENAME COLUMN nickname TO handle;
ALTER TABLE users DROP COLUMN handle;       -- fails if indexed, keyed or used elsewhere
ALTER TABLE users RENAME TO members;
DROP TABLE IF EXISTS old_stuff;

Any other change (type, constraints) needs a rebuild: create the new table, copy rows, drop the old one, rename the new one, all inside a transaction. See Altering Schema Safely.

Reading data

SQL
SELECT DISTINCT genre, count(*) AS n, avg(price) AS avg_price
FROM books
WHERE price > 10 AND title LIKE '%the%'      -- LIKE ignores ASCII case, GLOB does not
GROUP BY genre
HAVING n > 1
ORDER BY n DESC, genre
LIMIT 10 OFFSET 20;
Task Syntax
Inner / left join a JOIN b ON ..., a LEFT JOIN b ON ...
Right / full join RIGHT JOIN, FULL JOIN (3.39+)
Combine results UNION, UNION ALL, INTERSECT, EXCEPT
Subquery WHERE x IN (SELECT ...), EXISTS (SELECT ...)
CTE WITH name AS (SELECT ...) SELECT ...
Recursive CTE WITH RECURSIVE t(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM t WHERE n<9) ...
Null-safe compare a IS b, a IS NOT b
Case CASE WHEN x THEN y ELSE z END, or iif(x, y, z)

Wildcards: LIKE uses % and _, GLOB uses *, ? and [a-z]. See Querying in SQLite and Joins, Subqueries and CTEs.

Writing data

SQL
INSERT INTO users (email, age) VALUES ('[email protected]', 30), ('[email protected]', 41);

UPDATE users SET age = age + 1 WHERE email = '[email protected]';
UPDATE books SET price = o.new_price FROM offers AS o WHERE books.id = o.book_id;  -- UPDATE FROM

DELETE FROM users WHERE active = 0;

Upsert and RETURNING

Need Statement
Skip duplicates INSERT ... ON CONFLICT DO NOTHING (or INSERT OR IGNORE)
Insert or update INSERT ... ON CONFLICT (key) DO UPDATE SET col = excluded.col
Delete and re-insert INSERT OR REPLACE (omitted columns reset to defaults)
Get written rows add RETURNING col, ... to INSERT, UPDATE or DELETE

excluded.col is the row you tried to insert; a bare col is the stored row. See UPSERT and RETURNING.

SQL Upsert and RETURNING in one table

Press Run (or Ctrl + Enter) to see the result here.

The result has two rows: A1 with 8 (the existing 5 plus the new 3) and B2 with 10.

Transactions

SQL
BEGIN IMMEDIATE;        -- take the write lock now (DEFERRED is the default)
  UPDATE accounts SET balance = balance - 50 WHERE id = 1;
  UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;                 -- or ROLLBACK;

SAVEPOINT step1;  ...  ROLLBACK TO step1;  RELEASE step1;

Without an explicit BEGIN, every statement is its own transaction, so wrap bulk inserts in one transaction for a large speed-up. See Transactions.

Functions

Text, numbers and conditionals

Function Result
length(s), upper(s), lower(s) characters, case change (ASCII only for case)
substr(s, start, len) 1-based substring
instr(s, part) position, 0 if absent
replace(s, from, to) all occurrences replaced
trim(s), ltrim(s, chars), rtrim(s) strip spaces or listed characters
printf('%05.1f', x) / format(...) formatted text
hex(x), quote(x), unicode(c), char(n, ...) encodings
abs, round(x, n), max(a, b, ...), min(a, b, ...) scalar math
coalesce(a, b, ...), ifnull(a, b), nullif(a, b), iif(c, a, b) null handling
random(), randomblob(n) non-deterministic values
CAST(x AS INTEGER) explicit conversion ('12abc' gives 12)
SQL Text and number functions

Press Run (or Ctrl + Enter) to see the result here.

Expect SQLITE, base, 5, a+b+c, 003.1|ab | 7, 2.57, 3, yes and 12. Math functions such as sqrt and floor are available only when SQLite is built with them (this site's editor has them). See Built-in Functions.

Aggregates

count(*), count(col) (skips NULLs), count(DISTINCT col), sum, total (never NULL, always real), avg, min, max, group_concat(col, sep). sum() of no rows is NULL, total() is 0.0.

Window functions

row_number(), rank(), dense_rank(), ntile(n), lag(col, n), lead(col, n), first_value, last_value, and any aggregate used with OVER (PARTITION BY ... ORDER BY ... ROWS BETWEEN ...). A window function cannot appear in WHERE; compute it in a subquery or CTE and filter outside. See Window Functions.

SQL A running total and a rank

Press Run (or Ctrl + Enter) to see the result here.

The running totals are 5, 14 and 23. Rows b and c tie on quantity, so both get rank 1 and a gets rank 3.

Dates and times

SQLite stores dates as text (YYYY-MM-DD HH:MM:SS), Julian day numbers (real) or Unix seconds (integer). 'now' means the current UTC time.

Expression Result
date('now'), time('now'), datetime('now') current UTC date, time, both
date('2024-03-15', '+1 month', '-1 day') date arithmetic with modifiers
date(d, 'start of month') first day of the month (also start of year, start of day)
date(d, 'weekday 0') the next Sunday on or after d
strftime('%Y-%m', d) formatted piece (%Y %m %d %H %M %S %w %j %s)
unixepoch(d) Unix seconds (3.38+)
datetime(n, 'unixepoch') text from Unix seconds
datetime(d, 'localtime') convert UTC to the machine's time zone
julianday(a) - julianday(b) days between two dates
SQL Date functions with fixed dates

Press Run (or Ctrl + Enter) to see the result here.

Expect 2024-03-31, 2024-04-17 00:00:00, 2024/03/15 13:45, 5 (Friday, with Sunday as 0), 1710460800, 2024-03-15 00:00:00 and 14. See Dates, Times and Booleans.

JSON

Expression Result
json_extract(j, '$.a.b[1]') an SQL value (text, number, NULL) or JSON text for objects/arrays
j -> '$.a' the value as JSON text
j ->> '$.a' the value as an SQL value
json_object('k', 1), json_array(1, 2) build JSON
json_set(j, '$.b', 2), json_insert, json_replace, json_remove modify
json_each(j, '$.tags') one row per element (table-valued)
json_group_array(x), json_group_object(k, v) aggregate rows into JSON
json_valid(j) 1 if well-formed
SQL JSON in a query

Press Run (or Ctrl + Enter) to see the result here.

The first column is [10,20], then 20, {"k":1,"z":"w"}, {"a":1,"b":2} and 0. To index a JSON field, create an index on the expression (CREATE INDEX i ON t (payload ->> '$.user')) or use a generated column. See Generated Columns and JSON.

Indexes and plans

SQL
CREATE INDEX idx_books_genre ON books (genre);                  -- single column
CREATE UNIQUE INDEX idx_users_email ON users (email);           -- also enforces uniqueness
CREATE INDEX idx_o_cust_date ON orders (customer_id, ordered_on); -- composite: leftmost columns first
CREATE INDEX idx_open ON tasks (due) WHERE done = 0;            -- partial
CREATE INDEX idx_lower ON users (lower(email));                 -- expression

EXPLAIN QUERY PLAN SELECT * FROM books WHERE genre = 'Fiction';
-- SEARCH books USING INDEX idx_books_genre (genre=?)    good
-- SCAN books                                            reads every row
ANALYZE;                -- collect statistics
PRAGMA optimize;        -- cheap, run before closing a long-lived connection

A covering index (one that contains every column the query needs) shows as USING COVERING INDEX. See Indexes and EXPLAIN QUERY PLAN and the Optimizer.

Views and triggers

SQL
CREATE VIEW cheap_books AS SELECT * FROM books WHERE price < 10;

CREATE TRIGGER log_price
AFTER UPDATE OF price ON books
WHEN OLD.price <> NEW.price
BEGIN
  INSERT INTO price_log VALUES (NEW.id, OLD.price, NEW.price);
END;

Triggers run BEFORE, AFTER or INSTEAD OF (views) an INSERT, UPDATE or DELETE, and see the rows as OLD and NEW. See Views and Triggers.

Pragmas

Run a pragma by name to read it, or with = to set it. Most settings last only for the current connection.

Pragma Typical use
PRAGMA foreign_keys = ON; enforce foreign keys (per connection, off by default)
PRAGMA journal_mode = WAL; readers and the writer stop blocking each other (stored in the file)
PRAGMA synchronous = NORMAL; fewer disk syncs; common with WAL
PRAGMA busy_timeout = 5000; wait up to 5 s for a lock instead of failing at once
PRAGMA cache_size = -64000; page cache of about 64 MB (negative means KiB)
PRAGMA table_info(t); columns of a table
PRAGMA index_list(t); indexes of a table
PRAGMA foreign_key_check; rows that break foreign keys
PRAGMA integrity_check; verify the file (ok when healthy)
PRAGMA user_version = 3; a number you can use as a schema version
PRAGMA optimize; refresh query-planner statistics when needed

See PRAGMAs and Tuning.

Common patterns

Goal Pattern
Newest row per group row_number() OVER (PARTITION BY g ORDER BY ts DESC) in a CTE, filter = 1
Top N per group the same with <= N
Pagination ORDER BY id LIMIT 20 then WHERE id > :last_id (keyset) instead of large OFFSET
Soft delete deleted_at TEXT plus a view or partial index on WHERE deleted_at IS NULL
Count with a condition sum(status = 'done') or count(*) FILTER (WHERE status = 'done')
Many-to-many link table with a composite primary key (a_id, b_id)
Insert if missing INSERT ... ON CONFLICT DO NOTHING
Job queue claim UPDATE jobs SET state = 'running' WHERE id = (SELECT id FROM jobs WHERE state = 'queued' ORDER BY id LIMIT 1) RETURNING *
Safe parameters ? or :name placeholders, never string concatenation
Bulk load one transaction, a prepared statement, indexes created afterwards
Backup .backup file in the shell or VACUUM INTO 'file'
SQL Group, filter groups and join text ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Three genres have more than one book: Fiction (3 books, average 14.91), Mystery (2, 11.88) and Science (2, 21.98). For parameters and injection, see Parameters and SQL Injection; for backups and file care, see Backup, VACUUM and Maintenance.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which statement adds to an existing counter instead of replacing the whole row?

    DO UPDATE changes the stored row in place, so n = n + 1 builds on the existing value. REPLACE deletes the row first and IGNORE skips the new one.
  2. 2. What does date('2024-03-15', 'start of month', '+1 month', '-1 day') return?

    Move to the first of March, add one month to reach April 1, then subtract a day. This is the usual way to get the last day of a month.
  3. 3. Which pragma must be set on every new connection to enforce foreign keys?

    Enforcement is off by default and is not stored in the database file, so every connection has to switch it on. foreign_key_check only reports existing violations.
  4. 4. Which JSON operator returns an SQL value such as a plain number or text?

    ->> returns an SQL value, while -> returns JSON text, so ->> is the one to use in comparisons, grouping and sums.

Summary

  • Types live on values; use STRICT when you want them enforced, and typeof() to inspect.
  • ON CONFLICT DO UPDATE with excluded is the safe upsert; RETURNING reads back what changed.
  • Dates are text, numbers or Unix seconds, handled with date, datetime, strftime, julianday and unixepoch.
  • JSON lives in text columns: ->> for values, json_each for arrays, expression indexes for speed.
  • Switch on foreign_keys, set a busy_timeout and consider WAL on every connection that matters.
  • Check plans with EXPLAIN QUERY PLAN, and wrap bulk writes in one transaction.

You have reached the end of the SQLite course. Revisit the exercises to test yourself, or read What Is SQLite? again with fresh eyes.