Prisma Client Api

Prisma Client API Reference

Complete API reference for Prisma Client. This skill provides guidance on model queries, filtering, relations, and client methods for current Prisma projects.

When to Apply

Reference this skill when:

• Writing database queries with Prisma Client
• Performing CRUD operations (create, read, update, delete)
• Filtering and sorting data
• Working with relations
• Using transactions
• Configuring client options

Rule Categories by Priority

Quick Reference

• constructor - PrismaClient setup, adapter wiring, logging, and SQL commenter plugins
• model-queries - CRUD operations and bulk operations
• query-options - select, include, omit, sort, pagination
• filters - scalar and logical filter operators
• relations - relation reads and nested writes
• transactions - array and interactive transaction patterns
• raw-queries - $queryRaw and $executeRaw safety
• client-methods - lifecycle methods, extensions, and satisfies patterns for prisma-client

Client Instantiation

import { PrismaClient } from '../generated/client'
import { PrismaPg } from '@prisma/adapter-pg'

const adapter = new PrismaPg({
  connectionString: process.env.DATABASE_URL
})

const prisma = new PrismaClient({ adapter })

Model Query Methods

Query Options

Client Methods

Quick Examples

Find records

// Find by unique field
const user = await prisma.user.findUnique({
  where: { email: 'alice@prisma.io' }
})

// Find with filter
const users = await prisma.user.findMany({
  where: { role: 'ADMIN' },
  orderBy: { createdAt: 'desc' },
  take: 10
})

Create records

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

Update records

const user = await prisma.user.update({
  where: { id: 1 },
  data: { name: 'Alice Smith' }
})

Delete records

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

Transactions

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

Rule Files

Detailed API documentation:

references/constructor.md        - PrismaClient constructor options
references/model-queries.md      - CRUD operations
references/query-options.md      - select, include, omit, where, orderBy
references/filters.md            - Filter conditions and operators
references/relations.md          - Relation queries and nested operations
references/transactions.md       - Transaction API
references/raw-queries.md        - $queryRaw, $executeRaw
references/client-methods.md     - $connect, $disconnect, $on, $extends

Filter Operators

Relation Filters

Resources

• Prisma Client API Reference
• CRUD Operations
• Filtering and Sorting

How to Use

Pick the category from the table above, then open the matching reference file for implementation details and examples.

Client Methods

Prisma Client instance methods.

$connect()

Explicitly connect to the database:

const prisma = new PrismaClient({ adapter })

// Explicit connection
await prisma.$connect()

When to use

Usually not needed - Prisma connects automatically on first query. Use for:

• Fail fast on startup
• Health checks
• Pre-warming connections

async function main() {
  try {
    await prisma.$connect()
    console.log('Database connected')
  } catch (e) {
    console.error('Failed to connect:', e)
    process.exit(1)
  }
}

$disconnect()

Close database connection:

await prisma.$disconnect()

Graceful shutdown

process.on('beforeExit', async () => {
  await prisma.$disconnect()
})

// Or with SIGTERM
process.on('SIGTERM', async () => {
  await prisma.$disconnect()
  process.exit(0)
})

In tests

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

$on()

Subscribe to events:

Query events

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

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

Log events

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

prisma.$on('info', (e) => console.log(e.message))
prisma.$on('warn', (e) => console.warn(e.message))
prisma.$on('error', (e) => console.error(e.message))

$extends()

Add extensions for custom behavior:

Add custom methods

const prisma = new PrismaClient({ adapter }).$extends({
  client: {
    $log: (message: string) => console.log(message)
  }
})

prisma.$log('Hello!')

Add model methods

const prisma = new PrismaClient({ adapter }).$extends({
  model: {
    user: {
      async findByEmail(email: string) {
        return prisma.user.findUnique({ where: { email } })
      }
    }
  }
})

const user = await prisma.user.findByEmail('alice@prisma.io')

Query extensions

const prisma = new PrismaClient({ adapter }).$extends({
  query: {
    user: {
      async findMany({ args, query }) {
        // Add default filter
        args.where = { ...args.where, deletedAt: null }
        return query(args)
      }
    }
  }
})

Result extensions

const prisma = new PrismaClient({ adapter }).$extends({
  result: {
    user: {
      fullName: {
        needs: { firstName: true, lastName: true },
        compute(user) {
          return `${user.firstName} ${user.lastName}`
        }
      }
    }
  }
})

