How do you export a Cloudflare D1 database with wrangler d1 export and load a SQL file into D1, and what limits apply?
answer
- a .sql file in both directions
- loading means executing the file
- export blocks the database
- dump edits: transactions and statement size
basics
~20 swrangler d1 export --remote --output writes the schema and data as a .sql file and blocks the database while it runs. Loading uses wrangler d1 execute --remote --file, with files up to 5 GB and statements up to 100 KB.
solid answer
~40 s`wrangler d1 export <db> --remote --output=./db.sql` writes the database as SQL; `--table` (repeatable) narrows it, and `--no-data` or `--no-schema` keeps only one half. A remote export makes the database unavailable to other queries while it runs, so it is scheduled, not run at peak. Virtual tables, such as FTS5 indexes, cannot be exported: drop them, export, recreate. Loading is a matter of running the file: `wrangler d1 execute <db> --remote --file=dump.sql` runs the file, up to 5 GB, with each statement capped at 100 KB, so a huge multi-row `INSERT` fails with `Statement too long`. A dump from the SQLite command-line tool needs its `BEGIN TRANSACTION` and `COMMIT` removed, and a raw `.sqlite3` file must be converted to SQL first.
code
bash · 5 lines# Schema and data of one table, from the deployed database
npx wrangler d1 export catalogue --remote --table=products --output=./products.sql
# Load it into another database: loading a file means executing it
npx wrangler d1 execute catalogue-staging --remote --file=./products.sqlgo deeper
Recall that D1 exports to a .sql file with wrangler d1 export and loads one with wrangler d1 execute --file, and that --remote targets the deployed database.
Explain the export flags, why a remote export blocks queries, the 5 GB file and 100 KB statement limits, and the edits a sqlite3 dump needs.
Schedule exports around their blocking effect, handle FTS5 tables and large integers, and pair exports with Time Travel so a restore never loses good rows.
Set the team's archive policy beyond Time Travel's window and decide how large a database may grow before exporting it becomes an outage.
## Why export and import matter D1 moves data in and out as **SQL text**. That covers several everyday jobs: - copying production data into the local database `wrangler dev` uses, for debugging; - keeping an archive beyond Time Travel's 30-day window; - moving an existing SQLite application onto D1; - moving one tenant's data from one D1 database to another. ## Exporting with wrangler d1 export `wrangler d1 export <database> --output=<file>` writes a `.sql` file. With `--remote` it reads the deployed database; without it, the local one under `.wrangler/state`. | Flag | Effect | |---|---| | `--output` | path of the `.sql` file (required, and must not be a directory) | | `--table` | export only the named table; repeat it for several | | `--no-data` | schema only | | `--no-schema` | data only (cannot be combined with `--no-data`) | | `-y`, `--skip-confirmation` | skip the warning prompt | Things to plan around: - **A running remote export blocks other requests to the database.** Wrangler warns that the database will be unavailable to serve queries for the duration, so a large export belongs in a quiet window. - **Virtual tables cannot be exported**, and neither can a database that contains one. D1 supports virtual tables for full-text search with SQLite's FTS5 module; the workaround is to drop them, export, and recreate them. - **Large integers can lose precision.** Numeric values pass through JavaScript numbers, so a 64-bit value can come back less precise than it was stored. ## Importing with wrangler d1 execute --file Loading a `.sql` file into D1 means running it with `wrangler d1 execute`; there is no separate import step: ```sh npx wrangler d1 execute catalogue --remote --file=./catalogue.sql ``` The limits that apply: 1. **File size**: at most 5 GB per file, because the file is uploaded through R2; split bigger imports into several files. 2. **Statement length**: at most 100 KB per SQL statement. A dump with one giant multi-row `INSERT` fails with `Statement too long`; split it into several smaller `INSERT` statements. 3. **Transactions**: the file must not open its own transaction. A dump from `sqlite3 db.sqlite3 .dump` contains `BEGIN TRANSACTION` and `COMMIT`; remove them, or the import fails with `cannot start a transaction within a transaction`. ## Bringing in an existing SQLite database A raw `.sqlite3` file cannot be imported directly. Convert it with the SQLite command-line tool, `sqlite3 db_dump.sqlite3 .dump > db.sql`, then edit the output: - remove `BEGIN TRANSACTION` and `COMMIT;`; - remove the `CREATE TABLE _cf_KV` statement if present; `_cf_KV` is a table reserved by D1's storage layer; - keep the schema SQLite-compatible: a dump from another database engine uses types and syntax D1 will not accept. ## Typical workflows 1. **Production data for local debugging**: `wrangler d1 export catalogue --remote --output=./prod.sql`, then `wrangler d1 execute catalogue --local --file=./prod.sql` loads it into the database `wrangler dev` reads. Treat that file as production data: it can hold personal information, so keep it out of the repository and delete it when done, or export with `--no-data` and seed test rows instead. 2. **Schema review**: `--no-data` produces the schema alone, handy for comparing two databases that should match after migrations. 3. **Moving a tenant**: export the tenant's tables with `--table`, run the file against the new database with `execute --remote --file`, then switch the application to it. ## Foreign keys and order Tables must be created before rows refer to them, so order matters when the dump is hand-assembled. When an import has to violate foreign keys temporarily, `PRAGMA defer_foreign_keys = true` defers the checks until the end of the transaction. ## Export versus Time Travel | Need | Use | |---|---| | Undo a bad change from the last 30 days | Time Travel restore | | Keep a copy longer than 30 days, or outside D1 | export | | Salvage some rows while rewinding the rest | export first, then restore | | Move data into another database | export, then `execute --file` | Export and import are the portable path; Time Travel is the fast in-place one. A team that relies on Time Travel alone has nothing older than 30 days and nothing outside D1, so the export schedule is the part of the backup plan it has to own.
- A nightly remote export of a large D1 database coincides with error spikes. Why?A running export blocks other requests to that database, and Wrangler warns it will be unavailable to serve queries until the export finishes. Move the job to the quietest window, export fewer tables at a time with `--table`, or split the data across smaller databases so each export is short.
- An import fails with Statement too long. What is wrong with the file?One SQL statement exceeds D1's 100 KB statement limit, usually a single `INSERT` carrying thousands of rows. Split it into several `INSERT` statements of a few hundred rows each. The 5 GB limit applies to the whole file, separately.
- wrangler d1 migrations apply is the way to load a data dump into D1.
- A remote export takes a snapshot, so traffic is unaffected while it runs.
- A raw .sqlite3 file can be uploaded to D1 as-is.
- A sqlite3 .dump file imports into D1 without any edits.
- Export copes with FTS5 virtual tables like any other table.