SQLite cheat sheet

A scannable SQLite reference: 23 short snippets across 12 topics, each linking back to the lesson it came from.

At a glance

Quick snippets

The database in a file, and the sqlite3 CLI

A database in a single file

sqlite3 app.db

SQLite version 3.45.0
sqlite> .tables
sqlite> .schema orders
sqlite> .mode box          -- pretty output instead of pipe separated
sqlite> .headers on
sqlite> SELECT id, status FROM orders LIMIT 5;
sqlite> .import data.csv measurements --csv
sqlite> .dump orders > orders.sql
sqlite> .backup backup.db
sqlite> .quit

Pragmas worth setting

PRAGMA journal_mode = WAL;      -- better reader/writer concurrency
PRAGMA synchronous  = NORMAL;   -- the usual pairing with WAL
PRAGMA foreign_keys = ON;       -- per connection, off by default
PRAGMA busy_timeout = 5000;     -- wait instead of failing immediately
PRAGMA cache_size   = -64000;   -- negative means kibibytes, so about 64 MB
PRAGMA user_version = 3;        -- your own migration counter
PRAGMA optimize;                -- run before closing a long-lived connection
PRAGMA integrity_check;         -- full structural check (quick_check is faster)

Full lesson: The database in a file, and the sqlite3 CLI →

WAL mode, backups, and when not to use SQLite

WAL mode in practice

PRAGMA journal_mode = WAL;        -- persistent: stored in the file header
PRAGMA synchronous = NORMAL;     -- safe against process crash, not against power loss
PRAGMA wal_autocheckpoint = 1000;  -- pages, about 4 MB at the default page size
PRAGMA wal_checkpoint(TRUNCATE);   -- force a full checkpoint and shrink the log

Full lesson: WAL mode, backups, and when not to use SQLite →

Data types, affinity, STRICT tables and WITHOUT ROWID

Affinity, not types

create table legacy (v);            -- affinity: BLOB, nothing is converted
create table a (n integer);         -- INTEGER affinity: '42' is stored as 42
create table b (t text);            -- TEXT affinity: 42 is stored as '42'
create table c (r real);            -- REAL affinity
create table d (x numeric(10,2));   -- NUMERIC affinity

insert into a (n) values ('42'), ('4.5'), ('abc');
select n, typeof(n) from a;
-- 42    integer
-- 4.5   real      (numeric text that looks like a number is converted)
-- abc   text      (not numeric, so the value is stored as given)

STRICT tables

-- migration to a STRICT table, preserving the rowid
begin;
create table book_new (
  id integer primary key, title text not null, pages integer
) strict;
insert into book_new (id, title, pages)
  select id, title, cast(pages as integer) from book;
drop table book;
alter table book_new rename to book;
commit;

Full lesson: Data types, affinity, STRICT tables and WITHOUT ROWID →

Querying with SQL: joins, grouping, upserts and CTEs

Practical query habits

-- the same statement, prepared once and reused with different bindings
-- sqlite3_prepare_v2(db, "select id from book where isbn = ?1", ...)
-- sqlite3_bind_text(stmt, 1, isbn, -1, SQLITE_TRANSIENT)
-- sqlite3_step(stmt)
-- sqlite3_reset(stmt); sqlite3_clear_bindings(stmt);   -- reuse, do not finalize

explain query plan
select id from book where isbn = '9780441013593';
-- SEARCH book USING INDEX sqlite_autoindex_book_1 (isbn=?)

Full lesson: Querying with SQL: joins, grouping, upserts and CTEs →

Transactions, locking and concurrency

Journal modes and lock states

pragma journal_mode;          -- delete (default), wal, truncate, memory, off
pragma journal_mode = wal;    -- persistent: keeps the setting in the file
pragma synchronous = normal;  -- WAL: safe against application crashes
pragma busy_timeout = 5000;   -- wait up to 5s for a lock instead of failing
pragma foreign_keys = on;     -- off by default, per connection

-- DEFERRED: the write lock is taken at the first write statement
begin deferred;
-- IMMEDIATE: the write lock is taken now, so a conflict fails fast
begin immediate;
-- EXCLUSIVE: also blocks readers (rarely what you want in WAL)
begin exclusive;

Connections and concurrency

-- bulk insert: one transaction, one fsync
begin immediate;
insert into reading (sensor, ts, value) values (?, ?, ?);
-- ... thousands of rows through the same prepared statement
commit;

-- check the WAL size and checkpoint it after a large import
pragma wal_checkpoint(truncate);

Designing around a single writer

-- diagnose lock contention
pragma busy_timeout;            -- is it actually set on this connection?
select * from pragma_wal_checkpoint;
pragma wal_checkpoint(passive);

-- a read-only connection for analytics
-- file:app.db?mode=ro&immutable=0
-- and in SQL code:
pragma query_only = on;         -- rejects writes on this connection

Full lesson: Transactions, locking and concurrency →

Indexes, ANALYZE and EXPLAIN QUERY PLAN

Index shapes

-- statistics: run after a large change, not in a hot write path
analyze;
analyze book;          -- or a single table, which is cheaper

