[BUG]: SQL query to create table is missing "IF NOT EXISTS"

[paul-uz]

What version of drizzle-orm are you using?

0.26.0

What version of drizzle-kit are you using?

No response

Describe the Bug

When running generate to create the migration SQL files form the schemas, create table queries are missing "IF NOT EXISTS" in the query, resulting in errors when running the migration programmatically.

Expected behavior

Create table query should include "IF NOT EXISTS"

Environment & setup

No response

[AndriiSherman]

Could you please explain how you run migrations programmatically? Are you using the migrate function from drizzle-orm or have you written a custom script yourself (or are you using any other tool)?

[paul-uz]

Could you please explain how you run migrations programmatically? Are you using the migrate function from drizzle-orm or have you written a custom script yourself (or are you using any other tool)?

I'm using drizzle kit to create the migrations and yes, using the migrate function from drizzle orm.

I found the problem, it's literally not in the drizzle kit SQL builder. It's included for postgres tables but not mysql tables oddly.

[paul-uz]

Sorry, I think this should be in the drizzle kit repo.

I can make a PR for it, but I would like to understand why it's not included in the sql generation code?

[AndriiSherman]

Yeah, but the case is different here.

I mean, if you have an empty database and then run the migrations, there should be no errors, and that's a bug in the migrate function(if there is an error)

However, if you try to run migrations on a database where those tables already exist, but you run the migration first, you will see an error

[AndriiSherman]

It means you just need to skip this first migration. By skip I mean comment out the initial migration

We will include proper docs for this flow

[paul-uz]

I will confirm tomorrow when I'm at the computer to check what happens with an initial table creation, then a 2nd.

Again, why would postgres have IF NOT EXISTS included, but not mysql?

[janusqa]

I think having IF NOT EXISTS will be nice. Things will flow much more nicely. I am running into this issue using mysql, glad to hear that postgres has it already.

[paul-uz]

IMHO, it should be included in the MySQL migrations, as it's in the Postgres ones. I can't see any reason why it'd be in one and not the other.

[binaryartifex]

can confirm ive just bumped up against the exact same thing. last night i generated then migrated an initial few tables to a staging database in planetscale. this morning i added a few extra tables, it generated without an issue however on migration (straight from the docs) I got hit with already exists errors...

[limichange]

I got the same error when I ran the tutorial, and it appears that the migration code was run from scratch and duplicated the table creation.

[cxreiff]

Same for me I believe, I was following this guide: https://www.jacobparis.com/content/remix-drizzle

... and put migrate in the file where I export my DB client from, in order to run pending migrations on server startup. However my dev server now won't stand up because it tries to run the migrations and I get DatabaseError: Table 'rooms' already exists (errno 1050).

Pending this change being made, does anybody know of a better way to ensure migrations are run when the server starts, without throwing this error?

[DavidutzDev]

I have exactly the same problem too, but in addition I noticed that it also happens with Foreign Keys where I get the error: already exists. This has been fixed in the postgres implementation but not in mysql yet.

I hope this will be fixed fairly quickly, please let me know if you have found any temporary fixes. Thanks !

[FadiObaji]

@AndriiSherman This is not an enhancment request, it's clearly a code-breaking bug. Postgres and SQLite has it, but MySQL doesn't? It's not a DB specific feature, it is a Drizzle thing, so I don't see a reason for this to be left out like this! And this is making the migrations completely useless, we are always manually deleting the migrations and __drizzle_migrations table and re-doing everything all over again to overcome this thing...

[miguelrk]

Bumping this! I'm using SQLite and my migrations are missing IF NOT EXISTS, which breaks my setup since executing these migrations on existing DBs throws errors each time... I also think this should be default behavior for all database types...

Is there any news on this?

[Syth-1]

+1 for SQLite, migrating my project from raw SQL to drizzle, so i already have an existing table, hopefully this gets added officially, for now had to manually edit the generated migrations file

[pascalhuszar]

+1 for SQLite