Complete Prisma schema-design & relations guide: design 1:1, 1:N, N:N, referential actions, relationMode, composite keys, and name mapping type-safely

Database design decides an app's lifespan. Get the shape of the tables, how relations are drawn, and where constraints are placed wrong at the start, and a later fix becomes the highest-difficulty work of "fixing it without breaking data in a migration." That's exactly why designing the schema first, correctly, in a form where the intent appears in the types is worth it.

This article is an implementation guide to solidifying Prisma ORM (v7) schema and relation design at production quality. schema.prisma is not a mere config file but the source of types, the client, and migrations all (the single source of truth). Designing this carefully decides the type safety of all subsequent code. The overall production operation of Prisma is summarized in the Prisma ORM production-operations guide (v7). This article is positioned as the deep dive into "data modeling" within it.

Rules for this article: schema syntax and attributes are based on the Prisma official documentation (as of June 2026, the v7 family). The material is PostgreSQL. Because support and default native types differ by provider (MySQL/SQLite/SQL Server/MongoDB/CockroachDB), always confirm the latest specs in the official documentation before production rollout.

0. Mental model: place constraints on the DB, not on "the app's goodwill"

In a product where I was thorough about type safety, I make it a habit to design so that an illegal state can't be expressed in the first place. The database is the same. The premise that "the app will always put correct values in" is always broken by the second year of operation. Place uniqueness, NOT NULL, foreign keys, and referential actions on the schema (= DB constraints), not on an app if-statement — this is the first principle of data modeling that doesn't break down.

Prisma's schema is the place to declaratively write this "lean constraints onto the DB." And the constraints you write are reflected directly into the types too. String? is string | null, @unique is the argument of findUnique, a relation is the shape of include — your schema-design judgments come back as the code's types.

1. A relation is "one pair of 2 fields"

The first thing to make sink in with Prisma relations is that one relation is always expressed by 2 fields. The @relation(fields, references) of the side that has the foreign key (FK), and its back-reference side. Understand this correspondence and all relations come to look like variations of the same principle.

1.1 1:N (most common)

The most-used form. Place the FK on the "N" side.

model User {
  id    Int    @id @default(autoincrement())
  posts Post[] // 逆参照（リストになる）
}

model Post {
  id       Int  @id @default(autoincrement())
  author   User @relation(fields: [authorId], references: [id]) // FK側
  authorId Int  // 実際の外部キー列
}

Post.author and Post.authorId are the FK-holding side, and User.posts is the back-reference side (an array). To make author optional, add ? to both.

model Post {
  id       Int   @id @default(autoincrement())
  author   User? @relation(fields: [authorId], references: [id])
  authorId Int?  // 著者なしの投稿を許す
}

1.2 1:1

The difference from 1:N is just one — add @unique to the FK column. Enforce "at most 1 profile per user" on both the type and the DB with a unique constraint.

model User {
  id      Int      @id @default(autoincrement())
  profile Profile? // FKを持たない側は任意(?)にする
}

model Profile {
  id     Int  @id @default(autoincrement())
  user   User @relation(fields: [userId], references: [id])
  userId Int  @unique // ← これが 1対1 の鍵
}

Without userId @unique, this is just a 1:N. Whether it's "1:1 or 1:N" is decided by the presence of @unique — remember this one point and you won't get lost in design.

2. N:N: implicit or explicit

N:N (article ⇄ tag, etc.) has 2 designs. The only axis is "whether you want to give the join table itself attributes."

2.1 Implicit (a Prisma-managed join table)

If the join table needs no extra columns, leave it to Prisma. Just place lists on both models and Prisma auto-generates the join table (e.g., _CategoryToPost).

model Post {
  id         Int        @id @default(autoincrement())
  title      String
  categories Category[]
}

model Category {
  id    Int    @id @default(autoincrement())
  name  String
  posts Post[]
}

The implicit join table requires that both models have a single @id, and you can't specify fields / references / onDelete / onUpdate. It's optimal for a simple relation.

