Prisma ORM: Modern Database Access Guide

Introduction to Prisma

Prisma is a next-generation ORM that consists of the following tools:

• Prisma Client: Auto-generated, type-safe query builder for Node.js & TypeScript.
• Prisma Migrate: Migration system.
• Prisma Studio: GUI to view and edit data in your database.

The Schema File

Everything starts in schema.prisma:

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

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

Type-Safe Queries

Prisma generates a client based on your schema, giving you incredible autocomplete:

const user = await prisma.user.findUnique({
  where: { email: 'alice@prisma.io' },
  include: { posts: true }, // Typed result includes posts!
});

Advanced Prisma Patterns

Using Select for Partial Queries

Instead of fetching entire records, use select to only fetch the fields you need. This improves performance and reduces the data transferred:

// Select only specific fields
const userEmails = await prisma.user.findMany({
  select: {
    id: true,
    email: true,
    // Omit name and other fields
  },
})

// With relations - select nested fields
const usersWithPostTitles = await prisma.user.findMany({
  select: {
    name: true,
    posts: {
      select: {
        title: true,
        publishedDate: true,
        // Don't fetch full post content
      },
    },
  },
})

Filtering with Complex Conditions

Prisma's filter API makes building complex queries intuitive and type-safe:

// Complex filtering with AND, OR, NOT
const recentPublishedPosts = await prisma.post.findMany({
  where: {
    AND: [
      { published: true },
      { publishedDate: { gte: new Date('2024-01-01') } },
      {
        OR: [
          { title: { contains: 'TypeScript' } },
          { content: { contains: 'Prisma' } },
        ],
      },
    ],
  },
  orderBy: { publishedDate: 'desc' },
  take: 10,
})

// Finding posts without a specific tag
const postsWithoutPrisma = await prisma.post.findMany({
  where: {
    NOT: { tags: { has: 'Prisma' } },
  },
})

Relationships and Includes

Managing relationships is one of Prisma's strongest features. You can easily load related data with full type safety:

// One-to-many relationship
const userWithPosts = await prisma.user.findUnique({
  where: { id: userId },
  include: {
    posts: {
      select: { id: true, title: true },
      orderBy: { publishedDate: 'desc' },
      take: 5,
    },
  },
})

// Many-to-many relationships
model Post {
  id    Int     @id @default(autoincrement())
  title String
  tags  Tag[]
}

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

// Query with nested includes
const postsWithTags = await prisma.post.findMany({
  include: {
    tags: {
      select: { name: true },
    },
    author: {
      select: { name: true, email: true },
    },
  },
})

Prisma Migrations - Managing Schema Changes

Creating Your First Migration

Migrations track changes to your database schema over time. When you modify your schema, Prisma generates migration files:

# Create a new migration
npx prisma migrate dev --name add_user_role

# This will:
# 1. Create a migration file in prisma/migrations
# 2. Apply it to your dev database
# 3. Regenerate Prisma Client

Migration Files

Migration files contain SQL that describes exactly what changed. You can review and modify them before applying:

-- prisma/migrations/add_user_role/migration.sql
ALTER TABLE "User" ADD COLUMN "role" VARCHAR(255) NOT NULL DEFAULT 'user';
ALTER TABLE "User" CREATE INDEX "User_role_idx" on ("role");

Handling Migrations in Production

For production deployments, use migrate deploy which applies unapplied migrations without generating new ones:

# In CI/CD pipeline
npx prisma migrate deploy

# This applies pending migrations and is safe for production

Performance Optimization Techniques

Using Pagination

Always paginate large datasets to avoid loading too much data at once:

async function getPaginatedPosts(page: number, pageSize: number = 10) {
  const skip = (page - 1) * pageSize
  
  const [posts, total] = await Promise.all([
    prisma.post.findMany({
      skip,
      take: pageSize,
      orderBy: { publishedDate: 'desc' },
    }),
    prisma.post.count(),
  ])
  
  return {
    posts,
    pageInfo: {
      total,
      page,
      pageSize,
      pages: Math.ceil(total / pageSize),
    },
  }
}

Batch Operations

Use createMany and updateMany for bulk operations to reduce database round trips:

// Create multiple records efficiently
const newUsers = await prisma.user.createMany({
  data: [
    { email: 'alice@example.com', name: 'Alice' },
    { email: 'bob@example.com', name: 'Bob' },
    { email: 'charlie@example.com', name: 'Charlie' },
  ],
  skipDuplicates: true, // Skip if email already exists
})

// Update many records
const updated = await prisma.post.updateMany({
  where: { published: false },
  data: { published: true },
})

Raw Database Access

When Prisma's query API isn't enough, you can execute raw SQL while still getting type safety with prepared statements:

// Using $queryRaw for SELECT queries
const result = await prisma.$queryRaw`
  SELECT u.id, u.name, COUNT(p.id) as post_count
  FROM "User" u
  LEFT JOIN "Post" p ON u.id = p."authorId"
  WHERE u.email = ${userEmail}
  GROUP BY u.id
`

// Using $executeRaw for INSERT/UPDATE/DELETE
await prisma.$executeRaw`
  UPDATE "Post" SET "updatedAt" = NOW() 
  WHERE "authorId" = ${userId}
`

Debugging with Prisma Studio

Prisma Studio provides a visual interface for viewing and editing your database:

npx prisma studio

# Opens browser at http://localhost:5555
# Allows you to:
# - Browse all records
# - Create new records
# - Edit existing records
# - Delete records
# - Filter and search

Common Prisma Patterns

Soft Deletes

Instead of permanently deleting records, mark them as deleted:

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

// Soft delete a post
await prisma.post.update({
  where: { id: postId },
  data: { deletedAt: new Date() },
})

// Query active posts only
const activePosts = await prisma.post.findMany({
  where: { deletedAt: null },
})

Timestamps

Automatically track when records are created and updated:

model Post {
  id        Int     @id @default(autoincrement())
  title     String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

// updatedAt is automatically set on every update
const updated = await prisma.post.update({
  where: { id: postId },
  data: { title: 'New Title' },
  // updatedAt is automatically set to current time
})

Validation at the Schema Level

Use Prisma's validation features to enforce constraints at the database level:

model User {
  id    Int     @id @default(autoincrement())
  email String  @unique // Prevents duplicate emails
  name  String
  age   Int?
  posts Post[]
  
  // Database-level constraints
  @@index([email]) // For faster lookups
  @@unique([name, email]) // Multiple fields
}

Best Practices for Production

• Always use select or include: Only fetch the data you actually need.
• Implement pagination: Never load all records without pagination.
• Use database indexes: Index frequently queried fields for better performance.
• Test migrations thoroughly: Always test migrations on staging before deploying to production.
• Monitor slow queries: Use Prisma's query logging to identify slow queries.
• Use connection pooling: Configure connection pooling for production databases.
• Handle transactions carefully: Use Prisma transactions for operations that must succeed or fail together.

Troubleshooting Common Issues

Connection Issues

If Prisma can't connect to your database, check your DATABASE_URL environment variable and ensure the database is running.

Type Generation

If types aren't updating, regenerate Prisma Client:

npx prisma generate

Migration Conflicts

If your migration history gets out of sync with the database, reset your dev database and reapply migrations:

npx prisma migrate reset

Conclusion

Prisma combines the convenience of an ORM with the power of type safety and SQL clarity. Its intuitive API, excellent developer experience, and strong typing make it an excellent choice for modern Node.js applications. Whether you're building a small prototype or a large-scale application, Prisma's features scale with your needs. Start with simple queries, leverage type safety, and gradually adopt advanced patterns as your application grows.