-- with statistics, the planner can prefer the better of two indexes
-- without them, add an explicit hint by rewriting the query to be more selective

Pragmas that affect query speed

pragma cache_size = -64000;      -- negative means KiB: 64 MB page cache
pragma temp_store = memory;      -- temp tables and sorts in RAM
pragma mmap_size = 268435456;    -- 256 MB of memory-mapped I/O
pragma page_size;                -- must be set before the first table is created
pragma cache_spill;

-- prefer an index for a small table when the planner guesses wrong
analyze sqlite_schema;           -- or sqlite_master on older versions
-- and consider: ANALYZE sqlite_schema; update sqlite_stat1 set stat = '...'

Full lesson: Indexes, ANALYZE and EXPLAIN QUERY PLAN →

JSON, generated columns and date functions

JSON in a relational store

-- join to an array inside a document
select e.id, i.value ->> 'sku' as sku
from event e, json_each(e.body, '$.items') i
where i.value ->> 'sku' = 'A1';

-- stored in the compact binary form
update event set body = jsonb(body) where json_valid(body);
select json(body) from event;                  -- back to text when needed

Generated columns over JSON

create table event (
  id      integer primary key,
  body    text not null check (json_valid(body)),
  type    text generated always as (body ->> '$.type') stored,
  total   integer generated always as (cast(body ->> '$.total' as integer)) stored,
  created text generated always as (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')) virtual
) strict;

create index event_type_total on event (type, total);

select id, type, total from event where type = 'order' and total > 1000;

Full lesson: JSON, generated columns and date functions →

Migrations and schema versioning for embedded apps

Versioning the schema

pragma user_version;      -- an integer you own, stored in the file header
pragma user_version = 7;  -- set after migrations have been applied

-- the runner, in outline
-- 1. read pragma user_version
-- 2. for each migration with index > version, in order:
--      begin immediate
--      apply the statements
--      pragma user_version = index
--      commit
-- 3. if anything throws, roll back and leave the version untouched

Data migrations

-- a resumable backfill driven by the application
update book
set slug = lower(replace(title, ' ', '-'))
where id in (select id from book where slug is null order by id limit 1000);

-- after the last batch
create index book_slug on book (slug);
vacuum;

Full lesson: Migrations and schema versioning for embedded apps →

SQLite on mobile, desktop and in the browser

Mobile platforms

// iOS: the system libsqlite3, or GRDB / SQLite.swift as a wrapper
let dbQueue = try DatabaseQueue(path: dbPath)
try dbQueue.write { db in
    try db.execute(sql: "pragma journal_mode = wal")
    try db.execute(sql: "pragma foreign_keys = on")
}

// the file lives in Application Support, not Documents,
// unless the user is meant to see and manage it

Encryption and desktop packaging

-- SQLCipher is a drop-in build with page-level encryption
pragma key = 'x''2DD29CA8...';          -- or a passphrase
pragma cipher_page_size = 4096;
pragma kdf_iter = 256000;                -- key derivation iterations
pragma cipher_hmac_algorithm = HMAC_SHA512;
pragma cipher_kdf_algorithm = PBKDF2_HMAC_SHA512;

-- verify the key is correct before using the database
select count(*) from sqlite_master;      -- fails if the key is wrong
pragma rekey = 'new passphrase';         -- change the key in place

Encryption and desktop packaging

-- a consistent copy of a live database, without the WAL sidecar problems
vacuum into '/tmp/app-backup.db';

-- and integrity-check the copy before shipping it anywhere
pragma integrity_check;

Full lesson: SQLite on mobile, desktop and in the browser →

LiteFS, libSQL and replication for serverless apps

LiteFS in outline

# litefs.yml
fuse:
  dir: "/litefs"
data:
  dir: "/var/lib/litefs"
lease:
  type: "consul"
  hostname: "node-1"
  consul:
    url: "http://consul.service.consul:8500"
exec:
  - cmd: "/app/server"

LiteFS in outline

# inspect the mounted database and the replication state
litefs status
curl -s http://localhost:20202/   # the LiteFS HTTP API
# /: primary. /primary: current primary address
# /backup: a consistent backup including the WAL

libSQL and Turso

-- read-your-writes: record the frame after a write, then wait for it locally
-- select current_frame from pragma_libsql_frame;   -- provider specific
-- then poll until the local replica has replayed at least that frame

Full lesson: LiteFS, libSQL and replication for serverless apps →

Testing, tooling and benchmarks for SQLite apps

Tooling and maintenance

-- routine maintenance for a single-file application
pragma optimize;
pragma wal_checkpoint(truncate);
vacuum into 'backup/app-2026-09-18.db';
vacuum;

Full lesson: Testing, tooling and benchmarks for SQLite apps →

Next steps: choosing SQLite vs a client-server database

Hybrid patterns

-- if you share one schema across two engines, keep SQL within the common subset
-- and test the parts that differ, in CI, against both
-- differing areas to watch: date functions, upsert syntax,
-- type affinity, string concatenation, and identifier quoting

Full lesson: Next steps: choosing SQLite vs a client-server database →

FAQ

Related cheat sheets

SQL MySQL PostgreSQL MongoDB Redis

Last refreshed 2026-09-27.