2.2 Explicit (define the join model yourself)

If you need attributes of the relation itself like "when and by whom it was linked," make the join model explicit. This is overwhelmingly the more common form in practice.

model Post {
  id         Int                 @id @default(autoincrement())
  title      String
  categories CategoriesOnPosts[]
}

model Category {
  id    Int                 @id @default(autoincrement())
  name  String
  posts CategoriesOnPosts[]
}

model CategoriesOnPosts {
  post       Post     @relation(fields: [postId], references: [id])
  postId     Int
  category   Category @relation(fields: [categoryId], references: [id])
  categoryId Int
  assignedAt DateTime @default(now()) // 関連の属性
  assignedBy String

  @@id([postId, categoryId]) // 複合主キー
}

Declare a composite primary key with @@id([postId, categoryId]) and guarantee "the same combination only once." The strength of the explicit form is being able to give the relation audit info like assignedAt / assignedBy.

Design judgment (YAGNI): when in doubt, start from implicit and migrate to explicit the moment the relation needs attributes. Pre-emptively making everything an explicit model only increases the boilerplate.

3. Referential actions: don't trust the defaults, make the intent explicit

When you delete/update a parent record, what to do with the children — declare it with onDelete / onUpdate. The defaults here can be counterintuitive and a hotbed of accidents.

model Post {
  id       Int  @id @default(autoincrement())
  author   User @relation(fields: [authorId], references: [id], onDelete: Cascade)
  authorId Int
}

The specifiable values are the 5 of Cascade / Restrict / NoAction / SetNull / SetDefault. The default when omitted changes by whether the relation is optional or required.

Clause	Optional relation (FK is `?`)	Required relation
`onDelete`	`SetNull`	`Restrict`
`onUpdate`	`Cascade`	`Cascade`

Three points.

onDelete: Cascade is "delete a parent and the children too." It's reasonable for "children subordinate to the parent" like comments or line items, but don't casually attach it to 'children that must not disappear' like audit logs or payment records.
SetNull can't be used unless the FK is optional (?). SetDefault needs a @default on the FK column.
The default Restrict (refuse deletion if children remain on the parent) is on the safe side, but I recommend making onDelete explicit every time, not "vaguely the default." The intent appears in the schema and can be judged in review.

Portability note: Restrict can't be used in SQL Server, where you use NoAction. Self-relations and circular references also need NoAction in SQL Server / MongoDB.

4. relationMode: hold FKs in the DB or in Prisma

relationMode is the choice of whether to protect referential integrity with the DB's foreign-key constraints or pseudo-protect it with Prisma Client's query logic.

// 既定：本物のFK制約をDBに張る（PostgreSQL等）
datasource db {
  provider     = "postgresql"
  relationMode = "foreignKeys"
}

// FK非対応/無効なDB向け（PlanetScale、シャーディング等）
datasource db {
  provider     = "mysql"
  relationMode = "prisma"
}

foreignKeys (default): enforce real FK constraints and referential actions at the DB level. Since the DB guarantees integrity, this is usually the only choice.
prisma: in an environment that doesn't support / has disabled FKs (MongoDB, PlanetScale not drawing foreign keys, etc.), Prisma emulates integrity.

The most important pitfall of prisma mode: in this mode, Migrate / db push doesn't auto-generate indexes for FK columns. An FK without an index invites a full scan on every join, slow and high-cost. You need to stick @@index yourself.

model Post {
  id     Int  @id @default(autoincrement())
  userId Int
  user   User @relation(fields: [userId], references: [id])

  @@index([userId]) // prismaモードでは必須
}

5. IDs, constraints, defaults

5.1 Primary keys and composite constraints

model User {
  id    Int    @id @default(autoincrement())
  email String @unique
}

// 複合主キー
model Member {
  orgId  Int
  userId Int
  role   String

  @@id([orgId, userId])
}

