drizzle-orm: [BUG]: Check constraint are not being generated for postgres.
Report hasn’t been filed before.
- I have verified that the bug I’m about to report hasn’t been filed before.
What version of drizzle-orm are you using?
0.36.1
What version of drizzle-kit are you using?
0.28.0
Other packages
No response
Describe the Bug
Check constraints are not being generated when using drizzle with postgress. Respective docs: https://orm.drizzle.team/docs/indexes-constraints#check
Schema:
export const reports = createTable(
"report",
{
id: varchar("id", { length: 255 })
.notNull()
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
gameId: integer("game_id")
.references(() => games.id)
.notNull(),
userId: varchar("user_id", { length: 255 })
.notNull()
.references(() => users.id),
score: integer("score"),
},
(table) => ({
checkConstraint: check(
"score_check",
sql`0 < ${table.score} AND ${table.score} < 4`,
),
}),
);
Using npx drizzle-kit generate.
Generated SQL, does not contain score constraint:
CREATE TABLE IF NOT EXISTS "pandorasdb_report" (
"id" varchar(255) PRIMARY KEY NOT NULL,
"game_id" integer NOT NULL,
"user_id" varchar(255) NOT NULL,
"score" integer
);
--> statement-breakpoint
DO $$ BEGIN
ALTER TABLE "pandorasdb_report" ADD CONSTRAINT "pandorasdb_report_game_id_pandorasdb_game_id_fk" FOREIGN KEY ("game_id") REFERENCES "public"."pandorasdb_game"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
DO $$ BEGIN
ALTER TABLE "pandorasdb_report" ADD CONSTRAINT "pandorasdb_report_user_id_pandorasdb_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."pandorasdb_user"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
WHEN duplicate_object THEN null;
END $$;
(The tables use the prefix “pandorasdb”)
About this issue
- Original URL
- State: closed
- Created 2 years ago
- Reactions: 18
- Comments: 25
Just upgrade
drizzle-kit@latestanddrizzle-orm@latestand it fixed the issue for me.@sdifru The documentation needs to be updated.
This
Should be
Searching through the issues, I found a user who configured the database directly in Postgres and used
npx drizzle-kit pullto bring the configurations to the schemas.I configured my schemas based on this return and now everything is working. In my case, I needed to create multiple checks and I wasn’t able to do it before.
I believe that a lot has already been resolved, however, the documentation has not been updated.
Note that there is another open issue talking about “depreaction warning on composite primary key builder”.
And the solution is to start using an array instead of an object. However, the documentation has not yet been updated.
This is HUGE, this worked for primary keys for me and seems like maybe it is just an issue with their documentation afterall.
This generates a migration (
drizzle-kit generate) with aCHECKconstraint:But for some reason when I apply the migration (
drizzle-kit migrate), the constraint isn’t added.Hello,
I found what I did wrong. I was importing
checkfromdrizzle-orm/mysql-coreinstead ofdrizzle-orm/pg-core. No error was thrown, and the constraint was silently ignored.It’s still a bug, but in a way it’s a good news, it’s a very one to fix, and people will be even happier. Hope this helps you at least a bit!
Hello Same issue here
targeted_risks_check constraint is not generated
@nicobao that is still the old API I believe.
This:
should be
@anthonyalayo This mainly seems to be a documentation issue on our part. We have a PR open so we can avoid further confusion on this topic.
That was me 😃.
Unfortunately, there is still a lot of unresolved items. I just tested this on the latest and it is still occurring. This means you have to copy/paste check constraints everywhere instead of just making a type and using it.
On top of that,
drizzle-zodis still not respecting check constraints. This means you need to repeat all your constraints in the application code, a very bug prone process.Just to add my 2 cents, removing naming and returning an array solved the problem for me:
Ie, turn this:
Into this:
The solution is to run
npx drizzle-kitgenerate and thennpx drizzle-kit push.Yep same scenario and drizzle versions as @denispaluca