[BUG] Initial migration should use IF NOT EXISTS

[jschuur]

The initial migration file generated with drizzle-kit generate for an existing project seemed to use a CREATE TABLE table_name (..) format, rather than CREATE TABLE IF NOT EXISTS table_name (...).

At least this was the case for me for a turso/sqlite driver/dialect config and drizzle-kit: v0.21.2.

drizzle-kit introspect also did not add IF NOT EXISTS.

Shouldn't IF NOT EXISTS be use here, so that it can gracefully skip existing tables? The same should apply to index generating statements.

[migsdeving]

Same problem here, what are the necessary steps you need to take if you want to start using migrations after having used push for a while ? I tried uncommenting, that sql statement and running the migration, deleting it, generating a migration and running it (fails because it looks for that previous sql file I deleted)

[jschuur]

Same problem here, what are the necessary steps you need to take if you want to start using migrations after having used push for a while ?

If I recall correctly, I ended up manually adding IF NOT EXISTS to all the CREATE INDEX an CREATE TABLE statements.

So...

CREATE TABLE IF NOT EXISTS `user` ( ...schema here... );
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS `Account_userId_index` ON `account` (`userId`);--> statement-breakpoint

This avoids an error for existing database tables but still sets it up for new instances.

The --> statement-breakpoint bits are really just there for Drizzle to know how to split up the SQL statements when you run drizzle-kit migrate (see here), so they wouldn't run directly in a SQL client.

But yeah... this is clearly not ideal and will confuse people :)

[migsdeving]

Same problem here, what are the necessary steps you need to take if you want to start using migrations after having used push for a while ?

If I recall correctly, I ended up manually adding IF NOT EXISTS to all the CREATE INDEX an CREATE TABLE statements.

So...

CREATE TABLE IF NOT EXISTS `user` ( ...schema here... );
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS `Account_userId_index` ON `account` (`userId`);--> statement-breakpoint

This avoids an error for existing database tables but still sets it up for new instances.

The --> statement-breakpoint bits are really just there for Drizzle to know how to split up the SQL statements when you run drizzle-kit migrate (see here), so they wouldn't run directly in a SQL client.

But yeah... this is clearly not ideal and will confuse people :)

Yeah if I add IF NOT EXISTS I'm sure it will work, but I'm wondering what the reason is for drizzle not adding it automatically ? Also seems to be a bit of confusion on how to work with introspect and generate. If I introspect first and then generate, drizzle correctly generates the migration file only for the new tables, but when I migrate, it tries to run the instrospect generated file, and not the new one + doesn't prompt you on which one you want to run and ends up failing because the instrospect migration file is commented

[jschuur]

There's a hint in a tweet from yesterday by @AndriiSherman to 'a big change to the generate+migrate flow'.

He just replied 'yes' that it will address the stuff mentioned here.

[migsdeving]

There's a hint in a tweet from yesterday by @AndriiSherman to 'a big change to the generate+migrate flow'.

He just replied 'yes' that it will address the stuff mentioned here.

Well it does need a significant refactor. Hopefully it will be coming soon though