Do we have any idea of what the API could look like?

The easy way is to have a @check() attribute, and let a raw SQL string be passed as an argument. This is very similar to what typeorm does, except it might not be ergonomic to put raw SQL strings in the prisma schema.

// 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 @check() attribute but passing in Prisma's own syntax rather than raw SQL. It could allow for the check to be done in the Prisma layer rather than in the DB layer, so this would work for any datasource.

model User {
  id        Int               @id
  customer  Customer?
  name      String
  lastName  String
  age       Int
  @@check(name || !lastName)
  @@check(age >= 18)
}

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 expression for the goals table. Take special note that the type column is not a PostgreSQL enum, but rather a text column with a CHECK constraint.

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

+1!

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 CHECK would obviously make it very difficult to simultaneously support it and also support non-SQL databases and the generation of validation code for APIs, &c. What if a limited number of CHECK terms were introduced? For example, @check(len <= 8), @check(isLower) and a few others like that? The full generality of CHECK would still be available the way other features without a PSL equivalent are available, via DDL (the full generality of it is dependent on the database so how could that be avoided?).

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 enum natively.

+1 for this feature. Very much needed

Heyyy, I'm back. Could you please share as many examples of check constraints SQL here as possible?

The difficult bit for this feature will definitely be to figure out if we can represent all the checks in a structured way in Prisma Schema Language (and hence potentially even execute them in Prisma Client, skipping the database roundtrip - as suggested in #1604) or if we "just" present them as magic SQL strings without understanding what they do, and "only" take care of migrating and introspecting them.

We have a few examples at https://github.com/search?q=repo%3Aprisma%2Fdatabase-schema-examples+%22+CHECK+%22&type=code - but real-world examples from our actual users would of course be so much more useful. Thanks!

@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.

1. You can utilise the same knowledge of writing constraints for many database types (if we stick to basic constraints).
2. Maybe they can influence the behaviour of the generated Prisma client (smarter errors? better type checks?).
3. By being structured they should be easier to track in migrations.

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 @relation and just enforce in the answers @relation that it must contain exactly one correct answer.

Very sad to find this feature missing, definitely a +1.

This is a must have feature for me to migrate to Prisma, +1

Heyyy, I'm back. Could you please share as many examples of check constraints SQL here as possible?

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

+1 would be nice to have, even as a partial feature for SQL backed projects (understand the complexity though)

+1 add this feature

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

    "id" TEXT NOT NULL,
    "balance" INTEGER NOT NULL DEFAULT 0 CONSTRAINT balance_value_check CHECK (balance > 0),)
    
  

Please add this feature!!!

Please add this feature!!!

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 :/