Exploring SQLite from the Shell

1 · Anatomy of a .db File

A SQLite database is not a server process, a directory, or a proprietary blob — it is one ordinary file, and nearly everything about it is discoverable from that file alone, starting with its first 100 bytes.

Every SQLite database file begins with a 100-byte header. The first 16 bytes of that header are a fixed, zero-terminated magic string — "SQLite format 3\000", always exactly 16 bytes — which is how a shell, a library, or a tool like file recognizes a file as a SQLite database at all, independent of its name or extension. [fileformat2.html]

Two bytes into the header, at offset 16, sits the database's page size: a big-endian integer that must be a power of two between 512 and 32768, or the special value 1, which means 65536 (a page size added in SQLite 3.7.1, 2010-08-23). Everything after the header is "one or more pages" of that single, uniform size, fixed for the life of the file. Pages are numbered starting at 1, with a theoretical maximum of 4,294,967,294 pages in one database. [fileformat2.html]

At any moment, every page in the file serves exactly one purpose: a b-tree page (table interior, table leaf, index interior, or index leaf), a freelist page (trunk or leaf), a payload overflow page, a pointer-map page, or the single lock-byte page. Each table and each index in the database is physically implemented as its own b-tree — one b-tree per table, one b-tree per index — and a b-tree's root page number is exactly the value stored in the schema table's rootpage column. [fileformat2.html]

Page 1 is special twice over: it holds the 100-byte header, and it is itself the root page of a table b-tree — the b-tree for a table named sqlite_schema, which stores the complete database schema. Every other table's and index's root page is located by looking it up in sqlite_schema first, so this one page anchors the discovery of every other b-tree in the file. [fileformat2.html]

A database file sometimes has company: a -wal, -shm, or -journal sidecar file sitting right next to it, present only some of the time depending on journal mode and what the last writer was doing when it exited. What each sidecar means, and why you should never delete one by hand, is covered in full in §9 (Safety). [wal.html]

Sources: The Database File Format · The Schema Table · Write-Ahead Logging

2 · First 60 Seconds with Any .db

Handed an unfamiliar .db file, here is a seven-step ritual that costs well under a minute and never risks writing to it. Every command below is real, run against the workshop.db this guide builds in §3's Lab 0.

1. Identify it before you trust the extension. file reads the magic header, not the filename:

   $ file workshop.db mystery.db not-a-db.db
   workshop.db: SQLite 3.x database, last written using SQLite version 3043002, unused
                bytes 12, file counter 21, database pages 156, cookie 0x12, schema 4,
                UTF-8, version-valid-for 21
   mystery.db:  SQLite 3.x database, last written using SQLite version 3043002, unused
                bytes 12, file counter 1, database pages 156, cookie 0x1, schema 4,
                UTF-8, version-valid-for 1
   not-a-db.db: ASCII text

   [local sqlite3 3.43.2, captured 2026-07-17]

