[BUG]: `expo-sqlite` migrator seems to apply only the first migration

arthur-fontaine commented 6 months ago

What version of `drizzle-orm` are you using?

0.30.1

What version of `drizzle-kit` are you using?

0.20.14

Describe the Bug

When there is multiple migrations, Drizzle seems to only apply the first one.

I have created a minimal reproducible example in this repo.

As I said in the README of the repo, I have tested two cases:

First one with two migrations. The first migration creates a table with a TEXT column id. The second migration changes the id column to INTEGER. The result is that the id is a string, which is the type of the first migration. The second migration is not applied.
Second one with only one migration. The migration creates a table with a TEXT column id. The result is that the id is a number, as expected.

That's why I think that the issue comes from the fact that there is multiple migrations and that Drizzle only applies the first one.

Expected behavior

I would expect that all migrations are applied.

Environment & setup

"babel-plugin-inline-import": "^3.0.0"
"expo": "~50.0.5"
"expo-sqlite": "~13.3.0"
"react": "18.2.0"
"react-native": "0.73.4"

RomanNabukhotnyi commented 6 months ago

Hey @arthur-fontaine! You were almost right. Drizzle only applied the first query from the second migration. To work correctly, you need to separate all the queries using --> statement-breakpoint as shown below:

CREATE TABLE `messages_temp` (
    `id` INTEGER PRIMARY KEY NOT NULL,
    `text` TEXT
);
--> statement-breakpoint
INSERT INTO `messages_temp` (`id`, `text`)
SELECT CAST(`id` AS INTEGER), `text`
FROM `messages`;
--> statement-breakpoint
DROP TABLE `messages`;
--> statement-breakpoint
ALTER TABLE `messages_temp` RENAME TO `messages`;

arthur-fontaine commented 6 months ago

Thanks for your response @RomanNabukhotnyi. I was not aware about this statement-breakpoint, so I guess it is not a bug as I said.

However, I find this is not very clear. When I generated the migration, Drizzle generated a file with this content:

/*
 SQLite does not support "Changing existing column type" 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
                  https://stackoverflow.com/questions/2083543/modify-a-columns-type-in-sqlite3

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

The message is very clear about why Drizzle cannot generate the migration automatically, but does not say how to write it manually. Especially, there is no mention to the need to use --> statement-breakpoint. The documentation mention it (not even directly) in configuration documentation (here and here), but my first reflex when I saw this message was far from searching in this part of the documentation.

Maybe a guide that explains the needs to write a correct manual migration would be nice?

The title and the label of the issue would need to be changed.

brettimus commented 2 weeks ago

Opened a PR to update the error message: https://github.com/drizzle-team/drizzle-orm/pull/2871

drizzle-team / drizzle-orm