Part 3: Prisma Schema Deep Dive

June 1, 2026·Tech·visibility4 reads ·Development

In Part 2, we already set up a project using Prisma 7 + Next.js 16.2.2 + PostgreSQL + pnpm, and defined a minimal usable User model in prisma/schema.prisma.

The goal of this part is: to explain this schema from top to bottom — what datasource, generator, model, enum, and their attributes are, and how to use them. After reading, you should be able to:

Read/modify the current schema.prisma in the project on your own;
Extend the User model according to business needs;
Lay the syntax foundation for future relationship modeling and migration strategies.

1. Review our current schema.prisma

Let's first look at the current prisma/schema.prisma in the project (from Part 2):

generator client {
  provider = "prisma-client"
  output   = "../generated/prisma"
}

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

model User {
  id        Int      @id @default(autoincrement())
  name      String
  email     String   @unique
  createdAt DateTime @default(now())
}

Prisma official docs tell you: The Prisma Schema has only three blocks:

datasource: data source configuration;
generator: generator configuration;
data model: data model definitions (model, enum, etc.).

This part expands on these three sections.

2. datasource: Tell Prisma "where to connect to the database"

2.1 What does our current datasource look like?

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

The purpose of this configuration is:

The name is db (can be changed, but no need);
provider = "postgresql": tells Prisma to use the PostgreSQL connector;
url = env("DATABASE_URL"): reads the connection string from environment variables.

Combined with the .env from Part 2:

DATABASE_URL="postgresql://prisma:prisma@localhost:5432/next_prisma_demo?schema=public"

The full meaning is:

Protocol: postgresql://;
User/Password: prisma:prisma;
Host/Port: localhost:5432;
Database name: next_prisma_demo;
Schema: public (a Postgres schema concept, not the Prisma Schema file).

2.2 What else can you configure in datasource?

In the Prisma Schema Reference, datasource has more advanced options besides provider and url, such as relation mode, etc., but if you're using Postgres, these two basic configurations are usually enough.

Remember this sentence:

Prisma only knows "which database you are connecting to" through datasource, and decides which types and features to support accordingly.

3. generator: Tell Prisma "which client to generate"

3.1 Recommended generator syntax for Prisma 7

In Prisma 7, we write the following in the schema:

generator client {
  provider = "prisma-client"
  output   = "../generated/prisma"
}

According to the official docs, for the prisma-client generator, output is required to tell Prisma where to place the generated client.

With the project structure from Part 2, this means:

schema.prisma is in prisma/;
output = "../generated/prisma";
So the client will be generated in the generated/prisma directory at the project root.

That's why we import it like this in lib/prisma.ts:

import { PrismaClient } from '../generated/prisma'

3.2 Key points about generator

Based on Prisma 7 official docs, here are key points worth remembering about generator:

provider:
"prisma-client": generates Prisma Client (modern, ESM-first way);
output:
Required;
Determines the path where the generated client code is placed;
Use a path relative to the schema, e.g., ../generated/prisma.

If you later use Prisma in a monorepo/multi-package structure, output becomes more important; for this current single-package project, the default generated/prisma path is already clear enough.

4. data model: Every part of the User model

Now we get to the most important part: data model. The official docs call this the Data Model Definition, including model, enum, attributes, etc.

Our current User model is:

model User {
  id        Int      @id @default(autoincrement())
  name      String
  email     String   @unique
  createdAt DateTime @default(now())
}

We can break it down into a few dimensions:

Each line is a field;
Each field has "name + type + optional modifier + attributes";
Types can be scalar (String, Int, etc.) or relational (e.g., later we'll have posts Post[]).

For now, we'll only look at "scalar + basic attributes"; relationships will be covered in the next part.

4.1 Field structure: name + type + optional modifier

Take the email line as an example:

email     String   @unique

Breaking it down:

Field name: email (in Prisma Client, it becomes user.email);
Field type: String;
Attribute: @unique (field-level unique constraint).

Now look at the id line:

id        Int      @id @default(autoincrement())

Name: id;
Type: Int;
Attribute: @id indicates primary key;
@default(autoincrement()): default value comes from auto-increment sequence.

This confirms the official definition of a model field: field = name + type + optional modifier (?/[]) + attributes.

4.2 Scalar Types

Prisma supports a set of scalar types. The official docs list these common ones:

String
Boolean
Int
BigInt
Float
Decimal
DateTime
Json
Bytes

And some provider-specific native type mappings (via the @db.* attribute), such as @db.VarChar(255), @db.Decimal(10,2), etc.

If we want to extend the User model a bit, for example add a balance field, we can write:

model User {
  id        Int      @id @default(autoincrement())
  name      String
  email     String   @unique
  balance   Decimal  @default(0)
  createdAt DateTime @default(now())
}

When mapping to Postgres, we can add native type attributes, for example:

balance   Decimal  @db.Decimal(10, 2) @default(0)

This tells Prisma to use DECIMAL(10,2) in the database.

4.3 Nullable and Arrays

In Prisma, ? and [] after a type are two very common modifiers:

?: indicates the field can be NULL;
[]: indicates this is an array type (or relational array).

For example, if we want to make name an optional field:

name String?

Or add a string array field tags to User:

tags String[]

Currently, our User is intentionally kept simple: all fields are required, no arrays for now; later when we do relationship modeling, you'll see array type fields like posts Post[].

5. Field attributes: Add "constraints and semantics" to fields with @xxx

Prisma uses so-called "attributes" to describe field constraints and semantics, written after the field, starting with @.

We have already used these in User:

@id
@default(...)
@unique

5.1 @id: Primary Key

id Int @id @default(autoincrement())

Prisma docs state: An ID field uniquely identifies each record in a model. A model can have one @id or one @@id (composite primary key).

Here we use the most common pattern: "single-field integer primary key + auto-increment".

5.2 @default(...): Default Values

@default() can specify a default value or default function for a field. Common combinations:

Integer auto-increment: @default(autoincrement())
Current time: @default(now())
Fixed values: @default("guest"), @default(false), etc.;
UUID: @default(uuid()).

In User, we use:

id        Int      @id @default(autoincrement())
createdAt DateTime @default(now())

At the database level, this means: id uses a sequence/auto-increment, and createdAt defaults to the insert time.

5.3 @unique: Unique Constraint

email String @unique

Indicates that User.email must be unique in the database. Prisma Migrate will create a unique index for this.

Later, when you try to insert a duplicate email in the business logic, you'll receive an error like "P2002 Unique constraint failed", which can be used for user registration conflict checks.

5.4 @updatedAt: Auto-updating time (optional addition)

Although the current User model doesn't use it yet, another common attribute you'll almost certainly use is @updatedAt:

updatedAt DateTime @updatedAt

Every time Prisma updates this record, it will automatically set updatedAt to the current time;
You don't need to manually assign it in code.

You can consider upgrading the User model in the current project to:

model User {
  id        Int      @id @default(autoincrement())
  name      String
  email     String   @unique
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

6. Model attributes (@@xxx): Add rules at the model level

Model attributes are written at the bottom of the model block, do not belong to any field, start with @@, and common ones include:

@@map("table_name"): maps the table name;
@@unique([field1, field2]): composite unique;
@@index([field1, field2]): index;
@@id([field1, field2]): composite primary key.

6.1 @@map: Custom Table Name

You may not need this now, but understanding it is very useful. For example, if you want to call it User in code but have the table named users in the database:

model User {
  id        Int      @id @default(autoincrement())
  name      String
  email     String   @unique
  createdAt DateTime @default(now())

  @@map("users")
}

This means:

In Prisma Client, you still use prisma.user.findMany();
In the database, it actually operates on the users table.

Useful for "old database integration + new code" scenarios.

6.2 @@index: Add indexes for common queries

Suppose you frequently query by createdAt for the latest users; you can add an index:

model User {
  id        Int      @id @default(autoincrement())
  name      String
  email     String   @unique
  createdAt DateTime @default(now())

  @@index([createdAt])
}

Or if you have a combined query requirement, such as often filtering by (email, createdAt) combination, you can write:

@@index([email, createdAt])

Prisma Migrate will generate the corresponding index SQL, improving query performance.

7. enum: A better choice for fixed value sets (preparing to enhance User)

When a field's values come from a finite set, such as user roles, order statuses, task priorities, etc., it's not recommended to use arbitrary strings; the official docs recommend using enum.

7.1 Adding a Role enum field to User

We can add a simple enum on the current schema:

enum Role {
  USER
  ADMIN
}

Then add a role field to the User model:

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

The benefits are:

At the schema level, the possible values are locked: only USER or ADMIN;
The TypeScript types in Prisma Client will also reflect this enum;
At the database level, enums/constrained types will be used to guarantee validity (depending on the provider).

In code, it will look like this:

const user = await prisma.user.create({
  data: {
    name: 'Alice',
    email: 'alice@example.com',
    role: 'ADMIN', // has TS type hints
  },
})

The Prisma official Schema documentation recommends: whenever you have a "fixed set" string field, use enum if possible; this reduces spelling errors and magic strings.

8. About relationships (@relation): Will be explained in detail in the next part with a blog system

In Part 2 and this part, we deliberately used only a single-table User model to clearly explain the basics of the Schema language. Relationship modeling (such as User–Post–Comment) will be fully broken down in the next part using a complete "blog system" example.

Here is a conceptual diagram, similar to the models in the official docs:

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

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  authorId  Int
  author    User     @relation(fields: [authorId], references: [id])
  createdAt DateTime @default(now())
}

You can remember these points for now:

Relational fields: e.g., posts Post[], the type is another model, not a scalar;
Foreign key fields: e.g., authorId Int;
@relation(fields: [...], references: [...]): specifies the foreign key relationship.

The next part will focus on one-to-one, one-to-many, many-to-many, and nested writes using the "blog system User / Post / Comment".

9. Practical connection with the current project: What can you do now?

With Part 2 and this part, you can now make some safe enhancements to the current project's schema.prisma, for example:

Add updatedAt field to User:
```
updatedAt DateTime @updatedAt
```
Define the Role enum and add the role field to User;
Add indexes for frequently queried fields, e.g., createdAt;
Use @@map to match existing database table naming rules (if connecting to an old database).

After each schema modification, you can:

Run pnpm prisma migrate dev --name something to generate a migration and sync the local database;
Use pnpm prisma studio to open the UI and check if fields and data are as expected;
In Next.js, use queries like prisma.user.findMany() to access the new fields.

Later, when you integrate Prisma MCP, most of the AI's work will be modifying this schema and running these commands, so understanding the semantics of the schema is the prerequisite for being able to "review AI output".

10. Summary: What you truly need to master in this part

Quick check: if you meet these criteria, you have mastered the basic syntax of Prisma Schema:

You can read the three blocks in the current schema.prisma: generator / datasource / model User;
You know that generator client in Prisma 7 is configured with provider = "prisma-client" + output;
You know the meanings of provider = "postgresql" and url = env("DATABASE_URL") in datasource;
You can describe the type, nullability, and attribute semantics (@id, @default, @unique, @updatedAt) of each field in the User model;
You roughly know when enum is more suitable than plain strings (fixed sets like roles, statuses);
You can use @@index and @@map to add indexes and table name mappings to models.

X Reddit Facebook Instagram LinkedIn