const user = await prisma.user.findFirst()
console.log(user.fullName) // Computed field

Chain extensions

const prisma = new PrismaClient({ adapter })
  .$extends(loggingExtension)
  .$extends(softDeleteExtension)
  .$extends(computedFieldsExtension)

$transaction()

See transactions.md for details.

$queryRaw() / $executeRaw()

See raw-queries.md for details.

Type utilities

Prisma namespace

import { Prisma } from '../generated/client'

// Input types
type UserCreateInput = Prisma.UserCreateInput
type UserWhereInput = Prisma.UserWhereInput

// Output types
type User = Prisma.UserGetPayload<{}>
type UserWithPosts = Prisma.UserGetPayload<{
  include: { posts: true }
}>

Type-safe query fragments with satisfies

Type-safe query fragments:

import { Prisma } from '../generated/client'

const userSelect = {
  id: true,
  email: true,
  name: true
} satisfies Prisma.UserSelect

const user = await prisma.user.findUnique({
  where: { id: 1 },
  select: userSelect
})

With the prisma-client generator, use TypeScript satisfies for typed query fragments. You may still see older examples that use Prisma.validator() with prisma-client-js.

PrismaClient Constructor

Configure Prisma Client when instantiating.

Basic Instantiation

import { PrismaClient } from '../generated/client'
import { PrismaPg } from '@prisma/adapter-pg'

const adapter = new PrismaPg({
  connectionString: process.env.DATABASE_URL
})

const prisma = new PrismaClient({ adapter })

Constructor Options

adapter (Required for the SQL provider workflow)

Driver adapter instance:

import { PrismaPg } from '@prisma/adapter-pg'

const adapter = new PrismaPg({
  connectionString: process.env.DATABASE_URL
})

const prisma = new PrismaClient({ adapter })

accelerateUrl (For Accelerate users)

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

const prisma = new PrismaClient({
  accelerateUrl: process.env.DATABASE_URL,  // prisma:// URL
}).$extends(withAccelerate())

log

Configure logging:

const prisma = new PrismaClient({
  adapter,
  log: ['query', 'info', 'warn', 'error'],
})

Log levels

Log to events

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

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

errorFormat

Control error formatting:

const prisma = new PrismaClient({
  adapter,
  errorFormat: 'pretty',  // 'pretty' | 'colorless' | 'minimal'
})

comments

Attach SQL commenter plugins for observability, tracing, or query insights:

import { PrismaClient } from '../generated/client'
import { PrismaPg } from '@prisma/adapter-pg'
import { prismaQueryInsights } from '@prisma/sqlcommenter-query-insights'
import { queryTags, withQueryTags } from '@prisma/sqlcommenter-query-tags'
import { traceContext } from '@prisma/sqlcommenter-trace-context'

const prisma = new PrismaClient({
  adapter: new PrismaPg(process.env.DATABASE_URL!),
  comments: [prismaQueryInsights(), traceContext(), queryTags()],
})

await withQueryTags({ route: '/api/users', requestId: 'req-123' }, () =>
  prisma.user.findMany(),
)

Use comments only for SQL providers. This is the clean way to add trace or query-shape metadata without changing your query calls.

transactionOptions

Default transaction settings:

const prisma = new PrismaClient({
  adapter,
  transactionOptions: {
    maxWait: 5000,      // Max wait to acquire transaction (ms)
    timeout: 10000,     // Max transaction duration (ms)
    isolationLevel: 'Serializable',
  },
})

Singleton Pattern

Prevent multiple client instances in development:

// lib/prisma.ts
import { PrismaClient } from '../generated/client'
import { PrismaPg } from '@prisma/adapter-pg'

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined
}

function createPrismaClient() {
  const adapter = new PrismaPg({
    connectionString: process.env.DATABASE_URL!
  })
  return new PrismaClient({ adapter })
}

export const prisma = globalForPrisma.prisma ?? createPrismaClient()

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

Next.js Pattern

// lib/prisma.ts
import { PrismaClient } from '@/generated/client'
import { PrismaPg } from '@prisma/adapter-pg'

const createAdapter = () => new PrismaPg({
  connectionString: process.env.DATABASE_URL!
})

const prismaClientSingleton = () => {
  return new PrismaClient({ adapter: createAdapter() })
}

declare const globalThis: {
  prismaGlobal: ReturnType<typeof prismaClientSingleton>
} & typeof global

