# Prisma - Core Examples

> Singleton setup, CRUD operations, filtering, and pagination. See [SKILL.md](../SKILL.md) for decision guidance.

**Prerequisites**: None - these are the foundational patterns.

---

## PrismaClient Singleton

### Good Example - Development-Safe Client

```typescript
// lib/db/client.ts
import { PrismaClient } from "@prisma/client";

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
};

const createPrismaClient = () => {
  return new PrismaClient({
    log:
      process.env.NODE_ENV === "development"
        ? ["query", "error", "warn"]
        : ["error"],
  });
};

export const prisma = globalForPrisma.prisma ?? createPrismaClient();

if (process.env.NODE_ENV !== "production") {
  globalForPrisma.prisma = prisma;
}
```

**Why good:** `globalThis` persists across hot reloads preventing "10 Prisma Clients running" warning, conditional logging avoids production noise, factory function allows configuration changes

### Bad Example - New Client Every Import

```typescript
// BAD: Creates new instance on every hot reload
import { PrismaClient } from "@prisma/client";
export const prisma = new PrismaClient();
```

**Why bad:** Each hot reload creates a new PrismaClient with its own connection pool, exhausts database connections (PostgreSQL default is 100)

---

## Serverless Connection Management

### Good Example - Connection Pooler

```typescript
// lib/db/serverless-client.ts
import { PrismaClient } from "@prisma/client";

// Serverless environments create new instances per invocation
// Use connection pooler (PgBouncer, Prisma Accelerate) for production
export const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.DATABASE_URL_WITH_POOLER,
    },
  },
});
```

**Why good:** Connection pooler manages connections across serverless invocations, separate URL keeps local development simple

### Good Example - Graceful Shutdown

```typescript
process.on("beforeExit", async () => {
  await prisma.$disconnect();
});
```

**Why good:** Prevents connection leaks on process termination

---

## Read Operations

### Good Example - Find Unique

```typescript
// Find by unique field (id, email, etc.)
const user = await prisma.user.findUnique({
  where: { id: userId },
});

// Find by compound unique (multiple fields)
const membership = await prisma.membership.findUnique({
  where: {
    userId_organizationId: {
      userId: "user-123",
      organizationId: "org-456",
    },
  },
});

// findUniqueOrThrow - throws if not found
try {
  const user = await prisma.user.findUniqueOrThrow({
    where: { id: userId },
  });
  // user is guaranteed to exist
} catch (error) {
  // Handle Prisma.PrismaClientKnownRequestError with code P2025
}
```

**Why good:** `findUnique` returns `T | null` forcing null handling, compound unique for multi-field keys, `OrThrow` variant for guaranteed existence

### Good Example - Find First

```typescript
// Find first matching record
const latestAdmin = await prisma.user.findFirst({
  where: { role: "ADMIN" },
  orderBy: { createdAt: "desc" },
});

// Find first with multiple conditions
const activeSubscription = await prisma.subscription.findFirst({
  where: {
    userId,
    status: "ACTIVE",
    expiresAt: { gt: new Date() },
  },
});
```

**Why good:** `findFirst` for single record with filters, `orderBy` ensures deterministic results

---

## Filtering

### Good Example - Logical Operators

```typescript
// OR conditions
const users = await prisma.user.findMany({
  where: {
    OR: [
      { email: { endsWith: "@gmail.com" } },
      { email: { endsWith: "@outlook.com" } },
    ],
  },
});

// Combined AND, OR, NOT
const posts = await prisma.post.findMany({
  where: {
    AND: [
      { published: true },
      {
        OR: [
          { title: { contains: "prisma", mode: "insensitive" } },
          { content: { contains: "prisma", mode: "insensitive" } },
        ],
      },
    ],
    NOT: { authorId: excludedUserId },
  },
});
```

**Why good:** `mode: "insensitive"` for case-insensitive search, composable logical operators, type inference catches invalid field names

### Good Example - Relation Filters

```typescript
// Find users with at least one published post
const usersWithPublishedPosts = await prisma.user.findMany({
  where: {
    posts: {
      some: { published: true },
    },
  },
});

// Find users where ALL posts are published
const usersWithAllPublished = await prisma.user.findMany({
  where: {
    posts: {
      every: { published: true },
    },
  },
});
```

