Prisma has revolutionized how we interact with databases in TypeScript applications. Unlike traditional ORMs that use classes and inheritance, Prisma generates a type-safe client based on your data model, providing an intuitive API tailored to your specific schema.

In this guide, we'll set up Prisma with PostgreSQL and explore best practices for building production applications.

Why Prisma?

Prisma provides three core components:

• Prisma Client - Auto-generated, type-safe query builder
• Prisma Migrate - Declarative schema migrations
• Prisma Studio - Visual database browser

The type safety is comprehensive - even when selecting subsets of fields, TypeScript knows exactly what shape your data will have.

Getting Started

Install Prisma and initialize your project:

# Install dependencies
npm install prisma @prisma/client
npm install -D typescript @types/node

# Initialize Prisma with PostgreSQL
npx prisma init --datasource-provider postgresql

Configure your database connection in .env:

DATABASE_URL="postgresql://user:password@localhost:5432/mydb"

Designing Your Schema

The Prisma schema is the single source of truth for your database structure:

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?
  role      Role     @default(USER)
  posts     Post[]
  profile   Profile?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([email])
}

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

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

  @@index([authorId])
  @@index([published])
}

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

enum Role {
  USER
  ADMIN
  MODERATOR
}

Best Practice: Use cuid() for IDs instead of auto-increment. CUIDs are URL-safe, don't reveal record counts, and work well in distributed systems.

Migrations

Prisma Migrate handles database schema changes:

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

# Generate Prisma Client
npx prisma generate

For existing databases, baseline your migrations:

# Generate SQL from current schema diff
npx prisma migrate diff \
  --from-empty \
  --to-schema-datamodel prisma/schema.prisma \
  --script > prisma/migrations/0_init/migration.sql

# Mark as applied
npx prisma migrate resolve --applied 0_init

Type-Safe Queries

The generated Prisma Client provides full type safety:

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;
}

Basic CRUD Operations

// Create a user with profile
const user = await prisma.user.create({
  data: {
    email: 'alice@example.com',
    name: 'Alice',
    profile: {
      create: {
        bio: 'Software Engineer',
      },
    },
  },
  include: {
    profile: true,
  },
});
// TypeScript knows: user.profile is Profile | null

// Get user with posts and categories
const userWithPosts = await prisma.user.findUnique({
  where: { email: 'alice@example.com' },
  include: {
    posts: {
      where: { published: true },
      include: { categories: true },
      orderBy: { createdAt: 'desc' },
      take: 10,
    },
  },
});

// Full type safety - posts is typed correctly
userWithPosts?.posts.forEach(post => {
  console.log(post.title, post.categories);
});

// Update user role
const updatedUser = await prisma.user.update({
  where: { id: userId },
  data: {
    role: 'ADMIN',
    updatedAt: new Date(),
  },
});

// Delete user (profile cascades due to onDelete: Cascade)
await prisma.user.delete({
  where: { id: userId },
});

Advanced Patterns

Transactions

const result = await prisma.$transaction(async (tx) => {
  // Decrement sender balance
  const sender = await tx.account.update({
    where: { id: senderId },
    data: { balance: { decrement: amount } },
  });

  if (sender.balance < 0) {
    throw new Error('Insufficient funds');
  }

  // Increment receiver balance
  const receiver = await tx.account.update({
    where: { id: receiverId },
    data: { balance: { increment: amount } },
  });

  return { sender, receiver };
});

Raw SQL with TypedSQL

Prisma's TypedSQL lets you write raw SQL with full type safety:

-- @param {String} $1:userId
SELECT
  u.id,
  u.name,
  COUNT(p.id) as "postCount",
  COUNT(CASE WHEN p.published THEN 1 END) as "publishedCount"
FROM "User" u
LEFT JOIN "Post" p ON p."authorId" = u.id
WHERE u.id = $1
GROUP BY u.id;

import { getUserStats } from '@prisma/client/sql';

const stats = await prisma.$queryRawTyped(getUserStats(userId));
// stats is fully typed based on SQL return columns

New in Prisma 6: TypedSQL generates TypeScript types from your .sql files, giving you raw SQL power with ORM-level type safety.

Production Best Practices

Connection Pooling

For serverless environments, use connection pooling:

import { PrismaClient } from '@prisma/client';
import { withAccelerate } from '@prisma/extension-accelerate';

const prisma = new PrismaClient().$extends(withAccelerate());

// Queries can now use caching
const users = await prisma.user.findMany({
  cacheStrategy: {
    ttl: 60, // Cache for 60 seconds
  },
});

Logging

const prisma = new PrismaClient({
  log: [
    { emit: 'event', level: 'query' },
    { emit: 'stdout', level: 'error' },
    { emit: 'stdout', level: 'warn' },
  ],
});

prisma.$on('query', (e) => {
  console.log(`Query: ${e.query}`);
  console.log(`Duration: ${e.duration}ms`);
});

Conclusion

Prisma transforms database development in TypeScript. With its declarative schema, automatic migrations, and type-safe client, you can focus on building features instead of wrestling with SQL and type definitions.

Start with the schema, let Prisma generate the client, and enjoy catching database errors at compile time instead of runtime.