prisma

What it is

prisma is the dev-time CLI and @prisma/client is the runtime — together they form a type-safe ORM and migration toolkit for Node/TypeScript. You write a schema.prisma file (Prisma's own DSL), the CLI generates a tailored TypeScript client into node_modules/.prisma/client, and @prisma/client re-exports it from your app code. The generated types reflect your actual schema, so renaming a column or adding a field gets full IDE feedback on every query in the codebase.

prisma migrate produces SQL migrations from schema diffs and applies them; prisma db push is the dev-only shortcut that skips migration files and syncs directly. Supports PostgreSQL, MySQL/MariaDB, SQLite, SQL Server, MongoDB, and CockroachDB.

Install

# CLI as devDep (generates the client, runs migrations)
npm install -D prisma
pnpm add -D prisma
yarn add -D prisma
bun add -d prisma

# Runtime client as a regular dep
npm install @prisma/client
pnpm add @prisma/client
yarn add @prisma/client
bun add @prisma/client

Output: prisma binary on PATH; @prisma/client exposes the generated PrismaClient (after the first prisma generate).

# Scaffold a fresh project
npx prisma init --datasource-provider postgresql

Output: creates prisma/schema.prisma + a starter .env with a DATABASE_URL placeholder.

# Apply schema → DB (after editing schema.prisma)
npx prisma migrate dev --name init    # dev: generates SQL + applies + regenerates client
npx prisma db push                    # dev shortcut: applies without producing a migration file
npx prisma migrate deploy             # prod: apply pending migrations only
npx prisma generate                   # regenerate the client without DB changes
npx prisma studio                     # local web GUI for browsing data

Output: migrations under prisma/migrations/; generated client under node_modules/@prisma/client/.

Versioning & Node support

• Current major line is 6.x (released 2024). Keep prisma (CLI) and @prisma/client (runtime) pinned to the same version — mismatches produce cryptic runtime errors.
• Requires Node 18.18+ for 6.x. 5.x accepted Node 16. The minimum bumps once per major.
• Both packages are CJS-compatible (publish dual ESM+CJS via conditional exports) — works in Node, edge runtimes, and bundled environments.
• The CLI is a dev dependency only; the runtime client ships in your prod bundle.
• A new Prisma ORM 7 / prisma-client (no @prisma/client) generator and Rust→TS query-engine rewrite is in active rollout — check release notes before pinning.

Package metadata

• Maintainer: Prisma Data, Inc. (commercial company, OSS core)
• Project home: github.com/prisma/prisma
• Docs: prisma.io/docs
• npm (CLI): npmjs.com/package/prisma
• npm (client): npmjs.com/package/@prisma/client
• License: Apache-2.0
• First released: 2019 (1.0 of the current ORM; predecessor "Prisma 1" / Graphcool dates to 2016)
• Downloads: many millions per week — top-tier ORM by usage on npm.

Peer dependencies & extras

prisma and @prisma/client don't list runtime peers — they bundle the query engine binary (Rust) per platform. Add-ons:

Alternatives

Common gotchas

1. Migration drift between dev and prod. prisma db push (dev) doesn't write a migration file. If you db push locally and forget to convert to prisma migrate dev before deploying, prod has no migration to apply — schema changes silently miss. Treat db push as scratch-only.
2. Generated client size. @prisma/client ships per-platform query-engine binaries (~10–50 MB before tree-shake). Lambda / edge bundles need binaryTargets set on the generator block to include only the platforms you deploy to.
3. Transactions: interactive vs sequential. prisma.$transaction([a, b]) (sequential, array form) and prisma.$transaction(async (tx) => …) (interactive, callback form) are different APIs — only the callback form gives tx you can pass into helpers. Mixing them up causes "Transaction not found" errors.
4. Raw queries lose type safety. prisma.$queryRaw / $executeRaw return unknown[] unless you supply a generic. Use Prisma.sql to compose safely; avoid string interpolation (${userInput}) — it bypasses parameterisation and is a SQL-injection vector.
5. select and include cannot coexist on the same level — Prisma throws at type-check time. Use one or the other; nest select inside include for fine-grained shapes.
6. @db.Decimal becomes Decimal.js, not number. Prisma maps SQL DECIMAL to its bundled Decimal class for precision. JSON.stringifying a result with decimals produces {"price":{"d":[...],"e":2,"s":1}} — call .toString() or convert deliberately.
7. PrismaClient is a heavy object. Instantiate once per process. In Next.js dev (HMR), wrap in a globalThis cache so each hot-reload doesn't open a fresh pool — otherwise you'll exhaust Postgres connections within minutes.

Real-world recipes

The patterns that show up on real production codebases.

Interactive transactions

The callback form gives you a tx you can pass into helper functions — every query inside is part of the same DB transaction and rolls back together on a thrown error.

import { prisma } from "@/lib/db";

await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({ data: { email: "alice@example.com" } });
  await tx.post.create({ data: { authorId: user.id, title: "Hello" } });
  await tx.auditLog.create({ data: { userId: user.id, action: "SIGNUP" } });
});