2. Check size and sidecars. ls -l the file, then glob for leftover WAL/journal companions:

   $ ls -l workshop.db
   $ ls -l workshop.db-wal workshop.db-shm workshop.db-journal 2>/dev/null

   The file is exactly 638,976 bytes — page_size × page_count (4096 × 156) for this database. A clean multiple of the page size is what a healthy, checkpointed database looks like from the outside. The sidecar glob comes back empty here, because a freshly built database that closed cleanly reports plain journal_mode = delete by default — sidecars are the exception, not the rule; §9 covers what to do when one is present.

   [local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

3. Open it so you can't break it. -readonly makes any write fail instead of silently proceeding against someone's live application file:

   $ sqlite3 -readonly workshop.db "INSERT INTO tags(name) VALUES('nope')"
   Error: stepping, attempt to write a readonly database (8)

   stock frames the rejection as Error: stepping, attempt to write a readonly database (8) (8 = SQLITE_READONLY); brew reports the identical rejection as Error in Nth command line argument: attempt to write a readonly database — no numeric code, different prefix. Either way, nothing was written; the same guarantee holds for the file:workshop.db?mode=ro URI form.

   [local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

4. .dbinfo — the fastest way to "read" a file without a schema dump. Stock and brew agree on 20 of its 21 lines for workshop.db:

   Field	Value	Why it matters
   database page size	4096	the header field from §1, in bytes
   write/read format	1	1 = legacy rollback-journal format, 2 = WAL format
   reserved bytes	12	space reserved per page for extensions (e.g. encryption)
   database page count	156	× page size = the file's real byte size
   freelist page count	0	unused pages waiting to be reclaimed — none yet
   schema cookie	18	bumps on every schema change; a cheap "has the schema changed" check
   schema format	4	which historical schema-table dialect wrote this file
   text encoding	utf8	not UTF-16le/UTF-16be — matters for byte-level tooling
   number of tables / indexes / triggers / views	14 / 5 / 1 / 1	your first size-up before running a single SELECT
   schema size	2565	bytes of schema text stored in sqlite_schema.sql

   The one line excluded above, data version, is a connection-local counter that legitimately varies run-to-run regardless of binary — not a real version difference. A handful of other .dbinfo lines (including any app-defined user version / application id tags) fall outside this guide's captured ground truth, since this particular build script never sets them; don't expect every line .dbinfo prints to be covered here.

   [local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

5. List what's actually in it. .tables queries sqlite_schema for you and formats the result into columns:

   $ sqlite3 -readonly workshop.db .tables
   comments              issue_search_data    issue_search_docsize  projects
   events                issue_search_idx     issue_tags            tags
   issue_search          issue_search_config  open_issues           users
   issue_search_content  issues

   stock packs these into 4 columns per row; brew packs the identical 14 names into 5 columns — a rendering-width difference only, not a data difference. Five of these fourteen names (issue_search_*) are FTS5 shadow tables, not ordinary content — more on that in §5.

   [local sqlite3 3.43.2, captured 2026-07-17]

6. Get a row count for every table in one loop. No dot-command does this directly, but sqlite_schema plus a shell loop does:

   $ sqlite3 workshop.db "SELECT name FROM sqlite_schema WHERE type='table' AND name NOT LIKE 'sqlite_%'" \
     | while read -r t; do echo "$t $(sqlite3 workshop.db "SELECT count(*) FROM \"$t\"")"; done
   users 40
   projects 8
   issues 500
   comments 2000
   tags 12
   issue_tags 826
   events 5000
   issue_search 500
   issue_search_data 11
   issue_search_idx 9
   issue_search_content 500
   issue_search_docsize 500
   issue_search_config 1

   Byte-identical on both binaries. This is usually the fastest way to spot "the table that's suspiciously empty" or "the table that's 10x bigger than expected" before writing a single real query.

   [local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

7. Skim the schema, human-formatted. .schema --indent reformats CREATE statements for readability without changing what they say — it only removes column-alignment whitespace and tightens CHECK (...) to CHECK(...), confirmed byte-identical in content on both binaries:

   $ sqlite3 -readonly workshop.db ".schema --indent issue_tags"
   CREATE TABLE issue_tags(
     issue_id INTEGER NOT NULL REFERENCES issues(id),
     tag_id INTEGER NOT NULL REFERENCES tags(id),
     PRIMARY KEY(issue_id, tag_id)
   ) WITHOUT ROWID;

   That trailing ) WITHOUT ROWID; is itself a planted quirk — a composite-key table clustered directly on its primary key instead of a separate rowid; more in §5.

   [local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

Sources: Command Line Shell For SQLite · local sqlite3 3.43.2 / 3.53.3 ground truth, captured 2026-07-17

3 · Your sqlite3 Binaries

"Just run sqlite3" is not as safe an instruction as it sounds. This machine has three different sqlite3 binaries installed, and the one bare sqlite3 resolves to on PATH is neither of the two this guide otherwise compares.

[local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

Run the discovery ritual yourself before trusting any recipe that just says "run sqlite3":

$ command -v sqlite3
/opt/local/bin/sqlite3
$ /usr/bin/sqlite3 --version
3.43.2 2023-10-10 13:08:14 1b37c146ee9ebb7acd0160c0ab1fd11017a419fa8a3187386ed8cb32b709aapl (64-bit)
$ $(brew --prefix sqlite)/bin/sqlite3 --version
3.53.3 2026-06-26 20:14:12 d4c0e51e4aeb96955b99185ab9cde75c339e2c29c3f3f12428d364a10d782c62 (64-bit)
$ /opt/local/bin/sqlite3 --version
3.35.5 2021-04-19 18:32:05 1b256d97b553a9611efca188a3d995a2fff712759044ba480f9a0c9e98fae886

command -v sqlite3 resolves to the MacPorts build — the oldest and least-featured of the three, from 2021. Any recipe that just says "run sqlite3" silently exercises this one unless you use a full path or have reordered your own PATH. [local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

PRAGMA compile_options is the next thing worth checking — the two binaries this guide actively compares were built with meaningfully different feature sets:

$ /usr/bin/sqlite3 :memory: "PRAGMA compile_options" | wc -l
70
$ $(brew --prefix sqlite)/bin/sqlite3 :memory: "PRAGMA compile_options" | wc -l
62

stock reports 70 compile options, brew 62. Stock-only highlights: OMIT_LOAD_EXTENSION (Apple's build disables load_extension() and the .load dot-command entirely) and HAS_CODEC_RESTRICTED plus CCCRYPT256 (Apple's restricted-codec/CommonCrypto hooks, not reachable from ordinary SQL). Brew-only highlights: ENABLE_STAT4, ENABLE_GEOPOLY, ENABLE_PERCENTILE, ENABLE_OFFSET_SQL_FUNC, ENABLE_STMTVTAB, ENABLE_UNLOCK_NOTIFY, DIRECT_OVERFLOW_READ. Neither binary's option list contains an OMIT_FTS*, OMIT_JSON, or OMIT_RTREE flag — every guide-relevant extension (FTS3/4/5, JSON, R-Tree) is compiled into both. [local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

FTS5 availability was worth checking explicitly, and the answer is agreement, not a divergence: FTS5 is present on both binaries, including the Apple-shipped stock /usr/bin/sqlite3. A crude probe is a false lead — SELECT fts5('x') returns a blank line with exit code 0 on both binaries regardless of whether FTS5 is actually built in, since calling the FTS5 auxiliary function outside a tokenizer context just yields SQL NULL. Two probes that actually distinguish presence from absence:

$ sqlite3 :memory: "SELECT sqlite_compileoption_used('ENABLE_FTS5')"
1
$ sqlite3 :memory: "CREATE VIRTUAL TABLE t USING fts5(x)"

Both return 1 and succeed silently, respectively, on stock and brew. [local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

Bare sqlite3 on this machine's PATH resolves to the MacPorts build — neither the stock Apple build nor the newer Homebrew build sit on PATH at all. The Homebrew one must always be addressed through its real prefix: $(brew --prefix sqlite)/bin/sqlite3 — the exact construction every brew-side command in this guide uses, since assuming a bare sqlite3 on PATH reaches the Homebrew build is not a safe assumption here. [local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

A practical setup for working with both: an alias that always reaches the Homebrew build by its real prefix, leaving bare sqlite3 alone for whatever your PATH already resolves it to:

alias sqlite3-brew='$(brew --prefix sqlite)/bin/sqlite3'

Homebrew formulas lagging behind their own project's releases is a gotcha this guide has seen before in a completely different tool — see the Gas Town guide's §3 for Homebrew Core sitting two releases behind Gas Town's own tap. The lesson generalizes: check what a formula actually ships before trusting brew install/upgrade to be current.

One transcript convention from here on: for readability, this guide's transcripts write the command as plain sqlite3, the same way the upstream docs and most transcripts online do — they are not literally re-run through whatever bare sqlite3 resolves to on PATH. On a machine with this same shadowing, substitute /usr/bin/sqlite3 or the sqlite3-brew alias above for whichever binary a given transcript is actually demonstrating.

$ h1=$(/usr/bin/sqlite3 workshop.db .dump | shasum)
$ rm -f workshop2.db && /usr/bin/sqlite3 workshop2.db < build-workshop.sql
$ h2=$(/usr/bin/sqlite3 workshop2.db .dump | shasum)
$ [ "$h1" = "$h2" ] && echo DETERMINISTIC
DETERMINISTIC

$ rm -f workshop2.db workshop-brew.db

Sources: Command Line Shell For SQLite · local sqlite3 3.43.2 / 3.53.3 ground truth, captured 2026-07-17 · Gas Town guide §3 (Homebrew version-lag note)

4 · Shell Fluency

The sqlite3 shell speaks two grammars at one prompt, can be driven three different ways from outside a terminal, and renders the same query result in at least seven visibly different shapes. None of that is optional trivia — picking the wrong one is the difference between a script that works and one that silently breaks the moment someone else's ~/.sqliterc is involved.

Three ways to run it

Interactive — sqlite3 workshop.db with no further arguments drops you at the sqlite> prompt. One-shot — extra command-line arguments after the filename are each treated as one dot-command or SQL statement, evaluated in order, stdin never read; the docs' own example combines two positional arguments this way: sqlite3 test.db ".mode box" "SELECT * FROM users;". Piped — with no extra positional arguments, the shell reads and executes SQL from stdin until it's exhausted, then exits, non-interactively:

$ echo 'SELECT 1' | sqlite3 workshop.db
1

[cli.html; local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

$ sqlite3 workshop.db ".mode list
SELECT 1,2;"

$ sqlite3 -cmd ".mode box" workshop.db "SELECT id, title, status FROM issues LIMIT 1;"

The output-mode gallery

The identical 3-row query, rendered through -cmd ".mode X" -cmd ".headers on" in seven modes — every fragment below is the real, captured output (not reformatted); where the research notes only recorded a trimmed excerpt rather than the full 3-row transcript, that's exactly what's shown here, no filled-in gaps:

$ sqlite3 -cmd ".mode X" -cmd ".headers on" workshop.db "SELECT id, title, status FROM issues LIMIT 3;"

[local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

Shaping the output further

.headers on/off toggles the header row — verified to behave identically on both binaries. .width N N... fixes column widths in column mode; a real capture with .width 4 10 truncates/wraps a status value across two lines identically on both (in_progres / s for in_progress). .separator changes the field separator for list-family modes — a real .separator ';' capture in list mode came back byte-identical on both: id;status / 1;in_progress / 2;closed. .nullvalue TEXT sets what a SQL NULL prints as (default: empty string) — documented as a dot-command and mirrored by the -nullvalue startup flag; no local capture was made of this one specifically, so nothing beyond the documented default is claimed here. [cli.html; local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

Flags worth knowing

-cmd COMMAND runs a dot-command or SQL statement before stdin is read — usable multiple times, and distinct from the positional one-shot form. Every output mode also has a direct flag (-json, -line, -csv, -box, …one per mode, mutually exclusive); a real capture confirmed -json produces the same shape as .mode json and -line matches .mode line, including that same " = " vs ": " separator difference. -readonly opens the database read-only, already used throughout §2. [cli.html; local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

Redirecting output: .output, .once, .read

.output FILE redirects all subsequent query output to a file (no argument reverts to stdout); if FILE's first character is |, the rest runs as a shell command instead, and special forms -e/-x open a temp file in the system editor / spreadsheet app (.excel is literally an alias for .once -x). .once behaves the same way but reverts automatically after the next statement only. Both were verified for real (via -cmd, not the positional form, which hits the gotcha above): each wrote the expected count(*) result into a file and left stdout empty. .read FILE executes a .sql file's statements — verified printing 8 for SELECT count(*) FROM projects. [cli.html; local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

~/.sqliterc — powerful, and not on the official docs page

If ~/.sqliterc exists, the shell reads and executes it — as dot-commands or SQL — before any other input, on every invocation. This isn't documented on the live cli.html page itself (a full-text search of it turns up no "sqliterc"/"resource file" match at all); it's corroborated instead by the SQLite project's own forum, where a maintainer's answer to "how do I ignore my .sqliterc" is -init /dev/null. That same forum answer's own worked example of what people put there is .headers on and .mode column — exactly the kind of decorative, interactive-comfort setting this section is otherwise about. [SQLite forum]

[SQLite forum; SQLite forum]

.shell / .system, and --safe

.shell CMD and .system CMD (documented aliases of each other) run CMD in a real subshell and print its output — verified identically on both binaries (hello-from-shell, hello-from-system). At the opposite extreme, --safe (also -safe) disables everything that could touch anything other than the one database file named on the command line: .open (unless :memory: or --hexdb), ATTACH, side-effecting SQL functions (load_extension(), readfile(), writefile(), …), .shell/.system, .import, .load, .backup/.save, and .excel/.once/ .output. A one-time --nonce NONCE + .nonce NONCE pair can selectively re-enable exactly one restricted statement — the docs call .nonce dangerous if misused. A real probe confirms ordinary queries still run fine under --safe, while a blocked command fails on both binaries with the same core message, framed differently:

$ sqlite3 --safe workshop.db ".shell echo x"

stock: line 0: cannot run .shell in safe mode; brew: argv[3]: cannot run .shell in safe mode — the same "framing" difference (line N: vs argv[N]:) that recurs anywhere a one-shot invocation hits a runtime error. [cli.html; local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

"I want X" → mode or flag

[cli.html; climode.html]

Sources: Command Line Shell For SQLite · Query Result Formatting In The CLI · SQLite Release 3.33.0 changelog · SQLite forum: ignoring .sqliterc · SQLite forum: .sqliterc location · local sqlite3 3.43.2 / 3.53.3 ground truth, captured 2026-07-17

5 · Schema Spelunking

Every dot-command that shows you schema — .tables, .schema, .indexes — is a formatted query against one ordinary table, sqlite_schema (§1). This section queries it directly, compares the three ways SQLite exposes introspection (dot-commands, PRAGMA statements, and pragma_* functions), and ends with a blind schema-mapping lab against an unlabeled copy of workshop.db.

sqlite_schema: the table behind every dot-command

Every database has exactly one schema table, with no row describing itself: CREATE TABLE sqlite_schema(type text, name text, tbl_name text, rootpage integer, sql text);. sqlite_schema is the current name; sqlite_master is kept as a historical alternate that works everywhere (authorizer callbacks still refer to it by that name); sqlite_temp_schema/sqlite_temp_master are alternates that only resolve against a connection's TEMP database. [schematab.html]

Column type is one of 'table', 'index', 'view', 'trigger' — note 'table' covers virtual tables too; there is no separate 'virtual table' value. name is the object's name, except auto-generated indexes backing a UNIQUE/PRIMARY KEY constraint, which get synthetic names sqlite_autoindex_TABLE_N. tbl_name is the owning table (itself, for a table/view; the indexed table, for an index; the firing table, for a trigger). rootpage is 0/NULL for views, triggers, and virtual tables — they have no b-tree of their own. sql holds the normalized CREATE statement, NULL for the auto-generated constraint indexes. [schematab.html]

A real, captured example of that autoindex-naming rule: workshop.db's only UNIQUE-constrained columns without a covering PK — tags.name, users.username, projects.name — each produce exactly one autoindex, named sqlite_autoindex_tags_1, sqlite_autoindex_users_1, sqlite_autoindex_projects_1. issue_tags's composite PRIMARY KEY produces what pragma_index_list('issue_tags') reports as a fourth, sqlite_autoindex_issue_tags_1 — but because issue_tags is a WITHOUT ROWID table, that PK index has no b-tree of its own (the PK is the table's own clustered storage) and so has no sqlite_schema row at all. It's visible only through pragma_index_list, never through sqlite_schema or .indexes — only the first three autoindexes show up there, as the surprise below confirms. [local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

.tables and .schema are themselves just formatted queries against this table — .tables is roughly SELECT name FROM sqlite_schema WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%' ORDER BY 1 (already captured in §2's 14-name listing); .schema ?PATTERN? is roughly SELECT sql FROM sqlite_schema ORDER BY tbl_name, type DESC, name, optionally restricted to objects matching a glob pattern. Both, like .databases, cover every attached database unless explicitly restricted. [cli.html]

Querying sql directly, rather than going through .schema, is how you get a view or trigger's exact defining text. A real capture: SELECT sql FROM sqlite_schema WHERE type='view' and WHERE type='trigger' return byte-identical text on both binaries — exactly the CREATE VIEW/ CREATE TRIGGER statements from §3's Lab 0 script:

CREATE VIEW open_issues AS
  SELECT i.id, p.name AS project, i.title, i.priority, u.username AS author
  FROM issues i
  JOIN projects p ON p.id = i.project_id
  JOIN users u    ON u.id = i.author_id
  WHERE i.status <> 'closed';

CREATE TRIGGER trg_issue_close
AFTER UPDATE OF status ON issues
WHEN NEW.status = 'closed' AND OLD.status <> 'closed'
BEGIN
  INSERT INTO events (issue_id, actor_id, kind, at)
  VALUES (NEW.id, NEW.assignee_id, 'closed', NEW.created_at);
END;

[local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

.schema variants vs. querying sql directly

--indent reformats CREATE text for humans without changing what it says. §2 already captured this byte-for-byte for issue_tags (.schema --indent issue_tags, ending ) WITHOUT ROWID; with no space before either paren — the real transform only strips column-alignment whitespace and tightens CHECK (...) to CHECK(...)). Separately, .schema issues and .schema --indent issues were captured as byte-identical to each other on both binaries, planted quirk comments (-- quirk: NULL-heavy, -- quirk: mixed affinity) preserved verbatim in the stored text — comments inside a CREATE TABLE's parens really do live on in sqlite_schema.sql, not just in your source file. [local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

The same preservation is why .schema comments still shows the generated column inline, verbatim, exactly as §3's Lab 0 script wrote it — the flagship setup for the table_info/table_xinfo comparison below:

CREATE TABLE comments (
  id         INTEGER PRIMARY KEY,
  issue_id   INTEGER NOT NULL REFERENCES issues(id),
  author_id  INTEGER NOT NULL REFERENCES users(id),
  body       TEXT NOT NULL,
  body_len   INTEGER GENERATED ALWAYS AS (length(body)) VIRTUAL,
  created_at TEXT NOT NULL
);

[local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

.fullschema shows the whole schema plus a trailing sqlite_stat1 dump, bracketed by literal ANALYZE sqlite_schema; lines — 82 lines total, identical count on both binaries, including two representative captured rows: INSERT INTO sqlite_stat1 VALUES('issues','idx_issues_project','500 63'); and INSERT INTO sqlite_stat1 VALUES('issue_tags','issue_tags','826 2 1');. A full diff of the 82 lines between binaries found exactly one difference — the FTS5 shadow-table CREATE TABLE statements themselves: stock emits CREATE TABLE IF NOT EXISTS 'issue_search_data'(id INTEGER PRIMARY KEY, block BLOB);; brew emits the same line without IF NOT EXISTS. Every other CREATE statement and all 11 sqlite_stat1 rows matched exactly. [local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

.indexes with no argument holds this section's biggest surprise: stock lists all 5 real indexes, including the three sqlite_autoindex_* entries; brew's bare .indexes shows only the 2 named indexes, silently omitting all three autoindexes (both binaries still know about them — .indexes % and a direct sqlite_schema query show all 5 on both; this is a .indexes-specific default-filtering change, not a schema difference). .indexes issues — the single-table form — returns the identical single row (idx_issues_project) on both. [local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

PRAGMA table_info vs. table_xinfo

PRAGMA table_info(table) returns one row per normal column: cid (rank in this result set, not a stable id), name, type, notnull, dflt_value, pk (0, or 1-based position within the primary key). It does not report generated or hidden columns. A real capture on issues returned the documented 9-row cid|name|type|notnull|dflt_value|pk shape identically on both binaries, id correctly flagged pk=1, every other column pk=0. PRAGMA table_xinfo(table) adds a hidden column (0 normal, 2/3 a generated column, 1 a virtual-table hidden column) and is the complete superset — its extra rows are exactly what table_info omits. [pragma.html; local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

The concrete, real demonstration is comments' generated body_len column from above:

[local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

The pragma_* table-valued form, and the flagship join

Pragmas with no side effects can also be called as table-valued functions (pragma_table_info('t') instead of PRAGMA table_info(t)) — added in SQLite 3.16.0 (2017-01-02). The payoff is that a function-form pragma can be filtered with WHERE, aggregated, and — the one thing a plain PRAGMA statement can never do — joined against another table or pragma, one row per match. [pragma.html]

That join is this section's flagship recipe: every column of every real table, one row per column, in a single query —

SELECT m.name, p.name, p.type, p."notnull", p.pk
FROM sqlite_schema m
JOIN pragma_table_info(m.name) p
WHERE m.type='table' AND m.name NOT LIKE 'sqlite_%';

A real run of exactly this join returned 45 rows on both binaries, and diff-ing the two outputs reported zero differences. The notes don't carry the full 45-row transcript, but they do independently confirm this excerpt of it — the same 5 comments rows already captured above (join output reformatted as table.column, no new data):

comments|id
comments|issue_id
comments|author_id
comments|body
comments|created_at

[local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

Foreign keys

PRAGMA foreign_key_list(table) returns one row per REFERENCES constraint declared in that table's CREATE TABLE. A real capture on issues returned all 3 of its declared foreign keys, every one NO ACTION/NO ACTION/NONE for on-update/on-delete/match:

[pragma.html; local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

The rest of the FK map reads straight off §3's Lab 0 script (already byte-verified against the built database) rather than a fresh pragma capture per table: projects.owner_id → users.id, comments.issue_id → issues.id, comments.author_id → users.id, issue_tags.issue_id → issues.id, issue_tags.tag_id → tags.id. One column looks like it should be on this list and isn't: events.issue_id is declared plain INTEGER NOT NULL, with no REFERENCES clause at all — PRAGMA foreign_key_list(events) returns zero rows. The relationship is real (every event was generated against a valid issue id), just not enforced by the schema; Lab 2 below asks you to notice exactly this. [local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

Indexes: index_list / index_info

PRAGMA index_list(table) lists a table's indexes (name, uniqueness, origin — c = CREATE INDEX, u = UNIQUE constraint, pk = PRIMARY KEY constraint); PRAGMA index_info(index) lists that index's key columns. Three real captures:

[pragma.html; local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

WITHOUT ROWID detection

§2 already captured .schema --indent issue_tags ending ) WITHOUT ROWID; — the most direct tell. The pragma_index_list('issue_tags') capture just above is a second, independent signal: a WITHOUT ROWID table's declared PRIMARY KEY shows up as an origin=pk autoindex the same way an ordinary rowid table's would. Separately (documented, not locally re-verified): since SQLite 3.30.0, running PRAGMA index_info/index_xinfo with a WITHOUT ROWID table's own name — when no index by that name exists — returns that table's physical key layout directly. [pragma.html; local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

STRICT tables

CREATE TABLE ... STRICT (added in SQLite 3.37.0, 2021-11-27) swaps manifest typing for rigid, container-based typing. A real probe — CREATE TABLE strict_probe(x INTEGER) STRICT on a throwaway database — succeeds silently on both binaries; workshop.db itself has no STRICT tables (the planted mixed-affinity quirk on issues.priority specifically needs manifest typing to demonstrate). Inserting a TEXT value into that STRICT INTEGER column is rejected on both, framing differs the same way it does everywhere else in this guide:

$ sqlite3 strict-test.db "INSERT INTO strict_probe(x) VALUES ('not-a-number')"

stock: Error: stepping, cannot store TEXT value in INTEGER column strict_probe.x (19) (19 = SQLITE_CONSTRAINT); brew: Error in 2nd command line argument: cannot store TEXT value in INTEGER column strict_probe.x (no numeric code). The core message is identical; only the wrapper differs. [datatype3.html; local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

FTS5, virtual tables, and shadow-table noise

CREATE VIRTUAL TABLE ... USING fts5(...) still gets type='table' in sqlite_schema — there's no separate virtual-table type value. A real query pins this down exactly:

$ sqlite3 -readonly workshop.db "SELECT sql FROM sqlite_schema WHERE name='issue_search'"
CREATE VIRTUAL TABLE issue_search USING fts5(title, body)

Creating that one virtual table also creates 5 real backing "shadow" tables — issue_search_data, issue_search_idx, issue_search_content, issue_search_docsize, issue_search_config — confirmed both by §2's .tables capture (all 6 names mixed in alphabetically with the 8 ordinary tables, no visual distinction) and by SELECT type, name, tbl_name FROM sqlite_schema WHERE name LIKE 'issue_search%', whose 6 rows all carry type='table' and name = tbl_name. The naming suffix is the only tell — schema type alone never distinguishes FTS5 machinery from an ordinary table. [fts5.html; local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

Dot-command vs. PRAGMA vs. pragma_*

[pragma.html; cli.html]

Sources: The Schema Table · PRAGMA Statements · Command Line Shell For SQLite · SQLite FTS5 Extension · Datatypes In SQLite · local sqlite3 3.43.2 / 3.53.3 ground truth, captured 2026-07-17

6 · Contents Profiling

Knowing the schema (§5) tells you the shape of the containers. It says nothing about what's actually inside them. This section is a small toolkit of shell recipes for looking at real values — how much of a column is NULL, how many distinct values it holds, what range it spans — and a reminder that SQLite will cheerfully let a column hold data its own declared type never promised. Lab 3 turns the toolkit loose on workshop.db to find two things planted in it that a schema read alone would never reveal.

Sampling without loading everything

Two LIMIT variants cover most first looks at a table: SELECT * FROM t LIMIT 5 for the lowest-rowid rows (a "head"), SELECT * FROM t ORDER BY id DESC LIMIT 5 for the highest (a "tail"). Both were run against issues and returned identical rows on both binaries — the same kind of rowid-ordered sample already captured in §4's output-mode gallery (SELECT id, title, status FROM issues LIMIT 3, first row 1|Issue 1: crash on startup|in_progress) is exactly this pattern in practice, so it isn't reproduced a second time here. [local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

Why "random" isn't free

ORDER BY random() looks like a cheap way to eyeball a sample, but there's no index SQLite could ever build for it — random() returns a different value on every invocation, so the only way to honor the ORDER BY is to evaluate it for every row and sort the results, i.e. a full table scan, every time, no matter how the table is indexed elsewhere. .timer on makes the cost visible, with one binary-specific catch:

$ sqlite3 -cmd ".timer on" workshop.db "SELECT id, title FROM events ORDER BY random() LIMIT 5;"

stock: the query runs and returns rows, but .timer on is a silent no-op — no error, and no Run Time: line ever prints, on this or any other query. brew, identical invocation, prints a real timing line after the same query against the 5000-row events table:

Run Time: real 0.001437 user 0.001059 sys 0.000374

Every "compare cold vs. indexed query cost with .timer on" recipe in this guide — including Lab 4 below — only actually shows a number on the Homebrew binary; stock will run the same query just as correctly, just silently. The row values themselves aren't reproduced here (the notes preserved the timing, not a five-row transcript of events) — the point of this capture is the cost, not the content. [local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

Counting everything: the flagship loop, applied to profiling

§2's step 6 already established the recipe for a per-table row count in one pass — sqlite_schema filtered to real tables, piped through a while read loop calling count(*) per name. That same loop is this section's starting point for profiling too: before asking anything about a column's contents, know how many rows you're profiling. Re-run against workshop.db itself (not mystery.db) and you get the identical 13-table listing already shown there — events 5000 still the largest by a wide margin. [local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

Profiling one column: NULLs, distinctness, range

Three cheap aggregate recipes cover most of what's worth knowing about a single column, none of them requiring anything beyond ordinary SQL: count(*) - count(col) for a NULL count (count() ignores NULL, count(*) doesn't, so the difference is exactly the NULL count); count(DISTINCT col) for cardinality; min(col) / max(col) for range, meaningful on any orderable type including SQLite's TEXT dates. A real, non-quirk-spoiling profile of a few issues columns:

[local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

Low cardinality is the tell worth acting on: a 3-value column like status is cheap to fully characterize with one GROUP BY, and the shape is worth memorizing before you go looking for anything subtler —

$ sqlite3 workshop.db "SELECT status, count(*) FROM issues GROUP BY 1 ORDER BY 2 DESC;"
closed|345
open|103
in_progress|52

[local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

typeof(): what a column actually holds

SQLite's own documentation demonstrates this with a query that pulls a single row through several differently-affinitied columns and shows typeof() reporting a different storage class for each, depending only on what was actually inserted — not what the column was declared as. The mechanics: every affinity is derived from the declared type name by substring match ("INT" anywhere → INTEGER affinity, "CHAR"/"CLOB"/ "TEXT" → TEXT, "BLOB" or no type at all → BLOB, "REAL"/ "FLOA"/"DOUB" → REAL, otherwise → NUMERIC), and an INTEGER-affinity column will store a string like 'high' as genuine TEXT storage the moment it isn't a well-formed numeric literal — silently, with no error, on an ordinary table. typeof(X) is how you catch this after the fact: it reports the value's actual storage class, independent of whatever affinity its column has. [datatype3.html]

STRICT tables (CREATE TABLE ... (...) STRICT, SQLite 3.37.0, 2021-11-27) are the opt-in fix — rigid, container-based typing instead of manifest typing. workshop.db has none; a throwaway STRICT probe table confirms the enforcement is real when you opt into it (an INTEGER-affinity STRICT column rejects a text insert outright, framed differently per binary but the same underlying SQLITE_CONSTRAINT either way) — covered already in §5. [datatype3.html]

Finding interesting columns fast

Put the three techniques together and a quick triage emerges: run count(DISTINCT col) across every column you don't already understand — anything low (single digits on a 500+-row table) is a GROUP BY candidate worth characterizing fully, the way status was above. Run the NULL-count recipe on anything nullable — most come back boring (a handful of missing emails, an expected "still open" gap), but an unusually high rate is worth a second look. And run typeof() ... GROUP BY 1 on any numeric-looking column you don't fully trust, especially one with no STRICT or CHECK backing it — most will report exactly one storage class every time; a column reporting more than one is telling you something a .schema read never would.

Sources: Datatypes In SQLite · local sqlite3 3.43.2 / 3.53.3 ground truth, captured 2026-07-17

7 · Analysis & Query Plans

§6 found the interesting columns. This section is about asking real questions of them — reproducibly, with the query saved rather than retyped — and about reading EXPLAIN QUERY PLAN well enough to know whether SQLite is doing that efficiently. Reader's assumed comfort with window functions and CTEs already; the commentary below is about shell mechanics, not SQL semantics.

Making analysis reproducible: heredoc, .read, .once

§4 already verified the two building blocks a real analysis session composes: piped stdin (echo 'SQL' | sqlite3 db — non-interactive, runs to EOF and exits) and .read FILE (executes a saved .sql file's statements — verified printing 8 for SELECT count(*) FROM projects). A heredoc is nothing more than bash handing multi-line stdin to that same piped form, so a whole analysis session — mode-setting dot-commands and all — can be typed once and piped in directly, no separate file required:

$ sqlite3 -readonly workshop.db <<'SQL'
.headers on
.mode column
SELECT status, count(*) FROM issues GROUP BY 1 ORDER BY 2 DESC;
SQL

For anything worth re-running as the data changes, save it as a file instead — .read analysis.sql — and reach for .once FILE when you want one statement's result captured without redirecting everything after it: also already verified in §4 (wrote a count(*) result to a file and left stdout untouched). The three compose directly: write the query once into analysis.sql, then sqlite3 -cmd ".once result.txt" workshop.db ".read analysis.sql" runs it and captures the output in one line. [cli.html; local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

Four worked examples

All four ran with .timer on — per the version note below, only the Homebrew binary actually prints a Run Time: line, but the result rows themselves were identical on both. Each is exactly the kind of query worth saving as analysis.sql rather than retyping at the prompt. [local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

Issues closed per month, with a running total — a window function (SUM(...) OVER (ORDER BY ym)) over a monthly GROUP BY. Worth capturing with .mode list exactly as shown (pipe-separated, script-friendly) rather than a boxed table you'd have to reformat before charting it:

$ sqlite3 workshop.db "SELECT strftime('%Y-%m', closed_at) AS ym, count(*) AS closed,
       sum(count(*)) OVER (ORDER BY strftime('%Y-%m', closed_at)) AS running_total
FROM issues WHERE closed_at IS NOT NULL GROUP BY ym ORDER BY ym;"
2024-01|3|3
2024-02|6|9
2024-03|18|27
2024-04|20|47
2024-05|16|63
2024-06|25|88
...

Real output continues for 20 months total, ending 2025-08|4|345 — the full 345 closed issues accounted for. [local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

Top-5 users by event count, with RANK() — worth running exactly this way specifically because the real data has a tie, and RANK()'s tie behavior (leaving a gap in the numbering equal to the tie size) is the kind of thing easy to get wrong from memory and worth confirming against a live example instead:

$ sqlite3 workshop.db "SELECT actor_id, n, rnk FROM (
  SELECT actor_id, count(*) AS n, RANK() OVER (ORDER BY count(*) DESC) AS rnk
  FROM events WHERE actor_id IS NOT NULL GROUP BY actor_id
) ORDER BY rnk LIMIT 5;"
28|136|1
16|124|2
15|124|2
5|124|2
26|122|5

Three users tie at 124 events and all three correctly get rank 2; the next distinct value (user 26, 122 events) jumps straight to rank 5 — RANK() counts the tied rows themselves into the gap, unlike DENSE_RANK(), which would have given user 26 rank 3. [local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

Per-project resolution time, approximate median via NTILE(4) — partitioning by project_id and bucketing each partition's issues into quartiles by days-to-close, then averaging the 2nd-quartile bucket, is a cheap approximate median that doesn't need a separate percentile extension:

$ sqlite3 workshop.db "SELECT project_id, round(avg(days), 1) FROM (
  SELECT project_id, julianday(closed_at) - julianday(created_at) AS days,
         NTILE(4) OVER (PARTITION BY project_id
                         ORDER BY julianday(closed_at) - julianday(created_at)) AS q
  FROM issues WHERE closed_at IS NOT NULL
) WHERE q = 2 GROUP BY project_id ORDER BY project_id;"
1|39.0
2|35.8
3|34.8
4|34.3
5|40.0
6|38.2
7|39.3
8|52.5

[local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

Busiest issue by combined comments + events — a correlated subquery per row, summed and sorted. The technique worth noting here is .once-ing the result straight to a file the moment you find something worth keeping, rather than re-running the query later and hoping the data hasn't shifted underneath you:

$ sqlite3 workshop.db "SELECT i.id,
       (SELECT count(*) FROM comments c WHERE c.issue_id = i.id) AS n_comments,
       (SELECT count(*) FROM events e WHERE e.issue_id = i.id) AS n_events,
       (SELECT count(*) FROM comments c WHERE c.issue_id = i.id)
         + (SELECT count(*) FROM events e WHERE e.issue_id = i.id) AS total
FROM issues i ORDER BY total DESC LIMIT 5;"
358|6|20|26
8|4|21|25
123|9|15|24
20|9|15|24
368|6|18|24

Issue 358 wins outright at 26 combined; two issues (123 and 20) genuinely tie at 24. [local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

Reading EXPLAIN QUERY PLAN: SCAN vs. SEARCH

EXPLAIN QUERY PLAN prefixed on any statement shows what SQLite intends to do without running it. SCAN means a full-table walk — every row visited, in table or index order — while SEARCH means only a subset of rows are visited, via an index lookup. A SEARCH ... USING COVERING INDEX line is stronger still: the index alone holds every column the query needs, so SQLite never touches the underlying table b-tree at all for those rows. [eqp.html]

Pulling one issue's event breakdown by kind is a real, concrete case to check this against: it means filtering events on issue_id, the exact column §5's Lab 2 already flagged as declared with no REFERENCES and no index. Before creating one:

$ sqlite3 workshop.db "EXPLAIN QUERY PLAN SELECT kind, count(*) FROM events WHERE issue_id = 42 GROUP BY kind;"
QUERY PLAN
|--SCAN events
`--USE TEMP B-TREE FOR GROUP BY

A full scan of all 5000 events rows, plus a temp b-tree to satisfy the GROUP BY. Creating the missing index and re-running the identical EXPLAIN QUERY PLAN:

$ sqlite3 workshop.db "CREATE INDEX idx_events_issue ON events(issue_id);"
$ sqlite3 workshop.db "EXPLAIN QUERY PLAN SELECT kind, count(*) FROM events WHERE issue_id = 42 GROUP BY kind;"
QUERY PLAN
|--SEARCH events USING INDEX idx_events_issue (issue_id=?)
`--USE TEMP B-TREE FOR GROUP BY

SCAN becomes SEARCH ... USING INDEX idx_events_issue (issue_id=?) — only the matching rows for issue_id=42 are visited now; the GROUP BY still needs its own temp b-tree either way, since nothing indexes kind. This exact before/after text is byte-identical between binaries — no framing difference, no version note needed here. The index was dropped again immediately after capture, restoring workshop.db's planted no-index state for the rest of this guide. [eqp.html; local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

.timer and .stats: the other half of "is this fast?"

EXPLAIN QUERY PLAN tells you the shape of the work; .timer on and .stats put real numbers on it. Both carry the same binary-specific catch already demonstrated in §6 — enabling .timer on is silent on stock, whichever query you run; on brew the same query prints a real Run Time: line, as shown throughout this section. .stats on goes further, printing a ~20-line memory/page-cache/step-count report after every statement — mostly identical between binaries, but brew's report carries one extra line stock's never does: Temporary data spilled to disk:, worth watching specifically on the kind of GROUP BY-without-an-index query the EQP capture above just flagged. [local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

Sources: Command Line Shell For SQLite · EXPLAIN QUERY PLAN · local sqlite3 3.43.2 / 3.53.3 ground truth, captured 2026-07-17

8 · Export & Import

Everything so far has stayed inside the shell. This section is about getting data back out — to a spreadsheet, to jq, to another SQLite file, to plain diffable text — and back in again, plus the one distinction worth internalizing before §9: a binary copy and a textual dump are not the same kind of backup, and they fail differently.

CSV export

The standard recipe is three dot-commands then one query: .headers on, .mode csv, .once FILE, then the SELECT. A real capture of exactly this against issues produced byte-identical file content on both binaries — diff of the two produced .csv files reported no differences at all:

$ sqlite3 -cmd ".headers on" -cmd ".mode csv" -cmd ".once issues.csv" workshop.db \
    "SELECT id, title, status FROM issues LIMIT 3;"
$ cat issues.csv
id,title,status
1,"Issue 1: crash on startup",in_progress
...

RFC 4180 quoting kicks in automatically on the comma-bearing title field — nothing extra to configure. .once here is doing exactly the job §7 established: redirect one statement's output, then automatically revert. [local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

JSON, three ways

.mode json is the simplest: set it, run the query, get an array of objects back. A real capture, identical on both binaries:

$ sqlite3 -cmd ".mode json" workshop.db "SELECT id, title, status FROM issues LIMIT 3;"
[{"id":1,"title":"Issue 1: crash on startup","status":"in_progress"}, ...]

json_group_array(json_object(...)) builds the identical shape from inside SQL itself, as a single scalar value — useful when you want the JSON text as one row you can further process with SQLite's own json_* functions, or hand to something that expects exactly one value rather than a multi-row result set:

$ sqlite3 workshop.db "SELECT json_group_array(json_object('id', id, 'title', title, 'status', status))
FROM (SELECT id, title, status FROM issues LIMIT 3);"

Verified to produce identical JSON text to .mode json's output on both binaries. The -json shell flag is the third form — sqlite3 -json workshop.db "SELECT ..." — and matches .mode json's shape too, on both binaries; reach for it when you want JSON from a one-shot invocation without a separate -cmd. [local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

[local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

.dump: full, single-table, and piped straight into a new database

.dump converts the database (or, given a table argument, just that table) into plain SQL text — CREATE plus INSERT statements that reconstruct it exactly when piped back into sqlite3. .dump issues was verified byte-identical between binaries, CREATE TABLE and all 500 rows' INSERT text included, zero diff output. The same holds for .dump users, and piping it straight into a brand-new database is a real, working recipe for lifting one table out on its own:

$ sqlite3 workshop.db ".dump users" | sqlite3 users-only.db
$ sqlite3 users-only.db "SELECT count(*) FROM users;"
40

Verified round-tripping correctly on both binaries — the piped-into database ends up with exactly the 40 rows users actually has. [local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

A full .dump (no table argument) carries one binary difference already established in §3's Lab 0: brew prepends a leading /* WARNING: Script requires that SQLITE_DBCONFIG_DEFENSIVE be disabled */ comment that stock never emits. Everything after that first line is otherwise the same kind of byte-identical text just demonstrated for the single-table form. [local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

.import: CSV round-trip, and a header-row footgun worth knowing

.import FILE TABLE's handling of the first input row depends entirely on whether TABLE already exists. If it does not, the table is auto-created using that first row as column names, and data starts from row two. If it already exists, every row — including the first — is treated as data, which is exactly why --skip N exists: skip the header explicitly when importing into a pre-existing table. Both the modern form (--csv --skip 1) and the older flagless form behave identically on both binaries — no version difference here, contrary to what you might expect given how much else in this guide differs between stock and brew. [cli.html; local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

.excel

.excel is documented as a literal alias for .once -x: write the query result as CSV into a temporary file, invoke the system's default handler for CSV files (typically Excel or LibreOffice), then delete the temp file once that application has it open. Not run in this environment — it launches a real GUI application with no scriptable exit, so it's documented here from .help/cli.html text only, the same caveat already noted in §4. [cli.html]

.backup / .clone vs. .dump

.backup ?DB? FILE (an alias for .save) and .clone NEWDB are file/binary-level operations — already used in Lab 1 to produce mystery.db, a byte-faithful copy. .dump is categorically different: plain SQL text, logical rather than binary. The WARNING-line and FTS5-shadow-table differences already seen in this guide are proof that two different sqlite3 builds do not produce identical dumps of identical data, even though both binaries round-trip that same data correctly.

[cli.html]

$ rm -f issues.csv summary.json users-only.db reimport.db

Sources: Command Line Shell For SQLite · local sqlite3 3.43.2 / 3.53.3 ground truth, captured 2026-07-17

9 · Safety

Every command so far has been friendly: one reader, one writer, one sqlite3 process at a time. Production databases are rarely that polite — another process may hold a lock, a background job may be mid-write, and the file on disk may be in a state your shell session did not create. This section is about that reality: what SQLite's locking model actually guarantees, what WAL mode changes about it, and how to open, read, and back up a live database without becoming the reason it breaks.

The locking model, from the shell's point of view

Every connection — including a one-shot sqlite3 invocation — moves through a small state machine as it reads and writes: UNLOCKED (no access; other processes have full access), SHARED (many processes may hold SHARED at once to read, but none may write), RESERVED (a single process intends to write soon, while existing SHARED readers keep reading), PENDING (a would-be writer is waiting for every current SHARED reader to clear — and, critically, no new SHARED lock is granted once a connection holds PENDING), and EXCLUSIVE (required to actually write; no other lock of any kind coexists with it). [lockingv3.html]

SQLITE_BUSY is what a connection gets back when it fails to obtain a lock it needs because another connection already holds a conflicting one: "If the SHARED lock cannot be obtained, fail immediately and return SQLITE_BUSY," and "If the process that wants to write is unable to obtain a RESERVED lock, it must mean that another process already has a RESERVED lock. In that case, the write attempt fails and returns SQLITE_BUSY." Notice what the docs do not say: neither this page, rescode.html, nor c3ref/busy_timeout.html ever spells out the plain-English string a real sqlite3 session prints for this — that only shows up once you actually trigger it, below. [lockingv3.html]

Reproducing "database is locked" for real

The obvious demo — run a writer in the background, sleep briefly, run a second writer — turned out not to reproduce a lock: a one-shot CLI invocation's connection (and its transaction) closes the instant its last statement finishes, which happens almost immediately, so the "background" writer had already committed and released its lock before the second one ever ran. Getting a genuine lock takes keeping the first writer's transaction open on purpose — piping its SQL through a subshell that sleeps after printing it, so the CLI's interactive read loop (and the transaction) stays alive until the sleep ends:

$ ( { printf 'PRAGMA busy_timeout=0;\nBEGIN IMMEDIATE;\nSELECT %s;\n' "'holding lock'"; sleep 2; } \
      | sqlite3 workshop.db ) &
$ sleep 0.5
$ sqlite3 workshop.db "PRAGMA busy_timeout=0; INSERT INTO tags(name) VALUES('locked-probe')"

With this recipe, stock fails with Error: stepping, database is locked (5) (5 = SQLITE_BUSY — the real-world source of that exact string, which does not appear anywhere on sqlite.org's own docs pages); brew fails with the same core message framed differently: Error in 2nd command line argument: database is locked. [local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

PRAGMA busy_timeout = milliseconds; is the fix: it installs a busy handler that retries a locked operation for up to the given time instead of failing immediately. Demonstrated for real — same recipe, writer's PRAGMA busy_timeout=3000 instead of 0, holder releasing its lock after 1.5s: the writer's INSERT blocked for ~1.3 real seconds, then succeeded with no error at all. Each connection gets exactly one busy handler; setting this pragma installs (and can overwrite) that connection's. [pragma.html; local sqlite3 3.43.2, captured 2026-07-17]

Journal modes: rollback vs. WAL

A fresh workshop.db reports PRAGMA journal_mode as delete — the default rollback-journal mode, where SQLite writes changed pages' old content to a -journal sidecar before touching the main file, then deletes that sidecar on commit (§2's sidecar glob came back empty for exactly this reason). WAL mode inverts the arrangement: the main file is left untouched at commit time, and committed changes are appended as "frames" to a -wal sidecar instead — "a COMMIT occurs when a special record indicating a commit is appended to the WAL," not by writing to the main database file at all. A second sidecar, -shm (the wal-index, shared memory), holds an index that "helps readers locate pages in the WAL quickly and with a minimum of I/O" — it isn't the log itself, just a fast lookup into it. [wal.html]

Moving committed WAL frames back into the main file is called a checkpoint — the mechanism that keeps -wal from growing forever. SQLite checkpoints automatically whenever a commit pushes the WAL past roughly 1000 pages, and whenever the last open connection to the file closes. PRAGMA wal_checkpoint(MODE) forces one on demand: PASSIVE (the default, and the only mode automatic checkpointing uses) does as much as it can without blocking anyone; FULL and RESTART "try harder" and will block (invoking the busy-handler) until there's no writer and every reader is on the latest snapshot. Real capture on a fresh workshop.db: journal_mode=WAL reports back wal; wal_checkpoint(TRUNCATE) reports 0|0|0 (nothing to checkpoint yet); switching back reports delete again — identical on both binaries. WAL mode is also persistent once set: it survives close and reopen (unlike other journal modes, which revert to DELETE), because WAL mode is a property of the file itself, applying to every connection once any one sets it. This guide's own ground truth confirmed the mode switch and checkpoint output above but never separately captured a directory listing (ls workshop.db*) showing the -wal/ -shm sidecar files actually present on disk while in that mode — flagged here rather than asserted from memory; what is real, captured ground truth is the pragma output above and the round-trip page-count DIFF just below. [wal.html; pragma.html; local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

That round trip isn't equally clean on both binaries, though — a real, verified DIFF, not a formatting quirk. Cycling an identical copy through journal_mode=WAL → wal_checkpoint(TRUNCATE) → journal_mode=DELETE, with zero writes in between: stock's copy grew from 156 to 169 pages (13 leftover freelist pages, +53,248 bytes) and left a stray -shm file behind even after returning to journal_mode=DELETE; brew's copy returned to exactly 156 pages, 0 freelist pages, its original byte size, with no stray sidecar files at all. Merely cycling stock's sqlite3 through WAL mode and back — touching no data at all — permanently grows the file until a VACUUM (§10) cleans it up. [local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

Reading a live database safely

§2 already established the baseline: -readonly (or the equivalent file:...?mode=ro URI) makes a write fail instead of silently landing — real capture, both forms, both rejected, tags's row count unchanged before and after. The file: URI's mode parameter takes ro, rw, rwc (read-write, create if missing), or memory; any scheme other than file: just makes the whole string an ordinary filename instead of a URI (recognized since SQLite 3.15.0, 2016-10-14). [uri.html]

?immutable=1 goes further — it tells SQLite the file is on read-only media and will never change, even by another process, letting it skip locking and change-detection entirely for a real performance win. That guarantee is enforced by trust, not verified: the docs are explicit that if the underlying content changes anyway, SQLite may return incorrect results or SQLITE_CORRUPT errors. Real capture: SELECT count(*) FROM tags through file:workshop.db?immutable=1 correctly returns 12 on both binaries — but that's only safe because nothing was writing to the file at the same time. Reach for immutable=1 only on a genuinely static file (an archived snapshot, a bundled reference db); reach for plain -readonly / mode=ro for anything another process might still be writing. [uri.html; local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

.backup vs. a plain cp — and where --safe fits

[howtocorrupt.html; wal.html; local sqlite3 3.43.2, captured 2026-07-17]

§4 already covered --safe, the CLI's own least-privilege mode (disables .open, .shell/.system, and everything else that could touch the outside world). It belongs in this conversation for the same reason -readonly does: both narrow what your own shell session is allowed to do to a file, rather than trusting yourself not to make a mistake. The one register-backed DIFF there — stock's line 0: cannot run .shell in safe mode vs. brew's argv[3]: cannot run .shell in safe mode — is the same message-framing pattern as the locked-database error above, not a new one.

Sources: Write-Ahead Logging · File Locking And Concurrency In SQLite Version 3 · How To Corrupt An SQLite Database File · Uniform Resource Identifiers · PRAGMA Statements · local sqlite3 3.43.2 / 3.53.3 ground truth, captured 2026-07-17

10 · Maintenance

A database that opens and answers queries correctly today can still be quietly bloated, statistically stale, or one bad byte away from failing tomorrow. This section covers the maintenance side of stewardship: checking a file's integrity, accounting for its size, keeping the query planner honest, and — when integrity checking says something is actually wrong — how much of a corrupted file you can realistically get back.

Integrity checks: integrity_check vs. quick_check

PRAGMA integrity_check (optionally integrity_check(N) or integrity_check(TABLENAME)) is a low-level scan for out-of-sequence entries, misformatted records, missing pages, missing/surplus index entries, constraint violations, freelist problems, and doubly-used or unused sections of the file. On a healthy database it returns exactly one row, the literal string ok — real capture, both binaries, against the pristine workshop.db:

$ sqlite3 workshop.db "PRAGMA integrity_check;"
ok

PRAGMA quick_check runs the same kind of scan but skips the UNIQUE-constraint and index-vs-table cross-checks, making it O(N) instead of O(N log N) in total row count — a cheaper "is this file grossly broken" sanity check when a full integrity_check would be too slow. It also reports ok here. Neither catches FOREIGN KEY violations — PRAGMA foreign_key_check is the tool for that. Lab 6 below shows what both look like against a database that isn't healthy. [pragma.html; local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

Size accounting: page_size × page_count, and where freed space goes

PRAGMA page_size, page_count, and freelist_count are the three numbers behind every SQLite file's size on disk: page_size × page_count is the file's real byte size, and page_size × freelist_count is how much of that is dead weight already reclaimable by VACUUM. §2 already confirmed the pristine numbers: 4096-byte pages, 156 pages, 0 freelist pages — 4096 × 156 = 638,976, matching ls -l workshop.db exactly. [pragma.html; local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

Deleting rows doesn't shrink a file — it just marks pages reusable. Real capture, on a copy: DELETE FROM events WHERE id > 4000 leaves page_count unchanged at 156 but pushes freelist_count to 10, on both binaries. Running VACUUM on that same copy rebuilds the whole file — 0 freelist pages, 144 total pages, the file dropping to 589,824 bytes, again identical on both. VACUUM INTO 'compact.db' — a separate, non-destructive snapshot that leaves the source file untouched — produced a 634,880-byte file with all 500 issues rows intact; that number is real but doesn't line up exactly with the in-place VACUUM figure just above, and this guide's own ground truth doesn't reconcile the small discrepancy further — flagged here rather than explained away. [lang_vacuum.html; local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

VACUUM needs up to twice the file's size in free disk space (it builds the new copy before discarding the old), fails if the connection running it has an open transaction, and can change the ROWID of any row in a table without an explicit INTEGER PRIMARY KEY — worth knowing before running it against a table some external code has cached rowids from. [lang_vacuum.html]

ANALYZE, sqlite_stat1, and PRAGMA optimize

ANALYZE gathers statistics about tables and indexes and stores them where the query planner can use them to pick better plans — most valuable after a bulk load or schema change, and exactly what makes an EXPLAIN QUERY PLAN choice like §7's SCAN→SEARCH swap reflect real table shape rather than a guess. The default implementation stores everything in one table, sqlite_stat1. Real capture: ANALYZE followed by SELECT * FROM sqlite_stat1 LIMIT 5 returns 5 rows of the identical 3-column shape (tbl|idx|stat) on both binaries — but the specific 5 rows differ between runs, which is expected and not a version DIFF: the query has no ORDER BY, and sqlite_stat1 has no defined row order. (A separately captured example row, issues|idx_issues_project|500 63, shows the shape concretely — the first number in stat is the table's approximate row count.) PRAGMA optimize ran silently — no output, exit 0 — on both. [lang_analyze.html; local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

Running ANALYZE by hand is no longer the recommended workflow: applications with short-lived connections should run PRAGMA optimize; once, just before closing each connection (long-lived connections should run PRAGMA optimize=0x10002; once right after opening, then plain PRAGMA optimize; periodically). PRAGMA optimize "is usually a no-op but it will occasionally run one or more ANALYZE subcommands" when it judges statistics are stale or missing — and since SQLite 3.46.0 it automatically limits their scope so the command stays fast even on a large database. [lang_analyze.html]

$ rm -f corrupt.db rescued.db rescued-patched.db recover.sql dump-salvage.db

Sources: PRAGMA Statements · VACUUM · ANALYZE · Command Line Shell For SQLite · EXPLAIN QUERY PLAN · local sqlite3 3.43.2 / 3.53.3 ground truth, captured 2026-07-17

11 · Helper Tools Tour

Everything in this guide so far works with nothing but the sqlite3 binary itself — that was the point: it's the one tool guaranteed to be sitting on, or reachable from, almost any machine that has SQLite at all. The five tools below aren't prerequisites for anything this guide taught; they're power-ups worth reaching for once you know what plain sqlite3 doesn't do easily, each trading "one more thing to install" for a real, specific capability.

Task → best tool

Sources: sqlite-utils: CLI reference · litecli · VisiData: Installation · Datasette: Installation · DuckDB: Installation

12 · Troubleshooting Table

A field-guide version of everything above: the symptom you actually see, what it really means, and the fastest real command to confirm it before you act.

[howtocorrupt.html; cli.html; local sqlite3 3.43.2+3.53.3, captured 2026-07-17]

Sources: PRAGMA Statements · How To Corrupt An SQLite Database File · Command Line Shell For SQLite · local sqlite3 3.43.2 / 3.53.3 ground truth, captured 2026-07-17

13 · Capstone: Audit a Real Database

Everything above was demonstrated against one purpose-built workshop.db. This lab turns the whole guide into a ten-step, printable checklist for auditing any real .db file you're handed — your own project's database is the intended target. Work through it once, top to bottom, and you have an audit note you could paste directly into an issue.

Sources: every command above restates a recipe already sourced in §2, §3, §5, §6, §7, §8, §9, and §10 above — no new source citations.

14 · Cheatsheet

Every command in this guide, grouped and dense, in the shape you'll actually reopen this page for. Each group header links back to the section that taught it in full; a stock or brew chip appears only where that exact command's teaching section already carried one — no new comparisons introduced here.

Identify & open

Modes & output

Schema

Profiling

Analysis

Export-import

Safety

Maintenance

Every row above restates a command already sourced in its linked section — no new source citations on this page.

15 · Glossary

Every term this guide leans on, one line each, consistent with how it's actually used above. Link directly to any row with #g-<term>.

Every definition above restates usage already sourced in its linked section — no new source citations on this page.

16 · Quiz & Sources

Twenty questions, every answer traceable to a specific section above. Reveal each answer only after you've committed to one.

QUERY PLAN
|--SCAN events
`--USE TEMP B-TREE FOR GROUP BY

Sources

Primary: Command Line Shell For SQLite · PRAGMA Statements · The Schema Table · Datatypes In SQLite · SQLite FTS5 Extension · Query Result Formatting In The CLI · SQLite Release 3.33.0 changelog · SQLite forum: .sqliterc location · SQLite forum: ignoring .sqliterc · Generated Columns · Clustered Indexes and the WITHOUT ROWID Optimization · Database File Format · Write-Ahead Logging · File Locking And Concurrency In SQLite Version 3 · How To Corrupt An SQLite Database File · VACUUM · ANALYZE · EXPLAIN QUERY PLAN · Uniform Resource Identifiers · Result and Error Codes

Helper tools: sqlite-utils: CLI reference · litecli · litecli: Features · litecli: Config File · VisiData: Installation · VisiData: Loading Data · Datasette: Getting Started · Datasette: Installation · DuckDB: SQLite Extension · DuckDB: Installation

Local: sqlite3 3.43.2 (macOS stock) & 3.53.3 (Homebrew) ground truth, 2026-07-17.

All sources fetched 2026-07-17.