Prisma fundamentals explained

What Prisma is

Prisma splits into three tools most teams use daily:

• Prisma Schema — a declarative DSL for models, fields, relations, enums, and indexes. One file is the source of truth for both the database shape and generated types.
• Prisma Client — a type-safe query API generated from that schema (prisma.user.findMany(), create, update, nested writes).
• Prisma Migrate — versioned SQL migrations derived from schema diffs, applied in CI and production with history tracked in _prisma_migrations.

Prisma supports PostgreSQL, MySQL, SQLite, SQL Server, MongoDB (document model), and CockroachDB. PostgreSQL is the most common production pairing because JSONB, partial indexes, and advisory locks map cleanly to Prisma attributes. The runtime is JavaScript/TypeScript only — Python and Go teams use SQLAlchemy or sqlc instead.

Prisma is not a database server. It is a client library that speaks SQL (or the Mongo wire protocol) to your existing instance. You still size connections, run backups, and tune indexes on the engine underneath.

The Prisma schema language

A minimal schema has three blocks: datasource (connection URL), generator (outputs Prisma Client), and model definitions.

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

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

model Order {
  id        String   @id @default(cuid())
  status    OrderStatus @default(PENDING)
  totalCents Int
  createdAt DateTime @default(now())
  lines     OrderLine[]
}

enum OrderStatus {
  PENDING
  PAID
  SHIPPED
  CANCELLED
}

Field types map to SQL columns: String, Int, BigInt, Boolean, DateTime, Decimal, Json. Attributes control behavior:

• @id and @default(uuid()) or cuid() for primary keys.
• @unique for natural keys (email, slug).
• @relation for foreign keys with explicit fields and references.
• @@index([status, createdAt]) for compound indexes declared in schema.
• @map and @@map to rename columns/tables without changing model names in code.

Keep models aligned with how your API names things; use @map when the database uses snake_case legacy tables. Run npx prisma format in CI so schema style stays consistent across contributors.

Prisma Client query patterns

After npx prisma generate, import a singleton client (one instance per Node process to avoid connection exhaustion):

import { PrismaClient } from '@prisma/client'
export const prisma = new PrismaClient()

Reads

findUnique uses unique indexes (primary key or @unique). findFirst and findMany accept where, orderBy, take, skip, and cursor pagination via cursor + skip: 1. Use select to fetch only needed columns; use include to eager-load relations in one round trip and avoid N+1 queries.

Writes

create, update, upsert, and delete map to single-row operations. createMany and updateMany batch without returning rows (engine-dependent skipDuplicates on Postgres). Nested writes create parent and children atomically: prisma.order.create({ data: { lines: { create: [...] } } }).

Filters

The where object supports equals, in, contains, startsWith, relation filters (some, every, none), and boolean AND / OR / NOT. For full-text or geo queries Prisma does not model natively, drop to $queryRaw with tagged template literals and parameterized values.

Migrations with Prisma Migrate

Development workflow:

1. Edit schema.prisma.
2. Run npx prisma migrate dev --name add_order_status — creates a timestamped SQL folder under prisma/migrations/ and applies it locally.
3. Commit migration SQL to git — teammates and CI replay the same history.
4. Production: npx prisma migrate deploy in the release pipeline (no prompts).

prisma db push syncs schema without migration files — fine for throwaway prototypes, dangerous for production because there is no auditable history. Use Migrate for anything with users and data.

prisma migrate diff and prisma migrate resolve help when a migration fails mid-deploy or when integrating a database that already exists (prisma db pull introspects into schema, then baseline with migrate resolve --applied).

Relations and transactions

Relation kinds

• One-to-many — foreign key on the “many” side (OrderLine.orderId).
• One-to-one — unique constraint on the FK column.
• Many-to-many — implicit join table (Prisma creates _ModelAToModelB) or explicit join model with extra columns (quantity, role).

Always define both sides of a relation in schema so Prisma can traverse in either direction. Cascade deletes use onDelete: Cascade on the relation field — default is Restrict, which surfaces orphan errors instead of silent data loss.

Transactions

Wrap multi-step logic in prisma.$transaction(async (tx) => { ... }) for interactive transactions (adjust inventory, insert order, decrement stock in one unit). Pass an array of Prisma promises for batch transactions when isolation between steps is not needed. Serializable isolation for financial ledgers still requires explicit SET TRANSACTION via raw SQL on Postgres.