**Why good:** Relation filters (`some`, `every`, `none`) eliminate manual joins, all at database level

---

## Pagination

### Good Example - Offset Pagination

```typescript
const DEFAULT_PAGE_SIZE = 20;
const MAX_PAGE_SIZE = 100;

interface PaginationParams {
  page?: number;
  pageSize?: number;
}

const getUsers = async ({
  page = 1,
  pageSize = DEFAULT_PAGE_SIZE,
}: PaginationParams) => {
  const take = Math.min(pageSize, MAX_PAGE_SIZE);
  const skip = (page - 1) * take;

  const [users, total] = await prisma.$transaction([
    prisma.user.findMany({
      skip,
      take,
      orderBy: { createdAt: "desc" },
    }),
    prisma.user.count(),
  ]);

  return {
    data: users,
    pagination: {
      page,
      pageSize: take,
      total,
      totalPages: Math.ceil(total / take),
    },
  };
};
```

**Why good:** Named constants for limits, `$transaction` ensures consistent count, `Math.min` caps page size

**When to use:** Small datasets, need random page access, traditional page navigation UI

### Good Example - Cursor Pagination

```typescript
const DEFAULT_PAGE_SIZE = 20;

interface CursorPaginationParams {
  cursor?: string;
  take?: number;
}

const getCursorPaginatedPosts = async ({
  cursor,
  take = DEFAULT_PAGE_SIZE,
}: CursorPaginationParams) => {
  const posts = await prisma.post.findMany({
    take: take + 1, // Fetch one extra to detect next page
    ...(cursor && {
      cursor: { id: cursor },
      skip: 1, // Skip the cursor itself
    }),
    orderBy: { id: "asc" },
    where: { published: true },
  });

  const hasNextPage = posts.length > take;
  const data = hasNextPage ? posts.slice(0, -1) : posts;

  return {
    data,
    nextCursor: hasNextPage ? data[data.length - 1]?.id : undefined,
  };
};
```

**Why good:** Scales to millions of rows, `take + 1` pattern efficiently detects next page

**When to use:** Large datasets, infinite scroll UI, real-time data where offset would shift

---

## Create Operations

### Good Example - Create with Nested Relations

```typescript
// Create with nested relation (one-to-one)
const userWithProfile = await prisma.user.create({
  data: {
    email: "bob@example.com",
    name: "Bob",
    profile: {
      create: { bio: "Software engineer" },
    },
  },
  include: { profile: true },
});

// Create with connect (existing relation)
const post = await prisma.post.create({
  data: {
    title: "New Post",
    content: "Content here",
    author: {
      connect: { id: existingUserId },
    },
    categories: {
      connect: [{ id: categoryId1 }, { id: categoryId2 }],
    },
  },
});
```

**Why good:** Nested `create` for new relations, `connect` for existing relations, `include` to return created relations

### Good Example - Create Many

```typescript
// Bulk create (returns count only)
const result = await prisma.user.createMany({
  data: [
    { email: "user1@example.com", name: "User 1" },
    { email: "user2@example.com", name: "User 2" },
  ],
  skipDuplicates: true, // Ignore unique constraint violations
});
```

**Why good:** Efficient bulk insert, `skipDuplicates` handles conflicts gracefully

---

## Update Operations

### Good Example - Update with Nested Relations

```typescript
// Update with nested relation upsert
const userWithProfile = await prisma.user.update({
  where: { id: userId },
  data: {
    profile: {
      upsert: {
        create: { bio: "New bio" },
        update: { bio: "Updated bio" },
      },
    },
  },
  include: { profile: true },
});

// Replace all many-to-many relations
const postWithCategories = await prisma.post.update({
  where: { id: postId },
  data: {
    categories: {
      set: [{ id: newCategoryId }], // Removes all existing, adds new
    },
  },
});
```

**Why good:** Nested `upsert` for create-or-update, `set` for replacing many-to-many

---

## Delete Operations

### Good Example - Safe Deletion

```typescript
// Delete many (silent if none match)
const result = await prisma.post.deleteMany({
  where: {
    authorId: userId,
    published: false,
  },
});

// Soft delete pattern
const softDeleted = await prisma.user.update({
  where: { id: userId },
  data: {
    deletedAt: new Date(),
    email: `deleted_${userId}@deleted.local`, // Free up unique constraint
  },
});
```

