SQLite adds contextual trigger errors and remote database copying

A database trigger can know exactly which value violates an application rule while returning an error message that cannot mention it. SQLite 3.47.0 removes that limitation by allowing an SQL expression as the message argument to RAISE(). The trigger can construct its explanation from the values it is evaluating, so a caller receives more useful information without first reproducing the rule elsewhere.

The October 21 release also introduces the experimental sqlite3_rsync program for bandwidth-efficient copying of a live database. That is a separate feature, but it addresses another place where knowledge inside the database engine can improve an external operation. The utility compares database pages through SQLite instead of treating a live database as an arbitrary collection of file bytes.

I like these additions because they keep useful work close to the engine that already understands it. A trigger can explain the value it rejected. A copy program can use a database transaction and page representation. Neither change requires describing SQLite as a different kind of database.

A trigger can name the rejected value

The release's parser grammar now accepts an expression after the error action in RAISE(). Its release note describes this as allowing arbitrary expressions in the second argument. A message need not be a fixed string known when the trigger is defined.

An illustrative trigger can reject a negative quantity while including the item identifier in its message. The rule and its explanation remain beside each other in the trigger definition. This example requires the expression support in 3.47.0.

CREATE TABLE stock (
    item_id INTEGER PRIMARY KEY,
    quantity INTEGER NOT NULL
);

CREATE TRIGGER reject_negative_stock
BEFORE UPDATE OF quantity ON stock
WHEN NEW.quantity < 0
BEGIN
    SELECT RAISE(
        ABORT,
        'negative quantity for item ' || NEW.item_id
    );
END;

The concatenation is the new part of the example. The trigger already has access to the candidate row, and the message expression can use that context. A generic error would tell the caller which rule failed. The constructed error also identifies the item whose proposed update triggered the rule.

This does not make the example a complete inventory design. It demonstrates an update trigger with a particular condition. Inserts and any other business rules would need their own coverage. Keeping the example narrow makes it possible to see what the language extension supplies without pretending that one trigger establishes every invariant of an application.

Error detail becomes part of the interface

A database error can cross several layers before a person reads it. An application might log it, translate it into a domain error, or show a selected explanation to a user. Dynamic messages give those layers additional context, but the application still decides which audience receives the raw database text.

There is also a distinction between useful diagnostic content and a stable machine-readable contract. If a caller parses an item identifier out of an English sentence, it has created another dependency on message wording. The new expression capability makes the message more informative. It does not by itself specify a structured application error protocol.

I prefer using the extra context to explain a failure while preserving deliberate application error handling. The attraction is avoiding a second query solely to recover information the trigger already had. That judgment follows directly from the example's mechanics, without assuming that every application should expose its schema names or raw values to its users.

The grammar retains separate error actions, including ROLLBACK, ABORT, and FAIL. Accepting an expression in the message position does not collapse those choices into one operation. The new syntax expands what the explanation can contain while leaving the action as an explicit part of the trigger's statement.

A live database copy has a defined origin

The new utility's versioned implementation describes its purpose as copying a live SQLite database with a bandwidth-efficient protocol. It names the two sides ORIGIN and REPLICA. The direction is meaningful because the operation makes the replica a copy of the origin.

The usage text allows one side to be a local path and the other a remote path of the form USER@HOST:PATH. The program uses SSH to reach the remote side and provides options for selecting the SSH program or the remote sqlite3_rsync executable. A simple invocation illustrates the direction without implying an ongoing replication service.

sqlite3_rsync app.db operator@example.net:copies/app.db

The command describes an individual copying operation. Calling the destination a replica does not turn the tool into a system that continuously routes reads or coordinates application writes across hosts. An application still needs a design for when it creates copies and how it uses them.

The release explicitly labels the utility experimental. That status belongs in any account of its arrival because a new command's presence in the release is not equivalent to a promise that all operational details are settled. The implementation gives a concrete account of this version's protocol and requirements.

Page hashes reduce transferred content

The replica sends hashes for its existing pages, in page order. The origin compares those hashes with the corresponding content in its own database. It then sends page contents where the pages differ, along with pages the destination does not yet have. When much of an existing copy remains unchanged, this avoids retransmitting the full database content.

That mechanism explains both the saving and its limit. A hash exchange still has a cost, and the participants still inspect pages. Bandwidth efficiency does not mean the operation knows every changed row without examining the database. It compares page content, so a small logical update can affect whatever pages SQLite changes to represent that update.

The source reads pages through sqlite_dbpage and collects differing page numbers in a temporary table. The origin then joins those numbers with the database page interface to transmit the required contents. This is a database-aware implementation of the comparison, rather than an SQL export containing reconstructed insert statements.

On the destination side, the protocol applies page content through the corresponding page interface. A transaction-completion message supplies the origin's page count, and the implementation handles the resulting destination size before committing. Copying includes the database's shape as well as replacing the pages whose contents differ.

This version requires a WAL origin

The origin-side code opens the database, begins a transaction, and checks the journal mode. It reports an error if the origin is not in WAL mode. That requirement is visible in the release's source and should not be replaced with assumptions about what a later version of the utility might accept.

Beginning a database transaction establishes a database-level reading operation for the copy. The protocol then works over the pages visible through that operation. This is the relevant distinction when the database remains live. A coherent copy needs a defined database state, not a mixture of independently observed file contents.

The destination code also makes transaction outcome explicit. It commits the completed operation or rolls back when it has recorded an error in the relevant path. The source therefore exposes the copy as database work with a transaction boundary, rather than only a stream of writes to a filename.

For me, the useful addition is a copying tool that understands the unit it is copying. SQLite's database file remains the persistent artifact, and the program uses engine interfaces to construct its remote counterpart. The experiment has a specific scope, including its WAL requirement and page-hash protocol.

The trigger extension has a similarly specific benefit at a smaller boundary. A database rule can include its evaluated context in the message returned to a caller. In both cases, application code gains an operation that uses information already available inside SQLite, with the remaining interface decisions still visible to the application author.

Cleanup can outlive a custom tokenizer

Another 3.47.0 change lets an application drop an FTS5 table whose custom tokenizer is unavailable. A table can outlive the extension code that created it, and removing that table should not require reconstructing an implementation needed only to interpret its text. The release makes that cleanup possible even when the tokenizer has not been registered. This is a useful distinction between using an object and removing it.

The release also adds locale-aware tokenizer interfaces and an option for retaining unindexed values in contentless FTS5 tables. Those additions expand what new search tables can express, while the drop correction concerns existing database management. They affect different phases of a table's life. An application extending its tokenizer configuration gains new capabilities, and an application retiring a search table can complete the cleanup without first loading the missing custom tokenizer.