Prisma Advanced Patterns: Middleware, Query Extensions, Raw Queries, and Batch Operations

Prisma's query API covers 90% of use cases with clean, type-safe syntax. The remaining 10% — soft deletes, tenant scoping, audit logging, raw SQL for complex aggregates, and transactional batch imports — requires understanding Prisma's extension and middleware system. Done right, these patterns let you add cross-cutting concerns without polluting every query call site.

This post covers Prisma client extensions ($extends), the older middleware API for comparison, raw queries with $queryRaw and $executeRaw, interactive transactions, and migration strategies for production databases.

1. Client Extensions with $extends

$extends is the modern (Prisma 4.16+) way to extend the client. It's composable, tree-shakeable, and type-safe — unlike the older middleware API which was global and opaque.

// src/lib/db/extensions/soft-delete.ts
import { Prisma } from '@prisma/client';

// Models that support soft delete
type SoftDeletableModel = 'post' | 'comment' | 'organization';

export const softDeleteExtension = Prisma.defineExtension({
  name: 'softDelete',
  query: {
    $allModels: {
      // Intercept all findMany/findFirst/findUnique — exclude soft-deleted rows
      async findMany({ model, args, query }) {
        if (isSoftDeletable(model)) {
          args.where = { ...args.where, deletedAt: null };
        }
        return query(args);
      },

      async findFirst({ model, args, query }) {
        if (isSoftDeletable(model)) {
          args.where = { ...args.where, deletedAt: null };
        }
        return query(args);
      },

      async findUnique({ model, args, query }) {
        if (isSoftDeletable(model)) {
          // findUnique → findFirst for soft delete support
          return (query as any)({ ...args, where: { ...args.where, deletedAt: null } });
        }
        return query(args);
      },

      // Intercept delete → update deletedAt instead
      async delete({ model, args, query }) {
        if (isSoftDeletable(model)) {
          return (query as any)({
            ...args,
            data: { deletedAt: new Date() },
          });
        }
        return query(args);
      },

      async deleteMany({ model, args, query }) {
        if (isSoftDeletable(model)) {
          return (query as any)({
            ...args,
            data: { deletedAt: new Date() },
          });
        }
        return query(args);
      },
    },
  },
});

function isSoftDeletable(model: string): model is SoftDeletableModel {
  return ['post', 'comment', 'organization'].includes(model.toLowerCase());
}

// src/lib/db/extensions/audit-log.ts
import { Prisma } from '@prisma/client';

export const auditLogExtension = Prisma.defineExtension({
  name: 'auditLog',
  query: {
    $allModels: {
      async create({ model, args, query }) {
        const result = await query(args);
        await logAuditEvent(model, 'CREATE', null, result);
        return result;
      },

      async update({ model, args, query }) {
        // Capture before state
        const before = await (Prisma as any)[model].findUnique({ where: args.where });
        const result = await query(args);
        await logAuditEvent(model, 'UPDATE', before, result);
        return result;
      },

      async delete({ model, args, query }) {
        const before = await (Prisma as any)[model].findUnique({ where: args.where });
        const result = await query(args);
        await logAuditEvent(model, 'DELETE', before, null);
        return result;
      },
    },
  },
});

async function logAuditEvent(
  model: string,
  operation: 'CREATE' | 'UPDATE' | 'DELETE',
  before: unknown,
  after: unknown
): Promise<void> {
  // Don't audit the audit log itself — infinite loop
  if (model.toLowerCase() === 'auditlog') return;

  // Fire and forget — don't block the main operation
  setImmediate(async () => {
    try {
      await rawDb.auditLog.create({
        data: {
          model,
          operation,
          before: before ? JSON.stringify(before) : null,
          after: after ? JSON.stringify(after) : null,
          occurredAt: new Date(),
        },
      });
    } catch (err) {
      console.error('Audit log write failed:', err);
    }
  });
}

// src/lib/db/client.ts — compose extensions
import { PrismaClient } from '@prisma/client';
import { softDeleteExtension } from './extensions/soft-delete';
import { auditLogExtension } from './extensions/audit-log';
import { rlsExtension } from './extensions/rls';

const rawDb = new PrismaClient({
  log: process.env.NODE_ENV === 'development'
    ? [{ emit: 'event', level: 'query' }]
    : ['error'],
});

// Extensions compose left to right — each wraps the previous
export const db = rawDb
  .$extends(softDeleteExtension)
  .$extends(auditLogExtension)
  .$extends(rlsExtension);