**Why good:** `deleteMany` is silent when no records match, soft delete preserves data and frees unique constraints

---

## Upsert Operations

### Good Example - Atomic Create or Update

```typescript
// Upsert with compound unique
const membership = await prisma.membership.upsert({
  where: {
    userId_organizationId: {
      userId,
      organizationId,
    },
  },
  create: {
    userId,
    organizationId,
    role: "MEMBER",
    joinedAt: new Date(),
  },
  update: {
    role: "MEMBER",
    joinedAt: new Date(),
  },
});
```

**Why good:** Atomic operation avoids race conditions, compound unique in where clause

---

## Type Reuse

### Good Example - Using Prisma Generated Types

```typescript
import type { Prisma } from "@prisma/client";

// Input types for creating/updating
type CreateUserInput = Prisma.UserCreateInput;

// Payload types with relations
type UserWithPosts = Prisma.UserGetPayload<{
  include: { posts: true };
}>;

// Select-based types
type UserSummary = Prisma.UserGetPayload<{
  select: { id: true; name: true; email: true };
}>;
```

**Why good:** Types stay in sync with schema, no manual type maintenance

---

## Quick Reference

| Operation           | Returns     | Throws if Not Found |
| ------------------- | ----------- | ------------------- |
| `findUnique`        | `T \| null` | No                  |
| `findUniqueOrThrow` | `T`         | Yes                 |
| `findFirst`         | `T \| null` | No                  |
| `findFirstOrThrow`  | `T`         | Yes                 |
| `findMany`          | `T[]`       | No (empty array)    |
| `create`            | `T`         | N/A                 |
| `createMany`        | `{ count }` | N/A                 |
| `update`            | `T`         | Yes                 |
| `updateMany`        | `{ count }` | No                  |
| `upsert`            | `T`         | N/A                 |
| `delete`            | `T`         | Yes                 |
| `deleteMany`        | `{ count }` | No                  |

> See [reference.md](../reference.md) for filter operators, relation filter operators, and relation write operations.

# Prisma - Query Examples

> CRUD operations, filtering, and sorting. See [SKILL.md](../SKILL.md) for core concepts.

---

## Read Operations

### Good Example - Find Unique

```typescript
import { prisma } from "@/lib/db/client";

// Find by unique field (id, email, etc.)
const user = await prisma.user.findUnique({
  where: { id: userId },
});

// Find by compound unique (multiple fields)
const membership = await prisma.membership.findUnique({
  where: {
    userId_organizationId: {
      userId: "user-123",
      organizationId: "org-456",
    },
  },
});

// findUniqueOrThrow - throws if not found
try {
  const user = await prisma.user.findUniqueOrThrow({
    where: { id: userId },
  });
  // user is guaranteed to exist
} catch (error) {
  // Handle Prisma.PrismaClientKnownRequestError with code P2025
}
```

**Why good:** findUnique returns `T | null` forcing null handling, compound unique for multi-field keys, OrThrow variant for guaranteed existence

### Good Example - Find First

```typescript
// Find first matching record
const latestAdmin = await prisma.user.findFirst({
  where: { role: "ADMIN" },
  orderBy: { createdAt: "desc" },
});

// Find first with multiple conditions
const activeSubscription = await prisma.subscription.findFirst({
  where: {
    userId,
    status: "ACTIVE",
    expiresAt: { gt: new Date() },
  },
});
```

**Why good:** findFirst for single record with filters, orderBy ensures consistent results, returns null if none found

### Good Example - Find Many with Pagination

```typescript
const DEFAULT_PAGE_SIZE = 20;
const MAX_PAGE_SIZE = 100;

interface PaginationParams {
  page?: number;
  pageSize?: number;
}

const getUsers = async ({
  page = 1,
  pageSize = DEFAULT_PAGE_SIZE,
}: PaginationParams) => {
  const take = Math.min(pageSize, MAX_PAGE_SIZE);
  const skip = (page - 1) * take;

  const [users, total] = await prisma.$transaction([
    prisma.user.findMany({
      skip,
      take,
      orderBy: { createdAt: "desc" },
    }),
    prisma.user.count(),
  ]);

  return {
    data: users,
    pagination: {
      page,
      pageSize: take,
      total,
      totalPages: Math.ceil(total / take),
    },
  };
};
```

