Prisma Helper Agent

What's New in Prisma 7+ (2025)

•New prisma-client generator: Replaces deprecated prisma-client-js with better ESM/Bun/Deno support
•All-TypeScript engine: Faster, lighter ORM without Rust binary engines
•TypedSQL: Write .sql files with full type safety (v5.19.0+)
•createManyAndReturn() and updateManyAndReturn(): Bulk operations returning results
•omit: Exclude fields from queries (opposite of select)
•Relation load strategies: Choose between join or query loading

Core Concepts

Prisma consists of three main components:

•Prisma Schema: Data model definition (models, relations, attributes)
•Prisma Migrate: Database migration workflow
•Prisma Client: Type-safe query builder

CLI Commands

Command	Purpose
`prisma init`	Initialize Prisma in project
`prisma generate`	Generate Prisma Client from schema
`prisma migrate dev --name <name>`	Create and apply migration (development)
`prisma migrate deploy`	Apply pending migrations (production)
`prisma db push`	Push schema to database without migration
`prisma db pull`	Introspect database and update schema
`prisma studio`	Open visual database editor
`prisma format`	Format schema file

Schema Definition

Generator Configuration

Prisma 5.x/6.x (current stable):

prisma

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

Prisma 7+ (new format with runtime options):

prisma

generator client {
  provider = "prisma-client"   // replaces "prisma-client-js"
  output   = "./generated/client"
  runtime  = "bun"             // nodejs, deno, bun, workerd
  moduleFormat = "esm"         // esm or cjs
}

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

Model Definition

prisma

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

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

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

  @@index([authorId])
}

Field Attributes

Attribute	Purpose	Example
`@id`	Primary key	`id Int @id`
`@@id`	Composite primary key	`@@id([a, b])`
`@unique`	Unique constraint	`email String @unique`
`@@unique`	Compound unique	`@@unique([firstName, lastName])`
`@default`	Default value	`@default(now())`, `@default(uuid())`
`@updatedAt`	Auto-update timestamp	`updatedAt DateTime @updatedAt`
`@relation`	Define relationship	See relations section
`@map`	Map to database column	`@map("user_name")`
`@@map`	Map to database table	`@@map("users")`
`@@index`	Database index	`@@index([title, content])`
`@ignore`	Exclude from Prisma Client	`legacyField String @ignore`

Default Value Functions

Function	Purpose
`autoincrement()`	Auto-incrementing integer
`uuid()` / `uuid(7)`	UUID generation
`cuid()` / `cuid(2)`	CUID generation
`ulid()`	ULID generation
`nanoid(length)`	Nano ID generation
`now()`	Current timestamp
`dbgenerated(expr)`	Database-level default

Relations

One-to-One

prisma

model User {
  id      Int      @id @default(autoincrement())
  profile Profile?
}

model Profile {
  id     Int  @id @default(autoincrement())
  userId Int  @unique
  user   User @relation(fields: [userId], references: [id])
}

One-to-Many

prisma

model User {
  id    Int    @id @default(autoincrement())
  posts Post[]
}

model Post {
  id       Int  @id @default(autoincrement())
  authorId Int
  author   User @relation(fields: [authorId], references: [id])
}

Many-to-Many (Implicit)

prisma

model Post {
  id         Int        @id @default(autoincrement())
  categories Category[]
}

model Category {
  id    Int    @id @default(autoincrement())
  posts Post[]
}

Referential Actions

prisma

@relation(fields: [authorId], references: [id], onDelete: Cascade, onUpdate: Cascade)

Options: Cascade, Restrict, NoAction, SetNull, SetDefault

Prisma Client Queries

CRUD Operations

typescript

// Create
const user = await prisma.user.create({
  data: { email: 'user@example.com', name: 'User' }
})

// Create with relation
const userWithPosts = await prisma.user.create({
  data: {
    email: 'user@example.com',
    posts: {
      create: [{ title: 'Post 1' }, { title: 'Post 2' }]
    }
  },
  include: { posts: true }
})

// Read
const user = await prisma.user.findUnique({ where: { id: 1 } })
const user = await prisma.user.findUniqueOrThrow({ where: { id: 1 } })
const users = await prisma.user.findMany({ where: { published: true } })
const first = await prisma.user.findFirst({ where: { name: { contains: 'John' } } })

// Update
const user = await prisma.user.update({
  where: { id: 1 },
  data: { name: 'Updated Name' }
})

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

// Delete
await prisma.user.delete({ where: { id: 1 } })

// Bulk operations
await prisma.user.createMany({ data: [...], skipDuplicates: true })
await prisma.user.updateMany({ where: {...}, data: {...} })
await prisma.user.deleteMany({ where: {...} })

Filtering

typescript

// Comparison operators
where: { age: { gt: 18, lte: 65 } }
where: { name: { contains: 'john', mode: 'insensitive' } }
where: { email: { startsWith: 'admin', endsWith: '.com' } }
where: { id: { in: [1, 2, 3] } }
where: { id: { notIn: [4, 5, 6] } }

// Logical operators
where: { OR: [{ email: { contains: 'a' } }, { name: { contains: 'b' } }] }
where: { AND: [{ published: true }, { authorId: 1 }] }
where: { NOT: { email: { contains: 'test' } } }

// Relation filters
where: { posts: { some: { published: true } } }
where: { posts: { every: { published: true } } }
where: { posts: { none: { published: true } } }

// Null filtering
where: { profile: null }
where: { profile: { isNot: null } }

Select and Include

typescript

// Select specific fields
const user = await prisma.user.findUnique({
  where: { id: 1 },
  select: { id: true, email: true, posts: { select: { title: true } } }
})

// Include relations
const user = await prisma.user.findUnique({
  where: { id: 1 },
  include: { posts: true, profile: true }
})

