prisma
prisma copied to clipboard
prisma
Support SQL Check constraints
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.
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.
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.
@janpio Hello, is there any update for this feature? Do you planning to add this feature in close future?
If there is no message in an issue, or an entry for the issue in our roadmap at https://pris.ly/roadmap, then there is no update from us. If the issue is still open and marked as feature or improvement it means there is potential that we will tackle an issue in the future.
We will definitely do SQL checks one day - but I can not say when we will get to it. Right now it is unfortunately not a priority as we are working on other things.
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 https://github.com/prisma/prisma/issues/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!
Some examples I found in other issues:
-
https://github.com/prisma/prisma/issues/18301 - PostgreSQL, on domains
REATE DOMAIN language_sample AS VARCHAR(3)[] CONSTRAINT no_empty_element CHECK (NOT VALUE @> '{""}') CONSTRAINT is_uppercase CHECK (array_to_string(VALUE, ',') = upper(array_to_string(VALUE, ',')));2 more examples for that here: https://github.com/prisma/prisma/issues/1796
REATE DOMAIN year AS integer CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE <= 2155)));REATE DOMAIN percent_max_100 AS DOUBLE PRECISION CONSTRAINT max_100 CHECK ( value <= 100 ) -
https://github.com/prisma/prisma/issues/16654 - MySQL, json validation
REATE TABLE `User` ( `permissions` json NOT NULL, CHECK(JSON_SCHEMA_VALID('{ "type": "array", "items": { "type": "string" }}', permissions)) -
https://github.com/prisma/prisma/issues/15173 - Postgres, simple check
reate table appointment ( account_id uuid not null default(current_setting('app.account')::uuid) references account(id), date tstzrange not null, constraint "CHECK_Appointment_date_lower_not_null" check (lower(date) is not null), constraint "CHECK_Appointment_date_lower_not_infinity" check (lower(date) > '-infinity'), constraint "CHECK_Appointment_date_upper_not_null" check (upper(date) is not null), constraint "CHECK_Appointment_date_upper_not_infinity" check (upper(date) < 'infinity') ; -
https://github.com/prisma/prisma/issues/6823 - SQL Server, emulating an enum
REATE TABLE dbo.Table_1 ( /* You could create a reusable "Enum" by creating it as a type, or just adding the check constraint for a single one */ Test dbo.TestType NULL, Test2 nchar(5) NOT NULL CONSTRAINT Test2_Enum CHECK (Test2 IN('USER', 'ADMIN')), ) ON [PRIMARY] O -
https://github.com/prisma/prisma/issues/2505#issuecomment-1425661931 - MySQL, JSON validity check
REATE TABLE `comments` ( `id` bigint(20) unsigned NOT NULL, `body` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`body`)), PRIMARY KEY (`id`) ;
Pretty diverse set of functionality and examples already, but we obviously need more. Please comment to help out!
A few examples from my side (mySQL):
ALTER TABLE `course_model`
ADD CONSTRAINT `weekday_check` CHECK (`weekday` < 7),
ADD CONSTRAINT `time_start_check` CHECK (`time_start` RLIKE '^([0-1]\\d|2[0-3]):[0-5]\\d$'),
ADD CONSTRAINT `time_end_check` CHECK (`time_end` RLIKE '^([0-1]\\d|2[0-3]):[0-5]\\d$');
ALTER TABLE `course`
ADD CONSTRAINT `cancelation_check` CHECK ((`is_canceled` OR (`cancelation_reason` IS NULL)) AND (`is_canceled` = (`canceled_at` IS NOT NULL)));
ALTER TABLE `course_bundle_registration`
ADD CONSTRAINT `user_cancelation_check` CHECK (`is_user_canceled` = (`canceled_at` IS NOT NULL));
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.
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.
