SQLite 3.53 is now available, addressing a database corruption bug in WAL reset handling and introducing a wide range of updates to SQL, CLI, query planner, and API components.
A key addition is the Query Result Formatter, now used for CLI result formatting. The release notes state this enhances display capabilities, right-justifies numeric values in tabular output by default, and changes the default interactive CLI output to boxed Unicode tables for improved readability. Batch mode retains the legacy output format for compatibility.
SQLite 3.53 also expands SQL language support. ALTER TABLE can now add or remove NOT NULL and CHECK constraints. The new REINDEX EXPRESSIONS statement rebuilds expression indexes. TEMP trigger bodies may now query or modify tables in the main schema, and VACUUM INTO supports setting the reserve amount via a URI filename parameter. Two new SQL functions, json_array_insert() and jsonb_array_insert(), are also included.
For CLI users, the .mode command has received significant enhancements. Bare semicolons at the end of dot-commands are now ignored. Issues with .testcase and .check have been resolved. Non-empty .sql or .txt files provided on the command line are now interpreted as SQL or dot-command scripts.
Additional features include a one-shot mode for .timer, a new timeout option for .progress, and updates to .indexes so that its pattern matches index names rather than table names. Plus, floating-point to text conversions have been reworked for improved performance, with rounding now defaulting to 17 significant digits rather than 15.
The query planner receives several updates as well. SQLite now consistently uses sort-and-merge for EXCEPT, INTERSECT, and UNION, as this approach is generally faster than hash-table-based handling. The release also improves join order selection for large star-schema joins, expands the EXISTS-to-JOIN optimization, enhances omit-noop-join handling, and increases opportunities for single-index optimization in certain GROUP BY and ORDER BY scenarios.
Finally, it’s worth noting that a notable new addition is the self-healing index feature, which addresses the stale expression index issue. In addition, SQLite also introduces REINDEX EXPRESSIONS to directly rebuild expression indexes as needed.
For more details, see the changelog.
