Prisma Schema to ER Diagram

A schema.prisma file looks like a data model, and for diagramming purposes it mostly is one. It is not a SQL dump: relations are declared in the client schema and may or may not exist as database constraints, depending on the connector and relationMode. Which fields become columns, which become edges, and which exist only for the query API is the whole question.

Models, fields, and columns

Each model block becomes a table and each scalar field a column. Prisma's scalar types are String, Int, BigInt, Float, Decimal, Boolean, DateTime, Json, and Bytes; the connector picks the concrete database type. String is text in PostgreSQL and varchar(191) in MySQL, which is why a schema moved between providers can need native types such as @db.VarChar(255).

• @id marks the primary key; @@id([a, b]) declares a composite key.
• @default(autoincrement()) makes the key database-generated. @default(uuid()), @default(cuid()), and @default(now()) cover the other common cases.
• @unique and @@unique([a, b]) are candidate keys. A unique field is what turns a one-to-many relation into a one-to-one.
• @updatedAt is set by Prisma Client on every write. It is not a database default and does not create a trigger.
• A field ending in ? is optional and becomes a nullable column. Without the ?, the column is NOT NULL.
• @map and @@map rename a column or table at the database level without changing the model name.

One caveat about lists: a scalar list such as String[] is supported on PostgreSQL, CockroachDB, and MongoDB, and rejected on MySQL, SQLite, and SQL Server. A list of another model, such as Post[], is a relation field, not a column, and is always allowed.

Relations: why userId and author both exist

A Prisma relation is declared with two fields, one on each model. On the child model, authorId Int is a real column holding the foreign key, while author User @relation(fields: [authorId], references: [id]) is a virtual field the client uses to load the row. The relation field has no column; the scalar field has no relation information. Both are needed: one stores the value, the other says which field is the key.

The other side declares the inverse: posts Post[] on User. For one-to-many, only the child side carries fields and references. For one-to-one the pattern is the same, but the foreign-key field must also be @unique, because one parent row must not be referenced by two children. A non-unique foreign key is one-to-many even if both sides are named as if singular.

When two models have more than one relation, such as a Post with an author and an editor both pointing at User, each needs a name: @relation("PostAuthor", fields: [authorId], references: [id]). Without it, Prisma cannot match the inverse fields.

Implicit and explicit many-to-many

When both models have a list of the other and neither declares fields and references, Prisma treats it as an implicit many-to-many. The schema contains no join model, but the database contains a join table named after the two models, such as _CategoryToPost, with columns A and B and a unique index over the pair. The table is real, even though it is invisible in the schema file.

An explicit join model replaces that hidden table with a visible one. It is required when the link carries data, such as when a user joined a team, and it is the only option on MongoDB, which does not support implicit many-to-many. Each side then lists the join model instead of the other model directly.

A complete schema with four models

A blogging schema with one enum, one implicit many-to-many, two one-to-many relations, and one optional column:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

enum Role {
  USER
  EDITOR
  ADMIN
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  role      Role     @default(USER)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  posts     Post[]
}

model Post {
  id         Int        @id @default(autoincrement())
  title      String
  body       String?
  published  Boolean    @default(false)
  authorId   Int
  author     User       @relation(fields: [authorId], references: [id])
  categories Category[]
  comments   Comment[]

  @@index([authorId])
}

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

model Comment {
  id     Int    @id @default(autoincrement())
  body   String
  postId Int
  post   Post   @relation(fields: [postId], references: [id], onDelete: Cascade)
}

Reading the diagram

Four models become four entities. User has a generated key, a unique email, a nullable name, and a role defaulting to USER. Post carries authorId and points at User: one user, many posts. Comment does the same with Post and cascades on delete. Category connects to Post through the implicit many-to-many, so the drawing needs a join entity: five boxes and four edges, not a direct line.

Three fields are not columns: posts on User, and categories and comments on Post, are relation lists. Their keys live in Post and Comment as authorId and postId, plus the hidden join table for categories. This is why a diagram can look different from the schema at first glance.

To make the many-to-many explicit, replace the two list fields and add a join model. The shape is the same apart from the extra assignedAt column, which is the point: link data now has a home.

model Post {
  // ...
  categories PostCategory[]
}

model Category {
  // ...
  posts PostCategory[]
}

model PostCategory {
  postId     Int
  categoryId Int
  assignedAt DateTime @default(now())
  post       Post     @relation(fields: [postId], references: [id])
  category   Category @relation(fields: [categoryId], references: [id])

  @@id([postId, categoryId])
}

Indexes, enums, and provider differences

@@index([authorId]) is a performance hint, not a constraint: no edge, and usually omitted from a logical diagram. @@unique([a, b]) is a composite candidate key and can make a relation one-to-one. @@id([a, b]) is a composite primary key and belongs on the entity.

An enum is a named value set. PostgreSQL stores it as a database enum, MySQL as an inline ENUM, and some connectors do not support enums at all. Like a DBML enum, it is not an entity: it is the type of a column, and it should not get its own box.

The provider changes the DDL, not the model. postgresql, mysql, sqlite, sqlserver, cockroachdb, and mongodb produce different types and different relation support. MongoDB has no foreign keys, so its relations are application-level: real in Prisma, not enforced by the database. relationMode = "prisma", used on services that disallow foreign keys such as PlanetScale, has a similar effect on SQL: Prisma enforces actions in the client while the database holds no constraints. A diagram of such a schema shows edges the database does not have.

Common mistakes

• Writing author User without @relation(fields: ...). It is a relation field with no foreign key behind it and Prisma cannot infer the column.
• Forgetting the inverse relation field on the other model. Prisma validates both sides and rejects the schema.
• Making a one-to-one relation without @unique on the foreign key. Without it, the relation is one-to-many.
• Giving a required relation a nullable foreign key, or an optional relation a required one. The two declarations must agree.
• Assuming relationMode = "prisma" still creates foreign keys. It emulates them in the client; the database has no constraints.
• Assuming an implicit many-to-many is only a line. Prisma creates a hidden join table with real columns and a unique index.

How ERDGenerator imports schema.prisma

Paste a schema.prisma file and the importer reads the model blocks, ignoring datasource and generator. Scalar and optional fields become columns, with primary keys, composite keys, uniqueness, and autoincrement recognized. @relation(fields: [x], references: [y]) becomes a correctly directed edge, and list fields are not columns. Implicit many-to-many is materialized as a join table. Enum names stay as column types. Model names are rendered in snake_case, and @map and @@map are not applied. The result is editable.

Frequently asked questions

Why do I see both author and authorId in the schema? authorId is the real column. author is a relation field that lets the client query the related User; it has no column and should not appear as an attribute.

Does Prisma really create a table for an implicit many-to-many? Yes. It creates a table named after the two models, with columns A and B and a unique index over the pair. The table can be inspected in the database even though it is absent from schema.prisma.

Can two models be related in more than one way? Yes, for example a post with an author and an editor. Each relation needs a distinct name in @relation so Prisma can match the two sides.

Are relations enforced in MongoDB? No. MongoDB has no foreign keys. Prisma resolves relations in the client and emulates referential actions.