// Omit fields (exclude sensitive data)
const user = await prisma.user.findUnique({
  where: { id: 1 },
  omit: { password: true }
})

Pagination and Sorting

typescript

// Pagination
const users = await prisma.user.findMany({
  skip: 10,
  take: 20,
  orderBy: { createdAt: 'desc' }
})

// Cursor-based pagination
const users = await prisma.user.findMany({
  take: 10,
  cursor: { id: lastUserId },
  orderBy: { id: 'asc' }
})

// Multiple sort fields
orderBy: [{ lastName: 'asc' }, { firstName: 'asc' }]

// Sort by relation count
orderBy: { posts: { _count: 'desc' } }

// Null handling
orderBy: { name: { sort: 'asc', nulls: 'last' } }

Aggregation

typescript

// Aggregate
const result = await prisma.user.aggregate({
  _count: { _all: true },
  _avg: { age: true },
  _sum: { age: true },
  _min: { age: true },
  _max: { age: true }
})

// Group by
const grouped = await prisma.user.groupBy({
  by: ['country'],
  _count: { id: true },
  _avg: { age: true },
  having: { age: { _avg: { gt: 25 } } }
})

// Count relations
const user = await prisma.user.findUnique({
  where: { id: 1 },
  include: { _count: { select: { posts: true } } }
})

Transactions

Sequential Operations

typescript

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

Interactive Transactions

typescript

const result = await prisma.$transaction(async (tx) => {
  const user = await tx.user.findUnique({ where: { id: 1 } })
  if (!user) throw new Error('User not found')

  return tx.user.update({
    where: { id: 1 },
    data: { balance: { decrement: 100 } }
  })
}, {
  maxWait: 5000,
  timeout: 10000,
  isolationLevel: Prisma.TransactionIsolationLevel.Serializable
})

Atomic Operations

typescript

// Increment/decrement
await prisma.user.update({
  where: { id: 1 },
  data: { balance: { increment: 100 } }
})

// Multiply/divide
await prisma.product.update({
  where: { id: 1 },
  data: { price: { multiply: 1.1 } }
})

Raw SQL

TypedSQL (Recommended for v5.19.0+)

Create a .sql file in prisma/sql/:

sql

-- prisma/sql/findUsers.sql
SELECT id, email, name FROM users WHERE email LIKE $1

Run prisma generate --sql then use:

typescript

import { findUsers } from '@prisma/client/sql'
const users = await prisma.$queryRawTyped(findUsers('%@example.com'))

Raw Queries

typescript

// Parameterized query (safe)
const users = await prisma.$queryRaw`
  SELECT * FROM users WHERE email = ${email}
`

// Execute without return
await prisma.$executeRaw`
  UPDATE users SET name = ${name} WHERE id = ${id}
`

Connection Management

Best Practices

❌ Avoid: Creating new clients per request

typescript

app.get('/users', async (req, res) => {
  const prisma = new PrismaClient()  // Bad!
  const users = await prisma.user.findMany()
  await prisma.$disconnect()
  res.json(users)
})

✅ Prefer: Single shared instance

typescript

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

const globalForPrisma = globalThis as unknown as { prisma: PrismaClient }
export const prisma = globalForPrisma.prisma || new PrismaClient()

if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma

Explicit Connection/Disconnection

typescript

// For scripts and one-off operations
const prisma = new PrismaClient()

try {
  await prisma.$connect()
  // ... operations
} finally {
  await prisma.$disconnect()
}

Error Handling

typescript

import { Prisma } from '@prisma/client'

try {
  await prisma.user.create({ data: { email: 'existing@email.com' } })
} catch (e) {
  if (e instanceof Prisma.PrismaClientKnownRequestError) {
    if (e.code === 'P2002') {
      console.log('Unique constraint violation on:', e.meta?.target)
    }
  }
  throw e
}

Common error codes:

•P2002: Unique constraint violation
•P2003: Foreign key constraint violation
•P2025: Record not found

Testing

Unit Testing (Mocking)

typescript

import { mockDeep, DeepMockProxy } from 'jest-mock-extended'
import { PrismaClient } from '@prisma/client'

const prismaMock = mockDeep<PrismaClient>()

prismaMock.user.findUnique.mockResolvedValue({
  id: 1,
  email: 'test@example.com',
  name: 'Test User'
})

Integration Testing

typescript

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

beforeAll(async () => {
  await prisma.$connect()
})

afterAll(async () => {
  await prisma.$disconnect()
})

afterEach(async () => {
  // Clean up in correct order (respect foreign keys)
  await prisma.$transaction([
    prisma.post.deleteMany(),
    prisma.user.deleteMany()
  ])
})

Logging

typescript

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

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

Database Indexes

prisma

model Post {
  id      Int    @id
  title   String
  content String

  // Standard index
  @@index([title])

  // Composite index
  @@index([title, content])

  // PostgreSQL GIN index for full-text search
  @@index([content], type: Gin)

  // Full-text index (MySQL, MongoDB)
  @@fulltext([title, content])
}

Best Practices Summary

•Use a single PrismaClient instance across your application
•Always use parameterized queries to prevent SQL injection
•Use select to fetch only needed fields for performance
•Use include sparingly; prefer select with nested relations
•Use transactions for related operations that must succeed together
•Always disconnect in scripts and tests
•Use migrations for production; db push only in development
•Handle PrismaClientKnownRequestError for user-friendly errors
•Use indexes on frequently queried and filtered columns
•Keep environment variables for database URLs

When to Ask for Help

•Complex multi-tenant setups with row-level security
•Database-specific features not expressible in Prisma schema
•Performance optimization for very large datasets
•Unusual migration scenarios (data transformations, custom SQL)
•Prisma with non-standard database configurations