const prisma = globalThis.prismaGlobal ?? prismaClientSingleton()

export default prisma

if (process.env.NODE_ENV !== 'production') {
  globalThis.prismaGlobal = prisma
}

Query Events

Listen to query events:

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

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

Log Events

prisma.$on('info', (e) => console.log(e.message))
prisma.$on('warn', (e) => console.warn(e.message))
prisma.$on('error', (e) => console.error(e.message))

Filter Conditions and Operators

Filter operators for the where clause.

Equality

// Exact match (implicit)
where: { email: 'alice@prisma.io' }

// Explicit equals
where: { email: { equals: 'alice@prisma.io' } }

// Not equal
where: { email: { not: 'alice@prisma.io' } }

Comparison

// Greater than
where: { age: { gt: 18 } }

// Greater than or equal
where: { age: { gte: 18 } }

// Less than
where: { age: { lt: 65 } }

// Less than or equal
where: { age: { lte: 65 } }

// Combined
where: { age: { gte: 18, lte: 65 } }

Lists

// In array
where: { role: { in: ['ADMIN', 'MODERATOR'] } }

// Not in array
where: { role: { notIn: ['GUEST', 'BANNED'] } }

String Filters

// Contains
where: { email: { contains: 'prisma' } }

// Starts with
where: { email: { startsWith: 'alice' } }

// Ends with
where: { email: { endsWith: '@prisma.io' } }

// Case-insensitive (default for some databases)
where: { 
  email: { 
    contains: 'PRISMA',
    mode: 'insensitive' 
  } 
}

Null Checks

// Is null
where: { deletedAt: null }

// Is not null
where: { deletedAt: { not: null } }

// Using isSet (for optional fields)
where: { middleName: { isSet: true } }

Logical Operators

AND (implicit)

// Multiple conditions = AND
where: {
  email: { contains: '@prisma.io' },
  role: 'ADMIN'
}

AND (explicit)

where: {
  AND: [
    { email: { contains: '@prisma.io' } },
    { role: 'ADMIN' }
  ]
}

OR

where: {
  OR: [
    { email: { contains: '@gmail.com' } },
    { email: { contains: '@prisma.io' } }
  ]
}

NOT

where: {
  NOT: {
    role: 'GUEST'
  }
}

// Multiple NOT conditions
where: {
  NOT: [
    { role: 'GUEST' },
    { verified: false }
  ]
}

Combined

where: {
  AND: [
    { verified: true },
    {
      OR: [
        { role: 'ADMIN' },
        { role: 'MODERATOR' }
      ]
    }
  ],
  NOT: { deletedAt: { not: null } }
}

Relation Filters

some

At least one related record matches:

// Users with at least one published post
where: {
  posts: {
    some: { published: true }
  }
}

every

All related records match:

// Users where all posts are published
where: {
  posts: {
    every: { published: true }
  }
}

none

No related records match:

// Users with no published posts
where: {
  posts: {
    none: { published: true }
  }
}

is / isNot (1-to-1)

// Users with profile in specific country
where: {
  profile: {
    is: { country: 'USA' }
  }
}

// Users without profile
where: {
  profile: {
    isNot: null
  }
}

Array Field Filters

For fields like String[]:

// Has element
where: { tags: { has: 'typescript' } }

// Has some elements
where: { tags: { hasSome: ['typescript', 'javascript'] } }

// Has every element
where: { tags: { hasEvery: ['typescript', 'prisma'] } }

// Is empty
where: { tags: { isEmpty: true } }

JSON Filters

// Path-based filter
where: {
  metadata: {
    path: ['settings', 'theme'],
    equals: 'dark'
  }
}

// String contains in JSON
where: {
  metadata: {
    path: ['bio'],
    string_contains: 'developer'
  }
}

Full-Text Search

// Requires @@fulltext index
where: {
  content: {
    search: 'prisma database'
  }
}

Model Queries

CRUD operations for your Prisma models.

Read Operations

findUnique

Find a single record by unique field:

const user = await prisma.user.findUnique({
  where: { id: 1 }
})

const user = await prisma.user.findUnique({
  where: { email: 'alice@prisma.io' }
})

With composite unique key

// Model with @@unique([firstName, lastName])
const user = await prisma.user.findUnique({
  where: {
    firstName_lastName: {
      firstName: 'Alice',
      lastName: 'Smith'
    }
  }
})

