PostgreSQL + Prisma: our default data layer

Almost every full stack project we take on at Arventra opens with the same question: what database? Our answer for the last four years has been the same — PostgreSQL with Prisma on top. It's boring, well-understood, and it scales further than most teams ever need. This post is the long-form version of the argument, with the schema patterns, migration discipline, connection-pooling setup, and edge-runtime details we use on every project.

Postgres vs MongoDB: why Postgres wins ~90% of SaaS

Most products we build are relational at heart — users have organizations, organizations have projects, projects have items, items have comments. Postgres handles that natively with joins, foreign keys, and transactions. Document databases force you to denormalize the same data into multiple shapes, and consistency becomes your problem in application code.

Postgres also ships features that used to require separate services: JSONB for genuinely schemaless fields, full-text search good enough to skip Elasticsearch on most projects, row-level security for multi-tenant isolation, logical replication for read replicas, and partitioning for large tables.

Why Prisma is our default ORM

Prisma earns its keep through one feature: the generated TypeScript client matches your schema exactly. Add a column, run prisma generate, and every query in the codebase that touches that column is type-checked. We catch entire categories of bugs at build time that used to ship to production.

A typical schema:

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

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

model Organization {
  id        String    @id @default(cuid())
  name      String
  slug      String    @unique
  createdAt DateTime  @default(now())
  members   Member[]
  projects  Project[]
}

model Project {
  id      String  @id @default(cuid())
  name    String
  orgId   String
  org     Organization @relation(fields: [orgId], references: [id], onDelete: Cascade)
  items   Item[]

  @@index([orgId])
}

model Item {
  id        String   @id @default(cuid())
  title     String
  metadata  Json?
  projectId String
  project   Project  @relation(fields: [projectId], references: [id], onDelete: Cascade)
  createdAt DateTime @default(now())

  @@index([projectId, createdAt(sort: Desc)])
}

And a fully typed query — note that the result type is inferred, including the nested relations:

const project = await db.project.findFirst({
  where: { org: { slug: "acme" } },
  include: {
    items: { orderBy: { createdAt: "desc" }, take: 20 },
    org: true,
  },
});
// project.items[0].title  — string, type-checked
// project.org.name        — string, type-checked

Migration discipline that survives a team

Migrations are where most ORMs quietly break down. Our rules, refined across dozens of production deploys:

1. Never edit a migration after it has been applied to any shared environment. Roll forward with a new migration instead. Editing a committed migration causes the team's local databases to drift in ways that only surface weeks later.
2. Every PR that changes schema.prisma includes its generated migration. Reviewers read the SQL, not just the schema diff. prisma migrate dev --name describe_the_change produces the file; commit both.
3. Run migrations in a separate CI step before the app deploys. If migrations fail, the app does not roll forward. We use prisma migrate deploy against the production database from a dedicated job.
4. Backfill in code, not in migrations when the dataset is large. Migrations should be fast and idempotent. A separate one-shot script with batching and progress logging is safer.
5. Use shadow databases in CI to catch migration ordering bugs before they hit staging.

Connection pooling on serverless and edge

Postgres uses one OS process per connection. Serverless functions can spin up hundreds of cold instances. Without pooling, you exhaust the database in minutes. The standard pattern:

// .env
DATABASE_URL="postgresql://user:pass@host:6543/db?pgbouncer=true&connection_limit=1"
DIRECT_URL="postgresql://user:pass@host:5432/db"

DATABASE_URL points at a pooler (PgBouncer / Neon proxy / Supabase pooler) — your application connects here. DIRECT_URL bypasses the pooler — migrations and Prisma's introspection use this because they need session-level features the pooler can't proxy.

Our standard managed-Postgres choices:

• Neon for greenfield projects — branchable databases per PR, instant read replicas, generous free tier.
• Supabase when the project also needs auth, realtime, or storage in one stack.
• RDS / Aurora for enterprise clients with existing AWS commitments.
• Prisma Accelerate when we need true edge-runtime Postgres calls (Cloudflare Workers, Vercel Edge).

The Prisma client singleton (Next.js gotcha)

In dev, Next.js hot-reloads frequently. A naive new PrismaClient() per module creates dozens of connections. The fix:

// lib/db.ts
import { PrismaClient } from "@prisma/client";

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
};

export const db = globalForPrisma.prisma ?? new PrismaClient();
if (process.env.NODE_ENV !== "production") globalForPrisma.prisma = db;

When we reach past Prisma

Prisma isn't the right tool for everything. We drop into raw SQL (via $queryRaw) or a thinner layer like Drizzle when we need:

• Complex window functions, CTEs, or recursive queries that read better in plain SQL.
• Tight control over the generated query plan for hot paths.
• A genuinely tiny bundle on the edge where every kilobyte of client matters.
• Bulk operations on millions of rows where Prisma's per-row overhead becomes measurable.

Frequently asked questions

Is Prisma fast enough for high-traffic production?

Yes, with one caveat: enable the query engine binary that matches your deploy target, use a connection pooler, and don't fetch deep nested relations when a single join would do. We have Prisma-backed APIs serving tens of thousands of requests per minute on modest hardware.

What about Drizzle vs Prisma?

Drizzle is excellent — smaller bundle, SQL-first syntax, no separate query engine. We pick it when bundle size on the edge is critical or when the team strongly prefers a SQL-style API. For most full stack projects, Prisma's developer experience and migration tooling still win.

How do you handle multi-tenant data isolation?

Two layers. First, every query is filtered by organizationId in application code (we centralize this in a session-aware DB wrapper). Second, Postgres row-level security enforces the same rule at the database — so a bug in app code can't leak tenant A's data to tenant B.

Should I use JSONB or separate tables?

Separate tables for anything you query, sort, or join on. JSONB for genuinely opaque payloads (webhook bodies, user-customizable form configs, settings blobs). The "I'll just put it in JSON" instinct is almost always wrong by month six.

What about Postgres extensions like pgvector for AI?

We use pgvector heavily for AI features — embedding storage and similarity search inside the same Postgres that holds your operational data, no separate vector DB needed. Prisma supports it via the unsupported type plus raw SQL for the vector queries.

The bottom line

For new projects, this stack — Postgres + Prisma + TypeScript — is the boring, fast, scalable default we recommend to almost every client. It pairs perfectly with the Next.js 15 + RSC frontend we covered in the previous post.

If you'd like our team to architect or rebuild your data layer, talk to our full stack engineers.