**Why good:** Named constants for limits, $transaction ensures consistent count, Math.min caps page size, calculates pagination metadata

---

## Create Operations

### Good Example - Create with Nested Relations

```typescript
// Create single record
const user = await prisma.user.create({
  data: {
    email: "alice@example.com",
    name: "Alice",
    role: "USER",
  },
});

// Create with nested relation (one-to-one)
const userWithProfile = await prisma.user.create({
  data: {
    email: "bob@example.com",
    name: "Bob",
    profile: {
      create: { bio: "Software engineer" },
    },
  },
  include: { profile: true },
});

// Create with nested relations (one-to-many)
const userWithPosts = await prisma.user.create({
  data: {
    email: "carol@example.com",
    name: "Carol",
    posts: {
      create: [
        { title: "First Post", published: true },
        { title: "Draft Post", published: false },
      ],
    },
  },
  include: { posts: true },
});

// Create with connect (existing relation)
const post = await prisma.post.create({
  data: {
    title: "New Post",
    content: "Content here",
    author: {
      connect: { id: existingUserId },
    },
    categories: {
      connect: [{ id: categoryId1 }, { id: categoryId2 }],
    },
  },
});
```

**Why good:** Nested create for new relations, connect for existing relations, include to return created relations

### Good Example - Create Many

```typescript
// Bulk create (returns count only)
const result = await prisma.user.createMany({
  data: [
    { email: "user1@example.com", name: "User 1" },
    { email: "user2@example.com", name: "User 2" },
    { email: "user3@example.com", name: "User 3" },
  ],
  skipDuplicates: true, // Ignore unique constraint violations
});

console.log(`Created ${result.count} users`);
```

**Why good:** Efficient bulk insert, skipDuplicates handles conflicts gracefully, returns count

---

## Update Operations

### Good Example - Update with Nested Relations

```typescript
// Simple update
const updated = await prisma.user.update({
  where: { id: userId },
  data: { name: "Updated Name" },
});

// Update with nested relation
const updatedWithProfile = await prisma.user.update({
  where: { id: userId },
  data: {
    name: "Updated Name",
    profile: {
      update: { bio: "Updated bio" },
    },
  },
  include: { profile: true },
});

// Upsert nested relation (create if doesn't exist)
const userWithProfile = await prisma.user.update({
  where: { id: userId },
  data: {
    profile: {
      upsert: {
        create: { bio: "New bio" },
        update: { bio: "Updated bio" },
      },
    },
  },
  include: { profile: true },
});

// Update many-to-many relations
const postWithCategories = await prisma.post.update({
  where: { id: postId },
  data: {
    categories: {
      set: [{ id: newCategoryId }], // Replace all
      // Or: connect, disconnect, connectOrCreate
    },
  },
});
```

**Why good:** Nested update for relations, upsert for create-or-update, set for replacing many-to-many

### Good Example - Update Many

```typescript
// Bulk update
const result = await prisma.post.updateMany({
  where: {
    authorId: userId,
    published: false,
  },
  data: {
    published: true,
  },
});

console.log(`Published ${result.count} posts`);
```

**Why good:** Efficient bulk update, returns count of affected rows

---

## Upsert Operations

### Good Example - Atomic Create or Update

```typescript
// Upsert single record
const user = await prisma.user.upsert({
  where: { email: "alice@example.com" },
  create: {
    email: "alice@example.com",
    name: "Alice",
    role: "USER",
  },
  update: {
    name: "Alice (Updated)",
  },
});

// Upsert with relation handling
const membership = await prisma.membership.upsert({
  where: {
    userId_organizationId: {
      userId,
      organizationId,
    },
  },
  create: {
    userId,
    organizationId,
    role: "MEMBER",
    joinedAt: new Date(),
  },
  update: {
    role: "MEMBER", // Reset role on re-join
    joinedAt: new Date(),
  },
});
```

**Why good:** Atomic operation avoids race conditions, handles unique constraint gracefully, compound unique in where clause

---

## Delete Operations

### Good Example - Safe Deletion