findUniqueOrThrow

Same as findUnique but throws if not found:

const user = await prisma.user.findUniqueOrThrow({
  where: { id: 1 }
})
// Throws PrismaClientKnownRequestError if not found

findFirst

Find first matching record:

const user = await prisma.user.findFirst({
  where: { role: 'ADMIN' },
  orderBy: { createdAt: 'desc' }
})

findFirstOrThrow

const user = await prisma.user.findFirstOrThrow({
  where: { role: 'ADMIN' }
})

findMany

Find multiple records:

const users = await prisma.user.findMany({
  where: { role: 'USER' },
  orderBy: { name: 'asc' },
  take: 10,
  skip: 0
})

Create Operations

create

Create a single record:

const user = await prisma.user.create({
  data: {
    email: 'alice@prisma.io',
    name: 'Alice'
  }
})

With relations

const user = await prisma.user.create({
  data: {
    email: 'alice@prisma.io',
    posts: {
      create: [
        { title: 'First Post' },
        { title: 'Second Post' }
      ]
    }
  },
  include: { posts: true }
})

createMany

Create multiple records:

const result = await prisma.user.createMany({
  data: [
    { email: 'alice@prisma.io', name: 'Alice' },
    { email: 'bob@prisma.io', name: 'Bob' }
  ],
  skipDuplicates: true  // Skip records with duplicate unique fields
})
// Returns { count: 2 }

createManyAndReturn

Create multiple and return them:

const users = await prisma.user.createManyAndReturn({
  data: [
    { email: 'alice@prisma.io', name: 'Alice' },
    { email: 'bob@prisma.io', name: 'Bob' }
  ]
})
// Returns array of created users

Update Operations

update

Update a single record:

const user = await prisma.user.update({
  where: { id: 1 },
  data: { name: 'Alice Smith' }
})

Atomic operations

const post = await prisma.post.update({
  where: { id: 1 },
  data: {
    views: { increment: 1 },
    likes: { decrement: 1 },
    score: { multiply: 2 },
    rating: { divide: 2 },
    version: { set: 5 }
  }
})

updateMany

Update multiple records:

const result = await prisma.user.updateMany({
  where: { role: 'USER' },
  data: { verified: true }
})
// Returns { count: 42 }

updateManyAndReturn

const users = await prisma.user.updateManyAndReturn({
  where: { role: 'USER' },
  data: { verified: true }
})
// Returns array of updated users

upsert

Update or create:

const user = await prisma.user.upsert({
  where: { email: 'alice@prisma.io' },
  update: { name: 'Alice Smith' },
  create: { email: 'alice@prisma.io', name: 'Alice' }
})

Delete Operations

delete

Delete a single record:

const user = await prisma.user.delete({
  where: { id: 1 }
})
// Returns deleted record

deleteMany

Delete multiple records:

const result = await prisma.user.deleteMany({
  where: { role: 'GUEST' }
})
// Returns { count: 5 }

// Delete all
const result = await prisma.user.deleteMany({})

Aggregation Operations

count

const count = await prisma.user.count({
  where: { role: 'ADMIN' }
})

aggregate

const result = await prisma.post.aggregate({
  _avg: { views: true },
  _sum: { views: true },
  _min: { views: true },
  _max: { views: true },
  _count: { _all: true }
})

groupBy

const groups = await prisma.user.groupBy({
  by: ['country'],
  _count: { _all: true },
  _avg: { age: true },
  having: {
    age: { _avg: { gt: 30 } }
  }
})

Return Types

Query Options

Options for controlling query behavior.

select

Choose specific fields to return:

const user = await prisma.user.findUnique({
  where: { id: 1 },
  select: {
    id: true,
    name: true,
    email: true,
    // password: false (excluded by not including)
  }
})
// Returns: { id: 1, name: 'Alice', email: 'alice@prisma.io' }

Select relations

const user = await prisma.user.findUnique({
  where: { id: 1 },
  select: {
    name: true,
    posts: {
      select: {
        title: true,
        published: true
      }
    }
  }
})

Select with include inside

const user = await prisma.user.findMany({
  select: {
    name: true,
    posts: {
      include: {
        comments: true
      }
    }
  }
})

Select relation count

const users = await prisma.user.findMany({
  select: {
    name: true,
    _count: {
      select: { posts: true }
    }
  }
})
// Returns: { name: 'Alice', _count: { posts: 5 } }

