Complete Guide to Prisma with PostgreSQL

Setting Up Prisma

pnpm add @prisma/client
pnpm add -D prisma

npx prisma init

Defining Your Schema

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

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

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

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

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

model Tag {
  id    String @id @default(cuid())
  name  String @unique
  posts Post[]
}

Database Migrations

# Create migration
npx prisma migrate dev --name init

# Apply to production
npx prisma migrate deploy

Prisma Client Setup

// lib/prisma.ts
import { PrismaClient } from "@prisma/client";

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

export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient({
    log: process.env.NODE_ENV === "development"
      ? ["query", "error", "warn"]
      : ["error"],
  });

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

CRUD Operations

Create

// Create a user with posts
const user = await prisma.user.create({
  data: {
    email: "alice@example.com",
    name: "Alice",
    posts: {
      create: [
        { title: "Hello World" },
        { title: "My Second Post" },
      ],
    },
  },
  include: {
    posts: true,
  },
});

Read

// Find with filters and relations
const posts = await prisma.post.findMany({
  where: {
    published: true,
    author: {
      email: {
        contains: "@example.com",
      },
    },
  },
  include: {
    author: {
      select: {
        name: true,
        email: true,
      },
    },
    tags: true,
  },
  orderBy: {
    createdAt: "desc",
  },
  take: 10,
});

Update

// Update with nested writes
const updated = await prisma.user.update({
  where: { id: userId },
  data: {
    name: "New Name",
    posts: {
      updateMany: {
        where: { published: false },
        data: { published: true },
      },
    },
  },
});

Delete

// Soft delete pattern
const deleted = await prisma.post.update({
  where: { id: postId },
  data: { deletedAt: new Date() },
});

// Hard delete
await prisma.post.delete({
  where: { id: postId },
});

Advanced Queries

Transactions

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

// Interactive transaction
await prisma.$transaction(async (tx) => {
  const user = await tx.user.findUnique({ where: { id } });
  if (!user) throw new Error("User not found");

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

Raw Queries

const result = await prisma.$queryRaw`
  SELECT * FROM "User" WHERE email LIKE ${`%@example.com`}
`;

Performance Tips

1. Use
   select
   to limit fields - Don't fetch unnecessary data
2. Use
   include
   sparingly - N+1 queries can be expensive
3. Index frequently queried fields - Add
   @@index
   to your schema
4. Use connection pooling - Essential for serverless

Conclusion