Drizzle Team•7mo ago

drizzle-kit push inconsistent with database with migrations already applied

Hi team Just wanting to sanity check something here to make sure that I am not barking up the wrong tree...From my understanding, the drizzle-kit push command should look at changes that I have made and push these up to the database before I make a migration to commit that change for good? Anyway, here's my issue... So, I've got a pg_enum defined:

enum_schema    enum_name    enum_value
public            gate_types    approval
public            gate_types    manual
public            gate_types    post_deployment
public            gate_types    preparation
public            gate_types    approval_gate

enum_schema    enum_name    enum_value
public            gate_types    approval
public            gate_types    manual
public            gate_types    post_deployment
public            gate_types    preparation
public            gate_types    approval_gate

These were generated over time using migration's and I've recently upgraded to the new Drizzle Kit version (v0.21.4) (ORM: v0.30.10) If I run the drizzle-kit generate command I get: No schema changes, nothing to migrate 😴 But then, when I run drizzle-kit push:

[✓] Pulling schema from database...

[✓] Pulling schema from database...
 Warning  You are about to execute current statements:

ALTER TYPE "gate_types" ADD VALUE 'preparation';


PostgresError: enum label "preparation" already exists
    at [redacted] {
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '42710',
  file: 'pg_enum.c',
  line: '293',
  routine: 'AddEnumLabel'
}

[✓] Pulling schema from database...

[✓] Pulling schema from database...
 Warning  You are about to execute current statements:

ALTER TYPE "gate_types" ADD VALUE 'preparation';


PostgresError: enum label "preparation" already exists
    at [redacted] {
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '42710',
  file: 'pg_enum.c',
  line: '293',
  routine: 'AddEnumLabel'
}

I've also removed some logs from the above around dropping/adding FK's - but appears unrelated to this inconsistency

40 Replies

CalOP•7mo ago

Just re-tested this and it's still an issue on the latest version. Any advice folks?

Deepso•7mo ago

facing same issue kit is not updating an existing enum as well as not creating new enum

CalOP•7mo ago