include

Include related records:

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

Filtered include

const user = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' },
      take: 5
    }
  }
})

Nested include

const user = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: {
      include: {
        comments: {
          include: {
            author: true
          }
        }
      }
    }
  }
})

Include relation count

const users = await prisma.user.findMany({
  include: {
    _count: {
      select: { posts: true, followers: true }
    }
  }
})

omit

Exclude specific fields:

const user = await prisma.user.findUnique({
  where: { id: 1 },
  omit: {
    password: true
  }
})
// Returns all fields except password

Omit in relations

const users = await prisma.user.findMany({
  omit: { password: true },
  include: {
    posts: {
      omit: { content: true }
    }
  }
})

Note: Cannot use select and omit together.

where

Filter records:

const users = await prisma.user.findMany({
  where: {
    email: { contains: '@prisma.io' },
    role: 'ADMIN'
  }
})

See filters.md for detailed filter operators.

orderBy

Sort results:

// Single field
const users = await prisma.user.findMany({
  orderBy: { name: 'asc' }
})

// Multiple fields
const users = await prisma.user.findMany({
  orderBy: [
    { role: 'desc' },
    { name: 'asc' }
  ]
})

Order by relation

const users = await prisma.user.findMany({
  orderBy: {
    posts: { _count: 'desc' }
  }
})

Null handling

const users = await prisma.user.findMany({
  orderBy: {
    name: { sort: 'asc', nulls: 'last' }
  }
})

take & skip

Pagination:

// First page
const users = await prisma.user.findMany({
  take: 10,
  skip: 0
})

// Second page
const users = await prisma.user.findMany({
  take: 10,
  skip: 10
})

Negative take (reverse)

const lastUsers = await prisma.user.findMany({
  take: -10,
  orderBy: { id: 'asc' }
})
// Returns last 10 users

cursor

Cursor-based pagination:

// First page
const firstPage = await prisma.user.findMany({
  take: 10,
  orderBy: { id: 'asc' }
})

// Next page using cursor
const nextPage = await prisma.user.findMany({
  take: 10,
  skip: 1,  // Skip the cursor record
  cursor: { id: firstPage[firstPage.length - 1].id },
  orderBy: { id: 'asc' }
})

distinct

Return unique values:

const cities = await prisma.user.findMany({
  distinct: ['city'],
  select: { city: true }
})

Multiple distinct fields

const locations = await prisma.user.findMany({
  distinct: ['city', 'country']
})

Raw Queries

Execute raw SQL when Prisma's query API isn't sufficient.

$queryRaw

Execute SELECT queries and get typed results:

const users = await prisma.$queryRaw`
  SELECT * FROM "User" WHERE email LIKE ${'%@prisma.io'}
`

With type

type User = { id: number; email: string; name: string | null }

const users = await prisma.$queryRaw<User[]>`
  SELECT id, email, name FROM "User" WHERE role = ${'ADMIN'}
`

Dynamic table/column names

Use Prisma.raw() for identifiers (not safe for user input):

import { Prisma } from '../generated/client'

const column = 'email'
const users = await prisma.$queryRaw`
  SELECT ${Prisma.raw(column)} FROM "User"
`

With Prisma.sql

Build queries dynamically:

import { Prisma } from '../generated/client'

const email = 'alice@prisma.io'
const query = Prisma.sql`SELECT * FROM "User" WHERE email = ${email}`
const users = await prisma.$queryRaw(query)

Join multiple SQL fragments

import { Prisma } from '../generated/client'

const conditions = [
  Prisma.sql`role = ${'ADMIN'}`,
  Prisma.sql`verified = ${true}`
]

const users = await prisma.$queryRaw`
  SELECT * FROM "User" 
  WHERE ${Prisma.join(conditions, ' AND ')}
`

$executeRaw

Execute INSERT, UPDATE, DELETE (returns affected count):

const count = await prisma.$executeRaw`
  UPDATE "User" SET verified = true WHERE email LIKE ${'%@prisma.io'}
`
console.log(`Updated ${count} users`)

Delete example

const deleted = await prisma.$executeRaw`
  DELETE FROM "User" WHERE "deletedAt" < ${thirtyDaysAgo}
`

Insert example

const inserted = await prisma.$executeRaw`
  INSERT INTO "Log" (message, level, timestamp)
  VALUES (${message}, ${level}, ${new Date()})
`

