Backing up a Cloudflare D1 database that has FTS5 tables

Short answer. Cloudflare's D1 export endpoint fails with "cannot export databases with virtual tables (like FTS5)". Dump the schema from sqlite_master and the rows through the query API instead: CREATE statements first, rows in pages of 2,000 as INSERTs, FTS5 tables recreated with CREATE VIRTUAL TABLE and refilled by rowid, shadow tables skipped, indexes and triggers last. Restore with wrangler d1 execute --file and compare row counts plus one MATCH query.

The error

seops takes a nightly dump of every D1 database it knows about and stores it in R2. One of them started failing every night:

D1 Export error: cannot export databases with virtual tables (like FTS5)

The database powers a site search with an FTS5 table (codes_fts USING fts5(body, tokenize = 'unicode61 remove_diacritics 2')). The export API (POST /d1/database/{id}/export) refuses the whole database as soon as one virtual table exists. Removing FTS5 wasn’t an option: the site’s search runs on it.

The dump that works

Everything below goes through the normal query API (/d1/database/{id}/query), which has no such restriction.

1. Read the schema.

SELECT type, name, tbl_name, sql FROM sqlite_master WHERE sql IS NOT NULL ORDER BY rowid;

2. Classify. Normal tables are dumped as rows. Virtual tables (sql LIKE 'CREATE VIRTUAL TABLE%') get special handling. Their shadow tables (<name>_data, _idx, _content, _docsize, _config) are skipped entirely; SQLite rebuilds them. _cf_* and sqlite_* tables are skipped too.

3. Dump normal tables. Write the CREATE TABLE statement, then page through the rows:

SELECT * FROM "codes" ORDER BY rowid LIMIT 2000 OFFSET 0;

Each page becomes multi-row INSERT INTO "codes" (...) VALUES (...), (...); statements. Quote strings with doubled single quotes, write blobs as X'..', keep NULLs as NULL.

4. Dump FTS5 tables. Write the original CREATE VIRTUAL TABLE statement, then:

SELECT rowid, * FROM "codes_fts" ORDER BY rowid LIMIT 2000 OFFSET 0;

and insert with the rowid included: INSERT INTO "codes_fts" (rowid, body) VALUES (...). For a contentless or external-content FTS table, insert into the content source and run INSERT INTO fts(fts) VALUES('rebuild') at the end instead.

5. Indexes and triggers last, from the same sqlite_master read. Triggers that fill the FTS table must come after the data, or every row would be indexed twice.

6. Compress and store. gzip the SQL and put it in R2 under d1/<database>/<date>.sql.gz, next to the dumps that came from the export API. Record how the dump was made in the backup row (“query API, virtual table”).

Restore and verify

wrangler d1 execute my-db --local --file=2026-11-04.sql
wrangler d1 execute my-db --local --command="SELECT count(*) FROM codes; SELECT count(*) FROM codes_fts; SELECT count(*) FROM codes_fts WHERE codes_fts MATCH 'e13';"

Row counts must match the source (here 1,203 and 1,203), and a MATCH query must return the same hits as production. A dump that loads but can’t search is not a backup.

What seops does with it

In the panel, Settings › Infrastructure › Backups lists every D1 database, the last successful dump, the method used and the size. If the export API fails with the virtual-table error, the query-API path runs automatically. A database with no successful dump in 36 hours raises an alert.

Preguntas