cheat sheet

Prisma

Schema-first ORM for Node.js with auto-generated typed client, declarative migrations, relations, transactions, and seeding — for PostgreSQL, MySQL, SQLite, MongoDB, and more.

updated 05-25-2026

Prisma — Type-Safe Node.js ORM

What it is

Prisma is a next-generation, schema-first ORM for Node.js and TypeScript that generates a fully-typed query client from a single schema.prisma file. It bundles three things: an introspection + migration engine, a declarative DSL for modeling your database, and a query API where every result is type-checked at compile time. Reach for it when you want maximum TypeScript safety with relational data; consider drizzle-orm instead if you prefer SQL-first or need lightweight serverless cold-starts, or kysely for a query builder without an ORM layer.

Install

Install the CLI (prisma) as a devDependency and the runtime client (@prisma/client) as a regular dependency.

bash

npm install --save-dev prisma
npm install @prisma/client

# yarn / pnpm equivalents
pnpm add -D prisma && pnpm add @prisma/client
yarn add -D prisma && yarn add @prisma/client

Output: (none — exits 0 on success)

Initialize a new project — creates prisma/schema.prisma and a .env with a DATABASE_URL placeholder:

bash

npx prisma init --datasource-provider postgresql

Output:

text

Your Prisma schema was created at prisma/schema.prisma
Next steps:
1. Set the DATABASE_URL in the .env file to point to your existing database.
2. Run prisma db pull to turn your database schema into a Prisma schema.
3. Run prisma generate to generate the Prisma Client. You can then start querying your database.

Supported providers: postgresql, mysql, sqlite, sqlserver, mongodb, cockroachdb.

Syntax

The Prisma CLI follows a prisma <command> [options] shape. The runtime client is new PrismaClient() then chained model accessors.

bash

npx prisma <command> [--schema=path/to/schema.prisma] [options]

Output: (none — exits 0 on success)

Essential CLI commands

Command	Purpose
`prisma init`	Scaffold a new schema + `.env`
`prisma generate`	Regenerate the typed client into `node_modules/.prisma/client`
`prisma migrate dev --name <n>`	Create + apply a dev migration
`prisma migrate deploy`	Apply pending migrations in production (no shadow DB)
`prisma migrate reset`	Drop and recreate the database (dev only)
`prisma db push`	Sync schema → DB without creating a migration (prototyping)
`prisma db pull`	Introspect an existing DB and update `schema.prisma`
`prisma db seed`	Run the seed script defined in `package.json`
`prisma studio`	Open the GUI at `http://localhost:5555`
`prisma format`	Auto-format `schema.prisma`
`prisma validate`	Validate the schema without generating

The `schema.prisma` file

The Prisma schema is the single source of truth. It declares the data source (DB connection), one or more generators (typically the JS client), and the model definitions that map to tables/collections.

prisma

// prisma/schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

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

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

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

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  authorId  Int
  author    User     @relation(fields: [authorId], references: [id])
  tags      Tag[]
  createdAt DateTime @default(now())
  @@index([authorId, published])
}

model Tag {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[]
}

enum Role {
  USER
  ADMIN
}

The key attributes:

Attribute	Meaning
`@id`	Primary key
`@default(<expr>)`	Default value (`autoincrement()`, `now()`, `uuid()`, literal)
`@unique`	Add a UNIQUE constraint
`@updatedAt`	Auto-update on every write
`@relation(fields, references, onDelete)`	Foreign-key relation
`@@index([...])`	Composite index at model level
`@@unique([...])`	Composite unique constraint
`@@map("<name>")`	Map model/field to a different DB name

Regenerate the client after editing:

bash

npx prisma generate

Output:

text

Prisma schema loaded from prisma/schema.prisma
Generated Prisma Client (v5.20.0) to ./node_modules/@prisma/client in 187ms

Migrations

Migrations are SQL files Prisma writes to prisma/migrations/<timestamp>_<name>/migration.sql. In development, migrate dev creates and applies them in one step; in production, migrate deploy only applies (never authors).

Create a migration after editing the schema:

bash

npx prisma migrate dev --name init

Output:

text

Applying migration `20260525120000_init`

The following migration(s) have been created and applied from new schema changes:

migrations/
  └─ 20260525120000_init/
    └─ migration.sql