$queryRawUnsafe / $executeRawUnsafe

For fully dynamic queries (use with caution!):

// ⚠️ SQL injection risk - only use with trusted input
const table = 'User'
const users = await prisma.$queryRawUnsafe(
  `SELECT * FROM "${table}" WHERE id = $1`,
  userId
)

Parameterized unsafe query

const result = await prisma.$executeRawUnsafe(
  'UPDATE "User" SET name = $1 WHERE id = $2',
  'Alice',
  1
)

SQL Injection Prevention

Safe (parameterized)

// ✅ User input is parameterized
const email = userInput
const users = await prisma.$queryRaw`
  SELECT * FROM "User" WHERE email = ${email}
`

Unsafe (concatenation)

// ❌ SQL injection vulnerability!
const email = userInput
const users = await prisma.$queryRawUnsafe(
  `SELECT * FROM "User" WHERE email = '${email}'`
)

Database-Specific Features

PostgreSQL

// Array operations
const users = await prisma.$queryRaw`
  SELECT * FROM "User" WHERE 'admin' = ANY(roles)
`

// JSON operations
const users = await prisma.$queryRaw`
  SELECT * FROM "User" WHERE metadata->>'theme' = 'dark'
`

MySQL

// Full-text search
const posts = await prisma.$queryRaw`
  SELECT * FROM Post WHERE MATCH(title, content) AGAINST(${searchTerm})
`

Transactions with Raw Queries

await prisma.$transaction(async (tx) => {
  await tx.$executeRaw`UPDATE "Account" SET balance = balance - ${amount} WHERE id = ${senderId}`
  await tx.$executeRaw`UPDATE "Account" SET balance = balance + ${amount} WHERE id = ${recipientId}`
})

Handling Results

BigInt handling

PostgreSQL returns BigInt for COUNT:

const result = await prisma.$queryRaw<[{ count: bigint }]>`
  SELECT COUNT(*) as count FROM "User"
`
const count = Number(result[0].count)

Date handling

type Result = { createdAt: Date }
const users = await prisma.$queryRaw<Result[]>`
  SELECT "createdAt" FROM "User"
`
// createdAt is already a Date object

Relation Queries

Query and modify related records.

Include Relations

Load related records:

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

Filtered include

const user = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' },
      take: 5,
      select: { id: true, title: true }
    }
  }
})

Nested include

const user = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: {
      include: {
        comments: {
          include: { author: true }
        }
      }
    }
  }
})

Select Relations

const user = await prisma.user.findUnique({
  where: { id: 1 },
  select: {
    name: true,
    posts: {
      select: { title: true }
    }
  }
})

Nested Writes

Create with relations

const user = await prisma.user.create({
  data: {
    email: 'alice@prisma.io',
    posts: {
      create: [
        { title: 'Post 1' },
        { title: 'Post 2' }
      ]
    },
    profile: {
      create: { bio: 'Hello!' }
    }
  }
})

Create or connect

const post = await prisma.post.create({
  data: {
    title: 'New Post',
    author: {
      connectOrCreate: {
        where: { email: 'alice@prisma.io' },
        create: { email: 'alice@prisma.io', name: 'Alice' }
      }
    }
  }
})

Connect existing

const post = await prisma.post.create({
  data: {
    title: 'New Post',
    author: {
      connect: { id: 1 }
    }
  }
})

// Shorthand for foreign key
const post = await prisma.post.create({
  data: {
    title: 'New Post',
    authorId: 1
  }
})

Update Relations

Update related records

const user = await prisma.user.update({
  where: { id: 1 },
  data: {
    posts: {
      update: {
        where: { id: 1 },
        data: { title: 'Updated Title' }
      }
    }
  }
})

Update many related

const user = await prisma.user.update({
  where: { id: 1 },
  data: {
    posts: {
      updateMany: {
        where: { published: false },
        data: { published: true }
      }
    }
  }
})

Upsert related

const user = await prisma.user.update({
  where: { id: 1 },
  data: {
    profile: {
      upsert: {
        create: { bio: 'New bio' },
        update: { bio: 'Updated bio' }
      }
    }
  }
})

Disconnect

// 1-to-1 optional
const user = await prisma.user.update({
  where: { id: 1 },
  data: {
    profile: { disconnect: true }
  }
})

// Many-to-many
const post = await prisma.post.update({
  where: { id: 1 },
  data: {
    tags: {
      disconnect: [{ id: 1 }, { id: 2 }]
    }
  }
})

