Prisma

What is Prisma?

Prisma is a schema-first ORM for TypeScript and Node.js. You describe your data once in schema.prisma, run a generator, and get a client whose methods and return types come straight from that schema. There are no decorators, no repository classes and no hand-written SQL strings for the common cases.

The pitch is that the database schema becomes a single declarative file that humans read and tools consume. From it Prisma produces a typed client you import in your code, SQL migrations you can review and commit, and a studio UI for browsing data. Prisma supports PostgreSQL, MySQL, MariaDB, SQLite, SQL Server, CockroachDB and MongoDB, and the generated client translates each call into SQL or the MongoDB wire protocol before mapping rows back into plain objects.

If you have used an ActiveRecord-style ORM before, the mental shift is that Prisma is schema-first and client-generated rather than class-first and runtime-reflective. That single decision explains most of its strengths and most of its costs.

The schema is the source of truth

Everything starts in schema.prisma. It contains three kinds of block: a datasource (where the database is), a generator (what to emit), and one model per table.

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

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

model User {
  id        String   @id @default(uuid())
  email     String   @unique
  name      String?
  posts     Post[]
  createdAt DateTime @default(now())
}

A few conventions are worth internalising. A field ending in ? is nullable, while a list type such as Post[] is a relation rather than a column. Attributes start with @ for fields and @@ for blocks. @id marks the primary key, @unique creates a unique index, @default(...) supplies a value, and @map and @@map rename the underlying column or table when it does not match your model.

The model above maps to a User table with id, email, name and createdAt columns, plus a virtual posts relation that Prisma resolves with a join or a second query.

Generating and using the client

Prisma’s client is generated code. After editing the schema, run:

pnpm prisma generate

This reads schema.prisma and writes a client into node_modules/.prisma/client, or into a folder you choose with the newer prisma-client generator. Then you instantiate it once and reuse it:

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

const prisma = new PrismaClient();

const user = await prisma.user.findUnique({
  where: { email: "[email protected]" },
});

Because the client is generated, prisma.user and the shape of user are both known to the type checker. Rename a field in the schema, regenerate, and every stale usage fails to compile. That feedback loop is the main reason teams adopt Prisma.

In serverless or hot-reloading environments, avoid creating a new client per request or per reload. Attach one to the global object instead:

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;

Reading data

Prisma exposes one method per read shape. findUnique fetches a single row by a unique field, findFirst fetches one row matching an arbitrary filter, and findMany returns a list.

const post = await prisma.post.findUnique({ where: { id: 42 } });

const latest = await prisma.post.findFirst({
  where: { published: true },
  orderBy: { createdAt: "desc" },
});

const posts = await prisma.post.findMany({
  where: {
    published: true,
    title: { contains: "prisma", mode: "insensitive" },
    authorId: { in: [userId] },
  },
  orderBy: { createdAt: "desc" },
  take: 20,
  skip: 0,
});

Filter operators are named rather than symbolic: equals, not, in, notIn, lt, lte, gt, gte, contains, startsWith, endsWith and mode. Combine them with AND, OR and NOT arrays:

const posts = await prisma.post.findMany({
  where: {
    OR: [
      { title: { contains: "orm" } },
      { author: { email: { endsWith: "@example.com" } } },
    ],
    NOT: { published: false },
  },
});

take and skip implement offset pagination. For large result sets, prefer cursor pagination, which pages from the last row instead of counting from the start:

const page = await prisma.post.findMany({
  take: 20,
  skip: 1,
  cursor: { id: lastSeenId },
  orderBy: { id: "asc" },
});

findUnique will not accept a non-unique where; use findFirst for those. The findUniqueOrThrow and findFirstOrThrow variants reject with an error instead of returning null, which removes a branch when the row must exist.

Writing data

Creates, updates and deletes are equally typed. create, update, upsert and delete operate on one row, while createMany, updateMany and deleteMany operate on sets.

const user = await prisma.user.create({
  data: { email: "[email protected]", name: "Ada" },
});

await prisma.user.update({
  where: { id: user.id },
  data: { name: "Ada Lovelace" },
});

await prisma.user.upsert({
  where: { email: "[email protected]" },
  update: { name: "Ada Lovelace" },
  create: { email: "[email protected]", name: "Ada" },
});

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

For bulk inserts, createMany issues a single INSERT and is dramatically faster than looping over create:

await prisma.post.createMany({
  data: [
    { title: "Hello", authorId: user.id },
    { title: "World", authorId: user.id },
  ],
  skipDuplicates: true,
});

The trade-off is that createMany cannot write nested relations; it is for flat rows only. updateMany and deleteMany accept the same filters as findMany, so a missing where really does affect every row.

Relations, include and select

Relations are declared on both sides. User.posts is a list and Post.author is a single value, tied together by @relation(fields: [authorId], references: [id]) on the owning side.

By default Prisma returns scalar columns only. To load a relation you add include:

const user = await prisma.user.findUnique({
  where: { id: userId },
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: "desc" },
      take: 10,
    },
  },
});

select is the narrower tool: it chooses exactly which fields to return, for the model and for nested relations alike.

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

You cannot combine select and include at the same level, because select already answers the question of what to return. Reach for select when an endpoint has a fixed response shape, and include when you genuinely want the whole related record. Returning entire rows and trimming them in JavaScript wastes bandwidth and memory, so select prevents a common regression.

Nested writes

One of Prisma’s nicer features is writing a parent and its children in a single call. The nested create, connect, update and delete operations all run inside an implicit transaction.

const user = await prisma.user.create({
  data: {
    email: "[email protected]",
    posts: {
      create: [
        { title: "First post" },
        { title: "Second post", published: true },
      ],
    },
  },
  include: { posts: true },
});

Connecting existing rows instead of creating them uses connect, and disconnecting a relation uses disconnect:

await prisma.post.update({
  where: { id: postId },
  data: {
    author: { connect: { email: "[email protected]" } },
  },
});

Nested writes keep related data consistent without you opening a transaction by hand, which is exactly the kind of bookkeeping an ORM should own.

Migrations in development and production

Prisma Migrate turns the difference between your schema and your database into versioned SQL files under prisma/migrations.

pnpm prisma migrate dev --name add_posts
pnpm prisma migrate deploy
pnpm prisma migrate status

migrate dev is a development command. It compares the schema to the database, writes a new migration, applies it and regenerates the client. It also uses a shadow database to detect drift, so the development role needs permission to create and drop databases. If drift is found it may offer to reset the database, which deletes data.

migrate deploy is the production command. It applies pending migrations and nothing else: no generation, no shadow database, no resets. Run it in your deploy pipeline before the new code starts serving traffic.

For throwaway prototypes, prisma db push skips migration history entirely and forces the database to match the schema:

pnpm prisma db push

db push is fast and convenient, but it leaves no audit trail and can drop columns or tables to make the schema fit. Never point it at production. Other useful commands are prisma migrate reset to drop, recreate and re-seed a development database, and prisma migrate diff to show what would change without applying it.

Seeding

Seed data belongs in prisma/seed.ts. Register it in package.json so Prisma knows how to run it:

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

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

const prisma = new PrismaClient();

async function main() {
  await prisma.user.upsert({
    where: { email: "[email protected]" },
    update: {},
    create: {
      email: "[email protected]",
      name: "Ada",
      posts: { create: [{ title: "Welcome" }] },
    },
  });
}

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

Run it with pnpm prisma db seed. Because seeding runs after migrate dev and migrate reset, a freshly created database is never empty, which keeps onboarding and tests predictable.

Transactions

Prisma has two transaction APIs. The sequential form takes an array of operations and runs them in order:

const [debit, credit] = await prisma.$transaction([
  prisma.account.update({
    where: { id: 1 },
    data: { balance: { decrement: 5000 } },
  }),
  prisma.account.update({
    where: { id: 2 },
    data: { balance: { increment: 5000 } },
  }),
]);

The interactive form gives you a transaction client and lets you branch on results:

await prisma.$transaction(async (tx) => {
  const sender = await tx.account.findUniqueOrThrow({ where: { id: 1 } });
  if (sender.balance < 5000) throw new Error("insufficient_funds");

  await tx.account.update({
    where: { id: 1 },
    data: { balance: { decrement: 5000 } },
  });
  await tx.account.update({
    where: { id: 2 },
    data: { balance: { increment: 5000 } },
  });
});

Use tx for every query inside an interactive transaction; using the outer prisma client escapes the transaction. You can tune behaviour with maxWait, which controls how long to wait for a connection, and timeout, which caps how long the transaction may run, and set the isolation level with isolationLevel. Keep transactions short and never hold one open across an HTTP call or user interaction.

