James Ainslie

A SQLite database file and a file containing SQL statements can represent the same relational data, but they are not interchangeable inputs. rqlite 4.6.0, released on 29 November, brings backup and restoration commands into the CLI. The command names expose a distinction that matters during recovery. .backup produces a SQLite file, .dump produces SQL text and .restore consumes a SQL dump.

The distinction follows rqlite's architecture. The service maintains replicated database state through its own API and consensus machinery. Loading SQL through that service is different from replacing a local file beneath a running node. The new commands make supported operations more convenient without removing the format conversion between a binary backup and the SQL restore interface. That conversion remains an explicit step in the documented procedure.

I value the ability to obtain a normal SQLite artifact from a replicated service. It gives the relational data an independently inspectable form. The recovery procedure still needs to be explicit about which representation the receiving operation accepts, particularly when similarly named commands sit beside each other in the same client.

Two ways to export the database

The 4.6.0 backup documentation describes hot backups through the CLI or HTTP API. The interactive command writes the returned database to a local filename.

.backup archive.sqlite3

The CLI implementation issues a GET request to /db/backup, reads the response and writes it to that filename. The path names a file on the machine running the client. It does not name a destination inside each database node's data directory.

A SQL text export uses the same endpoint with a format argument. Its command is separate because the resulting artifact is separate.

.dump archive.sql

The implementation adds fmt=sql to the request and writes the returned text locally. The extension in these examples helps a human distinguish the outputs, but the request format determines their contents. Renaming a binary SQLite file with a .sql suffix does not convert it into statements the restore endpoint can execute.

Both paths use the shared request helper. The main CLI source shows that helper applying configured credentials and handling leader redirects explicitly. The interactive export is therefore a client of the existing database API, rather than direct access to the node's local SQLite storage.

Restoration executes SQL text

The restore documentation for this version states that a SQLite binary backup must be converted into SQL commands before restoration. It demonstrates using the SQLite command-line program's .dump operation for the conversion. A SQL dump obtained directly through the new rqlite .dump command already has the representation expected by the restore operation.

.restore archive.sql

The CLI reads the input file and posts it to /db/load with the text/plain content type. That request expresses loading SQL through rqlite. It does not request installation of an arbitrary database image into the existing Raft data directory. The documentation limits restoration to a new or empty cluster because behavior with existing data is undefined.

This also explains why .backup and .restore do not form a direct binary round trip in this release. The backup is a useful database artifact, while restore accepts executable SQL text. A recovery description that omits that boundary can leave an operator with a valid backup and an invalid restore request. The correct conversion belongs in the procedure, rather than in an assumption based on the command names.

Foreign key state needs attention

SQL dump files normally contain a command disabling foreign key constraints, according to the version-pinned restore documentation. Executing that text can consequently alter more than table contents. The CLI's restore implementation queries the server's status before loading the file and examines the reported foreign key constraint setting.

It then appends a PRAGMA foreign_keys statement reflecting the server's prior enabled or disabled state. The selected statement follows the loaded SQL text. That is a specific attempt to retain a server setting that the dump itself can change. The extra step comes from the semantics of the import format, rather than from the mere act of transferring bytes.

The status request is also an additional dependency of the CLI operation. Its implementation can fail if the response lacks the expected store status. Restoration therefore includes obtaining metadata before posting the SQL, and it cannot be understood solely as opening a file and sending its contents. The command needs the status result to construct the final input it submits.

A completed command has several stages

The implementation parses the load response and requires at least one result. If that result contains an SQL error, the CLI prints the error instead of the successful-restoration message. Otherwise it displays result metadata and reports success. A transport response and successful SQL execution are separate conditions in this path.

Export has its own sequence. The client must obtain the response and then write the local file before it prints its success message. The implementation reads the response into memory before the file write. This describes the behavior of the 4.6.0 CLI and should not be mistaken for a streaming file transfer merely because the server endpoint provides a backup.

I find these commands useful because they make common database management operations accessible from the same client used for queries. Their value depends on retaining the underlying distinctions. The backup contains a SQLite database, the dump contains SQL and restoration submits SQL through the service's load endpoint.

For an existing binary backup, the documented recovery path includes converting the file to SQL before loading a new or empty destination. For a SQL dump, the CLI can submit that text directly and restore the prior foreign key setting afterward. Those are concrete operations an operator can identify from the artifact in hand.