// Query logging in development
if (process.env.NODE_ENV === 'development') {
  rawDb.$on('query', (e) => {
    if (e.duration > 100) {
      console.warn(`Slow query (${e.duration}ms): ${e.query}`);
    }
  });
}

export type ExtendedPrismaClient = typeof db;

---

2. Model Extensions: Custom Methods

// src/lib/db/extensions/user-methods.ts
import { Prisma } from '@prisma/client';
import bcrypt from 'bcryptjs';

export const userMethodsExtension = Prisma.defineExtension({
  name: 'userMethods',
  model: {
    user: {
      // Add custom static methods to the User model
      async findByEmail(email: string) {
        return Prisma.getExtensionContext(this).findUnique({
          where: { email: email.toLowerCase() },
        });
      },

      async findActiveByTenant(tenantId: string) {
        return Prisma.getExtensionContext(this).findMany({
          where: { tenantId, status: 'active', deletedAt: null },
          orderBy: { createdAt: 'desc' },
        });
      },

      async createWithHashedPassword(data: {
        email: string;
        password: string;
        name: string;
        tenantId: string;
      }) {
        const hashedPassword = await bcrypt.hash(data.password, 12);
        return Prisma.getExtensionContext(this).create({
          data: { ...data, password: hashedPassword, email: data.email.toLowerCase() },
        });
      },
    },
  },
});

// Usage:
// const user = await db.user.findByEmail('alice@example.com');
// const users = await db.user.findActiveByTenant(tenantId);

---

3. Raw Queries

// src/lib/db/raw-queries.ts
import { db } from './client';
import { Prisma } from '@prisma/client';

// $queryRaw: returns typed results
// Use Prisma.sql template tag — prevents SQL injection
export async function getMonthlyRevenue(
  tenantId: string,
  year: number
): Promise<Array<{ month: number; revenue: number; order_count: number }>> {
  return db.$queryRaw`
    SELECT
      EXTRACT(MONTH FROM created_at)::int AS month,
      SUM(amount_cents)::float / 100 AS revenue,
      COUNT(*)::int AS order_count
    FROM orders
    WHERE tenant_id = ${tenantId}::uuid
      AND EXTRACT(YEAR FROM created_at) = ${year}
      AND status = 'completed'
    GROUP BY EXTRACT(MONTH FROM created_at)
    ORDER BY month
  `;
}

// $executeRaw: for INSERT/UPDATE/DELETE that don't return rows
export async function bulkUpdateStatus(
  ids: string[],
  status: string
): Promise<number> {
  const result = await db.$executeRaw`
    UPDATE posts
    SET status = ${status}, updated_at = NOW()
    WHERE id = ANY(${ids}::uuid[])
  `;
  return result; // Number of affected rows
}

// $queryRawUnsafe: when you need dynamic SQL (use sparingly — injection risk)
export async function searchWithDynamicSort(
  tenantId: string,
  sortColumn: 'createdAt' | 'title' | 'viewCount',  // Allowlist — never user input
  sortDir: 'asc' | 'desc'
): Promise<Post[]> {
  // Map to actual column names (safe allowlist)
  const columnMap = {
    createdAt: 'created_at',
    title: 'title',
    viewCount: 'view_count',
  } as const;

  const col = columnMap[sortColumn];
  const dir = sortDir === 'asc' ? 'ASC' : 'DESC';

  return db.$queryRawUnsafe(
    `SELECT * FROM posts WHERE tenant_id = $1 ORDER BY ${col} ${dir} LIMIT 100`,
    tenantId
  );
}

---

4. Interactive Transactions

// src/services/billing/transfer.ts
import { db } from '../../lib/db/client';

// Interactive transaction: full Prisma client available inside callback
export async function transferCredits(
  fromUserId: string,
  toUserId: string,
  amount: number
): Promise<void> {
  await db.$transaction(
    async (tx) => {
      // All operations use the SAME database transaction
      const sender = await tx.wallet.findUnique({
        where: { userId: fromUserId },
        select: { balance: true },
      });

      if (!sender || sender.balance < amount) {
        throw new Error('Insufficient balance');
      }

      // Both updates succeed or both fail (ACID)
      await tx.wallet.update({
        where: { userId: fromUserId },
        data: { balance: { decrement: amount } },
      });

      await tx.wallet.update({
        where: { userId: toUserId },
        data: { balance: { increment: amount } },
      });

      await tx.walletTransaction.create({
        data: {
          fromUserId,
          toUserId,
          amount,
          type: 'transfer',
        },
      });
    },
    {
      maxWait: 5000,    // Max time to acquire connection (ms)
      timeout: 10000,   // Max transaction duration (ms)
      isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
    }
  );
}

