Prisma Schema Generator

Prisma schema and ERD generation: from .prisma to live database

Prisma is a modern, open-source TypeScript/Node ORM that went stable in 2020 and has become one of the most adopted database toolkits in the JavaScript ecosystem. Instead of writing migrations by hand or littering decorators across model classes, you describe your data in a single schema.prisma file with a small declarative language. From that file Prisma generates a fully type-safe client, runs SQL migrations, and — with the right plugin — emits an ERD (entity-relationship diagram) so the documentation stays in sync with the code.

This generator turns a compact tabular notation into a valid Prisma model block you can paste into schema.prisma. Below is the long-form reference covering schema structure, field types, decorators, relations, migration workflows, supported databases, and trade-offs against TypeORM and Drizzle.

Schema structure

A schema.prisma file has three kinds of top-level blocks:

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

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

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

• datasource — connection to one database (only one allowed; multi-DB requires multiple schemas).
• generator — what to produce: prisma-client-js, prisma-erd-generator, custom generators.
• model — a table; fields, attributes, and relations.
• enum — a closed list of allowed values mapped to a native enum type (or to a string column if the DB has no enums).

Field types

• Scalars: String, Int, BigInt, Float, Decimal, Boolean, DateTime, Json, Bytes.
• Optional: add ? (name String?) — column allows NULL.
• List: add [] (tags String[]) — supported natively in PostgreSQL, emulated elsewhere.
• Enums: declare an enum Role { USER ADMIN } and reference it as a field type.
• Unsupported: Unsupported("geometry") — column exists in the DB but Prisma client can't read/write it directly.

Attribute decorators

• Field-level: @id (primary key), @unique, @default(value), @updatedAt (auto-set on update), @map("col_name") (DB column name), @db.VarChar(255) (native type override).
• Model-level: @@id([a,b]) (composite PK), @@unique([a,b]), @@index([col]), @@map("table_name"), @@schema("auth").
• Common defaults: @default(autoincrement()), @default(uuid()), @default(cuid()), @default(now()), @default(true).

Relations: 1:1, 1:N, N:N

// 1:1
model User    { id Int @id  profile Profile? }
model Profile {
  id     Int  @id @default(autoincrement())
  userId Int  @unique
  user   User @relation(fields: [userId], references: [id])
}

// 1:N
model User { id Int @id  posts Post[] }
model Post {
  id       Int  @id
  authorId Int
  author   User @relation(fields: [authorId], references: [id])
}

// N:N implicit (Prisma creates the join table)
model Post  { id Int @id  tags Tag[] }
model Tag   { id Int @id  posts Post[] }

For explicit N:N (when the join carries its own attributes — created_at, role, grade) declare a dedicated model with two FKs and reference it from both sides.

Migrations and the development loop

• prisma migrate dev — generates a new SQL migration from schema changes, applies it locally, and regenerates the client. Use only in development.
• prisma migrate deploy — applies pending migrations in production without prompting. Safe for CI/CD pipelines.
• prisma db push — pushes schema changes directly without creating a migration file. Great for prototyping; never use in production.
• prisma db pull — reverse-engineers an existing database into schema.prisma.
• prisma generate — produces the typed Prisma Client and any extra generators (ERD, GraphQL schema).
• prisma studio — GUI to browse and edit data, running on localhost.

Generating an ERD from schema.prisma

Add a community generator to your schema and the ERD is rebuilt every time you run prisma generate:

generator erd {
  provider = "prisma-erd-generator"
  output   = "./ERD.svg"     // or .png, .pdf, .md (Mermaid)
  theme    = "forest"
}

Other options: prisma-dbml-generator (output for dbdiagram.io), prisma-docs-generator, prisma-class-generator (NestJS DTOs).

Supported databases

• PostgreSQL — first-class, including arrays, JSONB, enums, multi-schema, partial indexes.
• MySQL / MariaDB — broad support; enums map natively.
• SQLite — excellent for tests and small apps; no native enums (mapped to TEXT with check).
• SQL Server — stable; some advanced features missing.
• MongoDB — preview-grade; relations use embedded references rather than joins.
• CockroachDB — PostgreSQL-compatible mode.

Prisma vs TypeORM vs Drizzle

• Prisma — declarative schema, autogenerated types, opinionated migrations. Best ergonomics, large bundle.
• TypeORM — decorator-driven, OOP feel, ActiveRecord and DataMapper. More flexible, less type-safe.
• Drizzle — SQL-first, tiny runtime, infers types from query builder. Closer to raw SQL, fewer abstractions.

FAQ

Does Prisma create the database for me? Yes — prisma migrate dev generates and applies SQL to an empty database, including creating the tables and indexes described in schema.prisma.

Does it support multi-schema in PostgreSQL? Yes — enable the multiSchema preview feature and use @@schema("auth") per model. Schemas appear in the generated client as namespaces.

What's the performance overhead vs raw SQL? Generally small (a few ms per query for type marshalling). For hot paths, Prisma's $queryRaw gives you raw SQL while keeping the client connection pool.

Can I keep the diagram up to date automatically? Yes — add prisma-erd-generator to schema.prisma and commit the regenerated SVG, or rebuild it in CI on every PR so reviewers see the change.

How do I model polymorphic relations? Prisma doesn't support classic polymorphic FKs. The recommended pattern is one join table per concrete type (table-per-type) or a discriminator column with separate nullable FKs.