If you’ve ever used raw SQL queries or traditional ORMs like Sequelize, you probably know the pain of debugging queries that error at runtime. Typo in a column name? Error only discovered when the application runs.

Prisma takes a different approach: type-safe database access with auto-generated types from your schema. This means if there’s a typo or wrong query, TypeScript immediately warns you before the code runs.

What is an ORM?

ORM (Object-Relational Mapping) is an abstraction layer between your application and database. Instead of writing raw SQL:

SELECT * FROM users WHERE id = 1;

You can use more readable syntax:

const user = await prisma.user.findUnique({ where: { id: 1 } });

Why Prisma?

Prisma isn’t just any ORM. Here’s what makes Prisma stand out:

Feature	Prisma	Traditional ORM
Type Safety	100% type-safe	Partial or manual
Schema Definition	Prisma Schema Language	Decorators/JS Objects
Migrations	Automated & versioned	Manual or semi-auto
Query Building	Fluent API with autocomplete	String-based or builder
Tooling	Prisma Studio, CLI, VS Code extension	Varies

Installation and Setup

1. Init Project

mkdir prisma-tutorial && cd prisma-tutorial
npm init -y
npm install typescript ts-node @types/node -D
npx tsc --init

2. Install Prisma

npm install prisma -D
npm install @prisma/client

3. Initialize Prisma

npx prisma init

This will create:

prisma/schema.prisma - Main schema file
.env - Environment variables file

4. Configure Database

Edit .env with your database connection string:

# PostgreSQL
DATABASE_URL="postgresql://user:password@localhost:5432/mydb?schema=public"

# MySQL
DATABASE_URL="mysql://user:password@localhost:3306/mydb"

# SQLite (great for development)
DATABASE_URL="file:./dev.db"

Prisma Schema: The Heart of Prisma

The prisma/schema.prisma file is where you define your database structure:

// filepath: prisma/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?
  password  String
  role      Role     @default(USER)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  posts    Post[]
  profile  Profile?
  comments Comment[]
}

enum Role {
  USER
  ADMIN
  MODERATOR
}

Attribute Explanation

Attribute	Function
`@id`	Primary key
`@unique`	Value must be unique
`@default()`	Default value
`autoincrement()`	Auto increment for integer
`now()`	Timestamp when record is created
`@updatedAt`	Auto update timestamp when record is updated
`?`	Optional field (nullable)

Relations: 1:1, 1:N, and N:M

One-to-One (1:1)

User has one Profile:

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

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

One-to-Many (1:N)

User has many Posts:

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

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

Many-to-Many (N:M)

Post has many Categories, Category belongs to many Posts:

model Post {
  id         Int        @id @default(autoincrement())
  title      String
  categories Category[]
}

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

Prisma automatically creates a junction table. If you need an explicit junction table with additional fields:

model Post {
  id         Int            @id @default(autoincrement())
  title      String
  categories PostCategory[]
}

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

model PostCategory {
  postId     Int
  categoryId Int
  assignedAt DateTime @default(now())

  post     Post     @relation(fields: [postId], references: [id])
  category Category @relation(fields: [categoryId], references: [id])

  @@id([postId, categoryId])
}

Migrations

After the schema is ready, run migration:

# Development: Create and apply migration
npx prisma migrate dev --name init

# Production: Apply pending migrations
npx prisma migrate deploy

# Reset database (deletes all data!)
npx prisma migrate reset

Migration Tips

Always review generated SQL - Prisma generates SQL in the prisma/migrations/ folder
Don’t edit migration files - If you need to change, create a new migration
Use descriptive names - add_user_avatar is better than update_1

Generate Prisma Client

Every time the schema changes, regenerate the client:

npx prisma generate

This will generate types based on your schema.

CRUD Operations

Setup Prisma Client

// filepath: src/lib/prisma.ts
import { PrismaClient } from '@prisma/client';

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
};

export const prisma = globalForPrisma.prisma ?? new PrismaClient();

if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma;
}

This pattern prevents multiple PrismaClient instances during hot reload in development.

Create

// Create single record
const user = await prisma.user.create({
  data: {
    email: '[email protected]',
    name: 'John Doe',
    password: 'hashedpassword',
  },
});

// Create with nested relation
const userWithProfile = await prisma.user.create({
  data: {
    email: '[email protected]',
    name: 'Jane Doe',
    password: 'hashedpassword',
    profile: {
      create: {
        bio: 'Full-stack developer',
        github: 'janedoe',
      },
    },
  },
  include: {
    profile: true,
  },
});

// Create many
const users = await prisma.user.createMany({
  data: [
    { email: '[email protected]', name: 'User 1', password: 'hash1' },
    { email: '[email protected]', name: 'User 2', password: 'hash2' },
  ],
  skipDuplicates: true, // Skip if email already exists
});

Read

// Find by ID
const user = await prisma.user.findUnique({
  where: { id: 1 },
});

// Find by unique field
const userByEmail = await prisma.user.findUnique({
  where: { email: '[email protected]' },
});

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

// Find many
const allUsers = await prisma.user.findMany();

// Find with conditions
const activeAuthors = await prisma.user.findMany({
  where: {
    role: 'USER',
    posts: {
      some: {
        published: true,
      },
    },
  },
});

Update

// Update single
const updatedUser = await prisma.user.update({
  where: { id: 1 },
  data: { name: 'John Updated' },
});

