Database Relations

Mastering data modeling. Learn how to define connections between your tables using Prisma's schema syntax, covering everything from simple lookups to complex join tables.

1:1

One-to-One Relationship

A relationship where a record in one table is connected to exactly one record in another. This is often used for splitting data for security (like credentials) or performance.

The Optional Modifier (?)

In the example below, userProfileId Int? uses the ? symbol. This means the relation is optional—a user account can exist without a profile.

schema.prisma

model UserAccount {
  id          Int      @id @default(autoincrement())
  username    String   @unique
  password    String

  // One-to-One Relation
  userProfileId Int?        @unique
  userProfile   UserProfile? @relation(fields: [userProfileId], references: [id])
}

model UserProfile {
  id          Int      @id @default(autoincrement())
  email       String   @unique
  fullName    String

  userAccount UserAccount?
}

1:n

One-to-Many Relationship

The most common relationship type. One record (User) relates to multiple records (Posts). The "Many" side contains the foreign key.

schema.prisma

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

  // User has many posts
  posts    Post[]
}

model Post {
  id      Int     @id @default(autoincrement())
  title   String
  content String
  
  // Post belongs to one User
  userId  Int
  user    User    @relation(fields: [userId], references: [id])
}

m:n

Many-to-Many Relationship

Implicit Strategy

Prisma handles the connection table for you. Best for simple lists (e.g., Tags, Categories) where you don't need extra data on the relationship.

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

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

Explicit Strategy

You define the join table (UserPost) manually. Best when you need metadata on the relation (e.g., "assignedAt", "role").

model User {
  id        Int        @id @default(autoincrement())
  userPosts UserPost[]
}

model Post {
  id        Int        @id @default(autoincrement())
  userPosts UserPost[]
}

// Junction Table
model UserPost {
  userId Int
  user   User @relation(fields: [userId], references: [id])
  postId Int
  post   Post @relation(fields: [postId], references: [id])

  @@id([userId, postId])
}