-
Notifications
You must be signed in to change notification settings - Fork 1.5k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Support SQL Check constraints #3388
Comments
Do we have any idea of what the API could look like? The easy way is to have a // typeorm snippet
@Entity()
@Check(`"name" IS NULL OR "lastName" IS NOT NULL`)
@Check(`"age" >= 18`)
export class User { ... } The first check asserts that if there is ever a value for "name" there must also be a value for "lastName". SQL conditions are very verbose, but can also be very powerful. The second way would be also using a
This could be a challenge, but if the syntax allows conditions as powerful as SQL's it's probably the way to go. A last alternative would be to have a code-first way to define the schema (which I'd prefer). In that case we could easily use a programming languages tools to validate the values. Check constraints are of extreme importance for defining the possible states of a row, so I hope there will be a solution for this by the time Prisma2 launches! I wouldn't consider it a ready for prod lib yet without that feature. |
I'm currently using check constraints in my database instead of postgres enums. I've been annotating my schema as follows: model Goal {
id Int @id @default(autoincrement()) @db.Integer
type GoalType @db.Text
@@map("goals")
}
enum GoalType {
DEBT
SAVING
} Unfortunately, introspection removes the custom enum I defined since the column type is actually a text column: model Goal {
id Int @id @default(autoincrement()) @db.Integer
type String @db.Text
@@map("goals")
} Here is the CREATE TABLE "public"."goals" (
"id" int4 NOT NULL DEFAULT nextval('goals\_id\_seq'::regclass),
"type" text NOT NULL CHECK (type = ANY (ARRAY\['DEBT'::text, 'SAVING'::text\])),
CONSTRAINT PRIMARY KEY ("id")
); FYI, this comment is related to an issue I had opened for the above issue: #4770 |
This missing feature is the only reason that keeps me from switching from TypeORM to Prisma. I would extremely appreciate to be able to create and manage my database schema completely with Prisma Migrate. Prisma's schema language is much more readable and clear than pure SQL (DDL) and has the big advantage over ORMs like TypeORM of not being "code first" and language/platform dependent. If Prisma Migrate had support for check constraints, it could position itself as a true "database version control" tool, independent of the superior prisma client. In this way, it could outperform classics like Liquibase or Flyway in the future, even beyond the NodeJS community. Is this feature planned? |
Hey, I want to also voice my interest in having this ability in the prisma schema. And I also noticed there are no plans to implement this functionality any time soon ... at least not according to the roadmap. Is this correct? |
I would like to second this. Seeing all the guides talk about pushing raw SQL to the db in order to make this happen made me very sad. |
We would like to see this as well if possible. |
I am also interested; it might be worth following #3102 as well. |
Would also be interested in this feature |
This comment was marked as off-topic.
This comment was marked as off-topic.
It would be great to have check constraints right in prisma schema. |
Would love to see that feature |
If only this feature was included in the roadmap. This is the only killjoy in migrating to prisma |
The full generality of |
I would appreciate if this feature is added, it would be a game changer! |
This is especially important for flavors of SQL that do not support ENUMs, such as SQL Server. |
please provide this check constraint feature |
Any news on this feature please 🙏 ? |
Another use case, single row tables: CREATE TABLE "System" (
"id" INTEGER NOT NULL PRIMARY KEY CHECK (id = 1),
...
); |
A few examples I've come across: CREATE TABLE Shipment (
# ...
CONSTRAINT valid_status CHECK (status IN ('created', 'cancelled', 'install_ready', 'install_in_progress', 'install_completed')),
CONSTRAINT building_id_set_when_ready CHECK (status IN ('created', 'cancelled') OR building_id IS NOT NULL)
); CREATE TABLE ShipmentDevice (
# ...
CONSTRAINT required_fields_set_when_completed CHECK (NOT (completed AND (zone_id IS NULL OR installation_timestamp IS NULL)))
); (whether these should be DB constraints or not is up for debate, just examples I've run into recently for your collection!) |
@RyKilleen If I am not mistaken, the first constraint could be expressed using a Prisma |
+1 for this feature. Very much needed |
@janpio - implementing this as magic SQL strings is obviously the easier and faster option. Therefore, as an MVP, I think it would make more sense to provide this first so folks are at least able to enjoy Prisma with Checks (since as you can see it's currently a blocker for many people to migrate to Prisma altogether). At a later iteration upgrading it to be using Prisma annotations would be great of course but providing it as magic SQL strings first would let you under-prioritize this upgrade for way down the line since it would at least not be a blocker for anyone. |
I think there's great value in constraints being part of the Prisma schema.
A good example I encountered with a client right now is: you want to model a quiz question which has multiple answers and there should be exactly one correct answer. With constraints, I would have been able to avoid an extra |
Very sad to find this feature missing, definitely a +1. |
This is a must have feature for me to migrate to Prisma, +1 |
Ensure you don't insert whitespace when adding e.g. an email: ALTER TABLE user
ADD CONSTRAINT trim_email CHECK (trim(email) = email); |
This tweak would've saved me some time, hope this helps: CREATE TABLE test (
id INT,
payment_frequency ENUM('monthly','weekly'),
salary INT,
weekly_salary INT,
CONSTRAINT CHK_frequency_salary CHECK (payment_frequency = 'weekly' AND weekly_salary IS NOT NULL OR payment_frequency ='monthly' AND salary IS NOT NULL)
); |
I'm also the opinion, that this would be a very nice feature. I want to have Nullable fields, that won't be nullable anymore once a "draft-mode" field is disabled. |
+1 would be nice to have, even as a partial feature for SQL backed projects (understand the complexity though) |
This comment was marked as off-topic.
This comment was marked as off-topic.
My example which I could not find among above mentioned create table hierarchy (
id int primary key,
name text not null,
parent_id int references hierarchy(id),
constraint "compare_between_columns" check (parent_id != id)
); |
my example is this. Correction is done in transaction and the SQL is manually updated, but it will nice to have PRISMA auto generate that
|
This comment was marked as off-topic.
This comment was marked as off-topic.
Unfortunately it seems very unlikely at this point, given the age of the ticket and its absence of the roadmap. Also we were asked about giving out some example use cases but no followup was performed. |
We are going to add this feature, not just right now. And I also can't give you a more concrete timeline than "some time in the future". The examples provided were super helpful for us to further understand the complexity of the task and I am very thankful to anyone who provided some. |
It's a shame that this feature is low on the priority list. Part of the reason why a lot of people use Prisma is for type safe querying and narrowing down a field's possible values is an extremely common thing to do and yet I don't get those types even with Prisma :/ |
Right now we have no way of expressing SQL check constraints in our schema. We need a spec for this that we can implement in the future.
The text was updated successfully, but these errors were encountered: