SQLite 3.53 adds constraint changes and repairs expression-index compatibility

SQLite released 3.53.0 on April 9, adding constraint-management operations, expression-index repair features and a new query-result formatter. The release log also includes the WAL-reset corruption fix and changes to floating-point text conversion.

Schema maintenance gets new options

The release allows ALTER TABLE to add and remove NOT NULL and CHECK constraints. It introduces REINDEX EXPRESSIONS and a self-healing index feature for stale expression indexes.

For embedded applications, our analysis is that this is a reason to revisit migration code rather than immediately replace it. A migration must still define what happens to existing records that violate a newly imposed rule. Test both a clean database and a database containing historical edge cases, and preserve the expected failure behavior.

Human output and machine contracts differ

SQLite now defaults to 17 significant digits for floating-point-to-text conversion rather than 15, with a configuration API available to alter that choice. The CLI gains more readable interactive output while batch sessions retain the legacy format.

Those changes call for separate tests. Human-readable diagnostics can improve without being suitable as a stable interchange format. If a program compares exported text, computes a digest over it or reparses a floating value, include that path explicitly.

A useful upgrade fixture should reopen an existing database, run its expression-index queries and export a small set of difficult numeric values. Compare the records and serialized representations independently. Start with the application’s bundled SQLite build, because a newer command-line utility on a developer’s machine does not establish which engine ships to users.