// Update many
const deactivated = await prisma.user.updateMany({
  where: {
    lastLogin: {
      lt: new Date('2024-01-01'),
    },
  },
  data: {
    role: 'INACTIVE',
  },
});

// Upsert (update or create)
const user = await prisma.user.upsert({
  where: { email: '[email protected]' },
  update: { name: 'John Doe Updated' },
  create: {
    email: '[email protected]',
    name: 'John Doe',
    password: 'hashedpassword',
  },
});

Delete

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

// Delete many
const deletedCount = await prisma.user.deleteMany({
  where: {
    role: 'INACTIVE',
  },
});

Advanced Queries

Filtering

// Multiple conditions
const users = await prisma.user.findMany({
  where: {
    AND: [
      { email: { contains: '@gmail.com' } },
      { role: 'USER' },
    ],
  },
});

// OR conditions
const users = await prisma.user.findMany({
  where: {
    OR: [
      { role: 'ADMIN' },
      { role: 'MODERATOR' },
    ],
  },
});

// NOT condition
const regularUsers = await prisma.user.findMany({
  where: {
    NOT: { role: 'ADMIN' },
  },
});

Sorting and Pagination

const posts = await prisma.post.findMany({
  orderBy: [
    { publishedAt: 'desc' },
    { title: 'asc' },
  ],
  skip: 10, // Offset
  take: 5,  // Limit
});

Select and Include

// Select specific fields
const users = await prisma.user.findMany({
  select: {
    id: true,
    email: true,
    name: true,
  },
});

// Include relations
const postsWithAuthor = await prisma.post.findMany({
  include: {
    author: {
      select: {
        id: true,
        name: true,
      },
    },
    categories: true,
  },
});

Prisma Studio

Prisma Studio is a GUI for exploring and editing data:

npx prisma studio

It will open browser at http://localhost:5555. You can:

Browse all tables
View and edit records
Filter and sort data
View relations

Performance Tips

1. Use Select to Limit Fields

// ❌ Fetches all fields including password
const users = await prisma.user.findMany();

// ✅ Only fetch what's needed
const users = await prisma.user.findMany({
  select: {
    id: true,
    email: true,
    name: true,
  },
});

2. Limit Nested Includes

// ❌ Deep nesting = many JOINs = slow
const post = await prisma.post.findUnique({
  where: { id: 1 },
  include: {
    author: {
      include: {
        posts: {
          include: {
            comments: {
              include: {
                author: true,
              },
            },
          },
        },
      },
    },
  },
});

// ✅ Split into multiple queries if needed
const post = await prisma.post.findUnique({
  where: { id: 1 },
  include: { author: true },
});

const comments = await prisma.comment.findMany({
  where: { postId: 1 },
  include: { author: { select: { id: true, name: true } } },
  take: 20,
});

3. Use Indexes in Schema

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

  @@index([authorId])
  @@index([published])
  @@index([createdAt])
  @@index([published, createdAt]) // Composite index
}

4. Batch Operations

// ❌ Multiple round trips
for (const id of userIds) {
  await prisma.user.update({
    where: { id },
    data: { lastSeen: new Date() },
  });
}

// ✅ Single query
await prisma.user.updateMany({
  where: { id: { in: userIds } },
  data: { lastSeen: new Date() },
});

Best Practices

1. Project Structure

src/
├── lib/
│   └── prisma.ts          # Singleton Prisma client
├── repositories/          # Data access layer
│   ├── user.repository.ts
│   └── post.repository.ts
├── services/              # Business logic
│   ├── user.service.ts
│   └── post.service.ts
└── ...

prisma/
├── schema.prisma
├── seed.ts
└── migrations/

2. Repository Pattern

// filepath: src/repositories/user.repository.ts
import prisma from '@/lib/prisma';
import { Prisma } from '@prisma/client';

export const userRepository = {
  async findById(id: number) {
    return prisma.user.findUnique({
      where: { id },
      select: {
        id: true,
        email: true,
        name: true,
        role: true,
        profile: true,
      },
    });
  },

  async findByEmail(email: string) {
    return prisma.user.findUnique({
      where: { email },
    });
  },

  async create(data: Prisma.UserCreateInput) {
    return prisma.user.create({ data });
  },

  async update(id: number, data: Prisma.UserUpdateInput) {
    return prisma.user.update({
      where: { id },
      data,
    });
  },

  async delete(id: number) {
    return prisma.user.delete({
      where: { id },
    });
  },
};

3. Error Handling

import { Prisma } from '@prisma/client';

async function createUser(data: Prisma.UserCreateInput) {
  try {
    return await prisma.user.create({ data });
  } catch (error) {
    if (error instanceof Prisma.PrismaClientKnownRequestError) {
      // P2002: Unique constraint violation
      if (error.code === 'P2002') {
        throw new Error('Email already exists');
      }
      // P2025: Record not found
      if (error.code === 'P2025') {
        throw new Error('User not found');
      }
    }
    throw error;
  }
}

Conclusion

Prisma is a game-changer for database access in TypeScript:

Type safety - Errors detected at compile time
Developer experience - Autocomplete, Prisma Studio, excellent docs
Migrations - Versioned, automated, predictable
Performance - Query optimization, connection pooling support
Ecosystem - Prisma Accelerate, Pulse, and growing community

Start with a simple schema, understand relations and queries, then scale up with the best practices discussed.

Prisma ORM Tutorial: Setup and Best Practices for TypeScript