Import data

D1 allows you to import existing SQLite tables and their data directly, enabling you to migrate existing data into D1 quickly and easily. This can be useful when migrating applications to use Workers and D1, or when you want to prototype a schema locally before importing it to your D1 database(s).

​​ Import an existing database

To import an existing SQLite database into D1, you must have:

1. The Cloudflare Wrangler CLI installed.
2. A database to use as the target.
3. An existing SQLite (version 3.0+) database file to import.

For example, consider the following users_export.sql schema & values, which includes a CREATE TABLE IF NOT EXISTS statement:

With your users_export.sql file in the current working directory, you can pass the --file=users_export.sql flag to d1 execute to execute (import) our table schema and values:

To confirm your table was imported correctly and is queryable, execute a SELECT statement against your users table directly:

Note that we apply a LIMIT 100 clause here as a precaution: if you were importing a larger database with hundreds or thousands of rows, you may not want to output every row to the terminal.

From here, you can now query our new table from our Worker using the D1 client API.

​​ Convert SQLite database files

If you have an existing SQLite database from another system, you can import its tables into a D1 database. Using the sqlite command-line tool, you can convert an .sqlite3 file into a series of SQL statements that can be imported (executed) against a D1 database.

For example, if you have a raw SQLite dump called db_dump.sqlite3, run the following sqlite command to convert it:

Once you have run the above command, you will need to edit the output SQL file to be compatible with D1:

1. Remove BEGIN TRANSACTION and COMMIT; from the file
2. Remove the following table creation statement (if present):

You can then follow the steps to import an existing database into D1 by using the .sql file you generated from the database dump as the input to wrangler d1 execute.

​​ Foreign key constraints

When importing data, you may need to temporarily disable foreign key constraints. To do so, call PRAGMA defer_foreign_keys = true before making changes that would violate foreign keys.

Refer to the foreign key documentation to learn more about how to work with foreign keys and D1.

​​ Export an existing D1 database

In addition to importing existing SQLite databases, you might want to export a D1 database for local development or testing. You can export a D1 database to a .sql file using wrangler d1 export and then execute (import) with d1 execute --file.

To export full D1 database schema and data:

To export single table schema and data:

To export only D1 database schema:

To export only D1 table schema:

​​ Known limitations

• Export is not supported for virtual tables, including databases with virtual tables. D1 supports virtual tables for full-text search using SQLite’s FTS5 module . As a workaround, delete any virtual tables, export, and then recreate virtual tables.
• A running export will block other database requests.

​​ Troubleshooting

If you receive an error when trying to import an existing schema and/or dataset into D1:

• Ensure you are importing data in SQL format (typically with a .sql file extension). Refer to how to convert SQLite files if you have a .sqlite3 database dump.
• Make sure the schema is SQLite3 compatible. You cannot import data from a MySQL or PostgreSQL database into D1, as the types and SQL syntax are not directly compatible.
• If you have foreign key relationships between tables, ensure you are importing the tables in the right order. You cannot refer to a table that does not yet exist.
• If you receive a "cannot start a transaction within a transaction" error, make sure you have removed BEGIN TRANSACTION and COMMIT from your dumped SQL statements.

​​ Next Steps

• Read the SQLite CREATE TABLE documentation.
• Learn how to use the D1 client API from within a Worker.
• Understand how database migrations work with D1.