// 複合ユニーク・複合インデックス
model Post {
  id       Int    @id @default(autoincrement())
  authorId Int
  title    String

  @@unique([authorId, title]) // 同一著者の同名記事を禁止
  @@index([authorId, title])  // 検索を高速化
}

@id / @unique are column-level, and @@id / @@unique / @@index are block-level that bundle multiple columns. Every model must have a unique identifier with either a single @id or a composite @@id.

5.2 Default-value generation functions

model Post {
  id        Int      @id @default(autoincrement())
  uuid      String   @default(uuid(7))   // 時系列ソート可能なUUIDv7
  cuid      String   @default(cuid(2))   // 衝突しにくい短いID
  createdAt DateTime @default(now())
  published Boolean  @default(false)
}

Representative functions are autoincrement() / cuid() (cuid(2) also OK) / uuid() (uuid(4) / uuid(7)) / now() / dbgenerated(...), etc. The primary-key strategy is a design judgment — for an ID in a distributed environment or exposed in a URL, uuid(7) / cuid(2) (hard to guess, and can have time-series property) falls to the safe side over a sequence. When you need a DB-specific default expression, leave it to the DB with dbgenerated("...").

6. Scalar types, native types, enum, self-relations

6.1 Types and native types

Prisma's scalar types are the 9 of String / Boolean / Int / BigInt / Float / Decimal / DateTime / Json / Bytes. You can override the DB's physical type with @db.*.

model Article {
  id       Int      @id @default(autoincrement())
  slug     String   @db.VarChar(200) // text ではなく varchar(200)
  body     String   @db.Text
  price    Decimal  // 金額は Float ではなく Decimal（丸め誤差を避ける）
  metadata Json     // PostgreSQL では jsonb にマップ
  tags     String[] // スカラーのリスト（PostgreSQL/CockroachDB/MongoDB）
}

Design tip: don't use Float for values requiring accuracy like amounts and quantities. Use Decimal (or an integer minor unit). On the payment foundation I unified amounts into integer minor units and structurally eliminated rounding errors. The choice of type is the design of correctness itself.

6.2 enum and self-relations

enum Role {
  USER
  ADMIN
}

model User {
  id   Int  @id @default(autoincrement())
  role Role @default(USER)

  // 自己参照（1対多）：上司と部下
  managerId Int?
  manager   User?  @relation("OrgChart", fields: [managerId], references: [id])
  reports   User[] @relation("OrgChart")
}

For a self-relation, Prisma recognizes the pair by both sides sharing the same @relation("name"). enum maps to the DB's enum type in PostgreSQL/MySQL/MongoDB/CockroachDB (SQLite/SQL Server are not natively supported).

7. Retrofit onto an existing DB: @map and db pull

The case of loading Prisma onto an existing DB rather than a new one is common, and here @map / @@map works. You can separate the name in the code (camelCase, etc.) and the physical name (snake_case, etc.).

model User {
  id        Int    @id @default(autoincrement())
  firstName String @map("first_name") // 列名は first_name
  posts     Post[]

  @@map("users") // テーブル名は users
}

To raise a schema from an existing DB, first take in the current state with introspection.

npx prisma db pull   # 既存DB → schema.prisma を生成（上書き）
npx prisma generate  # 型付きクライアントを生成

Against the raw schema generated by db pull, tidy the app-side naming with @map and make relations explicit with @relation — this is the standard for existing-DB introduction. Baselining (the procedure to start the migration history without breaking the existing DB) is detailed in the Prisma Migrate production-operations guide.

Multi-schema: in PostgreSQL/SQL Server, you can assign a model to a specific schema with @@schema("public") (assuming the multiSchema feature and the schemas specification in datasource).

8. Schema-design checklist

The items I always confirm in a schema before shipping to production.

Conclusion

Prisma schema design is the work of "placing constraints on the DB, not the app, and declaring their intent in a form that appears in the types." A relation is one pair of 2 fields, 1:1 is @unique, N:N is implicit/explicit by the presence of attributes, referential actions are explicit not relying on the default, and in relationMode = "prisma" you stick indexes yourself — the accumulation of these design judgments makes a data model that doesn't break later.

