Prisma Relations and Advanced Queries

Working with Relations in Prisma

Prisma handles one-to-one, one-to-many, and many-to-many relationships natively. Understanding how to query across relations efficiently is key to avoiding N+1 problems.

Select vs Include

// include — loads the full related model
const post = await db.post.findUnique({
    where: { id },
    include: { author: true }, // All author fields
});

// select — loads only specified fields (more efficient)
const post = await db.post.findUnique({
    where: { id },
    select: {
        id: true,
        title: true,
        author: {
            select: { name: true, image: true },
        },
    },
});

Nested Writes

// Create user with posts in one query
const user = await db.user.create({
    data: {
        name: 'Jane Doe',
        email: 'jane@example.com',
        posts: {
            create: [
                { title: 'First Post', slug: 'first-post', content: '...' },
                { title: 'Second Post', slug: 'second-post', content: '...' },
            ],
        },
    },
    include: { posts: true },
});

Raw Queries for Complex Cases

// When Prisma's query builder is not expressive enough
const topAuthors = await db.$queryRaw<Array<{
    id: string;
    name: string;
    postCount: bigint;
    avgViews: number;
}>>`
    SELECT
        u.id,
        u.name,
        COUNT(p.id) AS "postCount",
        AVG(p.view_count) AS "avgViews"
    FROM users u
    LEFT JOIN posts p ON p.author_id = u.id AND p.published = true
    GROUP BY u.id, u.name
    HAVING COUNT(p.id) > 5
    ORDER BY "postCount" DESC
    LIMIT 10
`;

Transactions

// Atomic operations — all succeed or all fail
const [post, _notification] = await db.$transaction([
    db.post.create({
        data: { title, slug, content, authorId },
    }),
    db.notification.create({
        data: {
            type: 'NEW_POST',
            recipientId: adminId,
            message: `New post: ${title}`,
        },
    }),
]);

// Interactive transaction for complex logic
const result = await db.$transaction(async (tx) => {
    const post = await tx.post.create({ data: { title, slug, content, authorId } });

    await tx.user.update({
        where: { id: authorId },
        data: { postCount: { increment: 1 } },
    });

    return post;
});

Using transactions ensures data consistency — if the notification fails, the post is never created, keeping your database in a valid state.