Database Queries
Common query patterns and optimization techniques with Prisma.
Overview#
Efficient queries are essential for:
- Application performance
- Reduced database load
- Scalable data access
- Better user experience
Prerequisites:
- Prisma ORM setup
- Understanding of SQL concepts
Implementation#
Basic Queries#
1// Find single record
2const user = await prisma.user.findUnique({
3 where: { id: userId }
4})
5
6// Find first matching record
7const post = await prisma.post.findFirst({
8 where: { published: true },
9 orderBy: { createdAt: 'desc' }
10})
11
12// Find many with conditions
13const users = await prisma.user.findMany({
14 where: {
15 email: { contains: '@example.com' },
16 createdAt: { gte: new Date('2024-01-01') }
17 }
18})Filtering#
1// Multiple conditions (AND)
2const posts = await prisma.post.findMany({
3 where: {
4 published: true,
5 authorId: userId,
6 createdAt: { gte: startDate }
7 }
8})
9
10// OR conditions
11const users = await prisma.user.findMany({
12 where: {
13 OR: [
14 { email: { contains: 'admin' } },
15 { role: 'ADMIN' }
16 ]
17 }
18})
19
20// NOT conditions
21const activeUsers = await prisma.user.findMany({
22 where: {
23 NOT: { deletedAt: { not: null } }
24 }
25})
26
27// Nested filtering
28const posts = await prisma.post.findMany({
29 where: {
30 author: {
31 email: { endsWith: '@company.com' }
32 }
33 }
34})Selecting Fields#
1// Select specific fields
2const users = await prisma.user.findMany({
3 select: {
4 id: true,
5 name: true,
6 email: true
7 }
8})
9
10// Include relations
11const userWithPosts = await prisma.user.findUnique({
12 where: { id: userId },
13 include: {
14 posts: {
15 where: { published: true },
16 orderBy: { createdAt: 'desc' },
17 take: 5
18 }
19 }
20})
21
22// Nested select
23const posts = await prisma.post.findMany({
24 select: {
25 id: true,
26 title: true,
27 author: {
28 select: {
29 name: true,
30 email: true
31 }
32 }
33 }
34})Pagination#
1// Offset pagination
2const page = 2
3const pageSize = 10
4
5const posts = await prisma.post.findMany({
6 skip: (page - 1) * pageSize,
7 take: pageSize,
8 orderBy: { createdAt: 'desc' }
9})
10
11// Cursor pagination (more efficient for large datasets)
12const posts = await prisma.post.findMany({
13 take: 10,
14 cursor: { id: lastPostId },
15 skip: 1, // Skip the cursor
16 orderBy: { createdAt: 'desc' }
17})
18
19// With total count
20const [posts, total] = await prisma.$transaction([
21 prisma.post.findMany({
22 skip: (page - 1) * pageSize,
23 take: pageSize
24 }),
25 prisma.post.count()
26])Aggregations#
1// Count
2const userCount = await prisma.user.count({
3 where: { role: 'USER' }
4})
5
6// Aggregate functions
7const stats = await prisma.order.aggregate({
8 _sum: { total: true },
9 _avg: { total: true },
10 _min: { total: true },
11 _max: { total: true },
12 _count: true
13})
14
15// Group by
16const postsByAuthor = await prisma.post.groupBy({
17 by: ['authorId'],
18 _count: { id: true },
19 orderBy: { _count: { id: 'desc' } },
20 take: 10
21})Sorting#
1// Single field
2const posts = await prisma.post.findMany({
3 orderBy: { createdAt: 'desc' }
4})
5
6// Multiple fields
7const posts = await prisma.post.findMany({
8 orderBy: [
9 { featured: 'desc' },
10 { createdAt: 'desc' }
11 ]
12})
13
14// Relation ordering
15const users = await prisma.user.findMany({
16 orderBy: {
17 posts: { _count: 'desc' }
18 }
19})Raw Queries#
1// Raw SQL query
2const users = await prisma.$queryRaw`
3 SELECT * FROM "User"
4 WHERE email LIKE ${`%${search}%`}
5 ORDER BY "createdAt" DESC
6 LIMIT ${limit}
7`
8
9// Raw SQL execute
10await prisma.$executeRaw`
11 UPDATE "Post"
12 SET "viewCount" = "viewCount" + 1
13 WHERE id = ${postId}
14`
15
16// Tagged template for safety
17const result = await prisma.$queryRaw`
18 SELECT
19 DATE_TRUNC('day', "createdAt") as date,
20 COUNT(*) as count
21 FROM "User"
22 WHERE "createdAt" >= ${startDate}
23 GROUP BY date
24 ORDER BY date
25`Query Optimization#
1// Use select instead of include when possible
2// Bad - fetches all user fields
3const posts = await prisma.post.findMany({
4 include: { author: true }
5})
6
7// Good - fetches only needed fields
8const posts = await prisma.post.findMany({
9 select: {
10 id: true,
11 title: true,
12 author: {
13 select: { name: true }
14 }
15 }
16})
17
18// Avoid N+1 queries
19// Bad - N+1 queries
20const posts = await prisma.post.findMany()
21for (const post of posts) {
22 const author = await prisma.user.findUnique({
23 where: { id: post.authorId }
24 })
25}
26
27// Good - single query with include
28const posts = await prisma.post.findMany({
29 include: { author: true }
30})Search Patterns#
1// Case-insensitive search
2const users = await prisma.user.findMany({
3 where: {
4 name: {
5 contains: searchTerm,
6 mode: 'insensitive'
7 }
8 }
9})
10
11// Multiple field search
12const results = await prisma.post.findMany({
13 where: {
14 OR: [
15 { title: { contains: query, mode: 'insensitive' } },
16 { content: { contains: query, mode: 'insensitive' } }
17 ]
18 }
19})
20
21// Full-text search (PostgreSQL)
22const results = await prisma.post.findMany({
23 where: {
24 OR: [
25 { title: { search: query } },
26 { content: { search: query } }
27 ]
28 },
29 orderBy: {
30 _relevance: {
31 fields: ['title', 'content'],
32 search: query,
33 sort: 'desc'
34 }
35 }
36})Usage Examples#
Reusable Query Functions#
1// lib/queries/posts.ts
2import { prisma } from '@/lib/db'
3
4interface PostQueryOptions {
5 page?: number
6 limit?: number
7 authorId?: string
8 published?: boolean
9 search?: string
10}
11
12export async function getPosts(options: PostQueryOptions = {}) {
13 const { page = 1, limit = 10, authorId, published, search } = options
14
15 const where = {
16 ...(authorId && { authorId }),
17 ...(published !== undefined && { published }),
18 ...(search && {
19 OR: [
20 { title: { contains: search, mode: 'insensitive' } },
21 { content: { contains: search, mode: 'insensitive' } }
22 ]
23 })
24 }
25
26 const [posts, total] = await prisma.$transaction([
27 prisma.post.findMany({
28 where,
29 skip: (page - 1) * limit,
30 take: limit,
31 orderBy: { createdAt: 'desc' },
32 include: {
33 author: {
34 select: { id: true, name: true }
35 }
36 }
37 }),
38 prisma.post.count({ where })
39 ])
40
41 return {
42 posts,
43 pagination: {
44 page,
45 limit,
46 total,
47 totalPages: Math.ceil(total / limit)
48 }
49 }
50}Best Practices#
- Use indexes on filtered columns - Add
@@indexto frequently queried fields - Select only needed fields - Reduce data transfer with select
- Avoid N+1 queries - Use include or select with relations
- Use cursor pagination for large datasets - More efficient than offset
- Monitor query performance - Enable query logging in development
Related Patterns#
- Prisma - Prisma setup and basics
- Transactions - Transaction handling
- Full-Text Search - Search implementation