13.9 · Reading query plans, typing and NULL, WAL locking, savepoints and full-text search

SQLite is small and forgiving, which is why its surprises are expensive: a constraint that is not enforced, a filter that cannot use an index, a lock you did not expect.

Mental model. Ask SQLite what it plans to do (EXPLAIN QUERY PLAN), turn on the checks you rely on (STRICT tables, constraints), and treat transactions as the unit of correctness and speed.

Plans and defaults

Index use in query plans, and the typing and NULL rules people assume.

  1. How do you read EXPLAIN QUERY PLAN, and which common query shapes stop SQLite using an index? 13.9.1 · L3
    EXPLAIN QUERY PLAN SELECT … returns one line per step, and the first word tells you the cost: SEARCH … USING INDEX is an index lookup (fast), SCAN reads the…
  2. How does SQLite’s dynamic typing and three-valued NULL logic bite: affinity, STRICT tables, IN and NOT IN? 13.9.2 · L3
    Typing is dynamic. A column’s declared type is an affinity, not a constraint: in the check an INTEGER column accepts the string 'abc' (stored as text), the…

Concurrency and search

WAL locking, savepoints and constraint failures, and FTS5.

  1. How does write-ahead logging change locking, what does SQLITE_BUSY mean, and how do you configure busy handling and transactions? 13.9.3 · L5
    The default journal mode is delete (a rollback journal); PRAGMA journal_mode = WAL switches to a write-ahead log and the mode is persistent in the file (the…
  2. How do savepoints and constraint failures behave inside a transaction, and when do you use FTS5 instead of LIKE? 13.9.4 · L3
    A transaction makes several statements all-or-nothing and much faster (one commit instead of one per statement, which in a loop of thousands of inserts is the…

More in Persistence & data