How can I "tell" Drizzle if a migration is considered "done" when migrating from Prisma?

Question

I'm migrating my project from Prisma to Drizzle following the migration guide and I've bumped into a problem. Drizzle correctly generates a migration file that says:

-- Current sql file was generated after introspecting the database
-- If you want to run this migration please uncomment this code before executing migrations

my problem is that this will fail when I start my application:

await client.connect();
// This command run all migrations from the migrations folder and apply changes to the database
await migrate(db, {
    migrationsFolder: resolve(__dirname, "../src/drizzle"),
});

with:

error: unterminated /* comment at or near "/*
DO $$ BEGIN
 CREATE TYPE "public"."AccountProvider" AS ENUM('LOCAL', 'DISCORD');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
"

so right after the unterminated /* that comes with the script. If I uncomment this script then it fails with some other error.

It is clear that this migration shouldn't run at all, since it was generated from my existing schema (so no migration is necessary). Is there a way to mark a migration as "done"? I checked and Drizzle created a drizzle schema in my database with an empty __drizzle_migrations table.

Answer 1

As you found out drizzle uses the special __drizzle_migrations table to determine which migrations need to run. It has a hash and a created_at column. It seems the hash column is ignored when checking if a migration needs to run.

So to fix your issue, first run the migrations and have it fail. This will update the journal and create the drizzle migrations table in your DB.

Then, look for the when in the entries of the file meta/_journal.json in your migrations path. Copy the when value, it should be a timestamp like e.g. 1732893263399.

Then, connect to your database, find the table __drizzle_migrations and add an entry with that timestamp. You could do this with some tool or UI, or with an SQL query like:

INSERT INTO __drizzle_migrations (created_at,hash) VALUES (1732893263399,'manual');

This should make drizzle kit ignore the migration with this timestamp in the when filed when running the next time. We just put some dummy value for the hash, as it is a required field in that table.

Run your migration again with this manual change and it should work.

I found this in a GitHub discussion and it worked for me.