Prisma ORM — Complete Guide

npm install @prisma/client
npm install -D prisma

npx prisma init

// prisma/schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

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

model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String?
  posts Post[]
  profile Profile?
  createdAt DateTime @default(now())
}

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

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())
}

# Create migration
npx prisma migrate dev --name add_users

# Apply migrations
npx prisma migrate deploy

# Reset database
npx prisma migrate reset

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 foundUser = await prisma.user.findUnique({
  where: { id: 1 },
});

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

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

// List all
const users = await prisma.user.findMany();

// List with pagination
const paged = await prisma.user.findMany({
  skip: 10,
  take: 10,
});

// Create with relation
const user = await prisma.user.create({
  data: {
    email: "alice@example.com",
    posts: {
      create: [
        { title: "First Post" },
        { title: "Second Post" },
      ],
    },
    profile: {
      create: {
        bio: "Developer",
      },
    },
  },
  include: {
    posts: true,
    profile: true,
  },
});

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

// Nested filter
const postsWithAuthor = await prisma.post.findMany({
  where: {
    author: {
      name: { contains: "Alice" },
    },
  },
  include: {
    author: true,
  },
});

// Filter by relation count
const usersWithMultiplePosts = await prisma.user.findMany({
  where: {
    posts: {
      some: {},
    },
  },
  include: {
    _count: {
      select: { posts: true },
    },
  },
});

// OR queries
const results = await prisma.user.findMany({
  where: {
    OR: [
      { name: { contains: "Alice" } },
      { email: { contains: "@example.com" } },
    ],
  },
});

// AND queries
const results2 = await prisma.user.findMany({
  where: {
    AND: [
      { name: { startsWith: "A" } },
      { email: { endsWith: ".com" } },
    ],
  },
});

// Distinct
const distinctEmails = await prisma.user.findMany({
  distinct: ["email"],
  select: { email: true },
});

// Ordering and limiting
const recent = await prisma.post.findMany({
  orderBy: { createdAt: "desc" },
  take: 10,
});

// Atomic transaction
const [user, post] = await prisma.$transaction([
  prisma.user.create({
    data: { email: "alice@example.com" },
  }),
  prisma.post.create({
    data: {
      title: "First Post",
      authorId: 1,
    },
  }),
]);

// Custom transaction
await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({
    data: { email: "alice@example.com" },
  });

  await tx.post.create({
    data: {
      title: "Welcome",
      authorId: user.id,
    },
  });
});

// Execute raw query
const result = await prisma.$queryRaw`
  SELECT * FROM "User" WHERE email = ${email}
`;

// Safe with parameters
const users = await prisma.$executeRaw`
  DELETE FROM "Post" WHERE "authorId" = ${userId}
`;

# Open GUI database browser
npx prisma studio

export class UserRepository {
  constructor(private prisma: PrismaClient) {}

  async findById(id: number) {
    return this.prisma.user.findUnique({
      where: { id },
      include: { posts: true },
    });
  }

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

  async create(data: { email: string; name?: string }) {
    return this.prisma.user.create({ data });
  }

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

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

  async getAll(skip = 0, take = 10) {
    return this.prisma.user.findMany({
      skip,
      take,
      include: {
        _count: { select: { posts: true } },
      },
    });
  }
}

// Soft deletes
model Post {
  id        Int     @id @default(autoincrement())
  title     String
  deletedAt DateTime?
}

const activePosts = await prisma.post.findMany({
  where: { deletedAt: null },
});

// Slugs
model Post {
  id   Int    @id @default(autoincrement())
  slug String @unique
  title String
}

const post = await prisma.post.findUnique({
  where: { slug: "my-post" },
});