Guia Prisma Schema e relações

Prisma is the most popular TypeScript ORM, and its schema-first approach makes database modeling intuitive and type-safe. Whether you are building a new project or migrating from raw SQL, understanding Prisma schema syntax, relations, and queries is essential. This comprehensive guide covers everything from basic model definitions to advanced patterns like soft deletes and multi-tenancy.

Convert SQL to Prisma schema instantly with our free tool →

What is Prisma?

Prisma is a next-generation ORM for Node.js and TypeScript. Unlike traditional ORMs that map classes to tables, Prisma uses a schema-first approach where you define your data model in a .prisma file, and Prisma generates a fully type-safe client from it.

Prisma eliminates the impedance mismatch between your application code and the database. You get full autocompletion, compile-time error checking, and no more writing raw SQL for basic CRUD operations.

// Install Prisma
npm install prisma --save-dev
npm install @prisma/client

// Initialize a new Prisma project
npx prisma init

// This creates:
// prisma/schema.prisma  — your data model
// .env                  — database connection URL

Schema Basics

Every Prisma project starts with a schema.prisma file. It contains three main blocks: datasource, generator, and model definitions.

// prisma/schema.prisma

// 1. Datasource — where your data lives
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

// 2. Generator — what client to generate
generator client {
  provider = "prisma-client-js"
}

// 3. Model — your database tables
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  role      Role     @default(USER)
  posts     Post[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

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

enum Role {
  USER
  ADMIN
  MODERATOR
}

The datasource block configures your database connection. The generator block tells Prisma what client to generate. Models define your database tables.

Field Types

Prisma supports a rich set of scalar types that map to native database column types. Understanding these types is crucial for designing your schema correctly.

Prisma also supports enums, which are particularly useful for status fields, roles, and other fixed-value columns:

enum OrderStatus {
  PENDING
  PROCESSING
  SHIPPED
  DELIVERED
  CANCELLED
}

model Order {
  id        Int         @id @default(autoincrement())
  status    OrderStatus @default(PENDING)
  total     Decimal
  items     Json        // flexible JSON column
  metadata  Bytes?      // binary data
  createdAt DateTime    @default(now())
}

// Using cuid() or uuid() for string IDs
model Product {
  id    String @id @default(cuid())
  // or: id String @id @default(uuid())
  name  String
  price Float
}

Field Modifiers & Attributes

Field modifiers and attributes control how fields behave at the database level. They are the building blocks of a well-designed schema.

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?                        // optional field
  bio       String?  @db.Text              // native type mapping
  tags      String[]                       // array field (PostgreSQL)
  role      Role     @default(USER)
  score     Float    @default(0)
  isActive  Boolean  @default(true)
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt      @map("updated_at")

  @@map("users")                           // table name in DB
  @@index([email])
}

Relations

Relations are the heart of any relational database, and Prisma makes them declarative and type-safe. There are three fundamental relation types.

One-to-One

Each record in one model corresponds to exactly one record in another model. The foreign key side must include @unique on the relation scalar field.

model User {
  id      Int      @id @default(autoincrement())
  email   String   @unique
  profile Profile?
}

model Profile {
  id     Int    @id @default(autoincrement())
  bio    String
  avatar String?
  user   User   @relation(fields: [userId], references: [id])
  userId Int    @unique  // @unique makes it one-to-one
}

// Query with one-to-one relation
const userWithProfile = await prisma.user.findUnique({
  where: { id: 1 },
  include: { profile: true },
});
// userWithProfile.profile?.bio

One-to-Many

The most common relation type. One record can be associated with many records in another model. The "many" side holds the foreign key.

model User {
  id    Int    @id @default(autoincrement())
  email String @unique
  posts Post[]                        // one user has many posts
}

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

model Comment {
  id      Int    @id @default(autoincrement())
  text    String
  post    Post   @relation(fields: [postId], references: [id])
  postId  Int
  author  User   @relation(fields: [authorId], references: [id])
  authorId Int
}

Many-to-Many

Prisma supports both implicit and explicit many-to-many relations. Implicit relations let Prisma manage the join table; explicit relations give you full control.

Implicit many-to-many (Prisma manages the join table):

model Post {
  id         Int        @id @default(autoincrement())
  title      String
  categories Category[]  // implicit many-to-many
}

model Category {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[]                        // implicit many-to-many
}

// Prisma auto-creates a _CategoryToPost join table

Explicit many-to-many (you define the join table):

model Post {
  id   Int       @id @default(autoincrement())
  title String
  tags  PostTag[]
}

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

// Explicit join table — you control extra fields
model PostTag {
  post      Post     @relation(fields: [postId], references: [id])
  postId    Int
  tag       Tag      @relation(fields: [tagId], references: [id])
  tagId     Int
  assignedAt DateTime @default(now())
  assignedBy String?

  @@id([postId, tagId])               // composite primary key
}

Self-Relations

Self-relations occur when a model references itself. They are common in tree structures, social graphs, and organizational hierarchies.

