DT
Drizzle Team•4mo ago
addamsson

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
addamsson
addamsson•4mo 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
addamsson
addamsson•4mo ago
addamsson
addamsson•4mo ago
it looks like that the driver itself is buggy
addamsson
addamsson•4mo ago
No description
addamsson
addamsson•4mo 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/pg-protocol@1.6.0/node_modules/pg-protocol/src/parser.ts:369:69
 ❯ Parser.handlePacket ../node_modules/.pnpm/pg-protocol@1.6.0/node_modules/pg-protocol/src/parser.ts:188:21
 ❯ Parser.parse ../node_modules/.pnpm/pg-protocol@1.6.0/node_modules/pg-protocol/src/parser.ts:103:30
 ❯ Socket.<anonymous> ../node_modules/.pnpm/pg-protocol@1.6.0/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/pg-protocol@1.6.0/node_modules/pg-protocol/src/parser.ts:369:69
 ❯ Parser.handlePacket ../node_modules/.pnpm/pg-protocol@1.6.0/node_modules/pg-protocol/src/parser.ts:188:21
 ❯ Parser.parse ../node_modules/.pnpm/pg-protocol@1.6.0/node_modules/pg-protocol/src/parser.ts:103:30
 ❯ Socket.<anonymous> ../node_modules/.pnpm/pg-protocol@1.6.0/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
Andrii Sherman•4mo 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
addamsson
addamsson•4mo 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 🐧🪺
bc 🐧🪺•3mo ago
Ran into same issue -- thanks @addamsson for your notes here helped me understand!
addamsson
addamsson•3mo ago
FYI I ended up not migrating to Drizzle after facing numerous problems and drawbacks
bc 🐧🪺
bc 🐧🪺•3mo 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 😅
addamsson
addamsson•3mo 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 🐧🪺
bc 🐧🪺•3mo ago
Sweet! Thanks for that
Want results from more Discord servers?
Add your server