Your database is now in sync with your schema.
✔ Generated Prisma Client (v5.20.0)

In production CI/CD, run migrate deploy (idempotent, no prompts):

bash

DATABASE_URL=$PROD_URL npx prisma migrate deploy

Output:

text

1 migration found in prisma/migrations
Applying migration `20260525120000_init`
The following migration(s) have been applied:
migrations/
  └─ 20260525120000_init/
    └─ migration.sql
All migrations have been successfully applied.

Reset (drop + recreate) for dev when migrations get tangled:

bash

npx prisma migrate reset --skip-seed

Output:

text

? Are you sure you want to reset your database? All data will be lost. › Yes
Database reset successful
The following migration(s) have been applied:
migrations/
  └─ 20260525120000_init/
    └─ migration.sql

For rapid prototyping, skip migrations entirely with db push — it diffs schema → DB and applies without writing a migration file:

bash

npx prisma db push

Output:

text

The database is now in sync with your Prisma schema.
✔ Generated Prisma Client (v5.20.0) in 142ms

The Prisma Client

The client is a class generated from your schema. Instantiate it once per process and reuse — never inside a request handler.

typescript

// src/db.ts
import { PrismaClient } from '@prisma/client';

// Reuse a single instance across hot-reloads in dev
const globalForPrisma = globalThis as unknown as { prisma?: PrismaClient };

export const prisma =
  globalForPrisma.prisma ?? new PrismaClient({
    log: ['query', 'error', 'warn'],
  });

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

Disconnect cleanly on shutdown:

typescript

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

CRUD operations

`findUnique` and `findFirst`

findUnique looks up by a unique field (or composite unique) and returns T | null. findFirst returns the first row matching arbitrary where filters.

typescript

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

const userByEmail = await prisma.user.findUnique({
  where: { email: 'alice@example.com' },
});

const firstAdmin = await prisma.user.findFirst({
  where: { role: 'ADMIN', createdAt: { gte: new Date('2026-01-01') } },
  orderBy: { createdAt: 'asc' },
});

bash

node --import tsx src/example.ts

Output:

text

{
  id: 1,
  email: 'alice@example.com',
  name: 'Alice Dev',
  role: 'USER',
  createdAt: 2026-01-15T10:30:00.000Z,
  updatedAt: 2026-01-15T10:30:00.000Z
}

`findMany` with filters, sort, pagination

typescript

const posts = await prisma.post.findMany({
  where: {
    published: true,
    OR: [
      { title: { contains: 'prisma', mode: 'insensitive' } },
      { content: { contains: 'orm' } },
    ],
    createdAt: { gte: new Date('2026-01-01') },
    author: { role: 'ADMIN' }, // filter on relation
  },
  orderBy: [{ createdAt: 'desc' }, { id: 'asc' }],
  skip: 0,
  take: 20,
  select: {
    id: true,
    title: true,
    author: { select: { name: true, email: true } },
    _count: { select: { tags: true } },
  },
});

bash

node --import tsx src/example.ts

Output:

text

[
  {
    id: 42,
    title: 'Prisma 5 deep dive',
    author: { name: 'Alice Dev', email: 'alice@example.com' },
    _count: { tags: 3 }
  },
  { id: 41, title: 'Drizzle vs Prisma', author: { ... }, _count: { tags: 2 } }
]

`create` (and nested writes)

Prisma can create related rows in a single call via nested writes — fewer round-trips than manual joins.

typescript

const user = await prisma.user.create({
  data: {
    email: 'alice@example.com',
    name: 'Alice Dev',
    profile: { create: { bio: 'JS dev' } },
    posts: {
      create: [
        { title: 'Hello world' },
        {
          title: 'Prisma intro',
          tags: {
            connectOrCreate: [
              { where: { name: 'orm' }, create: { name: 'orm' } },
              { where: { name: 'node' }, create: { name: 'node' } },
            ],
          },
        },
      ],
    },
  },
  include: {
    profile: true,
    posts: { include: { tags: true } },
  },
});

Output:

text

{
  id: 1,
  email: 'alice@example.com',
  name: 'Alice Dev',
  profile: { id: 1, bio: 'JS dev', userId: 1 },
  posts: [
    { id: 1, title: 'Hello world', tags: [] },
    { id: 2, title: 'Prisma intro', tags: [
      { id: 1, name: 'orm' }, { id: 2, name: 'node' }
    ] }
  ]
}

