rqlite writes can return the rows they change

A deleted row cannot be recovered by querying it after the deletion. That simple problem appears in rqlite issue 1157, opened in February 2023. The request asked for an API response containing a column from a DELETE statement with RETURNING. An inserted row could sometimes be fetched through a second query using its identifier. The same workaround did not recover the contents of a row that no longer existed.

rqlite 8.23.0, released on March 14, adds the requested support. Its implementation exposes a distinction that database wrappers can otherwise hide. A statement's ability to change data and its ability to return rows are separate properties. A write can do both, and the request path needs to preserve both facts.

I like rqlite's use of SQLite behind Raft because it keeps relational operations available within a comparatively small distributed system. Returning the values affected by a write is a useful extension of that model. It lets SQL describe the result the application needs without requiring a second request to reconstruct it from database state that may already have changed.

The result belongs to the write

Consider a table of pending work. Deleting one completed entry while returning its identifier and payload expresses a single database operation. The example does not claim any particular queue delivery guarantee. It demonstrates the connection between the mutation and the values returned by that mutation.

CREATE TABLE pending_work (
    id INTEGER PRIMARY KEY,
    payload TEXT NOT NULL
);

INSERT INTO pending_work VALUES (1, 'generate-report');

DELETE FROM pending_work
WHERE id = 1
RETURNING id, payload;

The original issue used this kind of deletion to explain the missing capability. A separate query before deletion would introduce another operation and leave the application responsible for relating the observed row to the subsequent mutation. A query after deletion cannot read that row at all. Returning columns from the modifying statement directly addresses the requested result.

The release is therefore useful beyond generated identifiers. The old execution response could report a last insert identifier and a count of affected rows. Those values describe aspects of a write, but they do not carry arbitrary columns from the affected records. A RETURNING result needs column names, values and their associated types, which are the structures normally associated with querying.

This difference is visible in the database tests at 8.23.0. A test inserts a row with RETURNING * and enables the internal query-result flag. It expects the inserted identifier and name in a row-shaped response. The same test disables that flag for another insertion and expects the ordinary execution metadata. Both writes take place, but the response contains different information.

Recognising a row-producing mutation

The SQL processor examines statements before execution. Its visitor recognises a ReturningClause in the parsed SQL and records that the statement produces rows. The processor then sets the statement's ForceQuery field. This is internal request metadata that directs result collection further down the execution path.

The processor already has another responsibility. It can rewrite calls to RANDOM() before replicated execution. The new code keeps the decision to rewrite random values separate from the decision to recognise RETURNING. When random rewriting is disabled, the processor can still parse a statement that contains the returning keyword. Disabling one transformation does not automatically mean that result classification should disappear.

There is a preliminary text check for the keyword to avoid parsing statements that cannot need this work when random rewriting is off. The actual classification comes from the parsed statement and the visitor's recognition of the returning clause. A text occurrence by itself does not set ForceQuery. This matters because SQL text can contain words in literals or identifiers without giving them the grammatical role of a clause.

The release changelog records parser upgrades alongside the support itself. That is a material part of the implementation. SQLite understanding a statement does not automatically make every layer around SQLite understand the response it must return. rqlite's own processing layer needs a suitable representation of the clause to select the correct database call.

The processor also continues past parse errors in this version. It does not make its own parser an unconditional rejection point for every SQL statement. That behaviour explains why support involves the parser and the execution flag together. A statement reaching SQLite and a statement receiving the intended row-producing treatment are related but distinct outcomes.

Collecting rows on the write connection

The database implementation checks ForceQuery inside its execution path. With the flag set, it invokes the row-query helper through the same execution-capable connection or transaction. Without the flag, it calls ExecContext and constructs a response containing the last insert identifier and affected-row count. The branch changes how the result is collected.

This does not turn the write into an ordinary read-only query. The query endpoint's database path still checks whether a statement is read-only and rejects attempts to change the database through that operation. A mutation that produces rows belongs on a path that can perform mutations. The ability to collect rows from a connection does not establish the consistency or replication policy for the statement.

The unified request implementation uses the database's read-only classification to distinguish reads from writes. Read-only statements use the query helper directly. Statements that can modify data use the execution helper, which can in turn choose row collection when ForceQuery is present. There are therefore two decisions, one about the statement's effects and another about its response.

The tests cover the unified request path as well as the ordinary execution path. After verifying row-returning insertion through execution, they send another flagged insertion through the request method and check the returned columns and values. This matters because a feature exposed through more than one request form needs consistent result handling at the common database layer.

The error tests exercise incomplete returning syntax and a reference to a missing column. Those cases need to remain database errors even when the execution helper uses the query interface. Changing the result collection method does not make invalid SQL valid, and callers still need to inspect the returned error before using any values.

The query helper collects rows into the response as it iterates over them. Consequently, selecting the columns in RETURNING also determines the data accumulated for the response. Returning a large payload from every affected row has different response costs from returning only an identifier. The deletion example names the required columns explicitly so the operation's output is visible in the SQL itself.

I find that separation more useful than treating every row-shaped response as evidence of a read. SQL can return information while changing state, and an API needs to represent that without obscuring the mutation. The implementation keeps the statement on a write-capable path while borrowing the machinery that already knows how to collect rows.

Why the rolling upgrade warning matters

The 8.23.0 changelog explicitly permits upgrading through a rolling restart, but warns that writes might return errors until every node runs the new version. It says queries should continue operating during the restart. That is a specific compatibility qualification attached to this release, and it affects the period when old and new nodes coexist.

The distinction between queries and writes is consistent with how substantial this change is inside rqlite. Supporting RETURNING involves request metadata and different execution response forms. The announcement is not merely declaring that a new keyword passes through to SQLite. It changes how a replicated write's result can travel back through the system.

The source does not justify promising that ordinary writes will always avoid the warning, or that only clients already using RETURNING can encounter it. The release notes say writes might return an error during mixed-version operation. Applications and deployment procedures should use that stated scope. Narrowing it without evidence would misrepresent the upgrade guidance.

After the upgrade, client response handling also needs to accept the result form requested by the SQL. A client that assumes every mutation has only an affected-row count cannot expose the returned columns. The server-side implementation and the client-side decoder solve different parts of the same operation. The practical result is that a modifying statement can carry the affected data back to its caller without another database query.