Prisma

Type-safe database queries for TypeScript

What this skill does

Prisma is a next-generation TypeScript ORM that makes database access safe, productive, and enjoyable. It replaces traditional ORMs with a declarative schema-first approach, generating a fully type-safe client that prevents SQL injection and runtime errors at compile time.

Prisma consists of three core tools: Prisma Schema (declarative data modeling), Prisma Client (auto-generated type-safe query builder), and Prisma Migrate (declarative migration system). It supports PostgreSQL, MySQL, SQLite, SQL Server, and MongoDB, with additional services like connection pooling (Accelerate) and AI-powered query optimization (Optimize).

Developers choose Prisma for its exceptional TypeScript integration, intuitive API that feels like working with objects rather than SQL, and robust migration system that handles database schema evolution safely across environments.

Prerequisites

• Node.js 16.13.0 or higher
• TypeScript 4.7+ (recommended)
• Supported database: PostgreSQL, MySQL, SQLite, SQL Server, or MongoDB
• Database connection string or hosted database

Quick start

npm install prisma @prisma/client
npx prisma init

Define your schema in prisma/schema.prisma:

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

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

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
}

Generate client and run migration:

npx prisma migrate dev --name init
npx prisma generate

Query your database:

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

const user = await prisma.user.create({
  data: {
    name: 'Alice',
    email: 'alice@example.com',
    posts: {
      create: { title: 'Hello World' }
    }
  }
})

Core concepts

Prisma Schema: Single source of truth defining your data model, database connection, and client generator. Uses declarative syntax with models, fields, relations, and attributes.

Prisma Client: Auto-generated, type-safe database client that provides an intuitive API for CRUD operations, complex queries, and transactions. Regenerated whenever your schema changes.

Prisma Migrate: Declarative migration system that tracks schema changes and generates SQL migration files. Handles both development and production database evolution.

Relations: Define connections between models using @relation attributes. Prisma handles foreign keys automatically and provides nested queries for traversing relationships.

Introspection: Reverse-engineer existing databases into Prisma schemas using prisma db pull, enabling gradual adoption in existing projects.

Key API surface

CRUD Operations:

// Create
prisma.user.create({ data: { name: 'John', email: 'john@example.com' } })
prisma.user.createMany({ data: [{ name: 'Jane' }, { name: 'Bob' }] })

// Read
prisma.user.findUnique({ where: { id: 1 } })
prisma.user.findMany({ where: { published: true } })
prisma.user.findFirst({ orderBy: { createdAt: 'desc' } })

// Update
prisma.user.update({ where: { id: 1 }, data: { name: 'Updated' } })
prisma.user.updateMany({ where: { active: false }, data: { active: true } })

// Delete
prisma.user.delete({ where: { id: 1 } })
prisma.user.deleteMany({ where: { active: false } })

// Upsert
prisma.user.upsert({
  where: { email: 'user@example.com' },
  update: { name: 'Updated Name' },
  create: { email: 'user@example.com', name: 'New User' }
})

Query Options:

// Filtering
prisma.post.findMany({
  where: {
    AND: [{ published: true }, { title: { contains: 'Prisma' } }],
    author: { email: { endsWith: '@example.com' } }
  }
})

// Ordering and pagination
prisma.user.findMany({
  orderBy: { createdAt: 'desc' },
  skip: 10,
  take: 20
})

// Field selection
prisma.user.findMany({
  select: { id: true, email: true, posts: { select: { title: true } } }
})

// Nested includes
prisma.user.findUnique({
  where: { id: 1 },
  include: { posts: { where: { published: true } } }
})

Transactions:

// Sequential transaction
await prisma.$transaction([
  prisma.user.create({ data: { email: 'user1@example.com' } }),
  prisma.post.create({ data: { title: 'Post 1' } })
])

// Interactive transaction
await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({ data: { email: 'user@example.com' } })
  await tx.post.create({ data: { title: 'Post', authorId: user.id } })
})

Common patterns

Nested writes for creating related records:

const user = await prisma.user.create({
  data: {
    email: 'user@example.com',
    posts: {
      create: [
        { title: 'First Post', content: 'Hello World' },
        { title: 'Second Post', content: 'Learning Prisma' }
      ]
    }
  },
  include: { posts: true }
})

Complex filtering with logical operators:

const posts = await prisma.post.findMany({
  where: {
    OR: [
      { title: { contains: 'prisma', mode: 'insensitive' } },
      { content: { contains: 'database' } }
    ],
    AND: {
      published: true,
      author: { email: { not: null } }
    }
  }
})

Aggregations and grouping:

const stats = await prisma.post.aggregate({
  _count: { id: true },
  _avg: { views: true },
  where: { published: true }
})

const userPostCounts = await prisma.user.findMany({
  select: {
    id: true,
    email: true,
    _count: { select: { posts: { where: { published: true } } } }
  }
})

Raw queries when needed:

const result = await prisma.$queryRaw`
  SELECT u.email, COUNT(p.id) as post_count
  FROM "User" u
  LEFT JOIN "Post" p ON u.id = p."authorId"
  GROUP BY u.email
`

Connection management:

// Explicit connections
await prisma.$connect()
await prisma.$disconnect()

// Connection in serverless
export default async function handler(req, res) {
  try {
    const result = await prisma.user.findMany()
    res.json(result)
  } finally {
    await prisma.$disconnect()
  }
}

Configuration

Environment variables:

DATABASE_URL="postgresql://user:password@localhost:5432/mydb"
DIRECT_URL="postgresql://user:password@localhost:5432/mydb"  # For migrations

Schema configuration:

generator client {
  provider        = "prisma-client-js"
  output          = "./generated/client"
  binaryTargets   = ["native", "linux-musl"]
  previewFeatures = ["jsonProtocol"]
}

datasource db {
  provider     = "postgresql"
  url          = env("DATABASE_URL")
  directUrl    = env("DIRECT_URL")
  relationMode = "prisma"  # For PlanetScale
}

Field attributes:

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique @db.VarChar(255)
  createdAt DateTime @default(now()) @map("created_at")
  profile   Json?
  posts     Post[]

  @@map("users")
  @@index([email, createdAt])
}

Client options:

const prisma = new PrismaClient({
  log: ['query', 'info', 'warn', 'error'],
  errorFormat: 'pretty',
  datasources: {
    db: { url: process.env.DATABASE_URL }
  }
})

Best practices

1. Use transactions for related operations that must succeed or fail together
2. Select only needed fields with select to optimize query performance
3. Index frequently queried fields using @@index in your schema
4. Use connection pooling (Prisma Accelerate) in serverless environments
5. Separate read/write databases using directUrl for migrations
6. Version your schema changes with descriptive migration names
7. Use middleware for logging, soft deletes, or data transformation
8. Handle connection cleanup in serverless functions with $disconnect()
9. Use preview features cautiously and test thoroughly before production
10. Leverage relation filters instead of multiple queries when possible

Gotchas and common mistakes

Migration issues: Always backup production data before running migrations. Use prisma migrate resolve --applied <migration_name> if migrations get out of sync. Never edit migration files manually after they've been applied.

Connection pool exhaustion: Prisma Client maintains its own connection pool. In serverless environments, use a single global instance or connection pooling service to avoid exceeding database connection limits.

Case sensitivity: Database and field names are case-sensitive. Use @@map and @map to handle naming conventions between Prisma schema and database schema.

Relation mode confusion: When using relationMode = "prisma", foreign key constraints aren't enforced at the database level. You must maintain referential integrity in application code.

JSON field limitations: JSON operations have limited query capabilities. Consider separate tables for complex relational data instead of nested JSON.

N+1 query problem: Use include or select with nested relations instead of separate queries in loops. Prisma doesn't automatically solve N+1 problems.

Transaction timeout: Interactive transactions have a default 5-second timeout. Use { timeout: 10000 } option for longer-running transactions.

Schema drift: Running prisma db push bypasses migration history. Use prisma migrate dev in development and prisma migrate deploy in production.

Type generation timing: Always run prisma generate after schema changes. The generated types won't match your schema until regenerated.

Decimal precision: Use @db.Decimal(precision, scale) for financial calculations. JavaScript numbers lose precision with large decimal values.

Unique constraint violations: Prisma throws PrismaClientKnownRequestError with code: 'P2002' for unique constraint violations. Handle these explicitly.

Soft deletes: Prisma doesn't have built-in soft deletes. Implement using middleware or a deleted boolean field with default filtering.