For batch inserts where order doesn't matter, the sequential array form is shorter — but you cannot pass tx into helpers:

await prisma.$transaction([
  prisma.user.create({ data: { email: "alice@example.com" } }),
  prisma.user.create({ data: { email: "bob@example.com" } }),
]);

Client extensions — soft delete

Extensions wrap the generated client to add cross-cutting behaviour without monkey-patching. Soft delete is the canonical example:

import { Prisma, PrismaClient } from "@prisma/client";

const base = new PrismaClient();

export const prisma = base.$extends({
  query: {
    user: {
      async findMany({ args, query }) {
        args.where = { ...args.where, deletedAt: null };
        return query(args);
      },
      async delete({ args }) {
        return base.user.update({
          where: args.where,
          data: { deletedAt: new Date() },
        });
      },
    },
  },
});

Every prisma.user.findMany() now silently filters soft-deleted rows; prisma.user.delete(...) becomes an UPDATE.

Multi-tenant via schema-per-tenant

PostgreSQL's search_path lets one connection swap schemas mid-session. Wrap PrismaClient construction:

function tenantClient(tenantId: string) {
  return new PrismaClient({
    datasources: { db: { url: `${process.env.DATABASE_URL}?schema=tenant_${tenantId}` } },
  });
}

Cache per-tenant clients; each pools its own connections.

Type-safe raw SQL with Prisma.sql

$queryRaw is the only way to express joins / window functions Prisma's API can't represent. Prisma.sql builds a parameterised query — never interpolate user input.

import { Prisma } from "@prisma/client";

const userId = 42;
const rows = await prisma.$queryRaw<{ id: number; count: bigint }[]>(Prisma.sql`
  SELECT u.id, COUNT(p.id) AS count
  FROM "User" u
  LEFT JOIN "Post" p ON p."authorId" = u.id
  WHERE u.id = ${userId}
  GROUP BY u.id
`);

The ${userId} is passed as a SQL parameter, not interpolated as text.

Production deployment

The dev workflow (prisma migrate dev, db push, studio) doesn't deploy. Production uses a narrower, safer subset.

Migration workflow

# Dev (local)
npx prisma migrate dev --name add_post_tags    # creates SQL + applies + regenerates

# Production
npx prisma migrate deploy                      # applies pending migrations only
npx prisma generate                            # regenerate client (run in build step)

Output:

Applying migration `20260415_add_post_tags`
The following migration(s) have been applied:
  migrations/
    └─ 20260415_add_post_tags/
       └─ migration.sql
✔ Generated Prisma Client (v5.18.0) to ./node_modules/@prisma/client in 142ms

Never run migrate dev in production — it can create a "shadow database" and silently reset state. migrate deploy is read-only against the schema; it only applies pending files from prisma/migrations/.

