SQLite 3.42.0 separates accepted JSON from retained search data

Deleting a searchable document does not necessarily remove its old terms from the search index immediately. FTS5 normally records deletions in a new index structure and removes older entries during later merges. That is efficient for updates, but it leaves information that can help reconstruct deleted content. SQLite 3.42.0, released on May 16, adds an FTS5 option that changes this behavior.

The release also broadens JSON input parsing to accept JSON5 syntax. These changes address different application needs. One governs what text the engine accepts as structured input. The other governs what information an index retains after an update. Both deserve more precise descriptions than a claim that SQLite now supports another format or makes deletion secure.

I value SQLite's stable, documented database format and its direct use inside applications. That makes changes to stored index representation particularly relevant. An option that changes which older readers can open a table needs an explicit compatibility decision, even when the surrounding database remains a SQLite file.

Why deleted search entries remain

The FTS5 documentation as of May 16 describes the normal deletion process. Updating or deleting an entry adds delete-keys to the new b-tree created by the transaction. Older full-text entries remain until merge operations remove them. Query execution can interpret the deletion information while those old entries still exist.

This is a distinction between the current logical result and the information retained in storage. A search that no longer returns a document does not establish that its prior indexed terms have disappeared. A person with access to the database can inspect information beyond the application's ordinary search results. FTS5's new option addresses that retained index content.

Setting the persistent secure-delete option to one causes FTS5 to remove the old full-text entries when rows are updated or deleted. The documentation states that this is slower. Immediate removal does more work than recording a deletion for later merging, so the option exchanges update efficiency for a different retention policy.

For an FTS5 table named documents, the configuration uses the table's special command interface. Creating the table and selecting its deletion policy are separate statements.

CREATE VIRTUAL TABLE documents USING fts5(body);
INSERT INTO documents(documents, rank)
VALUES('secure-delete', 1);

The command names the FTS5 table in the target columns and supplies the configuration key as a value. It is a table setting, not a global declaration that every index in the database now follows the same policy. The default value remains zero, so installing the new library does not by itself enable the behavior for existing tables.

The core database has another deletion policy

FTS5 secure deletion prevents old full-text entries from remaining available to someone with SQL access to the database. The documentation separately addresses someone who can inspect the SQLite database file itself. For that case it also requires the core secure_delete setting.

PRAGMA secure_delete = 1;

The two settings concern different representations of old data. FTS5 maintains a logical inverted index within its database storage. The core database manages the underlying pages and their contents. Removing a term from the FTS5 index is not the same statement as removing every residual byte from the file.

That distinction also limits what an application should promise its users. Neither command is a documented erasure procedure for every copy outside the active database. A backup policy has its own retention decisions. The release's claim concerns the FTS5 inverted index, and the documentation explains the additional core setting for file-level access.

This is where my preference for SQLite's inspectable file model affects the application design. A file that is easy to copy is also a file whose retained information may matter outside the process that created it. A deletion policy needs to describe the representation and access level it covers.

Secure deletion can exclude older FTS5 readers

Once rows have been updated or deleted with FTS5 secure deletion enabled, older FTS5 versions may no longer read or write that table. The May documentation identifies 3.42.0 as the first supporting version. It gives an example error in which a reader finds format version five while expecting version four.

The versioned secure-deletion tests exercise the command followed by deletions and integrity checks. These are useful examples of the option in the implementation that ships with the release. They also keep the discussion tied to the new behavior rather than to assumptions about how ordinary SQLite table deletion works.

The compatibility change concerns FTS5's internal table representation. It should not be described as a replacement of the entire SQLite database format. Nevertheless, an older utility that needs to query the affected table can fail. An application distributing database files must account for the FTS5 capabilities of its readers.

The documentation provides a way to revert the FTS5 representation for older readers by running the table's rebuild command using a supporting engine. That is an operation on the index, so it belongs in the compatibility plan. Turning a configuration value back off is not documented as an equivalent reversal of changes already made to stored index content.

JSON5 changes input acceptance

The JSON documentation at the release date lists the newly accepted syntax. Object keys can be unquoted identifiers, and objects and arrays can have a trailing comma. Input may contain comments or single-quoted strings. JSON5 also broadens numeric notation, including hexadecimal values and explicit positive signs.

Those conveniences apply to reading and interpreting text. SQLite continues generating strictly conforming JSON output. The json() function provides a direct conversion from accepted JSON5 text to that output form. For example, this input uses an unquoted key and a trailing comma.

SELECT json('{answer:42,}');

The result is {"answer":42}. The transformation removes the input syntax extension while retaining the object value. This makes the function useful at an application boundary where a producer accepts a human-oriented notation but a consumer requires ordinary JSON.

Such conversion does not preserve the source document's presentation. Comments are input syntax rather than object members. An application storing the normalized result is choosing a representation of the data, not a verbatim copy of what a person wrote. Keeping original text for editing is a separate storage decision.

Validation keeps its earlier meaning

A significant compatibility detail is that json_valid(X) continues to reject noncanonical JSON, even when SQLite can parse it using JSON5 extensions. Acceptance by the parser and conformance to the narrower JSON syntax are separate questions. Changing the first does not silently change a predicate that applications may already use in constraints.

The API behavior is explicit in the 3.42.0 JSON implementation. Its validity result requires both successful parsing and the absence of nonstandard syntax. The error-position function instead reports success when the text is interpretable with supported extensions.

A query can make that distinction visible without changing any stored data. Both expressions receive the same input text, so their different meanings account for the results.

SELECT
  json_valid('{answer:42,}'),
  json_error_position('{answer:42,}');

For this accepted JSON5 input, the first result is zero and the second is zero. The first zero says the text does not satisfy the strict validity predicate. The second says the parser found no syntax error under its accepted input rules. An application must choose the predicate that represents its intended contract.

For text that cannot be interpreted even with extensions, json_error_position() returns a positive, one-based character position. That differs from a byte offset API and should be presented accordingly in an editor. The function supplies a location where parsing first recognized an error, rather than a complete description of every defect in the document.

SQLite also lowers the maximum recursion depth for JSON arrays and objects from 2,000 to 1,000 in this release. Broader accepted syntax therefore arrives alongside a tighter nesting limit. A permissive parser still has resource limits, and an application accepting externally supplied documents needs to understand both.

The useful design choice is explicit normalization at the point where input becomes application data. JSON5 can make authored input more convenient while strict output remains suitable for consumers expecting JSON. In FTS5, the equally explicit choice is whether updates should remove old index entries immediately. Each feature adds a policy that an embedded database application can select for its own data. For JSON, a constraint can retain its strict meaning even as conversion accepts more input. For search, retaining the default continues the existing update behavior until the application selects immediate removal.