Chapter 17 · Schema Evolution, Migrations, Testing, and Release Compatibility
Compatibility Testing Across SQLite Versions and Build Options
Distinguish stable database-file compatibility from SQL-feature and compile-option compatibility, probe capabilities at runtime, and publish a defensible minimum-supported SQLite policy.
Learning outcomes
The .sqlite file extension does not tell you which SQL grammar, functions, virtual-table modules, or bug fixes the running library provides. SQLite’s database file format is famously portable, but application compatibility is a contract between the file, the SQL/features you use, the linked SQLite library, its compile options, and the host driver.
Record sqlite_version(), sqlite_source_id(), and compile options as runtime evidence.
Distinguish stable file-format compatibility from SQL syntax/function/extension compatibility.
Probe capabilities instead of inferring every feature from a version string.
Build a minimum-version matrix for generated columns, RETURNING, STRICT, JSON/JSONB, FTS5, and newer ALTER syntax.
Account for mobile, OS-bundled, and language-driver SQLite versions you may not directly control.
Publish a minimum-supported and recommended/qualified SQLite policy for FieldNotes.
Identify the actual library that opened the file
SELECT sqlite_version() AS sqlite_version;SELECT sqlite_source_id() AS sqlite_source_id;PRAGMA compile_options;PRAGMA module_list;PRAGMA function_list;sqlite_version() tells you the runtime release. sqlite_source_id() identifies the source checkout/build ancestry more precisely. PRAGMA compile_options reports compile-time options (with the SQLITE_ prefix omitted). Module/function lists provide additional capability evidence on current builds.
Python, Node.js, Java JDBC packages, .NET native bundles, Android, iOS, desktop applications, and operating systems can each link or bundle different SQLite releases. Always query the database connection itself.
File-format stability is not feature compatibility
An older SQLite engine may be able to recognize the database file format yet fail when it encounters schema SQL or application queries that use newer language features. A generated-column declaration, STRICT table, JSONB function, or virtual table module is not “stored compatibility magic” merely because the bytes live in a normal SQLite file.
| Feature | Minimum/version condition | Compatibility implication |
|---|---|---|
| Generated columns | SQLite 3.31.0+ | Older engines can consider schemas using generated columns malformed/unreadable. |
| RETURNING | SQLite 3.35.0+ | Application DML using RETURNING fails to parse on older libraries. |
| STRICT tables | SQLite 3.37.0+ | Schema syntax requires a sufficiently new library. |
| Built-in JSON default | SQLite 3.38.0+ by default | Can still be omitted with SQLITE_OMIT_JSON; probe functions. |
| JSONB | SQLite 3.45.0+ | SQLite-specific binary JSON functions/storage require 3.45+. |
| FTS5 | Build/module capability | May be compiled in, loadable, or unavailable; version alone is insufficient. |
| ALTER COLUMN SET/DROP NOT NULL | SQLite 3.53.0+ | Do not place this in migrations when minimum-supported runtime is older. |
Version gates are useful; capability probes are stronger
A numeric version check is appropriate when a feature has a clear minimum syntax release. Build-sensitive features need direct probes. The probe itself should be harmless and run in TEMP or a disposable transaction where possible.
import sqlite3con = sqlite3.connect(':memory:')version_text = con.execute('SELECT sqlite_version()').fetchone()[0]version = tuple(map(int, version_text.split('.')))source_id = con.execute('SELECT sqlite_source_id()').fetchone()[0]options = {{r[0] for r in con.execute('PRAGMA compile_options')}}caps = {{ 'generated_columns': version >= (3, 31, 0), 'returning': version >= (3, 35, 0), 'strict': version >= (3, 37, 0), 'jsonb': version >= (3, 45, 0), 'alter_not_null': version >= (3, 53, 0),}}# Direct function/module probes are better than guessing from option names.try: con.execute("SELECT json('{}')").fetchone() caps['json'] = Trueexcept sqlite3.OperationalError: caps['json'] = Falsetry: con.execute("CREATE VIRTUAL TABLE temp.fts_probe USING fts5(body)") caps['fts5'] = Trueexcept sqlite3.OperationalError: caps['fts5'] = Falseprint(version_text, source_id)print(caps)The example uses only the Python standard library and compares a numeric version tuple. Drivers may also expose a numeric SQLite library version API; capability probes remain necessary for build-sensitive features.
Probe syntax in a disposable transaction
For features that affect grammar/schema, the most convincing test is to execute the smallest representative statement in TEMP or a throwaway database, then roll it back/drop it.
-- Generated columnCREATE TEMP TABLE cap_generated( x INTEGER, twice INTEGER GENERATED ALWAYS AS (x*2) VIRTUAL);DROP TABLE cap_generated;-- STRICTCREATE TEMP TABLE cap_strict(x INTEGER) STRICT;DROP TABLE cap_strict;-- RETURNINGCREATE TEMP TABLE cap_returning(x INTEGER);INSERT INTO cap_returning VALUES (1) RETURNING x;DROP TABLE cap_returning;-- JSONB (3.45.0+)SELECT typeof(jsonb('{"a":1}'));-- FTS5 capabilityCREATE VIRTUAL TABLE temp.cap_fts USING fts5(body);DROP TABLE cap_fts;A minimum-supported SQLite policy
The FieldNotes example below deliberately separates minimum from qualified production release. The minimum answers “what can parse and run our required features?” The qualified release answers “what exact patched version did we test and approve for deployment?”
FieldNotes SQLite Compatibility Policy — 2026-08Required minimum engine: SQLite 3.45.0Reason: - generated columns (>=3.31) - RETURNING (>=3.35) - STRICT (>=3.37) - built-in JSON baseline (>=3.38, capability still probed) - JSONB is a required persisted/application feature (>=3.45)Required build capabilities: - JSON functions available - FTS5 only when Search feature is enabled; probe by creating TEMP fts5 table - foreign-key support/enforcement initializationMigration syntax policy: - DO NOT use ALTER ... ALTER COLUMN SET/DROP NOT NULL yet, because that requires >=3.53.0 and our minimum is 3.45.0. - use table rebuilds for minimum-compatible schema transformations.Qualified production baseline at course review: - SQLite 3.53.4 (2026-07-24) - test exact runtime sqlite_source_id and compile options in CI/release evidenceUnsupported database behavior: - app refuses write-open if runtime <3.45.0 - app refuses an unknown newer user_version unless explicitly compatible - missing required capability is a startup/deployment errorWhy 3.53.4 is recommended even if minimum is lower
A minimum-feature version is not a recommendation to deploy an old build forever. As of this course review, SQLite 3.53.4 is the latest patched 3.53 release. Production qualification should prefer a currently patched release and rerun the application’s migration, query, backup, integrity, and concurrency suites against that exact library.
Mobile and OS-bundled SQLite changes the control plane
| Environment | Typical constraint | Compatibility response |
|---|---|---|
| iOS/macOS system SQLite | OS controls bundled engine. | Query runtime; choose feature floor matching supported OS versions or bundle an allowed alternative where architecture permits. |
| Android platform SQLite | OS/API level can determine system engine behavior. | Test supported devices/API levels; avoid assuming desktop SQLite version. |
| Python standard library | Python build links a particular SQLite library. | Inspect sqlite3.sqlite_version/SELECT sqlite_version() in CI/runtime. |
| Node built-in SQLite | Node release bundles its own SQLite integration/library. | Test the supported Node line and report SQLite runtime identity. |
| JDBC/.NET bundled native libraries | Package/provider selects native engine. | Pin provider package and verify the native SQLite version after deployment. |
Compatibility CI matrix
For each supported runtime/build: [ ] open a predecessor-version database fixture [ ] record sqlite_version(), sqlite_source_id(), compile_options [ ] run capability probes [ ] run all migrations to target user_version [ ] run schema + constraint + query tests [ ] run transaction + two-connection concurrency tests on file DB [ ] run backup/restore verification [ ] run integrity_check + foreign_key_check [ ] open upgraded DB with the target application [ ] record pass/fail as release evidenceChapter synthesis
Evolution and compatibility review
Answer as a release engineer.
- Why can a stable SQLite file format still fail on an older runtime?
- What is the difference between user_version and schema_version?
- Why can FTS5 require a capability probe even on a new SQLite version?
- If minimum is 3.45.0, can migration 18 use ALTER COLUMN SET NOT NULL?
- Why test migrations from predecessor database fixtures?
- What should an app do when it sees a database user_version newer than it understands?
Review the answers
File compatibility does not imply support for newer schema/query syntax or modules. user_version is application metadata; schema_version is SQLite-managed invalidation state. FTS5 is build/module dependent. A 3.45 minimum cannot rely on 3.53-only ALTER syntax, so use a compatible rebuild. Predecessor fixtures actually exercise transformation code and legacy data. A write-capable app should normally fail closed on an unknown newer schema unless a documented backward-compatibility contract exists.
Bridge to performance engineering
A migration can be logically correct yet operationally expensive. Chapter 18 moves from correctness to measurement: representative datasets, transaction batching, PRAGMA tradeoffs, maintenance, and performance anti-patterns—always with evidence before tuning.