Drizzle Team•6mo ago

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

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

-- 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"),
});

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 $$;
"

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:

error: column "providerid" does not exist

error: column "providerid" does not exist

I think 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 and it has an empty __drizzle_migrations table.

12 Replies

addamssonOP•6mo ago

Update: it seems that the migrator is buggy if I run the generated migration against my database outside of Drizzle it works but if I run it with drizzle I get errors I narroved it down to this:

-- these work
CREATE INDEX IF NOT EXISTS "User_email_idx" ON "User" USING btree (email text_ops);--> statement-breakpoint
CREATE UNIQUE INDEX IF NOT EXISTS "User_email_key" ON "User" USING btree (email text_ops);--> statement-breakpoint
CREATE UNIQUE INDEX IF NOT EXISTS "Organization_name_key" ON "Organization" USING btree (name text_ops);--> statement-breakpoint
CREATE UNIQUE INDEX IF NOT EXISTS "Module_name_version_key" ON "Module" USING btree (name text_ops,version text_ops);--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "Module_version_idx" ON "Module" USING btree (version text_ops);--> statement-breakpoint

-- these all fail
CREATE UNIQUE INDEX IF NOT EXISTS "Server_providerId_key" ON "Server" USING btree (providerId text_ops);--> statement-breakpoint
CREATE UNIQUE INDEX IF NOT EXISTS "Channel_providerId_key" ON "Channel" USING btree (providerId text_ops);--> statement-breakpoint
CREATE UNIQUE INDEX IF NOT EXISTS "Team_name_orgId_key" ON "Team" USING btree (name text_ops,orgId text_ops);--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "Configuration_level_idx" ON "Configuration" USING btree (level enum_ops);--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "Configuration_namespace_key_level_idx" ON "Configuration" USING btree (namespace text_ops,key enum_ops,level text_ops);--> statement-breakpoint
CREATE UNIQUE INDEX IF NOT EXISTS "Configuration_namespace_key_relationId_key" ON "Configuration" USING btree (namespace text_ops,key text_ops,relationId text_ops);--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "Configuration_relationId_idx" ON "Configuration" USING btree (relationId text_ops);--> statement-breakpoint
CREATE UNIQUE INDEX IF NOT EXISTS "DailySyncChannelConnection_channelId_orgId_teamId_key" ON "DailySyncChannelConnection" USING btree (channelId text_ops,orgId text_ops,teamId text_ops);--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "DailySyncResponse_createdAt_idx" ON "DailySyncResponse" USING btree (createdAt timestamp_ops);--> statement-breakpoint
CREATE UNIQUE INDEX IF NOT EXISTS "DailySyncResponse_createdAt_key" ON "DailySyncResponse" USING btree (createdAt timestamp_ops);--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "DailySyncResponse_userId_idx" ON "DailySyncResponse" USING btree (userId text_ops);--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "Job_correlationId_idx" ON "Job" USING btree (correlationId text_ops);--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "Job_scheduledAt_idx" ON "Job" USING btree (scheduledAt timestamp_ops);--> statement-breakpoint
CREATE UNIQUE INDEX IF NOT EXISTS "Account_providerId_key" ON "Account" USING btree (providerId text_ops);

-- these work
CREATE INDEX IF NOT EXISTS "User_email_idx" ON "User" USING btree (email text_ops);--> statement-breakpoint
CREATE UNIQUE INDEX IF NOT EXISTS "User_email_key" ON "User" USING btree (email text_ops);--> statement-breakpoint
CREATE UNIQUE INDEX IF NOT EXISTS "Organization_name_key" ON "Organization" USING btree (name text_ops);--> statement-breakpoint
CREATE UNIQUE INDEX IF NOT EXISTS "Module_name_version_key" ON "Module" USING btree (name text_ops,version text_ops);--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "Module_version_idx" ON "Module" USING btree (version text_ops);--> statement-breakpoint

