Prisma Schema Design

Prisma Schema Design

Design Prisma schemas with datasource, generator, models, field types, and field attributes

When to Use

• Creating a new Prisma project and defining the initial schema
• Adding new models or fields to an existing Prisma schema
• Choosing the correct field types, attributes, and defaults for a data model
• Configuring datasource (PostgreSQL, MySQL, SQLite) and generator blocks

Instructions

1. Start every schema with a datasource block specifying the provider and connection string via env():

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

2. Add a generator block for Prisma Client. Place it immediately after the datasource:

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

3. Define models with PascalCase names. Every model must have exactly one @id field or a @@id composite key:

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

4. Use the correct scalar types: String, Int, BigInt, Float, Decimal, Boolean, DateTime, Json, Bytes. Append ? for optional fields.

5. Apply field-level attributes in this order: @id, @unique, @default(...), @map("column_name"), @db.VarChar(255).

6. Use @@map("table_name") to decouple Prisma model names from database table names when working with legacy databases.

7. Use @@unique([fieldA, fieldB]) for composite unique constraints and @@index([fieldA, fieldB]) for composite indexes.

8. Define enums at the schema level and reference them as field types:

enum Role {
  USER
  ADMIN
  MODERATOR
}

9. Use @default(autoincrement()) for integer IDs, @default(cuid()) or @default(uuid()) for string IDs. Prefer cuid() unless your system requires UUID format.

10. Add @updatedAt to a DateTime field to auto-set the timestamp on every update. Only one @updatedAt per model.

Details

Prisma schema is the single source of truth for your database structure and the generated TypeScript client. The schema file (prisma/schema.prisma) is declarative and uses its own DSL.

Datasource providers: postgresql, mysql, sqlite, sqlserver, mongodb, cockroachdb. Each provider supports a different subset of native types (e.g., @db.VarChar(255) for PostgreSQL, @db.Text for MySQL).

Native type annotations let you override the default database column type. Without them, Prisma maps scalar types to sensible defaults (e.g., String maps to text on PostgreSQL). Use @db.VarChar(n) when you need a length constraint at the database level.

Multi-file schemas are supported via the prismaSchemaFolder preview feature. Split large schemas into multiple .prisma files within the prisma/schema/ directory.

Trade-offs:

• cuid() IDs are shorter and URL-safe but not a standard format; uuid() is universally recognized but longer
• Json fields bypass Prisma's type system — validate JSON content at the application layer or use Zod
• @default(now()) is evaluated at the database level; @default(dbgenerated("gen_random_uuid()")) delegates to database functions
• Decimal preserves precision for financial data; Float introduces IEEE 754 rounding

Common mistakes:

• Forgetting @updatedAt means the field stays at its initial value forever
• Using Int for IDs on tables that will exceed 2.1 billion rows — use BigInt instead
• Not adding @@index on foreign key fields — Prisma does not create indexes automatically for relation fields

Source

https://prisma.io/docs/orm/prisma-schema

Process

1. Read the instructions and examples in this document.
2. Apply the patterns to your implementation, adapting to your specific context.
3. Verify your implementation against the details and edge cases listed above.

Harness Integration

• Type: knowledge — this skill is a reference document, not a procedural workflow.
• No tools or state — consumed as context by other skills and agents.

Success Criteria

• The patterns described in this document are applied correctly in the implementation.
• Edge cases and anti-patterns listed in this document are avoided.