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 ⚡
rphlmr ⚡5mo 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 ⚡
rphlmr ⚡5mo ago
cc @Andrii Sherman
Dr. Pure
Dr. PureOP5mo ago
well it also happens when you dont uncomment :Pepe_sad: (outside drizzle run)
zendev
zendev5mo 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 ⚡
rphlmr ⚡5mo 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
zendev5mo 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 ⚡
rphlmr ⚡5mo ago
ok I see, major bumps. There was some breaking changes between. I am searching the related changelogs
zendev
zendev5mo ago
Also for reference I have never used push in this project 😊
rphlmr ⚡
rphlmr ⚡5mo ago
did I read right? 0.23? My god I need to sleep
zendev
zendev5mo ago
Haha yes I am now on 0.23
rphlmr ⚡
rphlmr ⚡5mo 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
zendev5mo ago
I see. So what is the solution here then?
rphlmr ⚡
rphlmr ⚡5mo 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
zendev5mo ago
Amazing thank you I will try this and get back to you
rphlmr ⚡
rphlmr ⚡5mo ago
👍 if it is an open source project and you are still stuck, do not hesitate to share 🫡
zendev
zendev5mo 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?
Want results from more Discord servers?
Add your server