Queries & Relations CRUD Operations import { prisma } from '@/lib/prisma'; // Create const user = await prisma.user.create({ data: { email: 'alice@example.com',…
Queries & Relations
CRUD Operations
import { prisma } from '@/lib/prisma';
// Create
const user = await prisma.user.create({
data: { email: 'alice@example.com', name: 'Alice' },
});
// Create with relation
const post = await prisma.post.create({
data: {
title: 'Hello World',
author: { connect: { id: userId } }, // connect existing
tags: { create: [{ name: 'intro' }] }, // create and connect
},
include: { author: true, tags: true },
});
// Find one
const user = await prisma.user.findUnique({ where: { id: '...' } });
const user = await prisma.user.findUnique({ where: { email: 'a@b.com' } });
const user = await prisma.user.findFirst({ where: { role: 'ADMIN' }, orderBy: { createdAt: 'desc' } });
const user = await prisma.user.findFirstOrThrow({ where: { id: '...' } }); // throws if not found
// Find many
const users = await prisma.user.findMany({
where: {
isActive: true,
role: { in: ['USER', 'MOD'] },
name: { contains: 'ali', mode: 'insensitive' },
createdAt: { gte: new Date('2024-01-01') },
posts: { some: { published: true } }, // has at least one published post
},
select: { id: true, email: true, name: true }, // projection
include: { posts: { where: { published: true } } },
orderBy: [{ createdAt: 'desc' }, { name: 'asc' }],
skip: 20,
take: 10,
});
// Count
const count = await prisma.user.count({ where: { isActive: true } });
// Update
const updated = await prisma.user.update({
where: { id: userId },
data: {
name: 'Alice B.',
posts: { disconnect: [{ id: postId }] }, // disconnect relation
},
});
// Upsert
const user = await prisma.user.upsert({
where: { email: 'a@b.com' },
create: { email: 'a@b.com', name: 'Alice' },
update: { name: 'Alice B.' },
});
// Delete
await prisma.user.delete({ where: { id: userId } });
await prisma.user.deleteMany({ where: { isActive: false } });
Relations & Includes
// Include related records
const user = await prisma.user.findUnique({
where: { id: userId },
include: {
posts: {
where: { published: true },
orderBy: { createdAt: 'desc' },
take: 5,
include: { tags: true },
},
profile: true,
},
});
// Select (choose fields, more efficient than include)
const user = await prisma.user.findUnique({
where: { id: userId },
select: {
id: true,
name: true,
posts: {
select: { id: true, title: true },
take: 3,
},
},
});
// Nested writes
await prisma.user.update({
where: { id: userId },
data: {
posts: {
create: { title: 'New Post' }, // create and connect
connect: { id: existingPostId }, // connect existing
disconnect: { id: postToRemove }, // disconnect
delete: { id: postToDelete }, // delete
update: { // update connected
where: { id: postId },
data: { published: true },
},
},
},
});
Transactions & Raw SQL
// Sequential transaction (each step depends on previous result)
const [user, post] = await prisma.$transaction(async (tx) => {
const user = await tx.user.create({ data: { email: 'a@b.com', name: 'Alice' } });
const post = await tx.post.create({ data: { title: 'Hello', authorId: user.id } });
return [user, post];
});
// Batch transaction (independent, faster)
const [users, posts] = await prisma.$transaction([
prisma.user.findMany(),
prisma.post.findMany({ where: { published: true } }),
]);
// Raw SQL — tagged template (safe from SQL injection)
const users = await prisma.$queryRaw`
SELECT u.*, COUNT(p.id)::int AS post_count
FROM users u
LEFT JOIN posts p ON p.author_id = u.id
WHERE u.is_active = true
GROUP BY u.id
HAVING COUNT(p.id) > ${minPosts}
`;
// Execute raw (for INSERT/UPDATE/DELETE)
const result = await prisma.$executeRaw`
UPDATE users SET last_seen = NOW() WHERE id = ${userId}
`; // returns number of affected rows
Filtering Cheatsheet
// String filters
{ name: { equals: 'Alice' } }
{ name: { not: 'Alice' } }
{ name: { contains: 'ali' } }
{ name: { startsWith: 'Ali' } }
{ name: { endsWith: 'ce' } }
{ name: { contains: 'ali', mode: 'insensitive' } }
// Number / date filters
{ age: { gt: 18 } }
{ age: { gte: 18, lte: 65 } }
{ createdAt: { gte: new Date('2024-01-01') } }
// List filters
{ role: { in: ['ADMIN', 'MOD'] } }
{ role: { notIn: ['BANNED'] } }
// Null filters
{ deletedAt: null }
{ deletedAt: { not: null } }
// Relation filters
{ posts: { some: { published: true } } } // has at least one
{ posts: { every: { published: true } } } // all are
{ posts: { none: { published: true } } } // none are
{ posts: { is: { id: postId } } } // for to-one
// Logical
{ AND: [{ isActive: true }, { role: 'USER' }] }
{ OR: [{ role: 'ADMIN' }, { role: 'MOD' }] }
{ NOT: { role: 'BANNED' } }