-- these all fail
CREATE UNIQUE INDEX IF NOT EXISTS "Server_providerId_key" ON "Server" USING btree (providerId text_ops);--> statement-breakpoint
CREATE UNIQUE INDEX IF NOT EXISTS "Channel_providerId_key" ON "Channel" USING btree (providerId text_ops);--> statement-breakpoint
CREATE UNIQUE INDEX IF NOT EXISTS "Team_name_orgId_key" ON "Team" USING btree (name text_ops,orgId text_ops);--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "Configuration_level_idx" ON "Configuration" USING btree (level enum_ops);--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "Configuration_namespace_key_level_idx" ON "Configuration" USING btree (namespace text_ops,key enum_ops,level text_ops);--> statement-breakpoint
CREATE UNIQUE INDEX IF NOT EXISTS "Configuration_namespace_key_relationId_key" ON "Configuration" USING btree (namespace text_ops,key text_ops,relationId text_ops);--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "Configuration_relationId_idx" ON "Configuration" USING btree (relationId text_ops);--> statement-breakpoint
CREATE UNIQUE INDEX IF NOT EXISTS "DailySyncChannelConnection_channelId_orgId_teamId_key" ON "DailySyncChannelConnection" USING btree (channelId text_ops,orgId text_ops,teamId text_ops);--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "DailySyncResponse_createdAt_idx" ON "DailySyncResponse" USING btree (createdAt timestamp_ops);--> statement-breakpoint
CREATE UNIQUE INDEX IF NOT EXISTS "DailySyncResponse_createdAt_key" ON "DailySyncResponse" USING btree (createdAt timestamp_ops);--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "DailySyncResponse_userId_idx" ON "DailySyncResponse" USING btree (userId text_ops);--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "Job_correlationId_idx" ON "Job" USING btree (correlationId text_ops);--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "Job_scheduledAt_idx" ON "Job" USING btree (scheduledAt timestamp_ops);--> statement-breakpoint
CREATE UNIQUE INDEX IF NOT EXISTS "Account_providerId_key" ON "Account" USING btree (providerId text_ops);

the problem is that drizzle converts these to lower case so instead of providerId it looks for providerid @Andrew Sherman ☝️ I debugged this

addamssonOP•6mo ago

@Andrew Sherman can you eyeball this? https://github.com/adam-arold/drizzle-orm/commit/2bb3b873f6d18b0c67db30fe97ca4e899946ffa7

GitHub

example · adam-arold/drizzle-orm@2bb3b87

addamssonOP•6mo ago

it looks like that the driver itself is buggy

addamssonOP•6mo ago

addamssonOP•6mo ago

if you run the test i added and set a breakpoint here you'll see that the query is valid but the client returns an error:

error: column "providerid" does not exist
 ❯ Parser.parseErrorMessage ../node_modules/.pnpm/[email protected]/node_modules/pg-protocol/src/parser.ts:369:69
 ❯ Parser.handlePacket ../node_modules/.pnpm/[email protected]/node_modules/pg-protocol/src/parser.ts:188:21
 ❯ Parser.parse ../node_modules/.pnpm/[email protected]/node_modules/pg-protocol/src/parser.ts:103:30
 ❯ Socket.<anonymous> ../node_modules/.pnpm/[email protected]/node_modules/pg-protocol/src/index.ts:7:48
 ❯ Socket.emit node:events:518:28

⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
Serialized Error: {
  "code": "42703",
  "column": undefined,
  "constraint": undefined,
  "dataType": undefined,
  "detail": undefined,
  "file": "indexcmds.c",
  "hint": undefined,
  "internalPosition": undefined,
  "internalQuery": undefined,
  "length": 100,
  "line": "1884",
  "position": undefined,
  "routine": "ComputeIndexAttrs",
  "schema": undefined,
  "severity": "ERROR",
  "table": undefined,
  "where": undefined,
}

