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' } }