Advanced Prisma¶

The basics get you 80% of the way The other 20% is where you screw up if you don't know how relations work at scale, why aggregation queries can tank your server memory, when to drop down to raw SQL because the ORM abstraction is costing you 10x performance, and how to secure sensitive data at the ORM layer before it ever reaches the database

Relations in depth¶

One-to-One with optional relation¶

model User {
    id      String   @id @default(uuid())
    profile Profile?  // Optional - user may not have profile
}

model Profile {
    id     String @id @default(uuid())
    userId String @unique @map("user_id")
    user   User   @relation(fields: [userId], references: [id], onDelete: Cascade)
    // onDelete: Cascade - deletes profile when user is deleted
    bio    String
    avatar String?
}

The @unique on userId enforces one-to-one: no two profiles can reference the same user. The onDelete: Cascade means deleting a user automatically deletes their profile - use onDelete: Restrict if you want the database to prevent deleting users with active profiles

One-to-Many with filtered includes¶

// Get user with only their published posts (filtered include)
const user = await prisma.user.findUnique({
    where: { id: userId },
    include: {
        posts: {
            where: { published: true },
            orderBy: { createdAt: 'desc' },
            take: 10
        }
    }
});

Many-to-Many with additional fields on join table¶

Implicit many-to-many works for simple cases. When you need extra data on the relationship (like assignedAt or role in a team membership), you need an explicit join model:

model User {
    id        String         @id @default(uuid())
    teams     TeamMembership[]
}

model Team {
    id        String         @id @default(uuid())
    members   TeamMembership[]
}

model TeamMembership {
    userId    String   @map("user_id")
    teamId    String   @map("team_id")
    user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
    team      Team     @relation(fields: [teamId], references: [id], onDelete: Cascade)
    role      String   @default("member")  // Extra field on the relationship
    joinedAt  DateTime @default(now()) @map("joined_at")

    @@id([userId, teamId])
    @@map("team_memberships")
}

// Query with explicit join model
const teamsForUser = await prisma.user.findUnique({
    where: { id: userId },
    include: {
        teams: {
            include: {
                team: true  // Include the full team data
            },
            where: {
                role: 'admin'  // Filter by membership role
            }
        }
    }
});

Aggregations and group by¶

Prisma provides aggregation functions that map to SQL's COUNT, SUM, AVG, MIN, MAX:

// Aggregate all records
const stats = await prisma.order.aggregate({
    _count: { id: true },
    _sum: { totalAmount: true },
    _avg: { totalAmount: true },
    _min: { totalAmount: true },
    _max: { totalAmount: true },
    where: { status: 'delivered' }
});
// stats._count.id = 1523 (total delivered orders)
// stats._sum.totalAmount = 452890.50

// Group by with aggregation
const revenueByStatus = await prisma.order.groupBy({
    by: ['status'],
    _count: { id: true },
    _sum: { totalAmount: true },
    where: { createdAt: { gte: startDate } },
    orderBy: { _sum: { totalAmount: 'desc' } }
});
// [
//   { status: 'delivered', _count: { id: 1200 }, _sum: { totalAmount: 350000 } },
//   { status: 'pending', _count: { id: 300 }, _sum: { totalAmount: 95000 } }
// ]

Raw queries - when ORM isn't enough¶

Sometimes you need window functions, recursive CTEs, or complex joins that Prisma's query API doesn't support. That's when you drop to $queryRaw or $executeRaw:

// Raw SQL query with parameterization (safe - uses $1, $2 placeholders)
const users = await prisma.$queryRaw`
    SELECT
        id, email, username,
        COUNT(o.id) as order_count,
        SUM(o.total_amount) as total_spent,
        RANK() OVER (ORDER BY SUM(o.total_amount) DESC) as rank
    FROM users u
    LEFT JOIN orders o ON o.user_id = u.id
    WHERE u.is_active = ${true}
    GROUP BY u.id, u.email, u.username
    ORDER BY total_spent DESC
    LIMIT ${limit}
`;

// Raw execute for bulk operations
await prisma.$executeRaw`
    UPDATE orders
    SET status = 'archived'
    WHERE created_at < ${cutoffDate}
    AND status = 'delivered'
`;

WARNING: $queryRaw and $executeRaw accept template literal parameters safely (Prisma parameterizes them), but if you interpolate strings directly into the query with string concatenation, you reintroduce SQL injection:

// SAFE - template literal with parameter
await prisma.$queryRaw`SELECT * FROM users WHERE email = ${email}`;

// DANGEROUS - string interpolation
await prisma.$queryRawUnsafe(`SELECT * FROM users WHERE email = '${email}'`);
// $queryRawUnsafe is vulnerable to SQL injection - avoid it

Middleware and hooks¶

Prisma middleware lets you intercept and modify queries before or after they execute. This is where you implement audit logging, field-level encryption, query monitoring, and access control checks

Audit logging middleware¶

const prisma = new PrismaClient();

