Drizzle Team•6mo ago

Issue with constraint primary key when using drizzle-kit push 2 times.

Hello! I am facing an issue where if I use drizzle-kit push 2 times without making any changes on a schema with a table with a constraint primary key. First push. Works as intended.

$ drizzle-kit push
drizzle-kit: v0.23.0
drizzle-orm: v0.32.0

No config path provided, using default path
Reading config file '/home/pure/drizzle-test/drizzle.config.ts'
Using 'postgres' driver for database querying
[✓] Pulling schema from database...
[✓] Changes applied
Done in 0.30s.
pure@Pure:~/drizzle-test$ yarn db:push
yarn run v1.22.22

$ drizzle-kit push
drizzle-kit: v0.23.0
drizzle-orm: v0.32.0

No config path provided, using default path
Reading config file '/home/pure/drizzle-test/drizzle.config.ts'
Using 'postgres' driver for database querying
[✓] Pulling schema from database...
[✓] Changes applied
Done in 0.30s.
pure@Pure:~/drizzle-test$ yarn db:push
yarn run v1.22.22

Second push. Gives an error.

$ drizzle-kit push
drizzle-kit: v0.23.0
drizzle-orm: v0.32.0

No config path provided, using default path
Reading config file '/home/pure/drizzle-test/drizzle.config.ts'
Using 'postgres' driver for database querying
[✓] Pulling schema from database...
PostgresError: column "user_id" is in a primary key
    at ErrorResponse (/home/pure/drizzle-test/node_modules/drizzle-kit/bin.cjs:81460:27)
    at handle (/home/pure/drizzle-test/node_modules/drizzle-kit/bin.cjs:81237:7)
    at Socket.data (/home/pure/drizzle-test/node_modules/drizzle-kit/bin.cjs:81060:9)
    at Socket.emit (node:events:518:28)
    at addChunk (node:internal/streams/readable:559:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)
    at Readable.push (node:internal/streams/readable:390:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
    at TCP.callbackTrampoline (node:internal/async_hooks:130:17) {
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '42P16',
  file: 'tablecmds.c',
  line: '7398',
  routine: 'ATExecDropNotNull'
}
Done in 0.31s.

$ drizzle-kit push
drizzle-kit: v0.23.0
drizzle-orm: v0.32.0

No config path provided, using default path
Reading config file '/home/pure/drizzle-test/drizzle.config.ts'
Using 'postgres' driver for database querying
[✓] Pulling schema from database...
PostgresError: column "user_id" is in a primary key
    at ErrorResponse (/home/pure/drizzle-test/node_modules/drizzle-kit/bin.cjs:81460:27)
    at handle (/home/pure/drizzle-test/node_modules/drizzle-kit/bin.cjs:81237:7)
    at Socket.data (/home/pure/drizzle-test/node_modules/drizzle-kit/bin.cjs:81060:9)
    at Socket.emit (node:events:518:28)
    at addChunk (node:internal/streams/readable:559:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)
    at Readable.push (node:internal/streams/readable:390:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
    at TCP.callbackTrampoline (node:internal/async_hooks:130:17) {
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '42P16',
  file: 'tablecmds.c',
  line: '7398',
  routine: 'ATExecDropNotNull'
}
Done in 0.31s.

Ran sql command when pushing the second time:

STATEMENT:  ALTER TABLE "test_table" ALTER COLUMN "user_id" DROP NOT NULL;

STATEMENT:  ALTER TABLE "test_table" ALTER COLUMN "user_id" DROP NOT NULL;

A reproduction can be tried cloning this repository (the problem doesn't appear in https://drizzle.run). https://github.com/PureSci/drizzle-push-issue

GitHub

GitHub - PureSci/drizzle-push-issue

Contribute to PureSci/drizzle-push-issue development by creating an account on GitHub.

16 Replies

rphlmr ⚡•6mo ago

Hi 👋 I can reproduce. The only way is to comment

 (table) => {
        return {
            pk: primaryKey({ columns: [table.idOne, table.idTwo] }),
        };
    },

 (table) => {
        return {
            pk: primaryKey({ columns: [table.idOne, table.idTwo] }),
        };
    },

push, uncomment and push again :/ Looks like the same behaviour as https://orm.drizzle.team/kit-docs/faq#how-push-and-generate-works-for-postgresql-indexes. It only happen for composite PK

Drizzle ORM - FAQ & Troubleshooting

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

rphlmr ⚡•6mo ago

cc @Andrii Sherman

Dr. PureOP•6mo ago

well it also happens when you dont uncomment :Pepe_sad: (outside drizzle run)

zendev•6mo ago

I am running into a similar issue with migrate. The migration file looks like this

ALTER TABLE "artists_to_songs" DROP CONSTRAINT "artists_to_songs_artist_id_song_id";--> statement-breakpoint
ALTER TABLE "users_to_scenes" DROP CONSTRAINT "users_to_scenes_user_id_scene_unique_name";--> statement-breakpoint
ALTER TABLE "artists_to_songs" ADD CONSTRAINT "artists_to_songs_artist_id_song_id_pk" PRIMARY KEY("artist_id","song_id");--> statement-breakpoint
ALTER TABLE "users_to_scenes" ADD CONSTRAINT "users_to_scenes_user_id_scene_unique_name_pk" PRIMARY KEY("user_id","scene_unique_name");--> statement-breakpoint
ALTER TABLE "songs" ADD COLUMN "image_colors" text[] NOT NULL;

ALTER TABLE "artists_to_songs" DROP CONSTRAINT "artists_to_songs_artist_id_song_id";--> statement-breakpoint
ALTER TABLE "users_to_scenes" DROP CONSTRAINT "users_to_scenes_user_id_scene_unique_name";--> statement-breakpoint
ALTER TABLE "artists_to_songs" ADD CONSTRAINT "artists_to_songs_artist_id_song_id_pk" PRIMARY KEY("artist_id","song_id");--> statement-breakpoint
ALTER TABLE "users_to_scenes" ADD CONSTRAINT "users_to_scenes_user_id_scene_unique_name_pk" PRIMARY KEY("user_id","scene_unique_name");--> statement-breakpoint
ALTER TABLE "songs" ADD COLUMN "image_colors" text[] NOT NULL;

So you can see that drizzle-kit is dropping all composite PKs and then readding? It produces the following error when running drizzle-kit migrate:

PostgresError: constraint "users_to_scenes_user_id_scene_unique_name" of relation "users_to_scenes" does not exist

PostgresError: constraint "users_to_scenes_user_id_scene_unique_name" of relation "users_to_scenes" does not exist

Btw this just happened to me after updating to the latest version of drizzle-orm and drizzle-kit

rphlmr ⚡•6mo ago

To better understand, it happens on an existing project with existing migrations? Can you tell me what was the version before you upgrade? I would like to reproduce. For push, I am not surprised. I don’t know if it is really expected or a bug but push is mostly used for drafting the db. I am more worried about migrate that should be non destructive if it is not intentional (like renaming things). (Note that I am not part of the dev team, so I can be wrong 😬)

zendev•6mo ago

It was a project with existing migration files, yes. Before upgrade I was on drizzle-kit 0.19, now on 0.23 I hadn’t changed any names or anything in the schema except for adding that column which you can see at the bottom of the migration file I shared above.

rphlmr ⚡•6mo ago

ok I see, major bumps. There was some breaking changes between. I am searching the related changelogs

zendev•6mo ago

Also for reference I have never used push in this project 😊

rphlmr ⚡•6mo ago

~~did I read right? 0.23?~~ My god I need to sleep

zendev•6mo ago

Haha yes I am now on 0.23

rphlmr ⚡•6mo ago

I am pretty sure it is expected, at least once since https://github.com/drizzle-team/drizzle-kit-mirror/releases/tag/v0.22.0. But I need a joker ( @Andrew Sherman ). I can’t remember the exact moment, but when you don’t name the constraint, it is generated and the way it is generated has changed. In the code you provided, it tries to delete the previous one by guessing the naming. (users_to_scenes_user_id_scene_unique_name to rename it users_to_scenes_user_id_scene_unique_name_pk).

zendev•6mo ago

I see. So what is the solution here then?

rphlmr ⚡•6mo ago

@zendev ok I was able to recreate the same behaviour. So, if you have committed your changes (to be able to revert in case something mess-up). I guess you have done a drizzle-kit up like suggested? Keep somewhere a copy of the last generated migration (the one that has been created since you upgrade). Now you can drizzle-kit drop and drop this last migration. We will fix the schema and generate a new one. I am right assuming that you have primary keys defined with primaryKey()? (third arg of pgTable) Maybe your code looks like :

export const usersToScenes = pgTable(
  "users_to_scenes",
  {
    sceneUniqueName: serial("scene_unique_name").references(() => scenes.id),
    userId: serial("user_id").references(() => users.id),
  },
  (table) => ({
    primaryKey: primaryKey(table.userId, table.sceneUniqueName),
  })
);

export const usersToScenes = pgTable(
  "users_to_scenes",
  {
    sceneUniqueName: serial("scene_unique_name").references(() => scenes.id),
    userId: serial("user_id").references(() => users.id),
  },
  (table) => ({
    primaryKey: primaryKey(table.userId, table.sceneUniqueName),
  })
);

Now it should be:

export const usersToScenes = pgTable(
  "users_to_scenes",
  {
    sceneUniqueName: serial("scene_unique_name").references(() => scenes.id),
    userId: serial("user_id").references(() => users.id),
  },
  (table) => ({
    primaryKey: primaryKey({
      columns: [table.userId, table.sceneUniqueName], // new
      name: "users_to_scenes_user_id_scene_unique_name", // here set the "old" constraint name
    }),
  })
);

export const usersToScenes = pgTable(
  "users_to_scenes",
  {
    sceneUniqueName: serial("scene_unique_name").references(() => scenes.id),
    userId: serial("user_id").references(() => users.id),
  },
  (table) => ({
    primaryKey: primaryKey({
      columns: [table.userId, table.sceneUniqueName], // new
      name: "users_to_scenes_user_id_scene_unique_name", // here set the "old" constraint name
    }),
  })
);

if the name property of primaryKey is missing, Drizzle kit try to guess it [table_name][col_name_1][col_name_2], following the order in columns. the idea is to name all your constraint (primaryKey here) like the pattern mentioned, to prevent kit to try migrate them. note: I am not really 100% sure, I have solved similar issues in the past but it was months ago.

zendev•6mo ago

Amazing thank you I will try this and get back to you

rphlmr ⚡•6mo ago

👍 if it is an open source project and you are still stuck, do not hesitate to share 🫡

zendev•6mo ago

@Raphaël M (@rphlmr) ⚡ it worked! Thank you so much 🙏🏽 Just curious - do I now need to keep this “name” property in the schema forever?

Gaming

Programming

Issue with constraint primary key when using drizzle-kit push 2 times.