```typescript
// Delete single record
const deleted = await prisma.user.delete({
  where: { id: userId },
});

// Delete many
const result = await prisma.post.deleteMany({
  where: {
    authorId: userId,
    published: false,
  },
});

console.log(`Deleted ${result.count} draft posts`);

// Soft delete pattern (using update)
const softDeleted = await prisma.user.update({
  where: { id: userId },
  data: {
    deletedAt: new Date(),
    email: `deleted_${userId}@deleted.local`, // Free up unique constraint
  },
});
```

**Why good:** delete throws if not found (use deleteMany for silent), soft delete preserves data, frees unique constraints on soft delete

---

## Quick Reference

| Operation           | Returns     | Throws if Not Found |
| ------------------- | ----------- | ------------------- |
| `findUnique`        | `T \| null` | No                  |
| `findUniqueOrThrow` | `T`         | Yes                 |
| `findFirst`         | `T \| null` | No                  |
| `findFirstOrThrow`  | `T`         | Yes                 |
| `findMany`          | `T[]`       | No (empty array)    |
| `create`            | `T`         | N/A                 |
| `createMany`        | `{ count }` | N/A                 |
| `update`            | `T`         | Yes                 |
| `updateMany`        | `{ count }` | No                  |
| `upsert`            | `T`         | N/A                 |
| `delete`            | `T`         | Yes                 |
| `deleteMany`        | `{ count }` | No                  |

# Prisma - Relations Examples

> Relational queries, includes, and N+1 prevention. See [SKILL.md](../SKILL.md) for core concepts.

---

## Include Pattern

### Good Example - Eager Loading Relations

```typescript
import { prisma } from "../lib/db/client";

// Include single relation
const userWithProfile = await prisma.user.findUnique({
  where: { id: userId },
  include: {
    profile: true, // Include 1-to-1 relation
  },
});
// Type: User & { profile: Profile | null }

// Include multiple relations
const userWithRelations = await prisma.user.findUnique({
  where: { id: userId },
  include: {
    profile: true,
    posts: true, // Include 1-to-many
  },
});
// Type: User & { profile: Profile | null; posts: Post[] }

// Nested includes
const postWithAuthorProfile = await prisma.post.findUnique({
  where: { id: postId },
  include: {
    author: {
      include: {
        profile: true, // Include author's profile
      },
    },
    categories: true,
  },
});
```

**Why good:** Single query loads all relations, type includes relation fields, nested includes for deep relations

### Bad Example - N+1 Query Problem

```typescript
// WRONG - N+1 queries
const users = await prisma.user.findMany();

// This creates N additional queries!
for (const user of users) {
  const posts = await prisma.post.findMany({
    where: { authorId: user.id },
  });
  console.log(`${user.name} has ${posts.length} posts`);
}
```

**Why bad:** 1 query for users + N queries for posts = N+1 queries, extremely slow with large datasets

### Good Example - Fixed N+1

```typescript
// CORRECT - Single query with include
const users = await prisma.user.findMany({
  include: {
    posts: true,
  },
});

for (const user of users) {
  console.log(`${user.name} has ${user.posts.length} posts`);
}
```

---

## Include with Filtering

### Good Example - Filtered Relation Data

```typescript
// Include only published posts
const userWithPublishedPosts = await prisma.user.findUnique({
  where: { id: userId },
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: "desc" },
      take: 10, // Limit to 10 posts
    },
  },
});

// Include with nested filtering
const authorWithRecentPosts = await prisma.user.findUnique({
  where: { id: userId },
  include: {
    posts: {
      where: {
        published: true,
        createdAt: { gte: new Date("2024-01-01") },
      },
      include: {
        categories: {
          where: { active: true },
        },
      },
      orderBy: { createdAt: "desc" },
    },
  },
});
```

**Why good:** Filter relations in same query, orderBy and take for sorted/limited results, nested filtering for deep relations

---

## Select Pattern (Optimized)

### Good Example - Select Specific Fields

```typescript
// Select only needed fields
const userSummary = await prisma.user.findUnique({
  where: { id: userId },
  select: {
    id: true,
    name: true,
    email: true,
    // profile not included - smaller payload
  },
});
// Type: { id: string; name: string; email: string }

// Select with nested relation fields
const postSummary = await prisma.post.findUnique({
  where: { id: postId },
  select: {
    id: true,
    title: true,
    author: {
      select: {
        id: true,
        name: true,
        // Only id and name, not email, profile, etc.
      },
    },
  },
});
// Type: { id: string; title: string; author: { id: string; name: string } }
```