hey @Andrew Sherman (really sorry if I shouldn't ping!) just wondering if you could advise here?

Andrii Sherman•7mo ago

👀 so that's a bug and I think it's a regression @alexblokh is working on fixing all such issues he can take it, so we can release it tomorrow we already have a few things fixed and ready to be released in 0.21.5

CalOP•7mo ago

Hi Andrew, nice one, thanks! if you need me to file a proper GitHub issue or similar (or provide any help to test!) let me know 🙂

Andrii Sherman•7mo ago

we are fine with this thread for now I guess a drizzle schema code would be helpful it can be even a part of it just so we can reproduce this issue and add a proper test cases for it

CalOP•7mo ago

alright, let's give this a go...

export const ApprovalTypes = [
  'preparation',
  'post_deployment',
  'approval_gate',
] as const;

export const approval_types = pgEnum('gate_types', ApprovalTypes);

export const ApprovalTypes = [
  'preparation',
  'post_deployment',
  'approval_gate',
] as const;

export const approval_types = pgEnum('gate_types', ApprovalTypes);

export const approvals = pgTable('approvals', {
  id: identifierColumn(),
  releaseStepId: varchar('release_step_id')
    .references(() => releaseSteps.id, { onDelete: 'cascade' })
    .notNull(),
  type: approval_types('type').notNull(),
  approved: boolean('approved').notNull().default(false),
  comments: text('comments'),
  member_id: varchar('member_id')
    .references(() => members.id, {
      onDelete: 'cascade',
    })
    .notNull(),
  ...TIME_COLUMNS,
});

export const approvals = pgTable('approvals', {
  id: identifierColumn(),
  releaseStepId: varchar('release_step_id')
    .references(() => releaseSteps.id, { onDelete: 'cascade' })
    .notNull(),
  type: approval_types('type').notNull(),
  approved: boolean('approved').notNull().default(false),
  comments: text('comments'),
  member_id: varchar('member_id')
    .references(() => members.id, {
      onDelete: 'cascade',
    })
    .notNull(),
  ...TIME_COLUMNS,
});

const TIME_COLUMNS = {
  createdAt: timestamp('created_at').defaultNow().notNull(),
  modifiedAt: timestamp('modified_at').defaultNow().notNull(),
};

const TIME_COLUMNS = {
  createdAt: timestamp('created_at').defaultNow().notNull(),
  modifiedAt: timestamp('modified_at').defaultNow().notNull(),
};

to note here, this table did start off with a different name in the migrations, and the values in the enum have been added and removed over time (as you can see the original message) let me know if there is anything more i can provide here - unsure if this in isolation will trigger/cause the same issues, so if you need my migration files and full schema, happy to send that over email/discord dm's if needed - just not wanting to expose my full db structure in public

CalOP•7mo ago

https://github.com/drizzle-team/drizzle-orm/issues/2389 looks like this has been reported on the GitHub

GitHub

[BUG]: drizzle-kit push is not detecting Postgres enum label alread...

What version of drizzle-orm are you using? 0.30.9 What version of drizzle-kit are you using? 0.21.2 Describe the Bug This bug happens every so often. Is there a work around? pnpm drizzle-kit push d...

CalOP•7mo ago

hey @Andrii Sherman, just to let you know the latest update unfortunately has not resolved this issue

drizzle-kit: v0.22.1
drizzle-orm: v0.31.0

No config path provided, using default path
Reading config file '/Users/xx/drizzle.config.ts'
Using 'postgres' driver for database querying
[✓] Pulling schema from database...PostgresError: enum label "preparation" already exists
    at ErrorResponse (/Users/xx/node_modules/drizzle-kit/bin.cjs:79673:27)
    at handle (/Users/xx/node_modules/drizzle-kit/bin.cjs:79450:7)
    at TLSSocket.data (/Users/xx/node_modules/drizzle-kit/bin.cjs:79273:9)
    at TLSSocket.emit (node:events:517:28)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at TLSWrap.onStreamRead (node:internal/stream_base_commons:190:23)
    at TLSWrap.callbackTrampoline (node:internal/async_hooks:128:17) {
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '42710',
  file: 'pg_enum.c',
  line: '293',
  routine: 'AddEnumLabel'
}

drizzle-kit: v0.22.1
drizzle-orm: v0.31.0

No config path provided, using default path
Reading config file '/Users/xx/drizzle.config.ts'
Using 'postgres' driver for database querying
[✓] Pulling schema from database...PostgresError: enum label "preparation" already exists
    at ErrorResponse (/Users/xx/node_modules/drizzle-kit/bin.cjs:79673:27)
    at handle (/Users/xx/node_modules/drizzle-kit/bin.cjs:79450:7)
    at TLSSocket.data (/Users/xx/node_modules/drizzle-kit/bin.cjs:79273:9)
    at TLSSocket.emit (node:events:517:28)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at TLSWrap.onStreamRead (node:internal/stream_base_commons:190:23)
    at TLSWrap.callbackTrampoline (node:internal/async_hooks:128:17) {
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '42710',
  file: 'pg_enum.c',
  line: '293',
  routine: 'AddEnumLabel'
}

benemma•7mo ago

I agree we have the same issue with push. It's making our transition from Prisma to Drizzle feel ill-advised.

CalOP•6mo ago

Hi @Andrew Sherman! Hope you’re well, just wondering if we expect the latest updates to have resolved this regression? Let me know if I can help push this forward

Andrii Sherman•6mo ago

let me ping @alexblokh, he was working on fixing kit regressions can you try with latest drizzle-kit?

CalOP•6mo ago

give me 2 mins

Andrii Sherman•6mo ago

we are trying to reproduce, but seeing no diffs in push

CalOP•6mo ago

Same issue;

yarn drizzle-kit push
drizzle-kit: v0.22.4
drizzle-orm: v0.31.1

No config path provided, using default path
Reading config file '/Users/XXX/drizzle.config.ts'
Using 'postgres' driver for database querying
[✓] Pulling schema from database...
 Warning  You are about to execute current statements:

ALTER TYPE "gate_types" ADD VALUE 'preparation';

PostgresError: enum label "preparation" already exists
    at ErrorResponse (/Users/XXX/node_modules/drizzle-kit/bin.cjs:79675:27)
    at handle (/Users/XXX/node_modules/drizzle-kit/bin.cjs:79452:7)
    at TLSSocket.data (/Users/XXX/node_modules/drizzle-kit/bin.cjs:79275:9)
    at TLSSocket.emit (node:events:517:28)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at TLSWrap.onStreamRead (node:internal/stream_base_commons:190:23)
    at TLSWrap.callbackTrampoline (node:internal/async_hooks:128:17) {
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '42710',
  file: 'pg_enum.c',
  line: '293',
  routine: 'AddEnumLabel'
}

yarn drizzle-kit push
drizzle-kit: v0.22.4
drizzle-orm: v0.31.1

No config path provided, using default path
Reading config file '/Users/XXX/drizzle.config.ts'
Using 'postgres' driver for database querying
[✓] Pulling schema from database...
 Warning  You are about to execute current statements:

ALTER TYPE "gate_types" ADD VALUE 'preparation';

PostgresError: enum label "preparation" already exists
    at ErrorResponse (/Users/XXX/node_modules/drizzle-kit/bin.cjs:79675:27)
    at handle (/Users/XXX/node_modules/drizzle-kit/bin.cjs:79452:7)
    at TLSSocket.data (/Users/XXX/node_modules/drizzle-kit/bin.cjs:79275:9)
    at TLSSocket.emit (node:events:517:28)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at TLSWrap.onStreamRead (node:internal/stream_base_commons:190:23)
    at TLSWrap.callbackTrampoline (node:internal/async_hooks:128:17) {
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '42710',
  file: 'pg_enum.c',
  line: '293',
  routine: 'AddEnumLabel'
}

Andrii Sherman•6mo ago

do you have the same enum values in database and ts schema?

CalOP•6mo ago

Also note, I have stripped a bunch of other changes to do with constraints from the above log e.g

CalOP•6mo ago

No, the values do not match - due to me evolving the enum over time There are migrations where I have; 1. Renamed the table where the enum is attached 2. enum values have been amended over time

Andrii Sherman•6mo ago

you did those changes manually in database? or by generate+migrate from drizzle

CalOP•6mo ago

nope, only ran in via migrations i can try and spin up a new instance of db and run everything in again with a fresh migrate?

Andrii Sherman•6mo ago

it will work then, it should I guess we can reproduce it trying now have an idea yeah, reproduced it working on improving this experience Some context for everyone in this thread There is no way to remove a value from an enum in PostgreSQL itself. Here is a great article about it: https://supabase.com/docs/guides/database/postgres/enums#removing-enum-values Therefore, Drizzle Kit can't do this either. The behavior you experienced is a bug, and we will handle it such that if we detect any attempt to delete an enum value or change the order of values, you will see an error. You will then be asked to remove the enum and create a new one with the set of values you need. You can still add values to an enum and place them in a specific order within the array. What you encountered was a combination of us not catching that you removed an enum value and the limitations of PostgreSQL enums. If you remove the enum and create it again, there should be no issues. cc @Cal

CalOP•6mo ago

thanks @Andrew Sherman, just for clarification then, with the existing migrations, they are currently set up so that they will add values to an enum and keep the existing ones in place - I did read that article in my attempt at debugging this before reaching out for the support 😄 So that means if I were to use migrate on a new DB instance, then use push to manage further testing/updates, this issue would pop up again surely? just wondering what the DX would look like; migrate on a new db instance drop enum (and all columns that depend on it) then run push to add the enum values back in and then also re-create the columns that are missing? i suppose, instead of throwing an error, if we detect that an enum value is already in the pg_enum, it is essentially a no-op and does not attempt to insert?

Andrii Sherman•6mo ago

i suppose, instead of throwing an error, if we detect that an enum value is already in the pg_enum, it is essentially a no-op and does not attempt to insert?

We are doing exactly it, so push is reading pg_enum and other tables for all needed info and then read ts schema and search for a difference. As long as you have 3 values in ts schema and 5 enum values in database - it means you removed 2 of them

So that means if I were to use migrate on a new DB instance, then use push to manage further testing/updates, this issue would pop up again surely?

push vs generate+migrate are 2 different concepts actually small explanation: https://orm.drizzle.team/kit-docs/faq#should-i-use-generate-or-push https://orm.drizzle.team/kit-docs/overview#prototyping-with-db-push I guess we need to have some big turorial and blog post explaining the difference

CalOP•6mo ago

I suppose that's the scenario that I was referencing, was a quick-setup of the database with good known migrations, then push to test out local changes thereafter, but is that something that you'd not recommend and just push straight from the schema?

Andrii Sherman•6mo ago

this flow is good actually so you push as much as you need to test your schema and when you are ready with you schema -> generate the migration and then push to stage/prod/etc.

CalOP•6mo ago

We are doing exactly it, so push is reading pg_enum and other tables for all needed info and then read ts schema and search for a difference. As long as you have 3 values in ts schema and 5 enum values in database - it means you removed 2 of them

So in this case, correct that I removed the value from the enum on the drizzle-orm side of things, generated migrations with generate then migrate'd them into the db at some point over the evolution of the application as my needs changed I think the problem is that the push is now trying to re-insert the enum value even though it's in the db? (and I agree, there are more values in db due to the limitations of pg_enum etc) good stuff, I am glad that the flow makes sense as it feels like a natural DX to do that 🙂

Andrii Sherman•6mo ago

I guess I now understand what happened, and our update should make it straightforward for users to understand what happened. Let me explain Push is trying to reinsert because there are more enum values in the database than in the code. The reason why: - You changed enums and generated a migration. - Kit can't handle deletion or reordering of enums because PostgreSQL can't do it itself. - You migrate'd, but your generated SQL statements didn't include statements to reorder or remove values from the enum. - You tried to push, and as long as the new enum order was not updated in the database, we saw the error you encountered. We will: - Properly detect that some values were removed or reordered. - On generate, prompt you that we didn't generate some migration statements due to limitations and suggest ways to fix that yourself. - On push, fail with an error and explanation, asking you to push without the enum and then repush with the enum btw, we are not using enums on all our projects, we instead are doing this:

column: varchar("column_name").$type<"active" | "non_active">().notNull().default("active"),

column: varchar("column_name").$type<"active" | "non_active">().notNull().default("active"),

column will be of type "active" | "non_active" And then we can change the type as much as we need; it's just a TypeScript helper for us

CalOP•6mo ago

thank you @Andrew Sherman for the explanation here; the crux of this issue stems from the fact that generate/migrate allowed the DB to get into this state in the first place (due to the out of sync enum values - of course you should error, because otherwise developers would be relying on the fact they could have value A/B but some one could insert value C if they did it directly in the DB and cause all sorts of issues) So in terms of rectifying already broken migration files, what do you think the best cause of action should be here? Given that a temp fix to manually sort it is fine, although if other developers are working on the platform, I'd hate to explain that after the migrate they need to run a random script to fix the issue?

Andrii Sherman•6mo ago

and you can use $type with text, varchar or any other type

CalOP•6mo ago

this looks very appealing indeed, enum's always seem to cause issues hey? 😄

Andrii Sherman•6mo ago

yes outside of drizzle as well

CalOP•6mo ago

oh 100%

Andrii Sherman•6mo ago

you can use enum, if you are sure values won't change

So in terms of rectifying already broken migration files, what do you think the best cause of action should be here? Given that a temp fix to manually sort it is fine, although if other developers are working on the platform, I'd hate to explain that after the migrate they need to run a random script to fix the issue?

Do you have such issues now? or you just asking?

CalOP•6mo ago

Well I think I am in this scenario now, given that the migrate has brought me here and I'll have to write a script to correct the issue or am I missing something here?

Andrii Sherman•6mo ago

you would need to add values from database to a ts schema so it will also have 5 enum values and then handle it in a way we discussed

CalOP•6mo ago

👍 thought so 🙂

Andrii Sherman•6mo ago

remove enum and then create needed one We will provide errors with explanations, so that won't happen. We will release that tomorrow. If you don't have such issues now, then the best answer would be: 'That won't happen again 😅.' and thanks a lot for his case helped us to make an improvement

CalOP•6mo ago

no worries at all, was fun to get more of an understanding, awesome stuff, thanks! sorry to be back so soon 😄

CalOP•6mo ago

I've re-added the enum values, and push still has the same error?

CalOP•6mo ago

ah, order is important as stated 🙂 ignore me, order of enums fixed everything, thanks for all the help folks

Gaming

Programming

drizzle-kit push inconsistent with database with migrations already applied