I'm building a web app with PostgreSQL and Prisma, what should I watch out for?

Using PostgreSQL with Prisma is a solid choice, but there are a few common traps to watch for so you don’t get surprised later.

1) Know Prisma’s abstraction limits

Prisma is great for type-safe CRUD, but not every PostgreSQL feature maps cleanly.

Watch out for:

• Advanced SQL features like CTEs, window functions, materialized views, partial indexes, custom operators
• Triggers, rules, stored procedures — Prisma won’t manage these well
• Database-specific types that may need raw SQL or special handling

If you need these, you’ll often use:

• prisma.$queryRaw
• prisma.$executeRaw
• custom SQL migrations

2) Migrations need discipline

Prisma migrations are easy to use, but you should treat them carefully in production.

Common issues:

• Renaming a field can accidentally become “drop + add” behavior if handled poorly
• Large tables can make schema changes expensive
• Some changes lock tables or rewrite data in PostgreSQL

Good practices:

• Review generated SQL before applying to production
• Test migrations on a copy of production-like data
• Avoid huge “one-shot” migrations during peak traffic
• Prefer additive changes first, then cleanup later

3) Be careful with NULL vs optional vs empty

This is a frequent source of bugs.

In Prisma:

• String? means nullable
• A field can be absent, null, or empty string depending on your app logic

Make sure your app has a consistent rule for:

• optional fields
• empty strings
• missing relations

If not, validation bugs and odd query behavior show up quickly.

4) Understand relation loading

Prisma’s relation loading is convenient, but it can create performance issues if you’re not careful.

Watch out for:

• fetching too much nested data
• accidental N+1-style patterns in your application logic
• huge JSON payloads from deep includes

Tips:

• only select the fields you need
• use pagination on relation-heavy endpoints
• benchmark queries that load nested relations

5) Indexing matters a lot

PostgreSQL is powerful, but only if you index correctly.

Make sure you index:

• foreign keys
• columns used in WHERE
• columns used in ORDER BY
• unique fields
• common composite filters

Also consider:

• composite indexes for multi-column lookups
• partial indexes for special cases
• text search strategies if needed

Prisma lets you define indexes in schema, but for advanced index types you may need SQL migrations.

6) Watch transaction boundaries

Some operations need to be atomic.

Common pitfalls:

• creating a user and related records separately
• updating multiple rows without a transaction
• assuming Prisma calls are atomic when they aren’t

Use transactions for:

• multi-step writes
• money-related operations
• inventory or quota updates
• consistency-critical workflows

7) Connection pooling can bite you

This matters a lot in serverless or highly concurrent environments.

Problems:

• too many Prisma client instances
• exhausting PostgreSQL connections
• slow cold starts or connection storms

Best practices:

• create a singleton Prisma client in long-lived app processes
• use a pooler like PgBouncer if needed
• be extra careful in serverless deployments
• monitor connection usage

8) Watch out for enum changes

Prisma enums are convenient, but changing them in production can be tricky.

Things to know:

• removing enum values can break old rows
• adding enum values may require coordinated deploys
• PostgreSQL enum changes are more rigid than app-level enums

Safer approach:

• add new values first
• deploy code that supports both old and new
• migrate data
• remove old values later if needed

9) Don’t overuse include

It’s easy to ask Prisma for a whole graph of related data, but that can become expensive fast.

Prefer:

• select over include when possible
• fetching only what the UI or API actually needs
• splitting large queries into smaller targeted ones when appropriate

10) Raw SQL still has a place

Even with Prisma, you’ll sometimes need raw SQL for:

• performance tuning
• complex aggregations
• advanced PostgreSQL features
• bulk operations

Use it when necessary, but:

• parameterize inputs to avoid SQL injection
• keep raw SQL isolated and documented
• add tests around critical queries

11) Data validation should not rely only on Prisma

Prisma schema constraints help, but they’re not a full validation layer.

You still need app-level validation for:

• business rules
• cross-field checks
• request shape and formatting
• permissions and ownership logic

Use something like Zod, Valibot, Joi, or your framework’s validation tools.

12) Plan for production observability

You’ll want visibility into:

• slow queries
• failed migrations
• connection counts
• deadlocks / lock waits
• query patterns

At minimum:

• log slow requests and query durations
• enable PostgreSQL logging for slow queries
• monitor database size and index bloat
• have a rollback strategy for schema changes

---

Practical checklist

If you want a quick checklist:

• Use select instead of loading full objects
• Index foreign keys and common filters
• Review migration SQL before production
• Use transactions for multi-step writes
• Keep Prisma Client as a singleton
• Be cautious with enums and nullable fields
• Use raw SQL when Prisma becomes awkward
• Monitor query performance and connection counts

If you want, I can also give you:

1. a Prisma/PostgreSQL production checklist, or
2. a list of common schema design mistakes with examples.