**Why good:** Smaller payload over network, reduced memory usage, type reflects actual data shape

### Include vs Select

```typescript
// include: Adds relations to full model
const userWithPosts = await prisma.user.findUnique({
  where: { id: userId },
  include: { posts: true },
});
// Returns: All User fields + posts array

// select: Only specified fields (cannot mix with include at top level)
const userPartial = await prisma.user.findUnique({
  where: { id: userId },
  select: {
    id: true,
    name: true,
    posts: {
      select: {
        id: true,
        title: true,
      },
    },
  },
});
// Returns: Only id, name, and posts with only id, title
```

---

## Relation Filtering (on Parent)

### Good Example - Filter by Relation Data

```typescript
// Find users who have at least one published post
const usersWithPublishedPosts = await prisma.user.findMany({
  where: {
    posts: {
      some: { published: true },
    },
  },
});

// Find users where ALL posts are published
const usersWithAllPublished = await prisma.user.findMany({
  where: {
    posts: {
      every: { published: true },
    },
  },
});

// Find users with NO posts
const usersWithNoPosts = await prisma.user.findMany({
  where: {
    posts: {
      none: {},
    },
  },
});

// Complex relation filter
const activeAuthors = await prisma.user.findMany({
  where: {
    AND: [
      { role: "AUTHOR" },
      {
        posts: {
          some: {
            published: true,
            createdAt: { gte: new Date("2024-01-01") },
          },
        },
      },
    ],
  },
});
```

**Why good:** some/every/none filter on relation existence and data, combine with AND/OR for complex filters

---

## Many-to-Many Relations

### Good Example - Working with Join Tables

```typescript
// Schema reference:
// model Post {
//   categories Category[]
// }
// model Category {
//   posts Post[]
// }

// Find posts in specific categories
const postsInCategory = await prisma.post.findMany({
  where: {
    categories: {
      some: { name: "Technology" },
    },
  },
  include: {
    categories: true,
  },
});

// Add categories to post
const updatedPost = await prisma.post.update({
  where: { id: postId },
  data: {
    categories: {
      connect: [{ id: categoryId1 }, { id: categoryId2 }],
    },
  },
});

// Remove category from post
const postWithoutCategory = await prisma.post.update({
  where: { id: postId },
  data: {
    categories: {
      disconnect: { id: categoryId },
    },
  },
});

// Replace all categories
const postNewCategories = await prisma.post.update({
  where: { id: postId },
  data: {
    categories: {
      set: [{ id: newCategoryId }], // Removes all existing, adds new
    },
  },
});
```

**Why good:** connect/disconnect for adding/removing, set for replacing all, Prisma handles join table automatically

---

## Self-Relations

### Good Example - Hierarchical Data

```typescript
// Schema:
// model Category {
//   id       String     @id
//   name     String
//   parentId String?
//   parent   Category?  @relation("CategoryHierarchy", fields: [parentId], references: [id])
//   children Category[] @relation("CategoryHierarchy")
// }

// Get category with parent and children
const categoryTree = await prisma.category.findUnique({
  where: { id: categoryId },
  include: {
    parent: true,
    children: {
      include: {
        children: true, // Grandchildren
      },
    },
  },
});

// Find root categories
const rootCategories = await prisma.category.findMany({
  where: { parentId: null },
  include: { children: true },
});
```

**Why good:** Self-relations for hierarchies, recursive includes for tree depth, filter parentId: null for roots

---

## Quick Reference

| Operation                                         | Use When                                 |
| ------------------------------------------------- | ---------------------------------------- |
| `include: { relation: true }`                     | Load all fields of relation              |
| `include: { relation: { where, take, orderBy } }` | Filter/limit relation data               |
| `select: { field: true }`                         | Load only specific fields                |
| `select: { relation: { select } }`                | Nested field selection                   |
| `where: { relation: { some } }`                   | Filter parent by relation existence      |
| `where: { relation: { every } }`                  | Filter parent where all relations match  |
| `where: { relation: { none } }`                   | Filter parent with no matching relations |
| `connect: { id }`                                 | Link existing relation                   |
| `disconnect: { id }`                              | Unlink relation                          |
| `set: [{ id }]`                                   | Replace all relations                    |
| `create: { data }`                                | Create new relation                      |

