Prisma provides a powerful query API. Here's how to use advanced features for complex data requirements.
Complex Filtering#
1import { Prisma } from '@prisma/client';
2
3// Nested filters
4const users = await prisma.user.findMany({
5 where: {
6 AND: [
7 { email: { endsWith: '@company.com' } },
8 {
9 OR: [
10 { role: 'ADMIN' },
11 { permissions: { has: 'write' } },
12 ],
13 },
14 ],
15 NOT: {
16 status: 'BANNED',
17 },
18 },
19});
20
21// Filter on relations
22const usersWithOrders = await prisma.user.findMany({
23 where: {
24 orders: {
25 some: {
26 total: { gte: 100 },
27 status: 'COMPLETED',
28 },
29 },
30 },
31});
32
33// Filter by relation count
34const activeUsers = await prisma.user.findMany({
35 where: {
36 orders: {
37 some: {},
38 },
39 posts: {
40 none: {
41 status: 'DRAFT',
42 },
43 },
44 },
45});
46
47// Full-text search (PostgreSQL)
48const searchResults = await prisma.post.findMany({
49 where: {
50 OR: [
51 { title: { search: 'database' } },
52 { content: { search: 'database' } },
53 ],
54 },
55});
56
57// JSON field filtering
58const usersWithPreference = await prisma.user.findMany({
59 where: {
60 settings: {
61 path: ['notifications', 'email'],
62 equals: true,
63 },
64 },
65});Relation Queries#
1// Include nested relations
2const userWithDetails = await prisma.user.findUnique({
3 where: { id: userId },
4 include: {
5 profile: true,
6 posts: {
7 where: { published: true },
8 orderBy: { createdAt: 'desc' },
9 take: 5,
10 include: {
11 comments: {
12 take: 3,
13 orderBy: { createdAt: 'desc' },
14 },
15 },
16 },
17 _count: {
18 select: {
19 posts: true,
20 followers: true,
21 },
22 },
23 },
24});
25
26// Select specific fields
27const users = await prisma.user.findMany({
28 select: {
29 id: true,
30 name: true,
31 email: true,
32 posts: {
33 select: {
34 id: true,
35 title: true,
36 },
37 },
38 },
39});
40
41// Relation aggregations
42const userStats = await prisma.user.findUnique({
43 where: { id: userId },
44 include: {
45 _count: {
46 select: {
47 posts: { where: { published: true } },
48 comments: true,
49 likes: true,
50 },
51 },
52 },
53});
54
55// Fluent API for relations
56const posts = await prisma.user
57 .findUnique({ where: { id: userId } })
58 .posts({
59 where: { published: true },
60 orderBy: { createdAt: 'desc' },
61 });Aggregations#
1// Basic aggregation
2const stats = await prisma.order.aggregate({
3 _count: true,
4 _sum: { total: true },
5 _avg: { total: true },
6 _min: { total: true },
7 _max: { total: true },
8 where: {
9 status: 'COMPLETED',
10 createdAt: { gte: new Date('2024-01-01') },
11 },
12});
13
14// Group by
15const salesByMonth = await prisma.order.groupBy({
16 by: ['status'],
17 _count: true,
18 _sum: { total: true },
19 having: {
20 total: { _sum: { gt: 1000 } },
21 },
22 orderBy: {
23 _sum: { total: 'desc' },
24 },
25});
26
27// Count with filter
28const counts = await prisma.user.count({
29 where: {
30 role: 'USER',
31 createdAt: { gte: new Date('2024-01-01') },
32 },
33});
34
35// Distinct values
36const uniqueCategories = await prisma.post.findMany({
37 distinct: ['category'],
38 select: { category: true },
39});Transactions#
1// Sequential transaction
2const [user, post] = await prisma.$transaction([
3 prisma.user.create({
4 data: { email: 'new@example.com', name: 'New User' },
5 }),
6 prisma.post.create({
7 data: { title: 'Hello', authorId: 'temp' }, // Updated in next query
8 }),
9]);
10
11// Interactive transaction (recommended)
12const result = await prisma.$transaction(async (tx) => {
13 // Create user
14 const user = await tx.user.create({
15 data: { email: 'new@example.com', name: 'New User' },
16 });
17
18 // Create post with correct authorId
19 const post = await tx.post.create({
20 data: {
21 title: 'My First Post',
22 authorId: user.id,
23 },
24 });
25
26 // Update user's post count
27 await tx.user.update({
28 where: { id: user.id },
29 data: { postCount: { increment: 1 } },
30 });
31
32 return { user, post };
33});
34
35// Transaction with rollback
36const transfer = await prisma.$transaction(async (tx) => {
37 const sender = await tx.account.update({
38 where: { id: senderId },
39 data: { balance: { decrement: amount } },
40 });
41
42 if (sender.balance < 0) {
43 throw new Error('Insufficient funds');
44 }
45
46 const recipient = await tx.account.update({
47 where: { id: recipientId },
48 data: { balance: { increment: amount } },
49 });
50
51 return { sender, recipient };
52});
53
54// Transaction options
55await prisma.$transaction(
56 async (tx) => {
57 // Long-running transaction
58 },
59 {
60 maxWait: 5000, // Max time to wait for transaction
61 timeout: 10000, // Max transaction duration
62 isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
63 }
64);Raw Queries#
1// Raw query with type safety
2const users = await prisma.$queryRaw<User[]>`
3 SELECT * FROM "User"
4 WHERE email LIKE ${`%@company.com`}
5 ORDER BY "createdAt" DESC
6 LIMIT 10
7`;
8
9// Raw query with Prisma.sql for dynamic parts
10const column = 'name';
11const direction = Prisma.sql`DESC`;
12
13const sorted = await prisma.$queryRaw`
14 SELECT * FROM "User"
15 ORDER BY ${Prisma.raw(`"${column}"`)} ${direction}
16`;
17
18// Raw execute for updates
19const affected = await prisma.$executeRaw`
20 UPDATE "User"
21 SET "lastLoginAt" = NOW()
22 WHERE id = ${userId}
23`;
24
25// Join with raw SQL
26const userOrders = await prisma.$queryRaw<UserWithOrders[]>`
27 SELECT
28 u.id,
29 u.name,
30 COUNT(o.id) as "orderCount",
31 SUM(o.total) as "totalSpent"
32 FROM "User" u
33 LEFT JOIN "Order" o ON u.id = o."userId"
34 WHERE o."status" = 'COMPLETED'
35 GROUP BY u.id, u.name
36 HAVING SUM(o.total) > 1000
37 ORDER BY "totalSpent" DESC
38`;
39
40// CTEs and complex queries
41const analytics = await prisma.$queryRaw`
42 WITH monthly_sales AS (
43 SELECT
44 DATE_TRUNC('month', "createdAt") as month,
45 SUM(total) as revenue
46 FROM "Order"
47 WHERE status = 'COMPLETED'
48 GROUP BY DATE_TRUNC('month', "createdAt")
49 )
50 SELECT
51 month,
52 revenue,
53 LAG(revenue) OVER (ORDER BY month) as prev_revenue,
54 revenue - LAG(revenue) OVER (ORDER BY month) as growth
55 FROM monthly_sales
56 ORDER BY month DESC
57`;Middleware and Extensions#
1// Middleware for soft delete
2prisma.$use(async (params, next) => {
3 // Intercept delete and update to soft delete
4 if (params.action === 'delete') {
5 params.action = 'update';
6 params.args.data = { deletedAt: new Date() };
7 }
8
9 if (params.action === 'deleteMany') {
10 params.action = 'updateMany';
11 params.args.data = { deletedAt: new Date() };
12 }
13
14 return next(params);
15});
16
17// Query all non-deleted by default
18prisma.$use(async (params, next) => {
19 if (params.action === 'findMany' || params.action === 'findFirst') {
20 if (!params.args.where) {
21 params.args.where = {};
22 }
23 params.args.where.deletedAt = null;
24 }
25 return next(params);
26});
27
28// Prisma Client Extension
29const prismaWithExtensions = prisma.$extends({
30 model: {
31 user: {
32 async findByEmail(email: string) {
33 return prisma.user.findUnique({
34 where: { email },
35 });
36 },
37 async softDelete(id: string) {
38 return prisma.user.update({
39 where: { id },
40 data: { deletedAt: new Date() },
41 });
42 },
43 },
44 },
45 query: {
46 $allModels: {
47 async findMany({ model, operation, args, query }) {
48 const start = Date.now();
49 const result = await query(args);
50 console.log(`${model}.${operation} took ${Date.now() - start}ms`);
51 return result;
52 },
53 },
54 },
55});
56
57// Usage
58const user = await prismaWithExtensions.user.findByEmail('test@example.com');Performance Optimization#
1// Use select to reduce data transfer
2const users = await prisma.user.findMany({
3 select: {
4 id: true,
5 name: true,
6 // Don't select large fields like 'bio' if not needed
7 },
8});
9
10// Pagination with cursor
11const page = await prisma.post.findMany({
12 take: 20,
13 skip: 1, // Skip the cursor
14 cursor: { id: lastPostId },
15 orderBy: { createdAt: 'desc' },
16});
17
18// Batch operations
19const users = await prisma.user.createMany({
20 data: [
21 { email: 'user1@example.com', name: 'User 1' },
22 { email: 'user2@example.com', name: 'User 2' },
23 ],
24 skipDuplicates: true,
25});
26
27// Upsert
28const user = await prisma.user.upsert({
29 where: { email: 'user@example.com' },
30 update: { lastLoginAt: new Date() },
31 create: { email: 'user@example.com', name: 'New User' },
32});
33
34// Use connection pooling
35// In prisma/schema.prisma:
36// datasource db {
37// url = env("DATABASE_URL")
38// relationMode = "prisma"
39// }
40
41// Enable query logging for debugging
42const prisma = new PrismaClient({
43 log: [
44 { level: 'query', emit: 'event' },
45 { level: 'error', emit: 'stdout' },
46 ],
47});
48
49prisma.$on('query', (e) => {
50 console.log(`Query: ${e.query}`);
51 console.log(`Duration: ${e.duration}ms`);
52});Best Practices#
Queries:
✓ Use select to fetch only needed fields
✓ Paginate large result sets
✓ Use cursor-based pagination for stability
✓ Index frequently queried columns
Transactions:
✓ Use interactive transactions
✓ Keep transactions short
✓ Handle rollback scenarios
✓ Set appropriate timeouts
Performance:
✓ Enable query logging in development
✓ Use createMany for bulk inserts
✓ Avoid N+1 with include/select
✓ Use raw SQL for complex analytics
Conclusion#
Prisma's query API handles most use cases with type safety. For complex scenarios, use raw SQL while still benefiting from Prisma's type system. Master transactions for data integrity, use middleware for cross-cutting concerns, and optimize queries with proper select/include usage.