Sqlite Knowledge Patch

SQLite Knowledge Patch

Use this skill for SQLite SQL, C API, CLI, build, storage, FTS5, session, JSON, and JSONB work where current compatibility details affect the answer. Load only the reference files relevant to the task, and preserve the stated conditions and limits when applying their guidance.

Reference index

Highest-risk compatibility changes

Do not target the withdrawn release

SQLite 3.52.0 was withdrawn because new features caused backward- compatibility issues. Its planned features moved to 3.53.0. Do not target 3.52.0.

Use the corrected maintenance releases

• On the 3.51 line, use 3.51.3 or later because that release fixes a WAL-reset database-corruption bug.
• Use 3.50.1 or later for sqlite3_rsync; 3.50.0 can omit the replica's final page.
• Account for the query-result and memory-safety corrections listed in Storage, locking, and correctness.

Preserve OPFS SAHPool compatibility direction

The corrected filename digest creates a one-way compatibility boundary: 3.50.0 can read databases made by older SAHPool VFS versions, but older SAHPool VFS versions cannot read databases created by 3.50.0 or later.

Update .indexes consumers

The .indexes PATTERN argument matches index names rather than indexed-table names. Existing shell scripts can therefore return different results.

Account for changed output and formatting

• Floating-point-to-text conversion defaults to 17 significant digits rather than 15. SQLITE_DBCONFIG_FP_DIGITS changes it per connection.
• Interactive CLI sessions default to Unicode box output and right-justify numbers in tabular modes; batch sessions keep the legacy format.
• Built-in printf() and SQL format() suppress a negative-zero sign for a # format without + when every displayed digit is zero.

Respect new type and limit behavior

• Generated columns in STRICT tables enforce their declared types.
• SQLITE_LIMIT_LENGTH cannot be set below 30.
• The SQL-function argument limit is 1000 rather than 127.

Schema and SQL quick reference

Change constraints directly

ALTER TABLE can add and remove NOT NULL and CHECK constraints.

Repair expression indexes

Use the expression-index repair command when stored expression-index values are stale:

REINDEX EXPRESSIONS;

SQLite also self-heals stale expression indexes.

Use expression-based trigger messages

The message argument to a trigger's RAISE() may be any SQL expression, so it can include values from the affected row:

SELECT RAISE(ABORT, 'negative total for order ' || NEW.id);

Optimize all tables when requested

PRAGMA optimize uses a temporary analysis limit and automatically re-analyzes tables without sqlite_stat1 entries. Include option bit 0x10000 when every table should be checked for possible updates.

Choose ambiguous date shifts

Use ceiling or floor after a month or year shift to select how an ambiguous date is resolved:

SELECT date('2024-02-29', '+1 year', 'floor');

JSON and JSONB quick reference

Insert array elements

Use json_array_insert() for JSON and jsonb_array_insert() for JSONB.

Preserve JSONB during traversal

jsonb_each() and jsonb_tree() parallel the text traversal functions while keeping array and object rows as JSONB in the value column.

Count backward in arrays

A negative integer on the right of ->> selects an array element from the end:

SELECT '["first","middle","last"]' ->> -1;

Treat numeric-looking text keys as labels

For -> and ->>, a text right operand that looks numeric remains an object label rather than becoming an integer array index:

SELECT '{"0":"zero"}' -> '0';

Choose validation depth deliberately

For two-argument json_valid(), flag 6 accepts JSON5 text or plausible JSONB using a superficial check; flag 8 performs a linear-time deep JSONB check. Read JSON and JSONB for all modes and malformed-input behavior.

C API and configuration quick reference

Prepare without log noise

Use SQLITE_PREPARE_DONT_LOG with sqlite3_prepare_v3() when test-compiling SQL so ill-formed input does not send warnings to SQLite's error log.

Prepare schema-derived SQL

Virtual-table implementations can pass SQLITE_PREPARE_FROM_DDL to sqlite3_prepare_v3() for schema-derived SQL.

Separate lock timeouts

On builds that support blocking locks, sqlite3_setlk_timeout() sets the blocking-lock timeout independently of sqlite3_busy_timeout().

Control newly submitted comments

Disabling SQLITE_DBCONFIG_ENABLE_COMMENTS blocks comments only in newly submitted SQL. Comments already stored in sqlite_schema remain readable.

CLI and build quick reference

Run scripts and one-shot controls

A non-empty .sql or .txt command-line argument is executed as a script. Use .timer once for only the next SQL statement and .progress --timeout S to interrupt statements after S seconds.

Replace the removed Windows console option

SQLITE_USE_STDIO_FOR_CONSOLE is removed. Windows command-line-tool builds can define SQLITE_USE_W32_FOR_CONSOLE_IO to use Win32 console APIs without affecting the SQLite core.

Enable bundled extensions explicitly

The amalgamation contains carray and percentile, but they remain disabled unless built with SQLITE_ENABLE_CARRAY or SQLITE_ENABLE_PERCENTILE.

Apply details without extrapolation

When a task touches a listed edge case, use the exact semantics from its reference. Keep version-specific fixes, build conditions, default states, directional compatibility, experimental status, and may/should/must wording intact. If the references state only that behavior changed, do not invent a cause or replacement.