Prisma ORM Advanced Patterns for TypeScript Applications

Prisma has become the most popular TypeScript ORM, and for good reason: its type-safe query API, declarative schema language, and migration tooling dramatically reduce the friction of database operations. But many teams only scratch the surface of what Prisma offers. This guide covers advanced patterns that we use at Nexis Limited when building production TypeScript applications with Prisma.

Middleware for Cross-Cutting Concerns

Prisma middleware intercepts queries before they reach the database, enabling patterns like logging, soft deletes, and multi-tenancy filtering without modifying business logic:

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

// Soft delete middleware
prisma.$use(async (params, next) => {
    // Intercept delete operations and convert to soft delete
    if (params.action === 'delete') {
        params.action = 'update';
        params.args.data = { deletedAt: new Date() };
    }

    if (params.action === 'deleteMany') {
        params.action = 'updateMany';
        if (params.args.data !== undefined) {
            params.args.data.deletedAt = new Date();
        } else {
            params.args.data = { deletedAt: new Date() };
        }
    }

    // Automatically filter out soft-deleted records on reads
    if (params.action === 'findMany' || params.action === 'findFirst') {
        if (!params.args) params.args = {};
        if (!params.args.where) params.args.where = {};
        params.args.where.deletedAt = null;
    }

    return next(params);
});

Interactive Transactions

Prisma supports interactive transactions where multiple operations share a transactional context. This is essential for maintaining data consistency in complex business operations:

async function transferFunds(
    fromAccountId: string,
    toAccountId: string,
    amount: number
): Promise<void> {
    await prisma.$transaction(async (tx) => {
        // Debit source account
        const source = await tx.account.update({
            where: { id: fromAccountId },
            data: { balance: { decrement: amount } }
        });

        if (source.balance < 0) {
            throw new Error('Insufficient funds');
        }

        // Credit destination account
        await tx.account.update({
            where: { id: toAccountId },
            data: { balance: { increment: amount } }
        });

        // Record the transfer
        await tx.transfer.create({
            data: {
                fromAccountId,
                toAccountId,
                amount,
                executedAt: new Date()
            }
        });
    }, {
        isolationLevel: 'Serializable',
        timeout: 10000
    });
}

Raw Queries for Complex Operations

Some queries are too complex for Prisma's query API. The $queryRaw method executes raw SQL while still returning typed results:

interface RevenueByMonth {
    month: Date;
    revenue: number;
    orderCount: bigint;
}

const monthlyRevenue = await prisma.$queryRaw<RevenueByMonth[]>`
    SELECT
        date_trunc('month', created_at) AS month,
        SUM(total)::float AS revenue,
        COUNT(*) AS "orderCount"
    FROM orders
    WHERE created_at >= {startDate}
      AND status = 'completed'
    GROUP BY date_trunc('month', created_at)
    ORDER BY month DESC
`;

Multi-Tenancy with Prisma

For SaaS applications, isolating tenant data is critical. Row-level multi-tenancy adds a tenantId column to every table and filters all queries by tenant. Prisma Client Extensions (introduced in Prisma 4.16) provide a clean abstraction:

function getClientForTenant(tenantId: string) {
    return prisma.$extends({
        query: {
            $allModels: {
                async findMany({ args, query }) {
                    args.where = { ...args.where, tenantId };
                    return query(args);
                },
                async create({ args, query }) {
                    args.data = { ...args.data, tenantId };
                    return query(args);
                }
            }
        }
    });
}

// Usage
const tenantPrisma = getClientForTenant('tenant-abc');
const users = await tenantPrisma.user.findMany(); // Automatically filtered

Optimizing Query Performance

Prisma generates SQL, and understanding the generated queries helps optimize performance. Enable query logging to inspect what Prisma sends to the database:

const prisma = new PrismaClient({
    log: [
        { level: 'query', emit: 'event' }
    ]
});

prisma.$on('query', (e) => {
    console.log(`Query: ${e.query}`);
    console.log(`Duration: ${e.duration}ms`);
});

Use select and include judiciously. Avoid fetching relations you do not need. For list endpoints, use pagination with cursor-based queries instead of offset-skip patterns. Batch related lookups using findMany with where: { id: { in: ids } } rather than individual findUnique calls.

Schema Patterns

Polymorphic Relations

Prisma does not natively support polymorphic relations, but you can model them with a union-like approach: create a join table for each concrete type or use a discriminator field with optional relations. The join-table approach is more normalized and works well with Prisma's relation system.

Audit Logging

Use Prisma middleware or database triggers to automatically capture create, update, and delete events into an audit log table. The middleware approach keeps audit logic in the application layer, making it testable and portable across database backends.

Prisma strikes an excellent balance between developer experience and production readiness. By leveraging middleware, extensions, raw queries, and thoughtful schema design, you can build TypeScript applications that are both type-safe and performant. Explore our portfolio to see projects where we have applied these patterns at scale.