Complete Guide to Prisma with PostgreSQL

Prisma is a next-generation ORM that makes working with databases a joy. Combined with PostgreSQL, it provides a powerful foundation for any application.

Setting Up Prisma

Install the required packages:

Bash
2 linesCopy
1pnpm add @prisma/client
2pnpm add -D prisma

Initialize Prisma:

Bash
1 lineCopy
npx prisma init

This creates a

Defining Your Schema

PRISMA
43 linesCopy
1// prisma/schema.prisma
2generator client {
3  provider = "prisma-client-js"
4}
5
6datasource db {
7  provider = "postgresql"
8  url      = env("DATABASE_URL")
9}
10
11model User {
12  id        String   @id @default(cuid())
13  email     String   @unique
14  name      String?
15  posts     Post[]
16  profile   Profile?
17  createdAt DateTime @default(now())
18  updatedAt DateTime @updatedAt
19}
20
21model Profile {
22  id     String  @id @default(cuid())
23  bio    String?
24  user   User    @relation(fields: [userId], references: [id])
25  userId String  @unique
26}
27
28model Post {
29  id        String   @id @default(cuid())
30  title     String
31  content   String?
32  published Boolean  @default(false)
33  author    User     @relation(fields: [authorId], references: [id])
34  authorId  String
35  tags      Tag[]
36  createdAt DateTime @default(now())
37}
38
39model Tag {
40  id    String @id @default(cuid())
41  name  String @unique
42  posts Post[]
43}

Database Migrations

Create and apply migrations:

Bash
5 linesCopy
1# Create migration
2npx prisma migrate dev --name init
3
4# Apply to production
5npx prisma migrate deploy

Prisma Client Setup

Create a singleton client to prevent connection exhaustion:

TypeScript
18 linesCopy
1// lib/prisma.ts
2import { PrismaClient } from "@prisma/client";
3
4const globalForPrisma = globalThis as unknown as {
5  prisma: PrismaClient | undefined;
6};
7
8export const prisma =
9  globalForPrisma.prisma ??
10  new PrismaClient({
11    log: process.env.NODE_ENV === "development"
12      ? ["query", "error", "warn"]
13      : ["error"],
14  });
15
16if (process.env.NODE_ENV !== "production") {
17  globalForPrisma.prisma = prisma;
18}

CRUD Operations

Create

TypeScript
16 linesCopy
1// Create a user with posts
2const user = await prisma.user.create({
3  data: {
4    email: "alice@example.com",
5    name: "Alice",
6    posts: {
7      create: [
8        { title: "Hello World" },
9        { title: "My Second Post" },
10      ],
11    },
12  },
13  include: {
14    posts: true,
15  },
16});

Read

TypeScript
24 linesCopy
1// Find with filters and relations
2const posts = await prisma.post.findMany({
3  where: {
4    published: true,
5    author: {
6      email: {
7        contains: "@example.com",
8      },
9    },
10  },
11  include: {
12    author: {
13      select: {
14        name: true,
15        email: true,
16      },
17    },
18    tags: true,
19  },
20  orderBy: {
21    createdAt: "desc",
22  },
23  take: 10,
24});

Update

TypeScript
13 linesCopy
1// Update with nested writes
2const updated = await prisma.user.update({
3  where: { id: userId },
4  data: {
5    name: "New Name",
6    posts: {
7      updateMany: {
8        where: { published: false },
9        data: { published: true },
10      },
11    },
12  },
13});

Delete

TypeScript
10 linesCopy
1// Soft delete pattern
2const deleted = await prisma.post.update({
3  where: { id: postId },
4  data: { deletedAt: new Date() },
5});
6
7// Hard delete
8await prisma.post.delete({
9  where: { id: postId },
10});

Advanced Queries

Transactions

TypeScript
14 linesCopy
1const [user, post] = await prisma.$transaction([
2  prisma.user.create({ data: { email: "new@example.com" } }),
3  prisma.post.create({ data: { title: "New Post", authorId: "..." } }),
4]);
5
6// Interactive transaction
7await prisma.$transaction(async (tx) => {
8  const user = await tx.user.findUnique({ where: { id } });
9  if (!user) throw new Error("User not found");
10
11  await tx.post.create({
12    data: { title: "Post", authorId: user.id },
13  });
14});

Raw Queries

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

Performance Tips

1. Use
   CODE

   1 lineCopy

   select
   to limit fields - Don't fetch unnecessary data
2. Use
   CODE

   1 lineCopy

   include
   sparingly - N+1 queries can be expensive
3. Index frequently queried fields - Add
   CODE

   1 lineCopy

   @@index
   to your schema
4. Use connection pooling - Essential for serverless

Conclusion

Prisma with PostgreSQL provides a robust, type-safe database layer for your applications. The combination of schema-first design, automatic migrations, and excellent TypeScript support makes it a top choice for modern web development.