Introduction

Prisma is a next-generation ORM that makes database work simple and type-safe. With Prisma, you define your database schema in a declarative format and get automatic TypeScript types, migrations, and a powerful query API.

What is Prisma?

Prisma consists of:

  • Prisma Schema: Declarative schema definition
  • Prisma Client: Type-safe database client
  • Prisma Migrate: Database migrations
  • Prisma Studio: Visual database browser

Basic Schema Definition

Simple Schema

// schema.prisma
generator client {
  provider = "prisma-client-js"
}

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

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

Generated TypeScript Types

// Automatically generated
type User = {
  id: number;
  email: string;
  name: string | null;
  createdAt: Date;
  updatedAt: Date;
};

Field Types

Scalar Types

model Product {
  id          Int      @id @default(autoincrement())
  name        String
  price       Float
  inStock     Boolean  @default(true)
  description String?
  tags        String[]
  metadata    Json
  createdAt   DateTime @default(now())
}

Field Modifiers

model User {
  id       Int     @id              // Primary key
  email    String  @unique          // Unique constraint
  name     String?                  // Optional (nullable)
  posts    Post[]                   // One-to-many relation
  profile  Profile?                 // One-to-one relation (optional)
}

Relations

One-to-Many

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

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

Many-to-Many

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

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

One-to-One

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

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

Using Prisma Client

Basic Queries

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

// Create
const user = await prisma.user.create({
  data: {
    email: "alice@example.com",
    name: "Alice",
  },
});

// Read
const users = await prisma.user.findMany();
const user = await prisma.user.findUnique({
  where: { id: 1 },
});

// Update
const updatedUser = await prisma.user.update({
  where: { id: 1 },
  data: { name: "Alice Smith" },
});

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

Advanced Queries

// Include relations
const userWithPosts = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: true,
    profile: true,
  },
});

// Filtering
const activeUsers = await prisma.user.findMany({
  where: {
    email: {
      contains: "@example.com",
    },
    createdAt: {
      gte: new Date("2024-01-01"),
    },
  },
});

// Sorting and pagination
const users = await prisma.user.findMany({
  orderBy: { createdAt: "desc" },
  take: 10,
  skip: 20,
});

Migrations

Create Migration

npx prisma migrate dev --name add_user_table

This:

  1. Creates migration SQL
  2. Applies to database
  3. Regenerates Prisma Client

Migration Files

-- migrations/20241207120000_add_user_table/migration.sql
CREATE TABLE "User" (
    "id" SERIAL NOT NULL,
    "email" TEXT NOT NULL,
    "name" TEXT,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

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

CREATE UNIQUE INDEX "User_email_key" ON "User"("email");

Best Practices

✅ Do This

1. Use meaningful model names

model BlogPost { }  // ✅ Clear
model Bp { }        // ❌ Unclear

2. Add indexes for queries

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

  @@index([email, name])  // Composite index
}

3. Use enums for fixed values

enum UserRole {
  USER
  ADMIN
  MODERATOR
}

model User {
  id   Int      @id @default(autoincrement())
  role UserRole @default(USER)
}

4. Version control schema

  • Commit schema.prisma
  • Never edit migration files manually
  • Use prisma migrate dev for changes

❌ Don’t Do This

1. Don’t use reserved keywords

model User {
  type String  // ❌ 'type' is reserved
  class String // ❌ 'class' is reserved
}

2. Don’t forget indexes

// ❌ Slow queries
model Post {
  title String  // Searched often but no index
}

// ✅ Fast queries
model Post {
  title String

  @@index([title])
}

Converting from SQL

Use our tools:

Conclusion

Prisma simplifies database work with:

Benefits:

  • Type-safe queries
  • Automatic migrations
  • Great developer experience
  • Strong TypeScript integration

Key concepts:

  • Declarative schema
  • Relations are explicit
  • Migrations are versioned
  • Client is type-safe

Next Steps