SQLite 3.53: WAL fix and new functions

# SQLite 3.53: Key Bug Fixes and New SQL Functions

SQLite 3.53 is out — a compact embeddable C database engine in the public domain. The library is perfect for integrating into apps without licensing restrictions. The previous release, 3.51, dates back to November 2025, and version 3.52 was canceled. The consortium funds its development.

This version focuses on WAL mode stability, ALTER TABLE enhancements, and JSON tools. Query optimizations boost performance for table merges and UNION/INTERSECT/EXCEPT operations.

Critical Fix for Database Corruption in WAL

A bug causing database corruption in Write-Ahead Logging mode has been fixed. The issue dated back to 2010 (SQLite 3.7.0) and surfaced during race conditions: multiple connections opening the DB simultaneously and trying to write/commit transactions to the same file. The failure was rare, only reproducible in test scenarios.

This fix is critical for multithreaded apps using WAL, where concurrent access is the norm. Mid- and senior-level developers will appreciate eliminating this latent vulnerability without regressions.

DDL Enhancements: ALTER TABLE and REINDEX EXPRESSIONS

The ALTER TABLE command now supports dropping NOT NULL and CHECK constraints. Handy for schema evolution without full table rebuilds.

The new REINDEX EXPRESSIONS command rebuilds indexes on expressions (functions over columns or operations). It's used to restore outdated indexes after data changes.

Example scenario:

• An index on UPPER(column) degrades during updates.
• REINDEX EXPRESSIONS(name) restores integrity.

Temporary triggers (CREATE TEMP TRIGGER) on tables from other databases can now modify tables or read data.

JSON Functions and VACUUM Improvements

Added json_array_insert() and jsonb_array_insert() for inserting elements into JSON arrays while preserving order and types.

VACUUM INTO with URL targets now supports reserve=N to set the number of database backup copies.

List of new JSON features:

• json_array_insert(json, path, value): insertion by path.
• jsonb_array_insert(jsonb, path, value): binary variant for performance.
• Support for positional and named insertion.

Improvements to the sqlite3 CLI Utility

The QRF (Query Result Formatter) library is integrated for readable query result output.

Command changes:

• .mode expanded with new formats (framed tables by default).
• Numbers right-aligned in table output.
• Semicolon ; ignored after dot-commands.
• .sql/.txt files from the command line are executed as SQL + dot-commands.
• .timer once — timing only for the next query.
• .progress -timeout S — interruption on timeout.
• .indexes applies patterns to index names, not tables.

These features speed up debugging and analysis in the terminal.

Optimizations and Platform Changes

The query planner is optimized for table merges and EXCEPT/INTERSECT/UNION — performance gains on large datasets.

Float-to-text conversion is faster (sprintf-like operations).

WebAssembly build: new VFS opfs-wl uses Web Locks API for locking, similar to opfs.

sqlite3_rsync: -p/--port option for network rsync.

Windows RT support discontinued.

Float-to-text performance improved via vectorization in hot paths.

Key Takeaways

• Fixed rare but fatal WAL race with multi-connection access.
• ALTER TABLE drops NOT NULL/CHECK; REINDEX EXPRESS for expression-based indexes.
• json_array_insert() and CLI with QRF for JSON/debugging.
• Optimizations for UNION/EXCEPT and float formatting.
• WebAssembly: opfs-wl VFS with Web Locks.

— Editorial Team