Drizzle ORM changes fields and migration fails

[LBC000]

Describe the bug Drizzle ORM changes fields and migration fails

Steps to reproduce Steps to reproduce the behavior:

1. Change schema.ts usersAdmin. add email field
2. Run npm run db:generate; "db:generate": "drizzle-kit generate"
3. Rerun: npx nuxt dev --remote
4. Database migrations failed [POST] "https://xxxxx.cloudflare-nuxthub-02.pages.dev/api/_hub/database/batch": 500

at process.processTicksAndRejections (node:internal/process/task_queues:95:5) at async $fetch2 (node_modules/.pnpm/[email protected]/node_modules/ofetch/dist/shared/ofetch.37386b05.mjs:268:15) at SQLiteD1Session.batch (node_modules/.pnpm/[email protected]_@[email protected]_@[email protected]/node_modules/src/d1/session.ts:82:24) at migrate (node_modules/.pnpm/[email protected]_@[email protected]_@[email protected]/node_modules/src/d1/migrator.ts:51:3) at (server/plugins/migrations.ts:8:1)

Expected behavior Can change fields without error, d1 changes synchronously

[atinux]

Do you have the error locally without the --remote @LBC000 ?

[LBC000]

Do you have the error locally without the @LBC000 ?--remote

The first time I ran npm run db:generate; "db:generate": "drizzle-kit generate". Then I started the project, and D1 generated the table correctly. This proves that the remote link is correct. But when I want to add a new field, an error is reported, as described above

[atinux]

Can you update the dependencies and confirm this is still happening?

[khawarizmus]

I am having a similar situation. I didin't have a problem until I added a foreign key to some tables to point to a newly created table.

This was the generated migration file:

CREATE TABLE `spaces` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`name` text NOT NULL,
	`user_id` integer NOT NULL,
	`created_at` integer NOT NULL,
	`updated_at` integer NOT NULL,
	FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
ALTER TABLE `cards` ADD `space_id` integer REFERENCES spaces(id);--> statement-breakpoint
ALTER TABLE `entries` ADD `space_id` integer REFERENCES spaces(id);--> statement-breakpoint
CREATE INDEX `name_idx` ON `spaces` (`name`);--> statement-breakpoint
/*
 SQLite does not support "Creating foreign key on existing column" out of the box, we do not generate automatic migration for that, so it has to be done manually
 Please refer to: https://www.techonthenet.com/sqlite/tables/alter_table.php
                  https://www.sqlite.org/lang_altertable.html

 Due to that we don't generate migration automatically and it has to be done manually
*/

When I run npm run dev in remote mode I get the following error:

 ERROR  Database migrations failed [POST] "https://redacted.nuxt.dev/api/_hub/database/batch": 500 

  at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
  at async $fetch2 (node_modules/ofetch/dist/shared/ofetch.37386b05.mjs:268:15)
  at SQLiteD1Session.batch (node_modules/src/d1/session.ts:82:24)
  at migrate (node_modules/src/d1/migrator.ts:51:3)
  at <anonymous> (server/plugins/migrations.ts:8:1)

Can you update the dependencies and confirm this is still happening?

@atinux Which dependencies exactly? because I am on the latest drizzle kit and orm.

[khawarizmus]

@atinux I just updated the rest of the deps and the problem still persists.

npx npm-check-updates -u

Upgrading my-project/package.json
[====================] 32/32 100%

 @nuxt/icon         ^1.4.9  →   ^1.4.10
 @nuxthub/core      ^0.7.3  →    ^0.7.5
 @vueuse/core      ^11.0.1  →   ^11.0.3
 lucide-vue-next  ^0.435.0  →  ^0.436.0
 radix-vue          ^1.9.4  →    ^1.9.5

Run npm install to install new versions.

I run npm install after that. and still got the same issue

[khawarizmus]

To "fix" this i had to delete my project from both Cloudflare and Nuxthub. re-import it into Nuxthub. delete my migrations folder and regenerate the migrations (it will result in one file). re-link the locale project with remote Nuxthub. run npm run dev in remote mode.

Now this is fine because i don't have any data in my database yet and i everything is in development right now. but i can't see this as a solution for a production app.

[atinux]

Thanks for investigating @khawarizmus

Please avoid pinging me multiple times, I read the notifications when I can.

This is a Drizzle issue regarding migration, there is an open issue on it, I recommend you to take a look at the comments there until it's fixed!

https://github.com/drizzle-team/drizzle-orm/issues/1813

[khawarizmus]

Apologies for the pinging you multiple times. And thank you for point us to the right Issue.

[atinux]

Closing in favour of https://hub.nuxt.com/changelog/database-migrations

[khawarizmus]

Does this mean that nuxt hub is the one doing the migration instead of drizzle?

[atinux]

The one applying the migrations yes!