Prisma Advanced Patterns I Wish I Knew When Starting Our Production App

Prisma is the ORM I recommend for TypeScript applications. The type safety is excellent, the schema language is readable, and the basic CRUD operations are intuitive. You can be productive within an hour of starting.

But production applications hit edge cases that the getting-started guide does not cover. Transactions that need to be atomic. Queries that are too complex for the query builder. Connection pools that exhaust under load. Middleware for logging and soft deletes.

Here are the patterns I learned after a year of running Prisma in production at AIDToolStack.

Pattern 1: Interactive Transactions for Complex Operations

Basic Prisma queries are individually atomic — each query either succeeds or fails completely. But what about operations that require multiple queries to succeed together?

Example: creating a tool listing that also creates a category if it does not exist, adds tags, and logs the creation event. If the tag creation fails, you do not want a tool without tags in the database.

Interactive transactions: wrap multiple operations in prisma.$transaction(). All operations either commit together or roll back together. If any operation fails, none of them persist.

The callback receives a transaction client (tx) that you use instead of prisma for all queries within the transaction. This ensures all queries run in the same database transaction.

Important: keep transactions short. Long-running transactions hold database locks and can cause deadlocks or timeout under concurrent load. Do not make external API calls inside transactions.

Pattern 2: The Singleton Pattern for the Prisma Client

In development with hot module reloading, each file change creates a new Prisma client instance. Without the singleton pattern, you quickly exhaust database connections — each client opens its own connection pool.

The pattern: create a global variable that persists across hot reloads. In development, store the client on globalThis. In production, create a single instance normally.

This is in our lib/db.ts file. Every file that needs database access imports from this single location. Never create PrismaClient directly in component or route files.

Pattern 3: Middleware for Cross-Cutting Concerns

Prisma middleware intercepts every query before and after execution. Use it for:

Query logging: log every query with its duration. In development, this helps identify N+1 queries and slow operations. In production, log only queries exceeding a threshold (e.g., 500ms).

Soft deletes: instead of actually deleting records, set a deletedAt timestamp. Middleware intercepts delete operations and converts them to updates. It also intercepts find operations to exclude soft-deleted records automatically.

The soft delete middleware means: application code uses prisma.tool.delete() normally, but the record is preserved in the database with a deletedAt timestamp. Queries automatically exclude deleted records. This is transparent to the application code.

Pattern 4: Raw Queries for Complex Operations

Prisma's query builder handles 95% of queries. For the remaining 5% (complex aggregations, full-text search, database-specific features), use raw SQL.

prisma.$queryRaw for SELECT queries with full TypeScript typing. prisma.$executeRaw for INSERT/UPDATE/DELETE that returns affected row counts.

When I use raw queries at AIDToolStack: full-text search across multiple columns with ranking, complex aggregation queries for analytics (grouped counts with HAVING clauses), database-specific features (PostgreSQL's SIMILAR TO, array operations), and bulk upsert operations that Prisma's createMany does not support efficiently.

Important: always use tagged template literals (Prisma.sql) for raw queries to prevent SQL injection. Never concatenate user input into query strings.

Pattern 5: Connection Pool Configuration

Prisma maintains a connection pool — a set of pre-opened database connections that queries share. Default pool size is based on the formula: num_physical_cpus * 2 + 1.

In serverless environments (Vercel, Lambda), this default causes problems. Each serverless function instance opens its own pool. With 50 concurrent function instances, each with a pool of 5 connections, you have 250 connections to your database — likely exceeding its limit.

For serverless: set connection_limit=1 in the database URL (each instance uses one connection). Use an external connection pooler (PgBouncer, Neon's pooler, PlanetScale's built-in pooling) to manage connections efficiently.

For long-running servers: the default pool size is usually fine. Monitor with connection_limit and pool_timeout parameters. If queries are waiting for connections (timeout errors), increase the pool size.

Pattern 6: Optimistic Concurrency Control

When multiple users can edit the same record simultaneously, you need concurrency control. Without it, the last writer wins and silently overwrites the other's changes.

The pattern: add a version field (integer) to your model. When updating, include the current version in the WHERE clause. If another update changed the version between your read and write, the update affects zero rows — indicating a conflict.

Check the result: if count is 0, the record was modified by someone else. Show the user a conflict message with both versions and let them decide which changes to keep.

Pattern 7: Efficient Pagination

For cursor-based pagination with Prisma: use the cursor parameter with a unique, sortable field (usually the ID or a timestamp).

The pattern: findMany with take (page size), cursor (starting point), and skip: 1 (skip the cursor item itself to avoid duplicates).

For the first page: omit cursor and skip.

For subsequent pages: pass the last item's ID as the cursor.

This is more performant than offset pagination for large datasets because cursor-based pagination does not need to count skipped rows.

Pattern 8: Computed Fields and Virtual Relations

Sometimes you need fields that do not exist in the database but are computed from other fields. Prisma does not have built-in computed fields, but you can extend the result types.

Create a helper function that takes a Prisma result and returns it with additional computed fields. Or use Prisma client extensions (available since Prisma 4.16) to add computed fields directly to the query results.

Example: a tool's popularity score computed from views, bookmarks, and review count. Store the raw counts in the database, compute the score in the application layer.

Pattern 9: Seeding and Data Management

Our seed file (the one you are reading about in this blog post!) follows a pattern: define data as typed arrays, then upsert each record. Upsert (update or create) is idempotent — running the seed multiple times produces the same result without duplicates.

Tips for large seed files: batch operations where possible (createMany for records without unique constraints), use transactions for related records that must exist together, and add progress logging (console.log after each batch) so you know the seed is progressing.

Pattern 10: Type-Safe JSON Fields

Some data does not fit neatly into relational columns. Tags, metadata, and configuration often work better as JSON. But JSON fields lose type safety — Prisma types them as JsonValue (essentially any).

Our pattern: define a TypeScript type for the JSON shape, create helper functions that parse and validate JSON fields, and use Zod for runtime validation when reading JSON from the database.

This gives us: type safety when working with JSON data in application code, runtime validation that catches corrupt data, and the flexibility of schemaless storage for fields that vary between records.

The Migration Workflow

Our migration workflow: make schema changes in schema.prisma, run prisma migrate dev to create a migration file, review the generated SQL (sometimes Prisma's migration is not optimal), test the migration on a copy of production data, and apply to production with prisma migrate deploy.

Important: always review generated migrations. Prisma sometimes generates destructive operations (dropping and recreating a column instead of renaming) that lose data. The generated SQL is a starting point — edit it if necessary.

For zero-downtime migrations: use expand-and-contract pattern. Add the new column (nullable), deploy code that writes to both old and new columns, backfill the new column, deploy code that reads from the new column, and finally remove the old column in a separate migration.

Prisma is a powerful tool that scales from weekend projects to production applications handling millions of queries. These patterns are not necessary from day one — adopt them as your application grows and your needs become more sophisticated.