Delete related

const user = await prisma.user.update({
  where: { id: 1 },
  data: {
    posts: {
      delete: { id: 1 }
    }
  }
})

// Delete many
const user = await prisma.user.update({
  where: { id: 1 },
  data: {
    posts: {
      deleteMany: { published: false }
    }
  }
})

Set (replace all)

// Replace all related records
const post = await prisma.post.update({
  where: { id: 1 },
  data: {
    tags: {
      set: [{ id: 1 }, { id: 2 }]
    }
  }
})

Relation Filters

some

At least one matches:

const users = await prisma.user.findMany({
  where: {
    posts: { some: { published: true } }
  }
})

every

All match:

const users = await prisma.user.findMany({
  where: {
    posts: { every: { published: true } }
  }
})

none

None match:

const users = await prisma.user.findMany({
  where: {
    posts: { none: { published: true } }
  }
})

is / isNot (1-to-1)

const users = await prisma.user.findMany({
  where: {
    profile: { is: { country: 'USA' } }
  }
})

Count Relations

const users = await prisma.user.findMany({
  select: {
    name: true,
    _count: {
      select: { posts: true, followers: true }
    }
  }
})
// { name: 'Alice', _count: { posts: 5, followers: 100 } }

Filter counted relations

const users = await prisma.user.findMany({
  select: {
    name: true,
    _count: {
      select: {
        posts: { where: { published: true } }
      }
    }
  }
})

Transactions

Execute multiple operations atomically.

Sequential Transactions

Array of operations executed in order:

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

All or nothing

If any operation fails, all are rolled back:

try {
  await prisma.$transaction([
    prisma.user.create({ data: { email: 'alice@prisma.io' } }),
    prisma.user.create({ data: { email: 'alice@prisma.io' } }) // Duplicate!
  ])
} catch (e) {
  // Both operations rolled back
}

Interactive Transactions

For complex logic and dependent operations:

await prisma.$transaction(async (tx) => {
  // Decrement sender balance
  const sender = await tx.account.update({
    where: { id: senderId },
    data: { balance: { decrement: amount } }
  })
  
  // Check balance
  if (sender.balance < 0) {
    throw new Error('Insufficient funds')
  }
  
  // Increment recipient balance
  await tx.account.update({
    where: { id: recipientId },
    data: { balance: { increment: amount } }
  })
})

Transaction options

await prisma.$transaction(
  async (tx) => {
    // operations
  },
  {
    maxWait: 5000,    // Max wait to acquire lock (ms)
    timeout: 10000,   // Max transaction duration (ms)
    isolationLevel: 'Serializable'  // Isolation level
  }
)

Isolation levels

Nested Writes

Automatic transactions for nested operations:

// This is automatically a transaction
const user = await prisma.user.create({
  data: {
    email: 'alice@prisma.io',
    posts: {
      create: [
        { title: 'Post 1' },
        { title: 'Post 2' }
      ]
    },
    profile: {
      create: { bio: 'Hello!' }
    }
  }
})

Transaction Client

The tx parameter is a Prisma Client scoped to the transaction:

await prisma.$transaction(async (tx) => {
  // Use tx instead of prisma
  await tx.user.create({ ... })
  await tx.post.create({ ... })
  
  // Can call methods
  const count = await tx.user.count()
})

OrThrow in Transactions

Use with interactive transactions:

await prisma.$transaction(async (tx) => {
  // If not found, throws and rolls back entire transaction
  const user = await tx.user.findUniqueOrThrow({
    where: { id: 1 }
  })
  
  await tx.post.create({
    data: { title: 'New Post', authorId: user.id }
  })
})

Best Practices

Keep transactions short

// Good - only DB operations in transaction
const data = prepareData() // Outside transaction
await prisma.$transaction(async (tx) => {
  await tx.user.create({ data })
})

Handle errors

try {
  await prisma.$transaction(async (tx) => {
    // operations
  })
} catch (e) {
  if (e.code === 'P2002') {
    // Handle unique constraint violation
  }
  throw e
}

Use appropriate isolation

// Default is fine for most cases
await prisma.$transaction(async (tx) => {
  // operations
})

// Use Serializable for strict consistency
await prisma.$transaction(
  async (tx) => { /* operations */ },
  { isolationLevel: 'Serializable' }
)

Sequential vs Interactive