Prisma with Postgres: Schema, Migrations, and the Connection Trap

Q: Which Prisma migration command should run in production?

Always prisma migrate deploy. It applies pending migrations and nothing else. prisma migrate dev is a development tool that can reset the database when it detects drift in the migration history.

Q: Should I use Json or JsonB in a Prisma schema?

JsonB, via @db.JsonB, in almost every case. It stores a parsed binary form that supports GIN indexing and containment queries. The plain json type keeps the raw text and cannot be indexed usefully.

Q: Can I write raw SQL with Prisma?

Yes. $queryRaw is a tagged template that parameterises interpolated values automatically, making it safe against injection. Use it for window functions, recursive CTEs, and anything the query builder makes awkward.

Prisma gives you a typed client generated from a schema file, and against PostgreSQL that combination is genuinely good — right up until you deploy it serverless and exhaust the connection limit.

Prisma’s pitch is straightforward: describe your data model once, get a fully typed client and a migration system for free. Against PostgreSQL it delivers on that, and the developer experience is a real step up from writing SQL strings and hoping the shapes line up at runtime.

The part that catches teams out is not the modelling. It is what happens when the application scales horizontally and every instance opens its own pool against a database with a fixed connection ceiling.

Setting up Prisma against a Postgres database
Modelling a schema that reflects Postgres properly
Migrations: the difference between dev and deploy
The connection pooling trap
Querying, and when to stop using the query builder
How this fits the rest of the stack
FAQ

Setting up Prisma against a Postgres database

Prisma reads a schema file, generates a client from it, and talks to the database over a connection string. The setup is three commands and one environment variable.

npm install prisma --save-dev
npm install @prisma/client
npx prisma init --datasource-provider postgresql

That creates prisma/schema.prisma and a .env referencing DATABASE_URL. Point it at your instance and keep it out of version control — this string is a credential, not configuration.

DATABASE_URL="postgresql://app:[email protected]:5432/appdb?schema=public"

Modelling a schema that reflects Postgres properly

The schema file is where most of the value lives. Prisma maps its types onto Postgres types, and being explicit here means the database enforces your constraints rather than trusting the application to.

model User {
  id        String   @id @default(uuid()) @db.Uuid
  email     String   @unique
  createdAt DateTime @default(now()) @db.Timestamptz(6)
  posts     Post[]

  @@map("users")
}

model Post {
  id        BigInt   @id @default(autoincrement())
  title     String   @db.VarChar(200)
  body      String?
  published Boolean  @default(false)
  metadata  Json?    @db.JsonB
  authorId  String   @db.Uuid
  author    User     @relation(fields: [authorId], references: [id], onDelete: Cascade)

  @@index([authorId, published])
  @@map("posts")
}

Three details worth copying. Use @db.Timestamptz rather than the default timestamp, because a timestamp without a timezone is a bug waiting for a daylight-saving transition. Use @db.JsonB rather than Json, because jsonb is indexable and json is not. And declare onDelete explicitly so the referential behaviour lives in the database instead of in whichever code path happens to run.

The @@map directives keep your database naming convention independent of your client naming convention, which matters if anything other than Prisma ever reads these tables.

Migrations: the difference between dev and deploy

Prisma has two migration commands and using the wrong one in production is a genuinely bad day.

# Development: diffs the schema, writes a migration, applies it,
# and will happily reset the database if it detects drift.
npx prisma migrate dev --name add_post_metadata

# Production: applies pending migrations only. Never resets, never prompts.
npx prisma migrate deploy

migrate dev is a development tool. It can drop and recreate your database when the migration history does not match what it finds. migrate deploy is the one that belongs in a deployment pipeline — it applies existing migration files and nothing else.

Read the generated SQL before you commit it. Prisma writes plain SQL into prisma/migrations/, and the file is reviewable like any other code. This is where you catch the migration that adds a NOT NULL column to a large table and takes a lock for the duration.

For an index on a live table, edit the generated migration to use CREATE INDEX CONCURRENTLY. Prisma will not do it for you, and the default form takes a write lock on the table while it builds.

The connection pooling trap

