# Using the Prisma Client

> Learn how to use the Prisma client for queries, inserts, updates, transactions, and relations in the TanStack Start Prisma SaaS Kit.

*Canonical: https://makerkit.dev/docs/tanstack-prisma/database/client*

---

The Prisma client provides a fully type-safe API for querying and mutating data. TypeScript catches errors at compile time rather than runtime.

This guide is part of the [Database Configuration](./overview) documentation.

## Importing

Import the client as `db` from `@kit/database`:

```typescript
import { db } from '@kit/database';
```

If you need the Prisma types:

```typescript
import type { PrismaClient, Prisma } from '@kit/database';
```

The `db` object is the singleton Prisma client. Access models as properties (`db.user`, `db.organization`, `db.member`, ...) and call methods like `findMany`, `create`, `update`, and `delete`.

## Basic Operations

```typescript
import { db } from '@kit/database';

// Find many
const users = await db.user.findMany();

// Find with filter and relations
const memberships = await db.member.findMany({
  where: { organizationId: currentOrgId },
  include: { user: true },
});

// Find one
const user = await db.user.findUnique({
  where: { id: userId },
});

// Update
await db.user.update({
  where: { id: userId },
  data: { name: 'Alice Johnson' },
});
```

## Filters

Prisma exposes filter operators inside the `where` object:

```typescript
// Equality
await db.user.findMany({ where: { email: 'test@example.com' } });

// Not equal
await db.user.findMany({ where: { role: { not: 'admin' } } });

// Comparisons
await db.user.findMany({
  where: { createdAt: { gt: new Date('2024-01-01') } },
});

// Case-insensitive contains
await db.user.findMany({
  where: { name: { contains: 'john', mode: 'insensitive' } },
});

// Null checks
await db.user.findMany({ where: { image: null } });

// In array
await db.user.findMany({ where: { role: { in: ['admin', 'owner'] } } });

// Multiple conditions (AND is implicit)
await db.user.findMany({
  where: { banned: false, banExpires: null },
});

// OR
await db.user.findMany({
  where: { OR: [{ role: 'admin' }, { role: 'owner' }] },
});
```

## Selecting Columns

Use `select` to return only the columns you need:

```typescript
const userEmails = await db.user.findMany({
  select: { id: true, email: true },
});
```

## Ordering and Pagination

```typescript
const recentUsers = await db.user.findMany({
  orderBy: { createdAt: 'desc' },
  take: 10,
});

// Offset pagination
async function getPaginatedUsers(page: number, pageSize = 20) {
  return db.user.findMany({
    orderBy: { createdAt: 'desc' },
    take: pageSize,
    skip: page * pageSize,
  });
}

// Cursor pagination
async function getUsersAfter(cursorId: string, limit = 20) {
  return db.user.findMany({
    take: limit,
    skip: 1,
    cursor: { id: cursorId },
    orderBy: { createdAt: 'desc' },
  });
}
```

## Tenant-Scoped Queries

Always filter tenant-owned data by fields such as `organizationId` or `userId`.

```typescript
const members = await db.member.findMany({
  where: { organizationId: currentOrgId },
  orderBy: { createdAt: 'desc' },
  take: 20,
});
```

Avoid unscoped tenant queries:

```typescript
const allMembers = await db.member.findMany(); // avoid for tenant-owned data
```

## Insert

```typescript
// Single create
const newUser = await db.user.create({
  data: {
    id: crypto.randomUUID(),
    name: 'John Doe',
    email: 'john@example.com',
    emailVerified: false,
  },
});

// Bulk create
await db.member.createMany({
  data: [
    { id: crypto.randomUUID(), userId: 'user1', organizationId: 'org1', role: 'owner', createdAt: new Date() },
    { id: crypto.randomUUID(), userId: 'user2', organizationId: 'org1', role: 'member', createdAt: new Date() },
  ],
});
```

## Update and Delete

```typescript
// Update by unique field
const updated = await db.user.update({
  where: { id: userId },
  data: { emailVerified: true },
});

// Update many with a filter
await db.session.updateMany({
  where: { userId },
  data: { activeOrganizationId: null },
});

// Delete by unique field
await db.session.delete({ where: { id: sessionId } });

// Delete expired sessions
await db.session.deleteMany({
  where: { expiresAt: { lt: new Date() } },
});
```

{% callout type="warning" title="updateMany / deleteMany affect all matching rows" %}
`updateMany` and `deleteMany` without a `where` clause affect every row in the table. Always include a filter for tenant-scoped data.
{% /callout %}

## Upsert (Insert or Update)

```typescript
await db.subscription.upsert({
  where: { id: subscriptionId },
  update: { plan: 'pro', status: 'active' },
  create: {
    id: subscriptionId,
    plan: 'pro',
    referenceId: organizationId,
    status: 'active',
  },
});
```

## Relations

