# SQLite
> Use SQLite well: the sqlite3 shell, type affinity, WAL mode and pragmas, locking, JSON and FTS5, backups, tuning, and safe access from Go and Python.
Canonical: https://www.wiki.jodisand.me/sqlite/
Reviewed: 2026-09-24
Related: [SQL](https://www.wiki.jodisand.me/sql/index.md), [PostgreSQL](https://www.wiki.jodisand.me/postgresql/index.md), [Go](https://www.wiki.jodisand.me/go/index.md), [Python](https://www.wiki.jodisand.me/python/index.md), [Files and directories](https://www.wiki.jodisand.me/files/index.md)
## Cheatsheet
| Task | Command |
| --- | --- |
| Open or create a database | `sqlite3 app.db` |
| Run one statement from the shell | `sqlite3 app.db 'SELECT count(*) FROM users;'` |
| List tables, indexes | `.tables`, `.indexes users` |
| Show DDL | `.schema users`, `.schema` for everything |
| Readable output | `.mode box` (also `column`, `table`, `markdown`, `json`, `csv`, `line`) |
| Headers on | `.headers on` |
| Import a CSV with a header row | `.import --csv users.csv users` |
| Export a query as CSV | `.mode csv`, `.once out.csv`, then the `SELECT` |
| Dump SQL text | `.dump` or `sqlite3 app.db .dump > app.sql` |
| Consistent online backup | `sqlite3 app.db ".backup 'app-backup.db'"` |
| Copy compacted | `VACUUM INTO 'app-compact.db';` |
| Turn on WAL (persistent) | `PRAGMA journal_mode = WAL;` |
| Wait for locks instead of failing | `PRAGMA busy_timeout = 5000;` |
| Enforce foreign keys (per connection) | `PRAGMA foreign_keys = ON;` |
| Query plan | `EXPLAIN QUERY PLAN SELECT ...;` or `.eqp on` |
| Time each statement | `.timer on` |
| Check integrity | `PRAGMA integrity_check;` (`quick_check` for a fast pass) |
| Refresh planner statistics | `ANALYZE;` or `PRAGMA optimize;` at connection close |
| Recover a damaged file | `sqlite3 broken.db .recover \| sqlite3 recovered.db` |
| Columns of a table | `PRAGMA table_info(users);` or `SELECT * FROM pragma_table_info('users');` |
| Compile options and version | `SELECT sqlite_version();`, `PRAGMA compile_options;` |
Behaviour below is SQLite 3.45 or later; the `sqlite3` shell on Fedora and RHEL 9 is new enough for everything here except where a version is given. Reference: [sqlite.org/docs.html](https://sqlite.org/docs.html).
## What SQLite is and is not
SQLite is a library, not a server. Every process that opens the file is a full database engine; coordination happens through POSIX file locks on the database file, so there is no network protocol, no user accounts, no connection limit beyond file handles, and no background process to keep alive. The whole database, including its schema, is one file (`app.db`) plus, in WAL mode, `app.db-wal` and `app.db-shm` while a connection is open. That design makes it the right choice for an application's local state, an embedded configuration store, a CLI's cache, test fixtures and analytical work on files up to tens of gigabytes, and the wrong choice for many writers on different machines, which needs [PostgreSQL](https://www.wiki.jodisand.me/postgresql/) or [MySQL](https://www.wiki.jodisand.me/mysql/).
Writes are serialised: exactly one connection can write at a time, however many read. Throughput on a modern SSD is tens of thousands of small transactions per second in WAL mode with `synchronous=NORMAL`, and a single transaction can insert millions of rows per second, so "SQLite is slow" is nearly always one transaction per row or a missing index. Network filesystems (NFS, SMB, many FUSE mounts) do not implement the locking SQLite relies on; the documentation says not to use them, and corruption is the eventual result.
## The sqlite3 shell
```sh
sqlite3 app.db # interactive; creates the file on first write
sqlite3 -readonly app.db # refuse writes
sqlite3 -header -column app.db 'SELECT * FROM users LIMIT 5'
sqlite3 -json app.db 'SELECT id, name FROM users' | jq '.[0]'
sqlite3 -csv -header app.db 'SELECT * FROM users' > users.csv
sqlite3 app.db < schema.sql # run a script; add -bail to stop at the first error
sqlite3 :memory: 'SELECT sqlite_version()' # in-memory scratch database
```
Dot commands are shell features, not SQL: they take no trailing semicolon and are not available to your application.
```
.tables -- also .tables 'user%'
.schema users -- CREATE statements; .schema --indent for formatting
.fullschema -- includes the sqlite_stat tables
.indexes users
.mode box -- box, table, column, markdown, json, csv, tabs, line, insert, quote
.headers on
.width 8 30 0 -- fixed column widths in column mode; 0 is auto
.nullvalue NULL -- render NULL visibly
.timer on -- CPU and wall time per statement
.eqp on -- print EXPLAIN QUERY PLAN before each statement; .eqp full includes the bytecode
.changes on -- print rows changed after each statement
.import --csv users.csv users -- creates the table with TEXT columns if it does not exist; otherwise columns must match
.import --csv --skip 1 users.csv users -- skip the header when the table already exists
.output report.txt -- redirect until .output stdout; .once writes only the next statement's output
.dump users -- SQL text for one table; .dump alone for the whole database
.read migration.sql
.backup app-backup.db -- online backup through the backup API: consistent while others write
.restore app-backup.db -- overwrite the open database from a backup
.recover -- best-effort SQL from a corrupted file
.dbinfo -- page size, page count, freelist, schema cookie
.databases -- attached databases and their files
.parameter set :id 42 -- bind a parameter for the following statements
.shell ls -l -- run a shell command
.exit
```
`.import` treats every field as text and inserts into whatever columns the table has, in order; type affinity converts `'42'` to an integer for an `INTEGER` column but leaves `'N/A'` as text. Import into a staging table and `INSERT INTO ... SELECT` with casts and validation when the CSV is untrusted. `.dump | sqlite3 new.db` is a portable, version-independent copy, and the fastest way to move a database between hosts with different SQLite builds.
## Types and affinity
SQLite stores values, not columns, with a type: `NULL`, `INTEGER` (1 to 8 bytes), `REAL`, `TEXT` or `BLOB`. A column's declared type only sets an affinity that decides how an inserted value is converted, and a column declared `INTEGER` will store the text `'abc'` unless the table is `STRICT`. Any declared type name maps to one of five affinities: contains `INT` gives `INTEGER`; `CHAR`, `CLOB` or `TEXT` gives `TEXT`; `BLOB` or no type gives `BLOB`; `REAL`, `FLOA` or `DOUB` gives `REAL`; anything else (including `DATE`, `DATETIME`, `BOOLEAN`, `DECIMAL`) gives `NUMERIC`.
```sql
CREATE TABLE users (
id INTEGER PRIMARY KEY, -- alias for the 64-bit rowid: fastest key, reused after delete unless AUTOINCREMENT
email TEXT NOT NULL UNIQUE COLLATE NOCASE, -- case-insensitive uniqueness and lookup
name TEXT NOT NULL,
active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0, 1)), -- there is no boolean type; TRUE and FALSE are 1 and 0
balance INTEGER NOT NULL DEFAULT 0, -- cents; REAL is binary floating point
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), -- ISO-8601 UTC text sorts correctly
settings TEXT CHECK (settings IS NULL OR json_valid(settings))
) STRICT; -- 3.37+: rejects values that do not fit the declared type
CREATE TABLE tags (name TEXT PRIMARY KEY, colour TEXT) WITHOUT ROWID; -- clustered on the key; good for small-key lookup tables
SELECT typeof(id), typeof(email), typeof(balance) FROM users LIMIT 1; -- integer text integer
SELECT '42' = 42, 42 = 42.0, 'a' < 1, x'00' > 'z'; -- 0 1 0 1: NULL < INTEGER/REAL < TEXT < BLOB in comparisons
```
Comparisons between a text column and a numeric literal apply the column's affinity to the literal, so `WHERE id = '42'` still uses the index on an `INTEGER` column; the reverse (`WHERE text_col = 42`) does not. Integer division truncates: `7 / 2` is 3, `7 / 2.0` is 3.5. `STRICT` tables allow only `INT`, `INTEGER`, `REAL`, `TEXT`, `BLOB` and `ANY` as declared types. `INTEGER PRIMARY KEY AUTOINCREMENT` prevents reuse of deleted ids at the cost of a write to `sqlite_sequence` per insert; use it only when ids are exposed and reuse would confuse something. Foreign keys are parsed but not enforced unless `PRAGMA foreign_keys = ON` is run on every connection.
## WAL mode and pragmas
The default rollback journal copies original pages to `-journal` before writing, and a writer blocks readers while it commits. Write-ahead logging appends changed pages to `-wal` instead and periodically checkpoints them back into the main file. Readers see the last committed state without blocking the writer and the writer does not wait for readers, which is the concurrency model a web application or a multi-goroutine service needs. WAL is stored in the file header, so setting it once persists for every future connection.
```sql
PRAGMA journal_mode = WAL; -- returns 'wal' on success; needs no other connection to hold a lock
PRAGMA synchronous = NORMAL; -- per connection: fsync at checkpoint, not every commit; durable against process crash, may lose the last transactions on power loss
PRAGMA busy_timeout = 5000; -- per connection: retry a locked database for up to 5 s instead of SQLITE_BUSY immediately
PRAGMA foreign_keys = ON; -- per connection
PRAGMA cache_size = -65536; -- per connection: 64 MiB of page cache (negative is KiB, positive is pages)
PRAGMA temp_store = MEMORY; -- sorts and temp tables in RAM
PRAGMA mmap_size = 268435456; -- read pages through a 256 MiB memory map; skips a copy
PRAGMA wal_autocheckpoint = 1000; -- default: checkpoint when the WAL passes 1000 pages (about 4 MB)
PRAGMA wal_checkpoint(TRUNCATE); -- force a checkpoint and reset the WAL to zero bytes; blocks until readers finish
PRAGMA journal_size_limit = 67108864; -- cap the WAL file at 64 MiB after checkpoint
PRAGMA auto_vacuum = INCREMENTAL; -- must be set before any table exists, or run VACUUM after changing it
PRAGMA page_size = 4096; -- default since 3.12; only changes with VACUUM
PRAGMA secure_delete = ON; -- overwrite deleted content; slower
PRAGMA journal_mode; -- query any pragma by omitting the value
```
Pragmas without a note above persist in the file (`journal_mode`, `page_size`, `auto_vacuum`, `user_version`, `application_id`). The rest are per connection and belong in the connection-open code of the application, not in a one-off shell session. `PRAGMA user_version` is an integer slot the application owns and is the conventional place for a schema migration number.
A WAL database has three files while open. Copying only `app.db` while a process holds it open loses the un-checkpointed transactions in `app.db-wal`, and the `-shm` file is a shared-memory index that is safe to delete only when no connection is open. WAL needs write access to the directory to create the two side files, so a read-only mount or a directory the process cannot write makes even `SELECT` fail with `attempt to write a readonly database`; open with `?immutable=1` for a file that will never change.
## Transactions and locking
Autocommit wraps every statement in its own transaction, with an fsync per statement in rollback mode. Wrap batches in one transaction: a loop of 100,000 single-row inserts takes minutes in autocommit and well under a second inside `BEGIN ... COMMIT`.
```sql
BEGIN; -- DEFERRED: takes no lock until the first read or write
BEGIN IMMEDIATE; -- take the write lock now; fails fast with SQLITE_BUSY or waits busy_timeout
BEGIN EXCLUSIVE; -- in WAL mode the same as IMMEDIATE; in rollback mode also blocks readers
INSERT INTO users (email, name) VALUES ('a@example.com', 'A');
SAVEPOINT s1; UPDATE users SET active = 0 WHERE id = 1; ROLLBACK TO s1; RELEASE s1;
COMMIT;
```
Use `BEGIN IMMEDIATE` for any transaction that will write. A `DEFERRED` transaction that reads first and then writes must upgrade its shared lock to a write lock; if another writer committed in between, SQLite returns `SQLITE_BUSY_SNAPSHOT` immediately and does not consult `busy_timeout`, because retrying would violate the snapshot the reads already used. Starting `IMMEDIATE` moves the wait to the beginning where the timeout applies.
WAL readers never block and are never blocked, but a long-lived read transaction (a cursor left open, an ORM session that never commits) pins the WAL: checkpoints cannot recycle pages older than the oldest reader, and the WAL grows without bound. `PRAGMA wal_checkpoint(PASSIVE)` reports `busy`, `log` and `checkpointed` page counts; a `log` far above `checkpointed` with a small database points at such a reader. The isolation level is always serialisable within one file; there is no dirty read and no `READ COMMITTED`.
## JSON
The JSON functions are built in since 3.38 (earlier builds needed the `JSON1` extension). JSON is stored as text, or since 3.45 as the internal `JSONB` binary format that skips parsing on each access; `jsonb(...)` converts, and every function accepts either.
```sql
INSERT INTO users (email, name, settings) VALUES ('b@example.com', 'B', '{"theme":"dark","tags":["ops","db"],"limits":{"rows":500}}');
SELECT settings ->> 'theme', -- 'dark' (->> returns SQL text/number; -> returns JSON text with quotes)
settings -> 'limits' ->> 'rows', -- 500
json_extract(settings, '$.tags[0]'), -- 'ops'
json_array_length(settings, '$.tags'), -- 2
json_type(settings, '$.limits') -- 'object'
FROM users WHERE email = 'b@example.com';
UPDATE users SET settings = json_set(settings, '$.theme', 'light', '$.limits.rows', 1000); -- set or insert
UPDATE users SET settings = json_insert(settings, '$.beta', true); -- insert only if absent
UPDATE users SET settings = json_remove(settings, '$.tags[1]');
UPDATE users SET settings = json_patch(settings, '{"limits":{"rows":null}}'); -- RFC 7396 merge patch: null deletes
SELECT u.email, t.value FROM users u, json_each(u.settings, '$.tags') t; -- one row per array element
SELECT key, value, type, fullkey FROM json_tree('{"a":{"b":[1,2]}}'); -- recursive walk
SELECT json_group_array(json_object('id', id, 'email', email)) FROM users WHERE active = 1; -- rows to a JSON array
SELECT json_group_object(email, name) FROM users;
CREATE INDEX users_theme ON users (settings ->> 'theme'); -- expression index; the query must use the same expression
SELECT * FROM users WHERE settings ->> 'theme' = 'dark';
CREATE TABLE events (id INTEGER PRIMARY KEY, body TEXT, kind TEXT GENERATED ALWAYS AS (body ->> 'kind') VIRTUAL); -- 3.31+: index the generated column
```
`json_extract` returns SQL NULL for a missing path and the JSON `null` also as SQL NULL; `json_type(x, '$.k')` distinguishes them (`'null'` versus NULL). Path syntax is `$`, `.key`, `[n]` and `[#-1]` for the last element; keys with dots or spaces are quoted: `'$."my.key"'`.
## Full-text search with FTS5
FTS5 is a virtual table module that builds an inverted index over text columns and answers `MATCH` queries with ranking. Most builds include it (`PRAGMA compile_options` lists `ENABLE_FTS5`). Use an external-content table so the text is stored once and the FTS index only holds tokens.
```sql
CREATE TABLE articles (id INTEGER PRIMARY KEY, title TEXT, body TEXT, published_at TEXT);
CREATE VIRTUAL TABLE articles_fts USING fts5(
title, body,
content = 'articles', content_rowid = 'id', -- external content: read title and body from articles
tokenize = 'porter unicode61 remove_diacritics 2' -- stemming, Unicode case folding, accent-insensitive
);
-- keep the index in sync
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'); -- index existing rows once
SELECT a.id, a.title,
bm25(articles_fts) AS score, -- lower is better
snippet(articles_fts, 1, '', '', '...', 12) AS excerpt, -- column 1 = body, 12 tokens
highlight(articles_fts, 0, '', '') AS title_hl
FROM articles_fts
JOIN articles a ON a.id = articles_fts.rowid
WHERE articles_fts MATCH 'wal AND (checkpoint OR "write ahead")'
ORDER BY score
LIMIT 20;
SELECT rowid FROM articles_fts WHERE articles_fts MATCH 'title : sqlite'; -- restrict to a column
SELECT rowid FROM articles_fts WHERE articles_fts MATCH 'check*'; -- prefix
SELECT rowid FROM articles_fts WHERE articles_fts MATCH 'NEAR(wal checkpoint, 5)';
SELECT rowid FROM articles_fts WHERE articles_fts MATCH 'sqlite NOT mysql';
INSERT INTO articles_fts(articles_fts) VALUES ('optimize'); -- merge b-tree segments after bulk loads
INSERT INTO articles_fts(articles_fts, rank) VALUES ('rank', 'bm25(10.0, 1.0)'); -- weight title 10x body for ORDER BY rank
```
Query syntax treats bare words as implicit AND, supports `OR`, `NOT`, phrases in double quotes, `^` for column-start, and `column : term`. User input containing quotes or operators causes a syntax error; wrap each user token in double quotes (doubling any embedded quotes) before building the expression. With `content=''` (contentless) the table cannot return `snippet()` and cannot `'delete'` by content unless declared `contentless_delete=1` (3.43+).
## Backup, restore and vacuum
Copying the database file with `cp` is safe only when no process has it open. For a live database use the backup API, `VACUUM INTO`, or `.dump`, all of which read a consistent snapshot without blocking writers in WAL mode.
```sh
sqlite3 app.db ".backup 'backups/app-$(date +%F).db'" # page-level copy, same size and settings as the source
sqlite3 app.db "VACUUM INTO 'backups/app-$(date +%F).db'" # 3.27+: compacted copy without free pages
sqlite3 app.db .dump | gzip > "backups/app-$(date +%F).sql.gz" # portable SQL text; slowest, smallest
gunzip -c backups/app-2026-09-24.sql.gz | sqlite3 restored.db # restore from SQL
sqlite3 restored.db 'PRAGMA integrity_check; PRAGMA journal_mode;' # WAL setting is carried by .backup, not by .dump
```
`VACUUM` rebuilds the database into a new file and swaps it in, reclaiming pages freed by deletes (SQLite never shrinks the file otherwise), defragmenting tables and applying a changed `page_size` or `auto_vacuum`. It needs free space for a full copy, takes an exclusive lock for the duration and rewrites everything, so schedule it, do not run it after every delete. `PRAGMA auto_vacuum = INCREMENTAL` plus periodic `PRAGMA incremental_vacuum(200)` trims the file gradually. `PRAGMA freelist_count` says how many pages a vacuum would recover.
> [!WARNING] Restoring over a WAL database
> Replacing `app.db` while a stale `app.db-wal` sits next to it applies the old WAL to the new file on the next open and corrupts it. Stop every process, remove `app.db-wal` and `app.db-shm`, then copy the file in.
## Performance
```sql
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'a@example.com';
-- SEARCH users USING INDEX sqlite_autoindex_users_1 (email=?) good
-- SCAN users full table scan
-- USE TEMP B-TREE FOR ORDER BY sort not served by an index
-- SEARCH ... USING COVERING INDEX no table lookup needed
ANALYZE; -- gather statistics into sqlite_stat1; rerun after large changes
PRAGMA optimize; -- 3.18+: run ANALYZE only where useful; call before closing long-lived connections
CREATE INDEX orders_user_created ON orders (user_id, created_at DESC); -- leftmost prefix rule as in every B-tree
CREATE INDEX orders_open ON orders (user_id) WHERE closed_at IS NULL; -- partial index
CREATE INDEX users_email_lower ON users (lower(email)); -- expression index
```
The rules that move the needle, roughly in order: batch writes into transactions; use prepared statements and bind parameters rather than string formatting (parsing SQL costs more than executing it for small statements); WAL with `synchronous=NORMAL`; indexes for every `WHERE` and `JOIN` column the plan scans; `INTEGER PRIMARY KEY` rather than a text UUID as the rowid, or `WITHOUT ROWID` when a text key is unavoidable; a larger `cache_size` and `mmap_size` for read-heavy workloads; `PRAGMA optimize` on close. For bulk loads, create indexes after the inserts, and consider `PRAGMA synchronous = OFF` and `journal_mode = OFF` for a throwaway import where a crash means starting again. The bytecode `EXPLAIN` (without `QUERY PLAN`) is rarely needed; `EXPLAIN QUERY PLAN` and `.timer on` answer almost every question.
Connection pooling matters differently from a server database. Opening a connection is cheap but not free (it reads the schema), and each connection has its own page cache, so a service keeps a small pool. Because only one writer runs at a time, a pool of writers just queues on the lock; the common pattern is one connection dedicated to writes and a pool for reads.
## From Go
`modernc.org/sqlite` is a pure-Go translation of SQLite that needs no cgo and cross-compiles cleanly; `github.com/mattn/go-sqlite3` wraps the C library through cgo and is somewhat faster. Both register with `database/sql`. Set the connection pragmas in the DSN so every pooled connection gets them.
```go
package main
import (
"context"
"database/sql"
"fmt"
"time"
_ "modernc.org/sqlite"
)
func open(path string) (*sql.DB, error) {
// _pragma applies per connection; journal_mode persists in the file but is harmless to repeat.
dsn := fmt.Sprintf("file:%s?_pragma=busy_timeout(5000)&_pragma=journal_mode(WAL)&_pragma=synchronous(NORMAL)&_pragma=foreign_keys(1)&_txlock=immediate", path)
db, err := sql.Open("sqlite", dsn)
if err != nil {
return nil, err
}
db.SetMaxOpenConns(4) // readers; writes serialise on the file lock anyway
db.SetConnMaxIdleTime(5 * time.Minute)
if err := db.PingContext(context.Background()); err != nil {
return nil, err
}
return db, nil
}
func insertUsers(ctx context.Context, db *sql.DB, users []struct{ Email, Name string }) error {
tx, err := db.BeginTx(ctx, nil) // _txlock=immediate makes this BEGIN IMMEDIATE
if err != nil {
return err
}
defer tx.Rollback() // no-op after Commit
stmt, err := tx.PrepareContext(ctx, `INSERT INTO users (email, name) VALUES (?, ?)
ON CONFLICT (email) DO UPDATE SET name = excluded.name`)
if err != nil {
return err
}
defer stmt.Close()
for _, u := range users {
if _, err := stmt.ExecContext(ctx, u.Email, u.Name); err != nil {
return fmt.Errorf("insert %s: %w", u.Email, err)
}
}
return tx.Commit()
}
```
For a write-heavy service, open two `*sql.DB` values on the same file: one with `SetMaxOpenConns(1)` for writes and one for reads. That turns lock contention into an in-process queue with proper context cancellation instead of `busy_timeout` spinning. `:memory:` databases are per connection; a pool of size greater than one sees several empty databases, so use `file::memory:?cache=shared` or `SetMaxOpenConns(1)` in tests. Migrations are simplest as a slice of SQL strings applied in a transaction, advancing `PRAGMA user_version`. See [Go](https://www.wiki.jodisand.me/go/) for context and error conventions.
## From Python
The standard library `sqlite3` module wraps the system SQLite. Python 3.12 added the `autocommit` attribute, which is the recommended way to control transactions; the older `isolation_level` behaviour of implicitly opening transactions before DML and never before DDL is a persistent source of "why is my schema change not committed" bugs.
```python
import sqlite3
from contextlib import closing
def connect(path: str) -> sqlite3.Connection:
conn = sqlite3.connect(path, timeout=5.0, autocommit=False) # timeout = busy_timeout in seconds; 3.12+ autocommit
conn.row_factory = sqlite3.Row # rows indexable by name
conn.execute("PRAGMA journal_mode = WAL")
conn.execute("PRAGMA synchronous = NORMAL")
conn.execute("PRAGMA foreign_keys = ON")
return conn
with closing(connect("app.db")) as conn:
with conn: # commits on success, rolls back on exception; does not close
conn.executemany(
"INSERT INTO users (email, name) VALUES (:email, :name) ON CONFLICT (email) DO UPDATE SET name = excluded.name",
[{"email": "a@example.com", "name": "A"}, {"email": "b@example.com", "name": "B"}],
)
for row in conn.execute("SELECT id, email FROM users WHERE active = ? ORDER BY id", (1,)):
print(row["id"], row["email"])
# consistent online backup to another file
with closing(sqlite3.connect("app-backup.db")) as dst:
conn.backup(dst, pages=1024) # copies in steps so writers keep going
# ad-hoc queries from the command line with the module's shell (3.12+)
# python -m sqlite3 app.db "SELECT count(*) FROM users"
```
`autocommit=False` opens a transaction before the first statement and requires `commit()`; `autocommit=True` runs each statement alone and you issue `BEGIN IMMEDIATE` yourself for batches. `executemany` with a prepared statement is the fast path for bulk inserts; a Python loop calling `execute` is a few times slower, and either is fine inside one transaction. A connection may be used only from the thread that created it unless `check_same_thread=False`, and then callers must serialise access themselves. `conn.set_trace_callback(print)` shows every statement, and `sqlite3.sqlite_version` tells you which library version the module linked; features such as `->>` and `STRICT` depend on it, not on the Python version. See [Python](https://www.wiki.jodisand.me/python/) for logging and packaging.
## Oneliners
```sh
# Version of the library the shell uses (features depend on this, not the shell version)
sqlite3 :memory: 'SELECT sqlite_version()'
# Row counts for every table
sqlite3 app.db "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'" | while read -r t; do printf '%s\t' "$t"; sqlite3 app.db "SELECT count(*) FROM \"$t\""; done
# Size on disk by table and index (needs a build with the dbstat module; most distro builds have it)
sqlite3 app.db "SELECT name, sum(pgsize)/1024 AS kib FROM dbstat GROUP BY name ORDER BY kib DESC LIMIT 15"
# Free pages that VACUUM would reclaim, and the page size
sqlite3 app.db 'PRAGMA freelist_count; PRAGMA page_size;'
# Journal mode, and whether a WAL file is lingering
sqlite3 app.db 'PRAGMA journal_mode;' && ls -l app.db-wal app.db-shm 2>/dev/null
# Checkpoint and truncate the WAL right now
sqlite3 app.db 'PRAGMA wal_checkpoint(TRUNCATE);'
# Who has the file open (long readers pin the WAL)
fuser -v app.db app.db-wal 2>&1 | head
# Query plan without running the statement
sqlite3 app.db 'EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10;'
# Indexes that exist and their columns
sqlite3 app.db "SELECT tbl_name, name, sql FROM sqlite_master WHERE type='index' AND sql IS NOT NULL"
# Compare two databases' schemas
diff <(sqlite3 a.db .schema) <(sqlite3 b.db .schema)
# Diff the data too (sqldiff ships with the sqlite tools package)
sqldiff a.db b.db | head
# Import a CSV into a new table, then look at what came in
sqlite3 app.db '.import --csv users.csv users_raw' 'SELECT * FROM users_raw LIMIT 3'
# Export a query as JSON for jq
sqlite3 -json app.db 'SELECT id, email FROM users' | jq -r '.[] | .email'
# Export one table as CSV with a header
sqlite3 -csv -header app.db 'SELECT * FROM users' > users.csv
# Load a SQL dump into a fresh file, stopping at the first error
sqlite3 -bail new.db < app.sql
# Integrity check with a non-zero exit when it fails
[ "$(sqlite3 app.db 'PRAGMA integrity_check;')" = ok ]
# Recover what can be read from a corrupt file
sqlite3 broken.db .recover | sqlite3 recovered.db
# Set the schema version the application uses for migrations
sqlite3 app.db 'PRAGMA user_version = 7;'
# Foreign key violations in existing data
sqlite3 app.db 'PRAGMA foreign_key_check;'
# Shrink an in-place copy without touching the original
sqlite3 app.db "VACUUM INTO 'app-compact.db'"
# Run a read-only ad-hoc report against a live database
sqlite3 -readonly -box app.db 'SELECT date(created_at) d, count(*) FROM orders GROUP BY d ORDER BY d DESC LIMIT 14'
# Time a query
sqlite3 app.db '.timer on' 'SELECT count(*) FROM orders WHERE created_at >= date("now", "-30 days");'
# Show compile options that matter (FTS5, JSON, RTREE, THREADSAFE)
sqlite3 :memory: 'PRAGMA compile_options;' | grep -E 'FTS5|JSON|RTREE|THREADSAFE|DBSTAT'
```
## Scripts
Backup with verification and rotation: takes an online backup through the backup API, checks it, compresses it and keeps the newest N. Deletes older backups in the target directory.
```sh
#!/usr/bin/env bash
# usage: sqlite-backup.sh DB_PATH BACKUP_DIR [KEEP] default KEEP=14
set -euo pipefail
db=${1:?database path}; dir=${2:?backup dir}; keep=${3:-14}
[[ -f $db ]] || { printf 'no such database: %s\n' "$db" >&2; exit 2; }
mkdir -p "$dir"
name=$(basename "$db" .db)
out="$dir/$name-$(date +%Y%m%dT%H%M%S).db"
sqlite3 "$db" ".backup '$out'" # consistent snapshot; writers continue in WAL mode
check=$(sqlite3 "$out" 'PRAGMA integrity_check;')
[[ $check == ok ]] || { printf 'integrity check failed on %s: %s\n' "$out" "$check" >&2; rm -f "$out"; exit 1; }
gzip -9 "$out"
printf 'wrote %s (%s)\n' "$out.gz" "$(du -h "$out.gz" | cut -f1)"
# rotate: keep the newest $keep, delete the rest
ls -1t "$dir/$name-"*.db.gz 2>/dev/null | tail -n +"$((keep + 1))" | while IFS= read -r old; do
rm -f -- "$old"; printf 'removed %s\n' "$old"
done
```
Health report for a database: size, WAL state, free pages, missing statistics, tables without a primary key and foreign key violations. Read-only.
```sh
#!/usr/bin/env bash
# usage: sqlite-health.sh DB_PATH
set -euo pipefail
db=${1:?database path}
q() { sqlite3 -readonly "$db" "$1"; }
printf 'file %s (%s)\n' "$db" "$(du -h "$db" | cut -f1)"
printf 'version %s\n' "$(q 'SELECT sqlite_version();')"
printf 'journal %s' "$(q 'PRAGMA journal_mode;')"
[[ -f $db-wal ]] && printf ' wal=%s' "$(du -h "$db-wal" | cut -f1)"; echo
printf 'pages %s total, %s free (page_size %s)\n' "$(q 'PRAGMA page_count;')" "$(q 'PRAGMA freelist_count;')" "$(q 'PRAGMA page_size;')"
printf 'quick_check %s\n' "$(q 'PRAGMA quick_check;' | head -1)"
printf 'fk violations %s\n' "$(q 'PRAGMA foreign_key_check;' | wc -l)"
printf 'stats %s\n' "$(q "SELECT CASE WHEN EXISTS (SELECT 1 FROM sqlite_master WHERE name='sqlite_stat1') THEN 'present' ELSE 'missing: run ANALYZE' END;")"
echo 'tables without an explicit primary key:'
q "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' AND sql NOT LIKE '%PRIMARY KEY%' AND sql NOT LIKE '%WITHOUT ROWID%'" | sed 's/^/ /'
echo 'largest objects:'
q "SELECT ' ' || name || ' ' || (sum(pgsize)/1024) || ' KiB' FROM dbstat GROUP BY name ORDER BY sum(pgsize) DESC LIMIT 8" 2>/dev/null || echo ' (dbstat module unavailable)'
```
Migration runner in Python: applies numbered `.sql` files above the current `user_version`, each in its own transaction, so a failed migration leaves the database at the last good version.
```python
#!/usr/bin/env python3
"""usage: migrate.py DB_PATH MIGRATIONS_DIR (files named 0001_name.sql, 0002_name.sql, ...)"""
import re
import sqlite3
import sys
from pathlib import Path
db_path, mig_dir = sys.argv[1], Path(sys.argv[2])
conn = sqlite3.connect(db_path, timeout=10, autocommit=True)
conn.execute("PRAGMA foreign_keys = ON")
current = conn.execute("PRAGMA user_version").fetchone()[0]
files = sorted(mig_dir.glob("[0-9]*.sql"), key=lambda p: int(re.match(r"\d+", p.name).group()))
for f in files:
version = int(re.match(r"\d+", f.name).group())
if version <= current:
continue
print(f"applying {f.name}")
conn.execute("BEGIN IMMEDIATE")
try:
conn.executescript(f.read_text()) # executescript would COMMIT first in autocommit=False mode; autocommit=True avoids that
conn.execute(f"PRAGMA user_version = {version}")
conn.execute("COMMIT")
except Exception:
conn.execute("ROLLBACK")
print(f"failed at {f.name}; database remains at version {current}", file=sys.stderr)
raise
current = version
print(f"schema at version {current}")
```
## Troubleshooting
| Symptom | Cause | Fix |
| --- | --- | --- |
| `database is locked` | Another connection holds the write lock longer than `busy_timeout`, or `busy_timeout` is 0 | `PRAGMA busy_timeout = 5000` on every connection; shorten transactions; `fuser -v app.db` to find the holder |
| `database is locked` only when a read turns into a write | `DEFERRED` transaction cannot upgrade after another commit (`SQLITE_BUSY_SNAPSHOT`) | `BEGIN IMMEDIATE` for transactions that will write; `_txlock=immediate` in Go |
| `database is locked` on a network mount | File locking not implemented by NFS or SMB | Move the database to local disk; SQLite documents this as unsupported |
| `database disk image is malformed` | Copied while open, WAL mismatch, hardware, or a network filesystem | `PRAGMA integrity_check`; `.recover` into a new file; restore a `.backup` |
| `attempt to write a readonly database` on a `SELECT` | WAL needs to create `-wal` and `-shm` in a directory the process cannot write; or SELinux | Make the directory writable, or open with `?immutable=1` / `mode=ro`; check `ausearch -m avc` |
| `unable to open database file` | Wrong path, missing directory, or the process's working directory differs | Absolute path; `ls -ld` the directory; systemd `WorkingDirectory=` |
| WAL file grows without bound | An open read transaction stops checkpoints | Find the long reader (`fuser`, application cursor left open); `PRAGMA wal_checkpoint(TRUNCATE)` after it closes |
| File never shrinks after deletes | Freed pages return to the freelist, not to the OS | `VACUUM`, `VACUUM INTO`, or `auto_vacuum = INCREMENTAL` |
| Slow query | Full scan or temp B-tree in `EXPLAIN QUERY PLAN` | Add the index the plan needs; `ANALYZE`; avoid functions on indexed columns and leading `%` in `LIKE` |
| Thousands of inserts take minutes | One transaction per statement, each with an fsync | Wrap in `BEGIN IMMEDIATE ... COMMIT`; WAL with `synchronous = NORMAL` |
| Foreign key not enforced | `foreign_keys` is off by default per connection | `PRAGMA foreign_keys = ON` after every connect; `PRAGMA foreign_key_check` for existing violations |
| `no such module: fts5` or `no such function: json_extract` | Library built without FTS5, or older than 3.38 for JSON | `PRAGMA compile_options`; upgrade the library, not the application |
| Python DDL not committed, or `executescript` committed early | Implicit transaction rules under `isolation_level` | Use `autocommit` (3.12+) and manage `BEGIN`/`COMMIT` yourself |
| `:memory:` database empty in another goroutine or thread | Each connection gets its own in-memory database | `file::memory:?cache=shared`, or one connection |
| Ids reused after deleting the highest row | `INTEGER PRIMARY KEY` reuses the max rowid + 1 | `AUTOINCREMENT` when ids must never repeat |
| `too many SQL variables` | More than 32766 bound parameters (999 before 3.32) | Batch the `IN (...)` list, or insert into a temp table and join |
## Further reading
- [SQLite documentation index](https://sqlite.org/docs.html)
- [Write-ahead logging](https://sqlite.org/wal.html)
- [Datatypes and type affinity](https://sqlite.org/datatype3.html)
- [PRAGMA statements](https://sqlite.org/pragma.html)
- [FTS5 extension](https://sqlite.org/fts5.html)
- [Command line shell](https://sqlite.org/cli.html)