This is the failure that surprises people, and it looks like a database problem rather than an architecture one. PrismaClient maintains its own connection pool. One client, one pool, several connections held open.

Instantiate PrismaClient per request, or deploy to an environment that spins up many short-lived instances, and each one opens a pool against a database whose max_connections is a fixed number — commonly 100, often lower on managed plans. The symptom is too many clients already, usually under exactly the load you were hoping to handle.

// Wrong: a new pool on every module reload or request
export function handler() {
  const prisma = new PrismaClient();
  return prisma.user.findMany();
}

// Right: one client per process, reused across requests
const globalForPrisma = globalThis;
export const prisma =
  globalForPrisma.prisma ?? new PrismaClient();
if (process.env.NODE_ENV !== "production") globalForPrisma.prisma = prisma;

The singleton pattern fixes the development case, where hot reloading creates a client per reload. It does not fix the serverless case, where each concurrent invocation is a separate process holding a separate pool.

For that, put an external pooler in front — PgBouncer in transaction mode, or a managed pooling endpoint. Then set connection_limit=1 in the Prisma connection string so each instance holds a single connection and the pooler does the multiplexing. Add pgbouncer=true so Prisma disables prepared statements, which transaction-mode pooling does not support.

DATABASE_URL="postgresql://app:[email protected]:6432/appdb?pgbouncer=true&connection_limit=1"

Querying, and when to stop using the query builder

The generated client is typed against your schema, so a typo in a field name is a compile error rather than a runtime surprise. Relations load through include or select, and select is the one you want by default — include fetches whole related rows you may not need.

const authors = await prisma.user.findMany({
  where: { posts: { some: { published: true } } },
  select: {
    email: true,
    posts: {
      where: { published: true },
      select: { title: true, createdAt: true },
      orderBy: { createdAt: "desc" },
      take: 5,
    },
  },
});

Prisma’s nested reads issue multiple queries rather than a single join. That is usually fine and occasionally not — a deeply nested read in a hot path can turn into a query count you did not intend. Turn on query logging in development and look at what actually runs.

When a query gets genuinely complex — window functions, recursive CTEs, anything where you are fighting the builder — drop to raw SQL. $queryRaw is a tagged template that parameterises interpolated values, so it is safe by default. Reserve $queryRawUnsafe for cases where you are building the SQL string yourself, and treat it with the suspicion that name deserves.

const rows = await prisma.$queryRaw`
  SELECT author_id, count(*) AS total
  FROM posts
  WHERE created_at > ${since}
  GROUP BY author_id
  HAVING count(*) > ${threshold}
`;

How this fits the rest of the stack

Prisma’s connection behaviour is really a deployment question: how many instances of your service exist, and what connection ceiling does the database enforce. Those two numbers need to be visible in the same place. RunxBuild provisions the service and the managed Postgres instance together, over a private network, with the connection limit stated up front — and the RunxBuild hosting calculator shows the service and the database as separate line items so you can size the pool against the plan before the first deploy rather than after the first outage.

Useful related references:

FAQ

Which Prisma migration command should run in production?

Always prisma migrate deploy. It applies pending migrations and nothing else. prisma migrate dev is a development tool that can reset the database when it detects drift in the migration history.

Why does Prisma exhaust my Postgres connections?

Each PrismaClient instance holds its own pool. Creating a client per request, or running many short-lived serverless instances, multiplies pools against a fixed max_connections. Use a single client per process, and an external pooler with connection_limit=1 for serverless.

Should I use Json or JsonB in a Prisma schema?

JsonB, via @db.JsonB, in almost every case. It stores a parsed binary form that supports GIN indexing and containment queries. The plain json type keeps the raw text and cannot be indexed usefully.

Can I write raw SQL with Prisma?

Yes. $queryRaw is a tagged template that parameterises interpolated values automatically, making it safe against injection. Use it for window functions, recursive CTEs, and anything the query builder makes awkward.

Does Prisma support CREATE INDEX CONCURRENTLY?

Not automatically, but migrations are plain SQL files you can edit. For an index on a table already carrying traffic, change the generated statement to the concurrent form so it does not hold a write lock while building.