Importing 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:

CREATE TABLE IF NOT EXISTS users (
	id VARCHAR(50),
	full_name VARCHAR(50),
	created_on DATE
);
insert into users (id, full_name, created_on) values ('01GREFXCN9519NRVXWTPG0V0BF', 'Catlaina Harbar', '2022-08-20 05:39:52');
insert into users (id, full_name, created_on) values ('01GREFXCNBYBGX2GC6ZGY9FMP4', 'Hube Bilverstone', '2022-12-15 21:56:13');
insert into users (id, full_name, created_on) values ('01GREFXCNCWAJWRQWC2863MYW4', 'Christin Moss', '2022-07-28 04:13:37');
insert into users (id, full_name, created_on) values ('01GREFXCNDGQNBQAJG1AP0TYXZ', 'Vlad Koche', '2022-11-29 17:40:57');
insert into users (id, full_name, created_on) values ('01GREFXCNF67KV7FPPSEJVJMEW', 'Riane Zamora', '2022-12-24 06:49:04');

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:

$ wrangler d1 execute example-db --file=users_export.sql

🌀 Mapping SQL input into an array of statements
🌀 Parsing 1 statements
🌀 Executing on example-db (c7fe3a2a-9973-4231-85ff-edd63d7a4e6d):
🚣 Executed 1 command in 61.64555000513792ms

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

$ wrangler d1 execute example-db --command "SELECT * FROM users LIMIT 100;"

🌀 Mapping SQL input into an array of statements
🌀 Parsing 1 statements
🌀 Executing on example-db (63571930-b2f1-4bdb-bffa-a7db6ee04c5d):
🚣 Executed 1 command in 102.00563299655914ms
┌────────────────────────────┬──────────────────┬─────────────────────┐
│ id                         │ full_name        │ created_on          │
├────────────────────────────┼──────────────────┼─────────────────────┤
│ 01GREFXCN9519NRVXWTPG0V0BF │ Catlaina Harbar  │ 2022-08-20 05:39:52 │
├────────────────────────────┼──────────────────┼─────────────────────┤
│ 01GREFXCNBYBGX2GC6ZGY9FMP4 │ Hube Bilverstone │ 2022-12-15 21:56:13 │
├────────────────────────────┼──────────────────┼─────────────────────┤
│ 01GREFXCNCWAJWRQWC2863MYW4 │ Christin Moss    │ 2022-07-28 04:13:37 │
├────────────────────────────┼──────────────────┼─────────────────────┤
│ 01GREFXCNDGQNBQAJG1AP0TYXZ │ Vlad Koche       │ 2022-11-29 17:40:57 │
├────────────────────────────┼──────────────────┼─────────────────────┤
│ 01GREFXCNF67KV7FPPSEJVJMEW │ Riane Zamora     │ 2022-12-24 06:49:04 │
└────────────────────────────┴──────────────────┴─────────────────────┘

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.

​​ Converting 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:

$ sqlite3 db_dump.sqlite3 .dump > db.sql

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.

​​ 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). See how to convert SQLite files if you have a .sqlite3 database dump.
• Make sure the schema is SQLite3 Open external link 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 can’t refer to a table that doesn’t yet exist.
• If you get "cannot start a transaction within a transaction", make sure you have removed BEGIN TRANSACTION and COMMIT from your dumped SQL statements.

​​ Next Steps

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