drizzle-team
Implement `check` constraint in drizzle-kit
What is the status of check constraints? I'm trying to implement exclusive subtypes. Any recommendations as to how to approach this would be greatly appreciated. Thanks!
Unsure if this is the implementation and it's just not documented?
https://github.com/drizzle-team/drizzle-orm/blob/main/drizzle-orm/src/pg-core/checks.ts
Am I understanding that I have to use the check function and provide a SQL string?
Check API is implemented on the ORM side, but the Kit side is still pending, which is required for the feature to be usable.
I've renamed the issue to be able to track the feature in general, not just the documentation for it.
Hey @dankochetov! I am wondering if someone can update the documentation for CHECK if it has already been implemented on the ORM side? Thanks!
There's probably a lot of devs that have worked with SQL and never used CHECK, because it's almost never supported by ORMs. I'm currently using them with drizzle to model polymorphic relationship, which works great in Postgres :
ALTER TABLE "Like"
ADD CONSTRAINT check_like_polymorphism
CHECK (num_nonnulls("pictureId", "videoId", "songId") = 1);
This would enforce that only one of these 3 columns have a value while the other 2 must be null. Would be nice to document this pattern once kit support is in.
There's probably a lot of devs that have worked with SQL and never used CHECK, because it's almost never supported by ORMs. I'm currently using them with drizzle to model polymorphic relationship, which works great in Postgres :
ALTER TABLE "Like" ADD CONSTRAINT check_like_polymorphism CHECK (num_nonnulls("pictureId", "videoId", "songId") = 1);This would enforce that only one of these 3 columns have a value while the other 2 must be null. Would be nice to document this pattern once kit support is in.
@Hebilicious, Did you declare this constraint on the schema? I tried to add a RAW SQL like this, but when I ran the generate command, the schema was generated without constraint.
There's probably a lot of devs that have worked with SQL and never used CHECK, because it's almost never supported by ORMs. I'm currently using them with drizzle to model polymorphic relationship, which works great in Postgres :
ALTER TABLE "Like" ADD CONSTRAINT check_like_polymorphism CHECK (num_nonnulls("pictureId", "videoId", "songId") = 1);This would enforce that only one of these 3 columns have a value while the other 2 must be null. Would be nice to document this pattern once kit support is in.
@Hebilicious, Did you declare this constraint on the schema? I tried to add a RAW SQL like this, but when I ran the generate command, the schema was generated without constraint.
You must run the raw SQL manually, which can be done by editing a drizzle kit migration or creating a custom one. There's nothing in the drizzle schema though, so I add a comment in the table definition saying the check exists for now.
I just realized it is really simple to achieve that. I already wrote a seeder class that executes operations through transactions. To add RAW SQL into the database, I can use the same approach, which is a dedicated class to run RAW SQLs.
(table) => ({
ageCheck: check('age_check', sql`${table.age} >= 0`)
}),
Does the API look like so?