Once the schema is solid, next is how to keep applying it safely to the production DB. For non-stop schema changes and CI/CD, head to the Prisma Migrate production-operations guide. "I want to solidify everything end-to-end from data-model design to DB operation that doesn't break in production" — if you have such a requirement, I can help from design review through implementation.

Complete Prisma schema-design & relations guide: design 1:1, 1:N, N:N, referential actions, relationMode, composite keys, and name mapping type-safely

0. Mental model: place constraints on the DB, not on "the app's goodwill"

1. A relation is "one pair of 2 fields"

1.1 1:N (most common)

1.2 1:1

2. N:N: implicit or explicit

2.1 Implicit (a Prisma-managed join table)

2.2 Explicit (define the join model yourself)

3. Referential actions: don't trust the defaults, make the intent explicit

4. relationMode: hold FKs in the DB or in Prisma

5. IDs, constraints, defaults

5.1 Primary keys and composite constraints

5.2 Default-value generation functions

6. Scalar types, native types, enum, self-relations

6.1 Types and native types

6.2 enum and self-relations

7. Retrofit onto an existing DB: @map and db pull

8. Schema-design checklist

Conclusion

Prisma ORM Production-Operations Guide (v7): Rust-Free, driverAdapters, and Type-Safe Schema through Migrations, Transactions, and Serverless

Next.js × Prisma production implementation guide: solidify the App Router, Server Components, Server Actions, the Zod boundary, and connection management type-safely

Prisma Migrate production-operations guide: the correct dev/deploy separation, shadow DB, baselining an existing DB, zero-downtime expand-and-contract migration, and CI/CD

Prisma v6 → v7 migration guide: safely crossing Rust-free, mandatory driver adapters, the prisma-client generator, prisma.config.ts, and the removal of middleware

Also worth reading

Drizzle ORM Production Operation Guide: Generating Types from the Schema and Hardening Migrations, Transactions, and Edge in a Type-Safe Way

Echo × database production design: choosing pgx / sqlc / GORM, connection pools, transaction boundaries, and context propagation

SQL-injection prevention for Supabase / PostgreSQL — the traps of rpc, raw SQL, and dynamic SQL inside functions

0. Mental model: place constraints on the DB, not on "the app's goodwill"

1. A relation is "one pair of 2 fields"

1.1 1:N (most common)

1.2 1:1

2. N:N: implicit or explicit

2.1 Implicit (a Prisma-managed join table)

2.2 Explicit (define the join model yourself)

3. Referential actions: don't trust the defaults, make the intent explicit

4. relationMode: hold FKs in the DB or in Prisma

5. IDs, constraints, defaults

5.1 Primary keys and composite constraints

5.2 Default-value generation functions

6. Scalar types, native types, enum, self-relations

6.1 Types and native types

6.2 enum and self-relations

7. Retrofit onto an existing DB: @map and db pull

8. Schema-design checklist

Conclusion

Related articles

Prisma ORM Production-Operations Guide (v7): Rust-Free, driverAdapters, and Type-Safe Schema through Migrations, Transactions, and Serverless

Next.js × Prisma production implementation guide: solidify the App Router, Server Components, Server Actions, the Zod boundary, and connection management type-safely

Prisma Migrate production-operations guide: the correct dev/deploy separation, shadow DB, baselining an existing DB, zero-downtime expand-and-contract migration, and CI/CD

Prisma v6 → v7 migration guide: safely crossing Rust-free, mandatory driver adapters, the prisma-client generator, prisma.config.ts, and the removal of middleware

Also worth reading

Drizzle ORM Production Operation Guide: Generating Types from the Schema and Hardening Migrations, Transactions, and Edge in a Type-Safe Way

Echo × database production design: choosing pgx / sqlc / GORM, connection pools, transaction boundaries, and context propagation

SQL-injection prevention for Supabase / PostgreSQL — the traps of rpc, raw SQL, and dynamic SQL inside functions