Load related data in a single query with `include`:

```typescript
// User with their organization memberships
const userWithMemberships = await db.user.findUnique({
  where: { id: userId },
  include: {
    members: {
      include: { organization: true },
    },
  },
});

// Organization with members and selected user fields
const orgWithMembers = await db.organization.findUnique({
  where: { id: orgId },
  include: {
    members: {
      include: {
        user: {
          select: { id: true, name: true, email: true, image: true },
        },
      },
    },
  },
});
```

### Filtering Relations

```typescript
const orgWithAdmins = await db.organization.findUnique({
  where: { id: orgId },
  include: {
    members: {
      where: { role: 'admin' },
      orderBy: { createdAt: 'desc' },
      take: 5,
    },
  },
});
```

## Transactions

Use transactions for operations that must succeed or fail together. Prisma supports both an array form and an interactive callback form.

### Interactive transaction

```typescript
import { db } from '@kit/database';

const result = await db.$transaction(async (tx) => {
  const org = await tx.organization.create({
    data: {
      id: crypto.randomUUID(),
      name: 'New Org',
      slug: 'new-org',
      createdAt: new Date(),
    },
  });

  await tx.member.create({
    data: {
      id: crypto.randomUUID(),
      organizationId: org.id,
      userId,
      role: 'owner',
      createdAt: new Date(),
    },
  });

  return org;
});
```

If any operation in the callback throws, all changes are rolled back.

### Array transaction

```typescript
const [org, subscription] = await db.$transaction([
  db.organization.create({ data: orgData }),
  db.subscription.create({ data: subData }),
]);
```

## Auth Context Pattern

The kit provides lightweight auth context helpers to carry the current user and organization through your services. They return a plain context object — you use its fields in your `where` clauses and `data` payloads:

```typescript
import { createOrgAuthContext, db } from '@kit/database';

async function getProjects(userId: string, organizationId: string, role: string) {
  const ctx = createOrgAuthContext(userId, organizationId, role);

  return db.project.findMany({
    where: { organizationId: ctx.organizationId },
  });
}

async function createProject(
  userId: string,
  organizationId: string,
  role: string,
  data: { name: string },
) {
  const ctx = createOrgAuthContext(userId, organizationId, role);

  return db.project.create({
    data: {
      id: crypto.randomUUID(),
      name: data.name,
      organizationId: ctx.organizationId,
      createdAt: new Date(),
    },
  });
}
```

## Counting and Aggregations

```typescript
// Count with filter
const activeCount = await db.user.count({
  where: { banned: false },
});

// Aggregate
const stats = await db.member.aggregate({
  where: { organizationId: orgId },
  _count: true,
});
```

## Common Mistakes to Avoid

**Forgetting the organizationId filter**: Every query on tenant data must include an organization filter to prevent cross-tenant leaks.

**N+1 queries in loops**: Don't fetch related data in a loop. Use `include` to load relations in a single query:

```typescript
// Bad: N+1 queries
const users = await db.user.findMany();
for (const u of users) {
  const members = await db.member.findMany({ where: { userId: u.id } });
}

// Good: single query with relations
const users = await db.user.findMany({
  include: { members: true },
});
```

**updateMany / deleteMany without a WHERE**: Always include a filter — these operate on all matching rows.

**Using `db` instead of `tx` in transactions**: Inside a `$transaction` callback, use the `tx` client, not the global `db`. Using `db` bypasses the transaction.

## Performance Tips

1. **Select only needed columns** with `select` to reduce data transfer
2. **Add indexes** on columns used in `where`, `include`, and `orderBy`
3. **Use `take`** for large result sets
4. **Avoid N+1 queries** by using `include` instead of loops
5. **Use transactions** for multiple related writes

For the full Prisma Client documentation, see [prisma.io](https://www.prisma.io/docs/orm/prisma-client).

{% faq
   title="Frequently Asked Questions"
   items=[
     {"question": "How do I avoid N+1 queries?", "answer": "Use include to fetch related data in a single query instead of looping. For example, db.user.findMany({ include: { members: true } }) is one query, while fetching members in a loop would be N+1."},
     {"question": "When should I use select vs include?", "answer": "Use select to pick specific scalar fields (and optionally relations) and return a narrower shape. Use include to add related records to the default payload. select gives you the tightest payload; include is convenient when you want the full model plus its relations."},
     {"question": "Do transactions work with serverless deployments?", "answer": "Yes, but keep transactions short. Serverless functions have execution time limits, and long-running transactions can cause connection pool exhaustion. For complex operations, break them into smaller transactions with idempotency keys."},
     {"question": "How do I handle optimistic locking?", "answer": "Add an updatedAt field and include it in your where clause when updating. If the row changed since you read it, update throws a not-found error, signaling a conflict."}
   ]
/%}

---

**Next:** [Rate Limit Service →](./rate-limit-service)