Tree Structure (Parent-Child)

A category can have many sub-categories, and each sub-category belongs to one parent:

model Category {
  id       Int        @id @default(autoincrement())
  name     String
  parent   Category?  @relation("CategoryTree", fields: [parentId], references: [id])
  parentId Int?
  children Category[] @relation("CategoryTree")
}

// Query: Get category with all children
const category = await prisma.category.findUnique({
  where: { id: 1 },
  include: {
    children: {
      include: { children: true },  // nested children
    },
  },
});

Followers / Following

A many-to-many self-relation where users can follow other users:

model User {
  id         Int    @id @default(autoincrement())
  name       String
  followers  User[] @relation("UserFollows")
  following  User[] @relation("UserFollows")
}

// Follow a user
await prisma.user.update({
  where: { id: 1 },
  data: {
    following: { connect: { id: 2 } },
  },
});

// Get user with followers and following
const user = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    followers: true,
    following: true,
  },
});

Indexes

Indexes dramatically improve query performance. Prisma provides several index types through model-level attributes.

model User {
  id       Int    @id @default(autoincrement())
  email    String @unique
  tenantId String
  name     String

  @@unique([tenantId, email])           // composite unique
  @@index([tenantId])                   // index for tenant queries
  @@index([name])                       // index for name searches
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String
  authorId  Int
  published Boolean  @default(false)
  createdAt DateTime @default(now())

  @@index([authorId])                   // index for author lookups
  @@index([published, createdAt])       // composite for published feed
  @@fulltext([title, content])          // full-text search (MySQL)
}

Always add indexes to columns that appear in WHERE clauses, JOIN conditions, and ORDER BY. Monitor slow queries and add indexes accordingly.

Migrations

Prisma Migrate generates SQL migration files from schema changes. This gives you a versioned history of every database change.

Migration Workflow

Key Commands

# Create and apply a migration (development)
npx prisma migrate dev --name add_user_profile

# Apply pending migrations (production)
npx prisma migrate deploy

# Reset database and re-apply all migrations
npx prisma migrate reset

# Push schema without creating migration files (prototyping)
npx prisma db push

# Pull existing database schema into Prisma
npx prisma db pull

# Generate Prisma Client
npx prisma generate

# Open Prisma Studio (GUI)
npx prisma studio

# Seed the database
npx prisma db seed

prisma db push is ideal for prototyping — it syncs the schema without creating migration files. Use prisma migrate dev for production workflows where you need a migration history.