Connection pooling

PrismaClient opens one TCP connection per query, pooled internally up to connection_limit. Serverless functions (Lambda, Vercel) spawn new clients per cold start — without external pooling, you'll exhaust Postgres max_connections within minutes.

Edge runtime constraints

Prisma's Rust query engine doesn't run on Cloudflare Workers or Vercel Edge. Two options:

1. Driver adapters — @prisma/adapter-d1, @prisma/adapter-neon, @prisma/adapter-planetscale. The query engine compiles to WASM or the request is proxied through the adapter's driver.
2. Prisma Accelerate — the official hosted proxy. Your app makes HTTP requests to Accelerate, which talks to the DB.

// Cloudflare Workers + D1 + driver adapter
import { PrismaD1 } from "@prisma/adapter-d1";
import { PrismaClient } from "@prisma/client";

export default {
  async fetch(req: Request, env: Env) {
    const adapter = new PrismaD1(env.DB);
    const prisma = new PrismaClient({ adapter });
    const users = await prisma.user.findMany();
    return Response.json(users);
  },
};

Performance tuning

The Prisma client is fast, but its query shape choices have big runtime implications.

include vs select for shape control

include fetches the entire relation; select lets you pick fields, including from nested relations. For wide tables, prefer select:

// Bad — pulls every column of User and Post
const post = await prisma.post.findUnique({ where: { id }, include: { author: true } });

// Good — minimum columns only
const post = await prisma.post.findUnique({
  where: { id },
  select: { id: true, title: true, author: { select: { id: true, name: true } } },
});

N+1 detection

A naive findMany followed by per-row loads is a classic N+1 — load the relations in a single query with include instead:

// N+1 — runs N+1 queries
const users = await prisma.user.findMany();
for (const u of users) {
  u.posts = await prisma.post.findMany({ where: { authorId: u.id } });
}

// Single query — Prisma generates 2 SQL queries total (one per relation level)
const users = await prisma.user.findMany({ include: { posts: true } });

Raw fallback for hot paths

When a query is hot and prisma.findMany is doing more than you need, drop to $queryRaw and use Prisma only for shape inference. Loss of compile-time guarantees is offset by raw SQL control.

Reduce client size with binaryTargets

generator client {
  provider      = "prisma-client-js"
  binaryTargets = ["native", "linux-musl-openssl-3.0.x"]    // Lambda x86 only
}

Each target adds ~15 MB. Trim to just the platforms you deploy to.

ESM/CJS interop & bundling

@prisma/client publishes dual ESM + CJS via conditional exports — works in any modern bundler and in Node CJS holdouts. The prisma CLI is a CJS executable.

Bundlers (Webpack, Vite, esbuild) sometimes fail to copy the platform-specific query engine binary. Mitigations:

• Next.js: experimental.serverComponentsExternalPackages: ["@prisma/client"] keeps the binary outside the bundle.
• Vite + Vercel: use vite-plugin-node-polyfills only for the build; the Prisma client doesn't need polyfills, but the bundler may try.
• Lambda layers: pre-package the .prisma directory + @prisma/client as a layer to keep deploy bundles small.

Version migration guide

Common migration friction

• Field types: every major has tightened the mapping for DateTime, Decimal, and Json. Re-run prisma generate and TS-check.
• prisma.user.findFirst() vs findFirstOrThrow — added in 4.x. Older code that uses findUnique then if (!result) should switch to findUniqueOrThrow for cleaner error handling.
• previewFeatures drift — flags graduate. Read each release note carefully; some features that were free under preview now require Accelerate (e.g. some caching primitives).

Security considerations