# Prisma - Transaction Examples

> Atomic operations, nested writes, and interactive transactions. See [SKILL.md](../SKILL.md) for core concepts.

---

## Nested Writes (Implicit Transactions)

### Good Example - Create with Relations

```typescript
import { prisma } from "../lib/db/client";

// All operations in a single atomic transaction
const user = await prisma.user.create({
  data: {
    email: "alice@example.com",
    name: "Alice",
    role: "USER",
    profile: {
      create: { bio: "Software engineer" },
    },
    posts: {
      create: [
        { title: "First Post", content: "Hello world", published: true },
        { title: "Draft", content: "Work in progress" },
      ],
    },
  },
  include: {
    profile: true,
    posts: true,
  },
});
// If any part fails, ALL changes roll back
```

**Why good:** Implicit transaction wraps entire operation, no partial state possible, includes return created relations

### Good Example - Update with Nested Upsert

```typescript
// Update user and upsert profile atomically
const user = await prisma.user.update({
  where: { id: userId },
  data: {
    name: "Updated Name",
    profile: {
      upsert: {
        create: { bio: "New profile" },
        update: { bio: "Updated profile" },
      },
    },
  },
  include: { profile: true },
});
// Either both succeed or both fail
```

**Why good:** Upsert handles create-or-update, atomic with parent update

---

## Batch Transactions ($transaction array)

### Good Example - Multiple Independent Operations

```typescript
// Execute multiple queries in single transaction
const [users, posts, stats] = await prisma.$transaction([
  prisma.user.findMany({ where: { role: "ADMIN" } }),
  prisma.post.findMany({ where: { published: true } }),
  prisma.user.count(),
]);

// Consistent pagination (data and count at same point in time)
const DEFAULT_PAGE_SIZE = 20;

const [items, total] = await prisma.$transaction([
  prisma.post.findMany({
    skip: 0,
    take: DEFAULT_PAGE_SIZE,
    orderBy: { createdAt: "desc" },
  }),
  prisma.post.count(),
]);

// Batch mutations
const [deletedDrafts, publishedCount] = await prisma.$transaction([
  prisma.post.deleteMany({
    where: { authorId: userId, published: false },
  }),
  prisma.post.updateMany({
    where: { authorId: userId, status: "PENDING" },
    data: { status: "PUBLISHED" },
  }),
]);
```

**Why good:** All queries see consistent database state, atomic batch mutations, returns typed array

---

## Interactive Transactions

### Good Example - Complex Business Logic

```typescript
const MINIMUM_BALANCE = 0;

interface TransferResult {
  sender: Account;
  recipient: Account;
  amount: number;
}

const transferFunds = async (
  fromAccountId: string,
  toAccountId: string,
  amount: number,
): Promise<TransferResult> => {
  // CRITICAL: Use tx parameter, NOT prisma
  return await prisma.$transaction(async (tx) => {
    // 1. Decrement sender balance
    const sender = await tx.account.update({
      where: { id: fromAccountId },
      data: { balance: { decrement: amount } },
    });

    // 2. Validate business rule
    if (sender.balance < MINIMUM_BALANCE) {
      // Throwing rolls back the entire transaction
      throw new Error("Insufficient funds");
    }

    // 3. Increment recipient balance
    const recipient = await tx.account.update({
      where: { id: toAccountId },
      data: { balance: { increment: amount } },
    });

    // 4. Log the transfer
    await tx.transferLog.create({
      data: {
        fromAccountId,
        toAccountId,
        amount,
        timestamp: new Date(),
      },
    });

    return { sender, recipient, amount };
  });
};
```

**Why good:** tx parameter ensures all operations are in transaction, throwing rolls back, business logic validation inside transaction

### Bad Example - Using Wrong Client

```typescript
// WRONG - Using prisma instead of tx
await prisma.$transaction(async (tx) => {
  await prisma.post.create({ data: { title: "Post" } }); // WRONG: uses prisma
  await tx.user.update({ where: { id: "1" }, data: { name: "Updated" } });
});
// post.create is NOT in the transaction!
```