createMany is faster for bulk inserts but doesn't support nested writes:

typescript

const result = await prisma.tag.createMany({
  data: [{ name: 'cli' }, { name: 'orm' }, { name: 'sql' }],
  skipDuplicates: true,
});
console.log(result);

Output:

text

{ count: 3 }

`update`, `updateMany`, `upsert`

typescript

// update — throws if not found
const updated = await prisma.user.update({
  where: { id: 1 },
  data: { name: 'Alice D.', role: 'ADMIN' },
});

// updateMany — bulk, returns count, no relations in `data`
const result = await prisma.post.updateMany({
  where: { authorId: 1, published: false },
  data: { published: true },
});

// upsert — insert if missing, update if present
const tag = await prisma.tag.upsert({
  where: { name: 'typescript' },
  create: { name: 'typescript' },
  update: {}, // no-op when present
});

// Atomic numeric updates
const post = await prisma.post.update({
  where: { id: 1 },
  data: {
    // increment/decrement/multiply/divide instead of read-modify-write
    title: 'Updated title',
  },
});

Output: (none — exits 0 on success)

`delete` and cascades

typescript

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

// Bulk delete
const r = await prisma.post.deleteMany({
  where: { published: false, createdAt: { lt: new Date('2024-01-01') } },
});
console.log(r);

Output:

text

{ count: 17 }

onDelete: Cascade in the schema deletes related rows automatically when the parent is removed.

Relations and `include` / `select`

include adds related data, select picks specific scalar/relation fields. They are mutually exclusive at the same level. Both produce a precise return type that TypeScript narrows automatically.

typescript

// Include — full related rows
const post = await prisma.post.findUnique({
  where: { id: 1 },
  include: {
    author: true,
    tags: true,
    _count: { select: { tags: true } },
  },
});

// Select — only listed fields, plus narrowed types
const lean = await prisma.post.findUnique({
  where: { id: 1 },
  select: { id: true, title: true, author: { select: { name: true } } },
});

// Filter + paginate on a nested relation
const author = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' },
      take: 5,
    },
  },
});

Output: (none — exits 0 on success)

The inferred type of lean is { id: number; title: string; author: { name: string | null } } | null — no manual typing required.

Transactions

Prisma offers two transaction styles. The array form runs a fixed sequence of queries atomically; the interactive callback form lets you branch on intermediate results.

typescript

// Array — atomic batch (all or nothing)
const [user, post] = await prisma.$transaction([
  prisma.user.create({ data: { email: 'alice@example.com' } }),
  prisma.post.create({ data: { title: 'Hi', authorId: 1 } }),
]);

// Interactive — branching logic with rollback on throw
const result = await prisma.$transaction(
  async (tx) => {
    const account = await tx.account.findUnique({ where: { id: 1 } });
    if (!account || account.balance < 100) {
      throw new Error('Insufficient funds');
    }
    await tx.account.update({
      where: { id: 1 },
      data: { balance: { decrement: 100 } },
    });
    return tx.account.update({
      where: { id: 2 },
      data: { balance: { increment: 100 } },
    });
  },
  {
    isolationLevel: 'Serializable', // strongest
    maxWait: 5_000,
    timeout: 10_000,
  }
);

Output: (none — exits 0 on success)

If the callback throws, the entire transaction rolls back.

Raw queries

When the query builder can't express what you need (window functions, vendor-specific syntax, complex CTEs), drop to raw SQL. Use tagged-template form ($queryRaw / $executeRaw) which parameterises automatically — never string-concatenate user input.

typescript

// Returns rows
const users = await prisma.$queryRaw<Array<{ id: number; email: string }>>`
  SELECT id, email FROM "User" WHERE created_at > ${new Date('2026-01-01')}
`;

// Returns the affected-row count
const affected = await prisma.$executeRaw`
  UPDATE "Post" SET views = views + 1 WHERE id = ${42}
`;

// Unsafe variants — escape with `Prisma.sql` if you must build dynamic SQL
import { Prisma } from '@prisma/client';

