Exporting a Database with sqldump

kent sqldump exports a database to a .sql script that recreates it when replayed. With --schema-only it exports the structure alone, which is how the schema files published on The schema as SQL are produced.

It is read-only. kent sqldump opens the database in SQLite's read-only mode.

Synopsis

kent sqldump --in <dbfilename> [--out <outfile>] [--schema-only]

Arguments

Writing to a file, or to standard output

With --out, the script is written to that file. Without it, the script goes to standard output so it can be piped into something else:

$ kent sqldump --in old.sqlite | python3 migrate.py > new.sql

Because standard output carries the script itself, every message kent sqldump prints goes to standard error, errors and advisories alike. Nothing Kent prints is ever interleaved into the export.

What the script contains

In order: a comment header, PRAGMA foreign_keys=OFF and the PRAGMA user_version that restores the schema stamp, a transaction, every CREATE TABLE, every row as an INSERT, then the indexes, views and triggers, then COMMIT.

$ kent sqldump --in mydata.sqlite --out mydata.sql
$ head -10 mydata.sql
-- kent sqldump
-- source: mydata.sqlite
-- kent: 1.3.4
-- cordelia: 1.3.3
-- exported: 2026-09-16T22:55:58Z
-- user_version: 2

PRAGMA foreign_keys=OFF;
PRAGMA user_version = 2;
BEGIN TRANSACTION;

Table data comes before triggers deliberately. A trigger that changes another table when a row is inserted would otherwise fire a second time during the restore, leaving the restored database subtly different from the original.

PRAGMA user_version is carried through because Cordelia refuses to open a database whose schema version it does not recognize. Without it, a restored export would report version 0 and Cordelia would not open it.

Why the header records Cordelia's version

Cordelia defines the schema; Kent only transcribes it. A later build of Kent produces the same schema from the same database, so Kent's version does not identify what the schema came from. Cordelia's does, and for a reference artifact that is the line that matters.

Two exports of the same database match

Apart from the timestamp in the header, two exports of the same database are byte-identical. Tables are emitted in name order and rows in insertion order, so one export can be diffed against another, which is the point if you are writing a script to convert an old schema to a new one.

--schema-only

Emits every CREATE statement and the PRAGMA user_version, with no INSERTs. For a freshly created database that is the difference between 54 KiB and 2.3 MiB, since roughly 98% of a new database's rows are pre-seeded lookup data:

$ kent sqldump --in mydata.sqlite --schema-only --out schema_01.sql
$ grep -c '^CREATE' schema_01.sql
129
$ grep -c '^INSERT' schema_01.sql
0

The script still replays. It just produces an empty database with the same schema. This is the form to use for a per-version reference artifact, where diff schema_01.sql schema_02.sql is meant to read as a statement of what changed between two schema versions.

An old database is exported, not refused

Unlike every other Kent command, sqldump does not refuse a database whose schema version differs from the one this build expects. It exports it anyway, with a note on standard error. Refusing it would defeat the purpose, since exporting an old database is exactly what you do before migrating it.

$ kent sqldump --in old.sqlite --out old.sql
kent sqldump: note: this database reports schema version 0, but this build of cordelia expects 2. Exporting it anyway.
$ echo $?
0

Compare kent info, which refuses the same file.

The export is written over nothing

--in and --out naming the same file is refused before anything is read, rather than truncating the database you asked to export.

$ kent sqldump --in mydata.sqlite --out mydata.sqlite
kent sqldump: refusing to write the export over its own source: mydata.sqlite

A path given to --in that does not exist is reported as missing and is not created. SQLite's own open call creates the file it is given, so a mistyped path would otherwise leave a stray empty database behind.

$ kent sqldump --in nope.sqlite
kent sqldump: nope.sqlite does not exist

Schemas that cannot be exported

A Cordelia database uses none of these. kent sqldump refuses them rather than producing a script that would restore incorrectly. A wrong export is worse than a refused one.

Two of the three arise only when rows are written, so --schema-only lifts them. The virtual-table refusal is about the schema itself, so it stands in both modes.

Restoring an export

The script replays under any SQLite tool, not just Kent:

$ kent sqldump --in mydata.sqlite --out mydata.sql
$ sqlite3 restored.sqlite < mydata.sql
$ sqlite3 restored.sqlite 'PRAGMA user_version'
2
$ sqlite3 restored.sqlite 'SELECT count(*) FROM better_labels_lookup'
16866

Restore into an empty file, not into a database kent createdb already made. An export carries every CREATE TABLE, so replaying it into an existing Cordelia database fails immediately with table AadAccelFeatures already exists. It is self-contained: the schema and the data.

An export is the input to a migration, not a migration in itself. Converting an old schema to a new one means editing the script between dump and restore, which is what the pipe above is for. A conversion script should not strip the pre-seeded reference tables (FileTypeLookup, FieldTypeLookup, better_labels_definitions, better_labels_lookup, around 19,700 rows). Nothing regenerates them during a restore, because nothing creates the schema except the script itself; dropping their rows produces a database whose lookup tables are empty.

See also Error Messages and Exit Codes and The schema as SQL.

See also

• Exit codes
• The Data Dictionary
• Reporting on a Database with info
• Creating a Database with createdb
• Error Messages and Exit Codes