Raw SQL when you need it

The query builder covers most reads, but reporting queries and database-specific features sometimes need SQL. $queryRaw is a tagged template that parameterises values for you:

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

const rows = await prisma.$queryRaw<
  { day: Date; revenue: bigint }[]
>(Prisma.sql`
  SELECT date_trunc('day', created_at) AS day,
         sum(total_cents)             AS revenue
  FROM orders
  WHERE status = 'paid'
  GROUP BY 1
  ORDER BY 1 DESC
`);

Use $queryRaw for SELECT and $executeRaw for writes. Both accept tagged templates, which prevent SQL injection because interpolated values become bound parameters. $queryRawUnsafe and $executeRawUnsafe exist for genuinely dynamic SQL and should be treated as dangerous: never interpolate user input into them. Prisma.sql and Prisma.join let you compose fragments while keeping the parameterisation intact.

Pooling, serverless and Accelerate

Each PrismaClient instance owns a connection pool. On a long-lived server that is exactly what you want. On a serverless platform, every function instance creates its own client and therefore its own pool, and a traffic spike can exhaust the database’s max_connections in seconds.

The first lever is the connection string. Cap the pool and the wait time:

DATABASE_URL="postgresql://user:pass@host:5432/shop?connection_limit=5&pool_timeout=10"

In functions where each instance handles one request at a time, connection_limit=1 is often correct. Behind PgBouncer, add ?pgbouncer=true so Prisma stops using prepared statements that transaction pooling cannot preserve. If you cannot change the database’s connection limits, Prisma Accelerate sits in front as a managed pooler and cache; you wrap the client with its extension and route queries through a global endpoint. The older Data Proxy solved the same problem and has been superseded by Accelerate.

Whatever you choose, the rule is the same: reuse one client per process, and do not let the number of application instances multiply into more connections than the database can hold.

The N+1 question

An N+1 problem appears when you fetch a list and then issue one query per row to load a relation. Prisma avoids the classic form because include and select load relations as part of the same call: for a findMany with one relation it runs one query for the parents and one for the children, not one per parent.

You reintroduce the problem yourself by looping:

const users = await prisma.user.findMany();

for (const user of users) {
  // One query per user: this is the N+1.
  const posts = await prisma.post.findMany({ where: { authorId: user.id } });
}

Load the relation in the original query instead:

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

For deeply nested reads, Prisma’s default strategy issues one query per relation level. Where a single joined query is materially faster, the relationLoadStrategy: "join" option tells Prisma to use a LEFT JOIN instead. Measure before switching, because the two strategies trade round trips against duplicated parent columns.

Best practices

• Treat schema.prisma as the source of truth and change it through migrations, never by hand.
• Instantiate PrismaClient once per process and reuse it; guard hot reload with a global.
• Use select for fixed response shapes and include only when you want whole records.
• Prefer cursor pagination over large skip offsets.
• Use createMany for bulk inserts and nested writes for related rows.
• Keep interactive transactions short and always use the tx client inside them.
• Reach for $queryRaw with tagged templates, never the Unsafe variants, when you need SQL.
• Set connection_limit and, in serverless, use a pooler such as Accelerate or PgBouncer.
• Commit migration folders and run migrate deploy in CI/CD rather than db push.

Common mistakes

• Running prisma db push against production and losing columns.
• Creating a new PrismaClient per request and exhausting connections.
• Forgetting prisma generate after a schema change, then wondering why types are stale.
• Fetching full rows with include and trimming fields in JavaScript.
• Using the outer client inside an interactive transaction, which silently leaves the transaction.
• Ignoring where on updateMany or deleteMany and updating every row.
• Assuming findUnique accepts any filter; it requires a unique field.
• Interpolating user input into $queryRawUnsafe.
• Holding a transaction open across an awaited external API call.

Where to go next

Prisma is one answer to how to talk to a relational database from TypeScript. Compare it with Drizzle, which keeps the SQL visible and skips the generated client, and TypeORM, the class-and-decorator approach that predates both. Underneath every one of them is the database itself, so the PostgreSQL guide is depth that pays off regardless of ORM. If you are still choosing a runtime for the server that hosts all this, start with Node.js.