// Example migration file: prisma/migrations/20240101_add_user_profile/migration.sql
-- CreateTable
CREATE TABLE "Profile" (
    "id" SERIAL NOT NULL,
    "bio" TEXT NOT NULL,
    "avatar" TEXT,
    "userId" INTEGER NOT NULL,

    CONSTRAINT "Profile_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "Profile_userId_key" ON "Profile"("userId");

-- AddForeignKey
ALTER TABLE "Profile" ADD CONSTRAINT "Profile_userId_fkey"
    FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

Prisma Client Queries

Prisma Client provides an intuitive, type-safe API for all database operations. Every query is validated at compile time, so typos in field names or incorrect types are caught before your code runs.

Reading Data

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

// Find one by unique field
const user = await prisma.user.findUnique({
  where: { email: 'alice@example.com' },
});

// Find one or throw
const user = await prisma.user.findUniqueOrThrow({
  where: { id: 1 },
});

// Find first matching
const admin = await prisma.user.findFirst({
  where: { role: 'ADMIN' },
});

// Find many with pagination
const users = await prisma.user.findMany({
  where: { role: 'USER' },
  orderBy: { createdAt: 'desc' },
  skip: 0,
  take: 20,
  select: {                           // select specific fields
    id: true,
    email: true,
    name: true,
  },
});

// Count records
const userCount = await prisma.user.count({
  where: { role: 'USER' },
});

// Aggregate
const stats = await prisma.order.aggregate({
  _avg: { total: true },
  _sum: { total: true },
  _count: true,
});

Creating Data

// Create a single record
const user = await prisma.user.create({
  data: {
    email: 'alice@example.com',
    name: 'Alice',
    role: 'USER',
  },
});

// Create with nested relation
const userWithPosts = await prisma.user.create({
  data: {
    email: 'bob@example.com',
    name: 'Bob',
    posts: {
      create: [
        { title: 'First Post', content: 'Hello World' },
        { title: 'Second Post', content: 'Prisma is great' },
      ],
    },
  },
  include: { posts: true },
});

// Create many records
const result = await prisma.user.createMany({
  data: [
    { email: 'user1@example.com', name: 'User 1' },
    { email: 'user2@example.com', name: 'User 2' },
    { email: 'user3@example.com', name: 'User 3' },
  ],
  skipDuplicates: true,               // skip records with unique conflicts
});

Updating Data

// Update a single record
const user = await prisma.user.update({
  where: { email: 'alice@example.com' },
  data: { name: 'Alice Smith' },
});

// Update many
const result = await prisma.user.updateMany({
  where: { role: 'USER' },
  data: { role: 'MEMBER' },
});

// Upsert (create or update)
const user = await prisma.user.upsert({
  where: { email: 'alice@example.com' },
  update: { name: 'Alice Updated' },
  create: {
    email: 'alice@example.com',
    name: 'Alice New',
  },
});

// Delete
await prisma.user.delete({
  where: { id: 1 },
});

// Delete many
await prisma.post.deleteMany({
  where: { published: false },
});

Filtering & Relations

Prisma provides a powerful filtering API with support for logical operators, relation queries, and aggregation:

// Complex filtering
const posts = await prisma.post.findMany({
  where: {
    AND: [
      { published: true },
      {
        OR: [
          { title: { contains: 'prisma', mode: 'insensitive' } },
          { content: { contains: 'typescript', mode: 'insensitive' } },
        ],
      },
    ],
    author: {
      role: 'ADMIN',                  // filter by relation
    },
    createdAt: {
      gte: new Date('2024-01-01'),    // greater than or equal
    },
  },
  include: {
    author: {
      select: { name: true, email: true },
    },
    _count: {
      select: { comments: true },     // count related comments
    },
  },
  orderBy: [
    { createdAt: 'desc' },
  ],
  take: 10,
});

// Transaction
const [newUser, updatedPost] = await prisma.$transaction([
  prisma.user.create({ data: { email: 'new@example.com', name: 'New' } }),
  prisma.post.update({ where: { id: 1 }, data: { published: true } }),
]);

Advanced Patterns

Middleware

Prisma middleware lets you intercept queries before or after they execute. This is perfect for logging, soft deletes, and access control:

// Logging middleware
prisma.$use(async (params, next) => {
  const before = Date.now();
  const result = await next(params);
  const after = Date.now();
  console.log(
    `${params.model}.${params.action} took ${after - before}ms`
  );
  return result;
});

Soft Delete

Instead of permanently deleting records, mark them as deleted with a timestamp. Use middleware to automatically filter them out:

// Schema
model Post {
  id        Int       @id @default(autoincrement())
  title     String
  deletedAt DateTime? // null = not deleted
  // ...
}

// Soft delete middleware
prisma.$use(async (params, next) => {
  // Intercept delete -> update with deletedAt
  if (params.model === 'Post' && params.action === 'delete') {
    params.action = 'update';
    params.args['data'] = { deletedAt: new Date() };
  }

  // Intercept deleteMany -> updateMany
  if (params.model === 'Post' && params.action === 'deleteMany') {
    params.action = 'updateMany';
    if (params.args.data) {
      params.args.data['deletedAt'] = new Date();
    } else {
      params.args['data'] = { deletedAt: new Date() };
    }
  }

  // Filter out soft-deleted records on find
  if (params.model === 'Post' && params.action === 'findMany') {
    if (!params.args.where) params.args.where = {};
    params.args.where['deletedAt'] = null;
  }

  return next(params);
});

Multi-Tenancy

Add a tenantId field to every model and use Prisma middleware to automatically scope queries to the current tenant:

// Schema
model User {
  id       Int    @id @default(autoincrement())
  email    String
  tenantId String

  @@unique([tenantId, email])
  @@index([tenantId])
}

// Multi-tenancy middleware
function createTenantMiddleware(tenantId: string) {
  return prisma.$use(async (params, next) => {
    // Automatically inject tenantId on create
    if (params.action === 'create') {
      params.args.data.tenantId = tenantId;
    }

    // Automatically scope queries to tenant
    if (['findMany', 'findFirst', 'updateMany', 'deleteMany'].includes(params.action)) {
      if (!params.args.where) params.args.where = {};
      params.args.where.tenantId = tenantId;
    }

    return next(params);
  });
}

Raw SQL

When you need full control or complex queries that Prisma Client cannot express, use raw SQL:

// Tagged template for safe parameterized queries
const email = 'alice@example.com';
const user = await prisma.$queryRaw`
  SELECT * FROM "User" WHERE email = ${email}
`;

// Unparameterized raw query (use with caution)
const result = await prisma.$queryRawUnsafe(
  'SELECT * FROM "User" WHERE id = $1',
  1
);

// Execute raw SQL (INSERT, UPDATE, DELETE)
const affected = await prisma.$executeRaw`
  UPDATE "User" SET name = ${'Alice Smith'}
  WHERE email = ${email}
`;

// Raw SQL with type safety using Prisma.sql
import { Prisma } from '@prisma/client';

const orderBy = Prisma.sql`ORDER BY "createdAt" DESC`;
const users = await prisma.$queryRaw`
  SELECT id, email, name FROM "User"
  ${orderBy}
  LIMIT 10
`;

Prisma vs TypeORM vs Drizzle

Choosing the right ORM depends on your project needs. Here is a comparison of the three most popular TypeScript ORMs:

Frequently Asked Questions