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:

1. Prisma Schema: Data model definition (models, relations, attributes)
2. Prisma Migrate: Database migration workflow
3. 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):

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

Prisma 7+ (new format with runtime options):

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

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

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

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)

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

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

Referential Actions

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

Options: Cascade, Restrict, NoAction, SetNull, SetDefault

Prisma Client Queries

CRUD Operations

// 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

// 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

// 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

// 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

// 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

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

Interactive Transactions

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

// 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/:

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

Run prisma generate --sql then use:

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

Raw Queries

// 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

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

// 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

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

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

Error Handling

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)

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

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

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

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

1. Use a single PrismaClient instance across your application
2. Always use parameterized queries to prevent SQL injection
3. Use select to fetch only needed fields for performance
4. Use include sparingly; prefer select with nested relations
5. Use transactions for related operations that must succeed together
6. Always disconnect in scripts and tests
7. Use migrations for production; db push only in development
8. Handle PrismaClientKnownRequestError for user-friendly errors
9. Use indexes on frequently queried and filtered columns
10. 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