const orderBy = Prisma.sql`ORDER BY created_at DESC`;
const rows = await prisma.$queryRaw`SELECT * FROM "Post" ${orderBy} LIMIT 10`;

Output: (none — exits 0 on success)

Seeding

A seed script populates the DB with realistic data for development. Wire it via package.json:

json

{
  "prisma": {
    "seed": "tsx prisma/seed.ts"
  }
}

typescript

// prisma/seed.ts
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

async function main() {
  await prisma.user.upsert({
    where: { email: 'alice@example.com' },
    update: {},
    create: {
      email: 'alice@example.com',
      name: 'Alice Dev',
      role: 'ADMIN',
      posts: { create: [{ title: 'Welcome', published: true }] },
    },
  });
}

main()
  .then(async () => await prisma.$disconnect())
  .catch(async (e) => {
    console.error(e);
    await prisma.$disconnect();
    process.exit(1);
  });

Run it:

bash

npx prisma db seed

Output:

text

Environment variables loaded from .env
Running seed command `tsx prisma/seed.ts` ...
🌱 The seed command has been executed.

migrate reset automatically re-runs the seed.

Prisma Studio

A browser-based GUI for browsing and editing rows — useful in dev, never in production.

bash

npx prisma studio

Output:

text

Prisma Studio is up on http://localhost:5555

Open the URL; tables and relations are inferred from the schema.

Prisma vs Drizzle vs Kysely

A quick comparison of the three most-popular type-safe Node ORMs/query builders.

Aspect	Prisma	Drizzle	Kysely
Approach	ORM, schema-first DSL	ORM, schema-as-TS	Query builder only
Migrations	Generated SQL files	TS or SQL	None (use external)
Cold start (serverless)	Medium (large client)	Tiny	Tiny
Raw SQL ergonomics	OK (`$queryRaw`)	Excellent	Excellent
Edge-runtime support	Driver adapters	Native	Native
Type-safety	Best-in-class	Best-in-class	Best-in-class
Bundle size impact	Larger	Smaller	Smallest
Studio/GUI	Yes	Drizzle Studio	No

Pick Prisma for declarative schemas and migrations out of the box. Pick Drizzle for serverless/edge or when you want SQL-like syntax. Pick Kysely when you want a pure query builder over an existing schema.

Common pitfalls