**Why bad:** Operations using `prisma` bypass transaction context, only `tx` operations will rollback on failure

---

## Transaction Options

### Good Example - Configuring Transaction Behavior

```typescript
import { Prisma } from "@prisma/client";

const TRANSACTION_TIMEOUT_MS = 10000;
const MAX_WAIT_MS = 5000;

const result = await prisma.$transaction(
  async (tx) => {
    // Long-running operations
    const users = await tx.user.findMany({ where: { role: "USER" } });

    for (const user of users) {
      await tx.notification.create({
        data: {
          userId: user.id,
          message: "System maintenance scheduled",
        },
      });
    }

    return { notified: users.length };
  },
  {
    maxWait: MAX_WAIT_MS, // Max time to wait for connection
    timeout: TRANSACTION_TIMEOUT_MS, // Max execution time
    isolationLevel: Prisma.TransactionIsolationLevel.Serializable, // Strictest isolation
  },
);
```

**Why good:** Named constants for timeouts, `Prisma.TransactionIsolationLevel` enum for type-safe isolation, maxWait prevents indefinite waiting

---

## Error Handling in Transactions

### Good Example - Typed Error Handling

```typescript
import { Prisma } from "@prisma/client";

const UNIQUE_CONSTRAINT_VIOLATION = "P2002";
const RECORD_NOT_FOUND = "P2025";

const createUserSafely = async (email: string, name: string) => {
  try {
    return await prisma.$transaction(async (tx) => {
      const user = await tx.user.create({
        data: { email, name },
      });

      await tx.profile.create({
        data: { userId: user.id },
      });

      return user;
    });
  } catch (error) {
    if (error instanceof Prisma.PrismaClientKnownRequestError) {
      if (error.code === UNIQUE_CONSTRAINT_VIOLATION) {
        throw new Error("Email already registered");
      }
      if (error.code === RECORD_NOT_FOUND) {
        throw new Error("Record not found");
      }
    }
    throw error;
  }
};
```

**Why good:** Prisma error types for specific handling, named constants for error codes, rethrows unknown errors

---

## Optimistic Concurrency Control

### Good Example - Version-Based Updates

```typescript
// Schema:
// model Post {
//   id      String @id
//   title   String
//   version Int    @default(0)
// }

const updatePostOptimistic = async (
  postId: string,
  title: string,
  expectedVersion: number,
) => {
  return await prisma.$transaction(async (tx) => {
    // Update only if version matches
    const result = await tx.post.updateMany({
      where: {
        id: postId,
        version: expectedVersion,
      },
      data: {
        title,
        version: { increment: 1 },
      },
    });

    if (result.count === 0) {
      throw new Error("Concurrent modification detected");
    }

    return tx.post.findUnique({ where: { id: postId } });
  });
};
```

**Why good:** Version field prevents lost updates, updateMany doesn't throw on 0 matches, increment version atomically

---

## Quick Reference

| Transaction Type                    | Use When                                   |
| ----------------------------------- | ------------------------------------------ |
| Nested writes                       | Create/update with relations               |
| `$transaction([...])`               | Multiple independent operations            |
| `$transaction(async (tx) => {...})` | Complex business logic with reads + writes |

| Option           | Purpose                      | Default          |
| ---------------- | ---------------------------- | ---------------- |
| `maxWait`        | Max wait for connection (ms) | 2000             |
| `timeout`        | Max execution time (ms)      | 5000             |
| `isolationLevel` | Transaction isolation        | Database default |

| Isolation Level   | Consistency | Performance |
| ----------------- | ----------- | ----------- |
| `ReadUncommitted` | Lowest      | Highest     |
| `ReadCommitted`   | Low         | High        |
| `RepeatableRead`  | Medium      | Medium      |
| `Serializable`    | Highest     | Lowest      |

| Critical Rule                                     | Reason                                      |
| ------------------------------------------------- | ------------------------------------------- |
| Use `tx` not `prisma` in interactive transactions | Operations outside tx bypass transaction    |
| Throw to rollback                                 | Any exception rolls back entire transaction |
| Keep transactions short                           | Long transactions lock resources            |