error: column "providerid" does not exist
 ❯ Parser.parseErrorMessage ../node_modules/.pnpm/[email protected]/node_modules/pg-protocol/src/parser.ts:369:69
 ❯ Parser.handlePacket ../node_modules/.pnpm/[email protected]/node_modules/pg-protocol/src/parser.ts:188:21
 ❯ Parser.parse ../node_modules/.pnpm/[email protected]/node_modules/pg-protocol/src/parser.ts:103:30
 ❯ Socket.<anonymous> ../node_modules/.pnpm/[email protected]/node_modules/pg-protocol/src/index.ts:7:48
 ❯ Socket.emit node:events:518:28

⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
Serialized Error: {
  "code": "42703",
  "column": undefined,
  "constraint": undefined,
  "dataType": undefined,
  "detail": undefined,
  "file": "indexcmds.c",
  "hint": undefined,
  "internalPosition": undefined,
  "internalQuery": undefined,
  "length": 100,
  "line": "1884",
  "position": undefined,
  "routine": "ComputeIndexAttrs",
  "schema": undefined,
  "severity": "ERROR",
  "table": undefined,
  "where": undefined,
}

so somewhere along the line providerId is turned into providerid i just tried it with postgres.js and to my surprise I got the same error

import postgres from 'postgres';
import { drizzle } from 'drizzle-orm/postgres-js';
import { migrate } from 'drizzle-orm/postgres-js/migrator';
import { describe, test } from "vitest";
import * as schema from "../src/drizzle/schema";
import "dotenv/config";
import { resolve } from "node:path";

const migrationClient = postgres("postgresql://x:x@localhost:5433/x", { max: 1 });

const db = drizzle(queryClient);

describe("Given a Drizzle", () => {
    test("When", async () => {
        await migrate(drizzle(migrationClient), {
            migrationsFolder: resolve(__dirname, "../src/drizzle"),
        })
    });
});

import postgres from 'postgres';
import { drizzle } from 'drizzle-orm/postgres-js';
import { migrate } from 'drizzle-orm/postgres-js/migrator';
import { describe, test } from "vitest";
import * as schema from "../src/drizzle/schema";
import "dotenv/config";
import { resolve } from "node:path";

const migrationClient = postgres("postgresql://x:x@localhost:5433/x", { max: 1 });

const db = drizzle(queryClient);

describe("Given a Drizzle", () => {
    test("When", async () => {
        await migrate(drizzle(migrationClient), {
            migrationsFolder: resolve(__dirname, "../src/drizzle"),
        })
    });
});

Andrii Sherman•6mo ago

it's not migrator error, just migration was generated without "" I'll patch update drizzle-kit to generate migration properly

CREATE UNIQUE INDEX IF NOT EXISTS "Server_providerId_key" ON "Server" USING btree ("providerId" text_ops);

CREATE UNIQUE INDEX IF NOT EXISTS "Server_providerId_key" ON "Server" USING btree ("providerId" text_ops);

to not block you , you would need to add "" around providerId inside migration script generated by drizzle I'll try to patch update now

addamssonOP•6mo ago

oh. there is a relevant issue on GitHub too interestingly enough if i just copy the sql into dbeaver it executes without an error is there a fix for the schemaless enum problem? when i generate a migration from my current db schema it hardcodes public into all enum declarations which leads to failures ping?

bc 🐧🪺•6mo ago

Ran into same issue -- thanks @addamsson for your notes here helped me understand!

addamssonOP•6mo ago

FYI I ended up not migrating to Drizzle after facing numerous problems and drawbacks

bc 🐧🪺•6mo ago

I feel that, thanks for letting me know. What did you end up settling on? ORM/query builder/raw SQL/... ive tried sequelize, prisma, knex, drizzle and a couple others in the TS ecosystem (not to mention other lang ecosystems) and still trying to find my place 😅

addamssonOP•6mo ago

ill keep using prisma for the time being once the performance becomes a huge issue I'll migrate to kysely. it was super fast to set up, generated the schema from my prisma file without issues and worked like a charm out of the box

bc 🐧🪺•5mo ago

Sweet! Thanks for that

Gaming

Programming

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