Would you run a Prisma 7 schema with relationMode = "prisma" instead of database foreign keys, and what does the team give up if it does?
answer
- foreignKeys is the default on relational databases
- prisma mode: enforced by Prisma Client only
- creates are not checked
- no NoAction on PostgreSQL, no SetDefault
- add @@index on relation scalars yourself
basics
~20 sKeep the default foreignKeys mode wherever the database allows foreign keys, since it checks every writer. Use relationMode = "prisma" only when it does not, accepting that only Prisma Client is checked, creates go unchecked and you add the indexes.
solid answer
~40 s`relationMode` decides who enforces relations. The default, `foreignKeys`, makes Prisma Migrate create real foreign keys, so the database checks every writer and runs `onDelete` / `onUpdate`. `prisma` creates none and emulates the checks inside Prisma Client with extra queries. The docs call foreign keys the usual preferred choice, so emulation needs a reason, typically a database that forbids them. The costs are concrete. Raw queries, other services and console fixes are never checked, and creates are not checked even through the client, so orphans can appear. On PostgreSQL `NoAction` is rejected (P1012, use `Restrict`), and `SetDefault` is unavailable everywhere. No indexes come with the relations, so add `@@index` on every relation scalar, which `prisma validate` warns about. Switching modes later adds or drops the keys on the next migration.
code
prisma · 17 linesdatasource db {
provider = "postgresql"
relationMode = "prisma" // emulated in Prisma Client; no foreign keys created
}
model User {
id Int @id @default(autoincrement())
orders Order[]
}
model Order {
id Int @id @default(autoincrement())
userId Int
user User @relation(fields: [userId], references: [id], onDelete: Restrict)
@@index([userId]) // prisma validate warns until the relation scalar has an index
}go deeper
Know that Prisma uses real foreign keys by default on relational databases, and that relationMode can switch that off.
Explain what emulation checks and what it skips, and which referential actions it supports.
Make an emulated schema safe: indexes on relation scalars, writers through one client, and an orphan check.
Decide whether emulation is justified for this database, and own the integrity it moves from the database to the team.
## The choice On a relational database Prisma 7 enforces relations one of two ways, set by `relationMode` in the `datasource` block: - **`foreignKeys`**, the default on every relational connector. Prisma Migrate and `db push` create real foreign keys, and the database enforces both the reference and the `onDelete` / `onUpdate` actions. - **`prisma`**, which emulates them. No foreign keys are created. Prisma Client runs extra queries to check references on update and to carry out referential actions. It is the only mode on MongoDB. The docs' advice is direct: where the database supports foreign keys, they are usually the preferred choice. A lead therefore needs a reason to pick emulation, and must know what it gives up. ## When emulation is the reason, not the preference The usual reason is a database that does not allow foreign keys. Some hosted MySQL-compatible services forbid them, and there `relationMode = "prisma"` is how a schema keeps relation fields and referential actions. The docs also allow for teams that prefer not to use foreign keys on a database that supports them. That is a database decision first, and the Prisma setting follows it. ## What emulation gives up | Concern | `foreignKeys` | `prisma` | |---|---|---| | Who enforces | the database, for every writer | Prisma Client only; raw queries, other services and scripts are not checked | | Creates | a missing parent is rejected | **not checked**, so a create can point at a row that does not exist | | Referential actions | all the database supports | `Cascade`, `Restrict`, `SetNull`; no `SetDefault`, and on PostgreSQL and SQLite no `NoAction` (`prisma validate` fails with P1012 and suggests `Restrict`) | | Indexes | the database may index foreign key columns itself (MySQL does) | none come with the relations; add `@@index` on each relation scalar, and `prisma validate` warns until you do | | Cost | one statement | extra queries per emulated check or action | | Error text | the database's constraint message | a Prisma message naming the violated relation | The first row is the one that matters for integrity. With emulation, only code that goes through Prisma Client keeps relations whole. A backfill run as raw SQL, a second service writing the same tables, or a manual fix in a database console can leave orphans, and nothing reports them. ## If you must emulate 1. Add `@@index` (or `@unique` / `@@id` where it fits) on every relation scalar. Treat the validate warning as an error in CI. 2. Route every writer through one Prisma Client codebase, or accept that other writers must re-implement the checks. 3. Ban raw writes to related tables, or review each one for the references it could break. 4. Run a scheduled orphan check: a query per relation that finds child rows whose parent is missing. 5. Choose `Restrict` where you would have used `NoAction`, and avoid relying on `SetDefault`. ## Switching later Changing `relationMode` is not just a config flip. The next `prisma migrate dev` or `db push` adds or drops the foreign keys. Moving to `foreignKeys` fails if orphans already exist, so clean the data first. Moving to `prisma` drops the database's guarantees, along with any indexes the database had created only for those keys. ## Choosing On PostgreSQL with one service writing through Prisma, keep `foreignKeys`: the database guards every path at no extra query cost. Choose `prisma` when the database leaves no choice, and budget for the indexes, the orphan checks and the write discipline that the database would otherwise have provided.
- Why can a create leave an orphan under relationMode = "prisma"?The docs say emulation checks references when you update a record but not when you create one, so a create can store a `userId` that points at no user. With `foreignKeys`, the database rejects that insert. Under emulation, validate parent ids in the code that creates children, or create the child through a nested write from its parent.
- What happens when a team switches an existing database from prisma mode to foreignKeys?The next `prisma migrate dev` or `db push` generates `ADD CONSTRAINT ... FOREIGN KEY` statements. They fail if any child row already references a missing parent, so find and fix orphans first. Once the keys exist, the database enforces them for every writer, including raw SQL.
- relationMode = "prisma" still protects rows written with $executeRaw.
- Emulation checks a missing parent on create as well as on update.
- Prisma adds indexes for relation fields in either mode.
- NoAction works on PostgreSQL under relationMode = "prisma".
- Switching relationMode changes nothing in the database.
Assumes Prisma ORM 7.10, where relationMode sits in the datasource block and prisma validate rejects onDelete: NoAction on PostgreSQL under relationMode = "prisma".