// Batch transaction: array of operations (more efficient, less flexible)
export async function batchCreateTags(tags: string[], postId: string): Promise<void> {
  await db.$transaction(
    tags.map((name) =>
      db.tag.upsert({
        where: { name },
        create: { name },
        update: {},
      })
    )
  );
}

---

5. Bulk Insert with createMany and Chunking

// src/lib/db/bulk-operations.ts

// createMany: single INSERT ... VALUES (...), (...) — much faster than N individual creates
export async function bulkInsertEvents(
  events: Array<{ userId: string; type: string; payload: object; occurredAt: Date }>
): Promise<number> {
  const CHUNK_SIZE = 1000; // PostgreSQL limit: ~65K params per statement
  let total = 0;

  for (let i = 0; i < events.length; i += CHUNK_SIZE) {
    const chunk = events.slice(i, i + CHUNK_SIZE);
    const result = await db.event.createMany({
      data: chunk,
      skipDuplicates: true, // Ignore conflicts on unique constraints
    });
    total += result.count;
  }

  return total;
}

// Upsert many: no built-in, but efficient with raw SQL
export async function upsertProducts(
  products: Array<{ id: string; name: string; price: number; tenantId: string }>
): Promise<void> {
  if (products.length === 0) return;

  const values = products
    .map((_, i) => `($${i * 4 + 1}::uuid, $${i * 4 + 2}, $${i * 4 + 3}::int, $${i * 4 + 4}::uuid)`)
    .join(', ');

  const params = products.flatMap((p) => [p.id, p.name, p.price, p.tenantId]);

  await db.$executeRawUnsafe(
    `INSERT INTO products (id, name, price, tenant_id)
     VALUES ${values}
     ON CONFLICT (id) DO UPDATE SET
       name = EXCLUDED.name,
       price = EXCLUDED.price,
       updated_at = NOW()`,
    ...params
  );
}

---

6. Migration Best Practices

# Development workflow
npx prisma migrate dev --name add_user_preferences

# Production: generate SQL, review, then apply
npx prisma migrate diff \
  --from-schema-datasource prisma/schema.prisma \
  --to-schema-datamodel prisma/schema.prisma \
  --script > migration.sql

# Review migration.sql — then apply:
npx prisma migrate deploy

# Never use migrate reset in production
# Never use migrate dev in production (drops + recreates)

// src/scripts/migrate-with-backfill.ts
// Pattern: add column + backfill data in same deploy (but separate steps)

async function addAndBackfillColumn() {
  // Step 1: Migration already added nullable column `user.displayName`
  // Step 2: Backfill in batches (never backfill entire table in one query)

  let cursor: string | undefined;
  let processed = 0;
  const BATCH_SIZE = 500;

  do {
    const users = await db.user.findMany({
      where: { displayName: null },
      select: { id: true, name: true },
      take: BATCH_SIZE,
      cursor: cursor ? { id: cursor } : undefined,
      skip: cursor ? 1 : 0,
    });

    if (users.length === 0) break;

    await db.$transaction(
      users.map((u) =>
        db.user.update({
          where: { id: u.id },
          data: { displayName: u.name },
        })
      )
    );

    processed += users.length;
    cursor = users[users.length - 1].id;
    console.log(`Backfilled ${processed} users`);

    // Rate limit: avoid overwhelming DB during business hours
    await new Promise((r) => setTimeout(r, 50));
  } while (true);

  console.log(`✅ Backfill complete: ${processed} users`);
}

---

Cost Reference

---

See Also

• PostgreSQL Row-Level Security: Multi-Tenant Isolation and Audit Policies
• Database Schema Versioning: Flyway, Liquibase, and Prisma Migrate
• PostgreSQL Full-Text Search: tsvector, tsquery, and Ranking
• PostgreSQL Partitioning: Range, List, and Hash Strategies
• TypeScript Branded Types: Nominal Typing and Type-Safe IDs

---

Working With Viprasol

Prisma codebase with N+1 queries, missing soft delete, no audit trail, or migrations causing downtime? We introduce client extensions for cross-cutting concerns, raw queries for complex analytics, interactive transactions for financial operations, and zero-downtime migration patterns — all with full TypeScript type safety.

Talk to our team → | See our web development services →