🐛 BUG: `d1 export` produces unimportable sql files

[hrueger]

Which Cloudflare product(s) does this pertain to?

D1

What version(s) of the tool(s) are you using?

3.51.2 [Wrangler]

What version of Node are you using?

21.6.10

What operating system and version are you using?

macOS Sonoma 14.4.1

Describe the Bug

Observed behavior

When exporting a D1 database with some relations using npx wrangler d1 export, it can generate an SQL file that cannot be imported again (✘ [ERROR] no such table: main.B). This is due to INSERTs happening before all tables are created. I've prepared a minimal reproduction repository: https://github.com/hrueger/d1-export-order

Expected behavior

All CREATE TABLE queries happen first, all INSERTs afterwards.

Steps to reproduce

See https://github.com/hrueger/d1-export-order

in short, you need two tables, where the first table has a foreign key to the second table. Insert one row each, export that and try to import it again using npx wrangler execute ... --file ...

In the minimal reproduction there's also a fixed sql file, where the INSERT is just moved to the bottom.

Please provide a link to a minimal reproduction

https://github.com/hrueger/d1-export-order

Please provide any relevant error logs

✘ [ERROR] no such table: main.B

As per our discord conversation on friday, I'm tagging @geelen. Thanks for looking into this!

[hrueger]

For anyone needing a temporary workaround:

const tempPath = "db-temp.sql";
let fileContent = fs.readFileSync("export.sql").toString();
// move every create table query to top of file (CREATE TABLE ... ;)
const createTableRegex = /CREATE TABLE [^;]+;/g;
const createTableQueries = fileContent.match(createTableRegex);
if (createTableQueries) {
  for (const query of createTableQueries) {
    fileContent = fileContent.replace(query, "");
    fileContent = query + "\n" + fileContent;
  }
}
fs.writeFileSync(tempPath, fileContent);
console.log("Importing database");
childProcess.execSync(`npx wrangler d1 execute --config packages/root/wrangler.toml --persist-to wrangler-dev-data --e preview --local --file "${tempPath}" dts-prev-eu-west-1-main`, { stdio: "inherit" });
fs.rmSync(tempPath);

[furSUDO]

@hrueger you are a life saver