Connection pooling and serverless

Each Prisma Client instance opens a pool of connections to the database. In long-lived Node servers, one singleton per process is enough. In serverless (Vercel, AWS Lambda), every cold start can spawn a new pool and exhaust Postgres max_connections.

Mitigations:

• PgBouncer or Neon/Supabase pooler in front of Postgres; point DATABASE_URL at the pooler port with ?pgbouncer=true for transaction mode.
• Prisma Accelerate or Prisma Data Proxy — managed connection pooling and optional edge caching (evaluate cost vs self-hosted PgBouncer).
• Limit pool size via connection_limit in the URL for small Lambdas.
• Disconnect in short-lived scripts with await prisma.$disconnect() — not in warm serverless handlers where reuse matters.

Read replicas: Prisma 5+ supports read replicas via client extensions or separate datasource URLs; route analytics findMany to replica clients and writes to primary.

Worked example: Harbor Supply order API

Harbor Supply runs a B2B parts catalog on Next.js API routes and PostgreSQL. Their Prisma schema models Customer, Order, OrderLine, and Sku with a many-to-one from lines to SKU for price snapshots.

Checkout flow in one interactive transaction:

1. tx.sku.findMany with where: { id: { in: skuIds } } and row locks via raw FOR UPDATE on high-contention SKUs during flash sales.
2. Validate stock ≥ requested quantity; abort with a typed error if not.
3. tx.order.create with nested lines.create storing unitPriceCents at purchase time (not live SKU price).
4. tx.sku.updateMany decrementing stockQty.

Admin dashboards use include: { lines: { include: { sku: true } } } on order detail pages. List endpoints use select for id, status, and total only — cutting payload size 80% vs loading full graphs. Slow reports (monthly revenue by category) run as parameterized $queryRaw with GROUP BY because Prisma’s aggregation API is awkward for multi-join rollups. Migrations ship through GitHub Actions: prisma migrate deploy before rolling new app containers.

ORM decision table

Common pitfalls

• N+1 queries — loading orders then fetching lines per order in a loop; fix with include or dataloaders.
• New client per request — exhausts connections; use a module singleton.
• db push in production — no migration audit trail; use migrate deploy.
• Missing indexes on filter columns — Prisma does not auto-index every where field; align with your indexing guide.
• Over-fetching with include — deep graphs on list endpoints; use select and pagination.
• Decimal as float — use Prisma Decimal type and decimal.js for money, not JavaScript number.
• Serverless without pooler — connection storms during traffic spikes.
• Ignoring migration drift — hand-edited prod schema without updating schema.prisma breaks the next deploy.

Production checklist

• Single Prisma Client instance per Node process; document serverless pooling strategy.
• All schema changes through Prisma Migrate; migrations committed and reviewed like application code.
• DATABASE_URL and secrets in env vars, never in schema files.
• Indexes on foreign keys and frequent where / orderBy columns declared in schema or verified with EXPLAIN.
• Integration tests against a disposable Postgres (Testcontainers or CI service) with prisma migrate deploy before test run.
• prisma generate in CI and postinstall so Client matches schema.
• Logging: enable query logging in staging only; use OpenTelemetry Prisma instrumentation in prod for slow-query alerts.
• Graceful shutdown: $disconnect() on SIGTERM in long-running servers.
• Raw SQL for reporting paths documented and parameterized — no string concatenation.
• Quarterly review: drop unused models, archive old migrations policy, validate pool metrics under peak load.

Key takeaways

• Prisma trades a code-generation step for compile-time safety and a unified schema + migration story.
• Prisma Client excels at typed CRUD and nested writes; complex SQL still belongs in raw queries.
• Migrate is the production path; treat db push as dev-only.
• Connection pooling is mandatory for serverless Postgres, not optional tuning.
• Pair Prisma with solid database fundamentals — the ORM does not replace index design or backup discipline.

Related reading

• PostgreSQL fundamentals explained — MVCC, WAL, and indexing under your Prisma models
• Next.js fundamentals explained — API routes and server components that commonly host Prisma Client
• TypeScript fundamentals explained — types and modules that Prisma Client extends
• Database connection pooling explained — PgBouncer and pool sizing for ORM workloads