1. Raw query injection. $queryRawUnsafe("SELECT * FROM users WHERE id = " + userId) is a SQL-injection vector. Always use $queryRaw\...`(tagged template) orPrisma.sql` for composition. The "unsafe" variant exists for migrations only.
2. Connection-string handling. DATABASE_URL typically contains credentials. Never commit. Set via runtime env or secrets manager; in CI, mask in logs.
3. prisma migrate dev writes the shadow DB. Granting the dev DB user CREATEDB to allow shadow-DB creation is a privilege boundary — never grant in prod. In CI, set PRISMA_MIGRATE_SHADOW_DATABASE_URL to a separate isolated DB or skip with --skip-shadow-database-check.
4. Decimal precision. @db.Decimal for money values protects against float drift; using Float for currency is a known footgun (0.1 + 0.2 !== 0.3).
5. Audit prisma studio. Studio gives full table access with no auth. Never expose its port (5555) on a non-local interface; never run against prod credentials.

Testing strategies

Transaction-wrap per test

Each test starts a transaction and rolls back at the end — no data leaks between tests, no per-test cleanup.

import { test as base } from "vitest";
import { PrismaClient, Prisma } from "@prisma/client";

const prisma = new PrismaClient();

export const test = base.extend<{ db: Prisma.TransactionClient }>({
  db: async ({}, use) => {
    await prisma.$transaction(async (tx) => {
      await use(tx);
      throw new Prisma.PrismaClientKnownRequestError("rollback", { code: "P2034", clientVersion: "" });
    }).catch(() => {});
  },
});

Container-backed test DB

Use Testcontainers to spin up a real Postgres per test run — production-faithful, no shared state:

// vitest globalSetup
import { PostgreSqlContainer } from "@testcontainers/postgresql";

export async function setup() {
  const container = await new PostgreSqlContainer().start();
  process.env.DATABASE_URL = container.getConnectionUri();
  return async () => container.stop();
}

Troubleshooting common errors

• P1001: Can't reach database server — wrong host/port or network unreachable. Run psql $DATABASE_URL -c "SELECT 1" to verify.
• P2002: Unique constraint failed — duplicate insert on a @unique field. Catch and return 409, or use upsert.
• P1017: Server has closed the connection — connection pool exhausted, usually under serverless. Add pooling (PgBouncer or Accelerate).
• PrismaClientInitializationError: Engine not found — binaryTargets doesn't include your deploy platform. Add linux-musl-openssl-3.0.x for Alpine / Lambda.
• Error querying the database: db error: FATAL: password authentication failed — env var didn't expand. Quote the URL or use dotenv -e .env -- npx prisma migrate dev.

Ecosystem integrations

Prisma's plugin model (since 5.x) is the $extends API — every notable add-on now uses it. The most active integrations:

Editor tools:

• Prisma VS Code extension — syntax, format on save, jump-to-definition for model references.
• prisma studio — local GUI; never run against prod.

When NOT to use this

Prisma is excellent for type-safe relational data with moderate schema complexity. Reach for an alternative when:

• You need heavy raw SQL. Window functions, CTEs, complex joins, vendor-specific extensions — Prisma's query API covers ~85% of common SQL. Beyond that, you fall back to $queryRaw for every hot path. Pick Drizzle ORM or Kysely when SQL is your real interface — they're closer to "type-safe query builder" than "ORM".
• You need true horizontal scaling. Prisma's client opens one connection per query (pooled); high-concurrency deployments need PgBouncer / Accelerate. Drizzle on edge with a driver adapter has less overhead.
• Tiny serverless functions. Prisma's client (with platform binaries) adds ~10-50 MB to bundles. For a 100-line edge function, Drizzle or Postgres.js (postgres) are tighter.
• Non-relational data first. Prisma's MongoDB support exists but is second-class. For MongoDB-first apps use Mongoose or TypeORM.
• Legacy DB you can't migrate. Prisma is schema-first — it expects to own migrations. If you have an existing DB you can't reshape, prisma db pull works but you lose much of the workflow.

See also

• JavaScript: prisma — schema, queries, relations, transactions
• Concept: API — type-safe DB boundaries
• Packages: npm-zod — pair with Prisma at API edges