Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

🐛 BUG: Exported SQL Fails to Import Due to Missing Column Names in INSERT Statements #7507

Open
nmemoto opened this issue Dec 10, 2024 · 0 comments
Labels
bug Something that isn't working

Comments

@nmemoto
Copy link

nmemoto commented Dec 10, 2024

Which Cloudflare product(s) does this pertain to?

D1

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

3.94.0[wrangler]

What version of Node are you using?

20.16.0

What operating system and version are you using?

macOS 15.1.1

Describe the Bug

Observed behavior

I encountered issues when trying to export and re-import data during iterative development. The INSERT statements generated during the export process did not include column names. This caused problems because the column creation order in the tables during development differed from the order in the finalized table definitions.

As a result, the exported SQL file failed to correctly import the data into the updated database schema.

Expected behavior

The exported SQL file should include column names in the INSERT statements to ensure compatibility when importing data, even if the column order differs between the development and finalized table definitions.

Steps to reproduce

Use drizzle-orm with the following migration files:

ls -al migrations/
meta
0000_init.sql
0001_~
0002_~
・・・

Export data from the database:

for table in $(echo "artists" "artist_timelines" "albums" "album_timelines" "tracks" )
do
  npx wrangler d1 export spotify-db --table=$table --output ./data/$table.sql --no-schema --local
done

Delete the database and recreate it:

rm -rf migrations/*
npx drizzle-kit generate --name=init
npx wrangler d1 migrations apply spotify-db --local

Attempt to re-import the exported data:

for table in $(echo "artists" "artist_timelines" "albums" "album_timelines" "tracks" )
do
  npx wrangler d1 execute spotify-db --file=./data/$table.sql --local
done

During development, I frequently modify the table structure by adding and removing columns using ALTER statements. Once the table structure is finalized, I consolidate these changes into a single CREATE TABLE statement.

To preserve the data generated during application testing, I exported the data and attempted to re-import it into the finalized schema. However, because the exported SQL file lacked column names in the INSERT statements and the column order differed, the import failed.

Please provide a link to a minimal reproduction

yield `INSERT INTO ${sqliteQuote(table)} VALUES(${formattedCells.join(",")});`;

Please provide any relevant error logs

No response

@nmemoto nmemoto added the bug Something that isn't working label Dec 10, 2024
@github-project-automation github-project-automation bot moved this to Untriaged in workers-sdk Dec 10, 2024
nmemoto added a commit to nmemoto/workers-sdk that referenced this issue Dec 10, 2024
nmemoto added a commit to nmemoto/workers-sdk that referenced this issue Dec 10, 2024
@CarmenPopoviciu CarmenPopoviciu moved this from Untriaged to In Progress in workers-sdk Dec 12, 2024
@CarmenPopoviciu CarmenPopoviciu moved this from In Progress to Untriaged in workers-sdk Dec 12, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something that isn't working
Projects
None yet
Development

Successfully merging a pull request may close this issue.

1 participant