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.

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 documentation.

Importing

Import the client as db from @kit/database:

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

If you need the Prisma types:

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

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:

// 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:

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

Ordering and Pagination

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.

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

Avoid unscoped tenant queries:

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

Insert

// 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

// 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() } },
});

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.

Upsert (Insert or Update)

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:

// 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

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

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

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:

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

// 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:

// 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

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

For the full Prisma Client documentation, see prisma.io.

Frequently Asked Questions

How do I avoid N+1 queries?

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.

When should I use select vs include?

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.

Do transactions work with serverless deployments?

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.

How do I handle optimistic locking?

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 →