Forgetting prisma generate — every schema change requires regenerating the client; otherwise TypeScript autocomplete is stale. Wire it into postinstall: "postinstall": "prisma generate".
Instantiating PrismaClient per request — opens new DB connections each time. Always create one global instance (globalForPrisma trick) and reuse it.
Connection-pool exhaustion in serverless — bundled Prisma uses a connection per Lambda. Use Prisma Accelerate or a driver adapter (@prisma/adapter-neon, @prisma/adapter-planetscale) for edge runtimes.
Using db push instead of migrate dev in CI — db push skips migration history. Use it for prototyping, but commit migrations for production-grade changes.
Mixing findUnique and findFirst — findUnique only accepts unique fields and crashes on non-uniques. Use findFirst for arbitrary filters.
Big include trees — include: { posts: { include: { tags: true } } } issues multiple queries; can pull megabytes for one user. Use select to trim and take to bound.
Not awaiting transactions — prisma.$transaction(...) returns a Promise. Forgetting await silently runs the queries individually.
Raw SQL string interpolation — $queryRawUnsafe(\... WHERE id = ${input}`)is SQL injection. Use$queryRaw`... WHERE id = ${input}`` (tagged template) instead.
DATABASE_URL shape mismatch — connection strings differ per provider: Postgres uses ?schema=public&pool_timeout=20, MySQL uses ?connection_limit=10. Check the connection-URL reference.

Real-world recipes

Fastify route with typed Prisma access

A typed REST endpoint backed by Prisma — the response type is inferred from select.

typescript

// src/routes/users.ts
import type { FastifyInstance } from 'fastify';
import { z } from 'zod';
import { prisma } from '../db';

export default async function (app: FastifyInstance) {
  app.get('/users/:id', async (req) => {
    const id = z.coerce.number().parse((req.params as any).id);
    return prisma.user.findUniqueOrThrow({
      where: { id },
      select: {
        id: true, email: true, name: true,
        posts: { where: { published: true }, take: 5 },
      },
    });
  });

  app.post('/users', async (req) => {
    const body = z.object({
      email: z.string().email(),
      name: z.string().optional(),
    }).parse(req.body);
    return prisma.user.create({ data: body });
  });
}

bash

curl http://localhost:3000/users/1

Output:

text

{"id":1,"email":"alice@example.com","name":"Alice Dev","posts":[{"id":42,"title":"Hello","published":true,...}]}

Soft-delete with middleware

Add a deletedAt column and intercept delete / deleteMany to set it instead of removing rows. Prisma's $extends API replaces the older $use middleware.

typescript

// src/db.ts
import { PrismaClient } from '@prisma/client';

const base = new PrismaClient();

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

typescript

await prisma.user.delete({ where: { id: 1 } });
const visible = await prisma.user.findMany();
console.log(visible.length);

Output:

text

Schema-first migration on a CI runner

In CI, run migrate deploy against the production DB only on green builds.

yaml

# .github/workflows/deploy.yml
jobs:
  migrate:
    runs-on: ubuntu-latest
    needs: test
    env:
      DATABASE_URL: ${{ secrets.PROD_DATABASE_URL }}
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with: { node-version: 22 }
      - run: npm ci
      - run: npx prisma migrate deploy

bash

gh workflow run deploy.yml --ref main

Output:

text

✓ Run started

Multi-tenant: schema-per-tenant

Prisma supports a schema parameter in the connection URL (?schema=tenant_42). For dynamic switching, instantiate one PrismaClient per tenant and pool them in a Map.

typescript

const clients = new Map<string, PrismaClient>();

export function clientFor(tenantId: string) {
  let c = clients.get(tenantId);
  if (!c) {
    c = new PrismaClient({
      datasources: { db: { url: `${process.env.DATABASE_URL}?schema=tenant_${tenantId}` } },
    });
    clients.set(tenantId, c);
  }
  return c;
}

typescript

const db = clientFor('alicedev');
const users = await db.user.findMany();

Output: (none — exits 0 on success)

Bulk import with `createMany` + chunking

Bulk inserts via createMany hit DB row-limits around ~32k entries per call. Chunk to stay safe.

typescript

const chunk = <T>(arr: T[], size: number) =>
  Array.from({ length: Math.ceil(arr.length / size) }, (_, i) =>
    arr.slice(i * size, (i + 1) * size)
  );

const rows = Array.from({ length: 250_000 }, (_, i) => ({
  email: `user${i}@example.com`,
  name: `User ${i}`,
}));

let inserted = 0;
for (const batch of chunk(rows, 5_000)) {
  const r = await prisma.user.createMany({ data: batch, skipDuplicates: true });
  inserted += r.count;
  console.log(`Inserted ${inserted}/${rows.length}`);
}

bash

node --import tsx scripts/import.ts

Output:

text

Inserted 5000/250000
Inserted 10000/250000
...
Inserted 250000/250000

Run Prisma against a specific env file

When .env doesn't apply (staging migrations, e2e tests), use dotenv-cli to inject a different file.

bash

# Apply migrations against staging
npx dotenv -e .env.staging -- npx prisma migrate deploy

# Run seed against the test DB
npx dotenv -e .env.test -- npx prisma db seed

Output:

text

Datasource "db": PostgreSQL database "myapp_staging" at "db.staging:5432"
All migrations have been successfully applied.

Prisma — Type-Safe Node.js ORM

What it is

Install

Syntax

Essential CLI commands

The schema.prisma file

Migrations

The Prisma Client

CRUD operations

findUnique and findFirst

findMany with filters, sort, pagination

create (and nested writes)

update, updateMany, upsert

delete and cascades

Relations and include / select

Transactions

Raw queries

Seeding

Prisma Studio

Prisma vs Drizzle vs Kysely

Common pitfalls

Real-world recipes

Fastify route with typed Prisma access

Soft-delete with middleware

Schema-first migration on a CI runner

Multi-tenant: schema-per-tenant

Bulk import with createMany + chunking

Run Prisma against a specific env file

The `schema.prisma` file

`findUnique` and `findFirst`

`findMany` with filters, sort, pagination

`create` (and nested writes)

`update`, `updateMany`, `upsert`

`delete` and cascades

Relations and `include` / `select`

Bulk import with `createMany` + chunking