// Middleware that logs all delete operations
prisma.$use(async (params, next) => {
    // params.model - the model name (e.g., 'User', 'Post')
    // params.action - the operation type (e.g., 'delete', 'update')

    if (params.action === 'delete' || params.action === 'deleteMany') {
        console.log(`[AUDIT] ${params.model} ${params.action} at ${new Date().toISOString()}`);

        // Log to audit table
        await prisma.auditLog.create({
            data: {
                model: params.model,
                action: params.action,
                args: JSON.stringify(params.args),
                userId: getCurrentUserId()  // From your auth context
            }
        });
    }

    const result = await next(params);
    return result;
});

Field-level encryption middleware¶

const crypto = require('crypto');
const ALGORITHM = 'aes-256-gcm';
const ENCRYPTION_KEY = process.env.FIELD_ENCRYPTION_KEY;  // 32 bytes, hex-encoded

function encrypt(text) {
    const iv = crypto.randomBytes(16);
    const cipher = crypto.createCipheriv(ALGORITHM, Buffer.from(ENCRYPTION_KEY, 'hex'), iv);
    let encrypted = cipher.update(text, 'utf8', 'hex');
    encrypted += cipher.final('hex');
    return iv.toString('hex') + ':' + encrypted + ':' + cipher.getAuthTag().toString('hex');
}

function decrypt(text) {
    const parts = text.split(':');
    const iv = Buffer.from(parts.shift(), 'hex');
    const authTag = Buffer.from(parts.pop(), 'hex');
    const encrypted = parts.join(':');
    const decipher = crypto.createDecipheriv(ALGORITHM, Buffer.from(ENCRYPTION_KEY, 'hex'), iv);
    decipher.setAuthTag(authTag);
    let decrypted = decipher.update(encrypted, 'hex', 'utf8');
    decrypted += decipher.final('utf8');
    return decrypted;
}

// Middleware to encrypt/decrypt sensitive fields
prisma.$use(async (params, next) => {
    // Encrypt on create/update
    if (params.action === 'create' || params.action === 'update') {
        if (params.args.data?.ssn) {
            params.args.data.ssn = encrypt(params.args.data.ssn);
        }
        if (params.args.data?.creditCardNumber) {
            params.args.data.creditCardNumber = encrypt(params.args.data.creditCardNumber);
        }
    }

    // Decrypt on read
    if (params.action === 'findUnique' || params.action === 'findFirst' || params.action === 'findMany') {
        const result = await next(params);

        // Handle single result
        if (result && !Array.isArray(result)) {
            if (result.ssn) result.ssn = decrypt(result.ssn);
            if (result.creditCardNumber) result.creditCardNumber = decrypt(result.creditCardNumber);
        }

        // Handle array result
        if (Array.isArray(result)) {
            for (const item of result) {
                if (item.ssn) item.ssn = decrypt(item.ssn);
                if (item.creditCardNumber) item.creditCardNumber = decrypt(item.creditCardNumber);
            }
        }

        return result;
    }

    return next(params);
});

Request logging for performance monitoring¶

prisma.$use(async (params, next) => {
    const start = Date.now();
    const result = await next(params);
    const duration = Date.now() - start;

    if (duration > 100) {
        console.warn(`[SLOW QUERY] ${params.model}.${params.action} took ${duration}ms`);
        // Send to APM (Datadog, New Relic, Sentry):
        // apm.trace('prisma.query', { model: params.model, action: params.action, duration });
    }

    return result;
});

Pagination patterns¶

Offset pagination (simple but slow at high offsets)¶

const page = 1;
const pageSize = 20;

const users = await prisma.user.findMany({
    skip: (page - 1) * pageSize,
    take: pageSize,
    orderBy: { createdAt: 'desc' }
});

Cursor-based pagination (recommended for production)¶

// First page - no cursor
const firstPage = await prisma.user.findMany({
    take: 20,
    orderBy: { id: 'asc' }  // Must use unique field for cursor
});

// Get the last item's ID as cursor
const cursor = firstPage[firstPage.length - 1].id;

// Next page - use cursor
const nextPage = await prisma.user.findMany({
    take: 20,
    skip: 1,  // Skip the cursor itself
    cursor: { id: cursor },
    orderBy: { id: 'asc' }
});

Cursor-based pagination is stable even when new records are inserted - offset pagination can skip or duplicate results when the underlying data changes between pages

Transaction patterns¶

// Sequential transaction - multiple operations, single commit
const [user, totalOrders] = await prisma.$transaction([
    prisma.user.update({
        where: { id: userId },
        data: { isActive: false }
    }),
    prisma.order.count({
        where: { userId, status: 'pending' }
    })
]);

// Interactive transaction - conditional logic within
const result = await prisma.$transaction(async (tx) => {
    // All operations use 'tx' instead of 'prisma'
    const account = await tx.account.findUnique({
        where: { id: accountId }
    });

    if (account.balance < amount) {
        throw new Error('Insufficient funds');
    }

    await tx.account.update({
        where: { id: accountId },
        data: { balance: { decrement: amount } }
    });

    await tx.transaction.create({
        data: {
            fromId: accountId,
            toId: targetAccountId,
            amount: amount,
            type: 'TRANSFER'
        }
    });
});

prerequisites¶

db_14_prisma_intro.md - you need to understand Prisma schema, migrations, and basic CRUD before the advanced patterns make sense. If you don't know how findMany works, the middleware section will look like alien technology

next → db_16_migrations.md