Database Operations

Learn how to query and mutate data using Prisma ORM with type-safe patterns for your TanStack Start SaaS application.

Perform type-safe database operations with Prisma ORM.

Prisma generates a fully typed client from your schema, so TypeScript catches invalid field references and type mismatches before your code runs. You get IntelliSense for every query, automatic relation handling, and transactions when you need atomicity. Import the client with import { db } from '@kit/database'.

Quick Reference

OperationMethod
Get onefindUnique(), findFirst()
Get manyfindMany()
Createcreate(), createMany()
Updateupdate(), updateMany()
Deletedelete(), deleteMany()
Countcount()
Transaction$transaction()

Query Data

Prisma's query API uses model methods like findMany(), findUnique(), and findFirst(). Results are always typed to your schema.

Find First

When you need a single record, use findFirst() with a where clause. If no record matches, the result is null:

import { db } from '@kit/database';
const user = await db.user.findFirst({
  where: { email: 'user@example.com' },
});

Or use findUnique() when querying by a unique field:

const user = await db.user.findUnique({
  where: { email: 'user@example.com' },
});

Find Many

For lists of records, use findMany() with orderBy for sorting and take for limiting:

import { db } from '@kit/database';
const results = await db.project.findMany({
  where: { organizationId },
  orderBy: { createdAt: 'desc' },
  take: 10,
});

With Relations

Prisma makes fetching related data easy with include or select. This is much simpler than writing SQL joins:

import { db } from '@kit/database';
const organization = await db.organization.findUnique({
  where: { id: organizationId },
  include: {
    members: {
      include: { user: true },
    },
  },
});
// Access nested data
organization?.members.forEach((member) => {
  console.log(member.user.name);
});

Complex Queries

Combine AND, OR, and comparison operators to build complex filters:

import { db } from '@kit/database';
const results = await db.project.findMany({
  where: {
    AND: [
      { organizationId },
      { createdAt: { gt: startDate } },
      { name: { contains: 'search', mode: 'insensitive' } },
    ],
  },
});

Insert Data

Use create() to insert new records. Prisma automatically returns the created record with all fields.

Single Insert

const project = await db.project.create({
  data: {
    name: 'New Project',
    organizationId,
  },
});

Multiple Inserts

Use createMany() to insert multiple records in a single query. This is much faster than inserting one at a time:

const result = await db.project.createMany({
  data: [
    { name: 'Project 1', organizationId },
    { name: 'Project 2', organizationId },
  ],
});
console.log(`Created ${result.count} projects`);

With Default Values

If your schema defines default values (like @default(now()) for timestamps or @default(uuid()) for IDs), you can omit those fields. The database fills them in automatically:

const user = await db.user.create({
  data: {
    email: 'user@example.com',
    // id, createdAt use defaults from schema
  },
});

Update Data

Use update() with where to identify the record and data for the new values.

Update by ID

The most common pattern: update a specific record by its ID. Fields marked @updatedAt in the schema are refreshed automatically:

const updatedProject = await db.project.update({
  where: { id: projectId },
  data: {
    name: 'Updated Name',
  },
});

Conditional Update

Update multiple records that match certain criteria with updateMany(). This example archives all projects in an organization that haven't been active in a month:

const result = await db.project.updateMany({
  where: {
    organizationId,
    lastActivity: { lt: oneMonthAgo },
  },
  data: { status: 'archived' },
});
console.log(`Archived ${result.count} projects`);

Partial Update

Only the fields you include in data are updated; others remain unchanged:

await db.project.update({
  where: { id: projectId },
  data: {
    name: 'New Name',
    // Other fields remain unchanged
  },
});

Delete Data

Use delete() for single records and deleteMany() for multiple.

Delete by ID

Remove a specific record:

await db.project.delete({
  where: { id: projectId },
});

Conditional Delete

Delete multiple records matching criteria. This example cleans up expired invitations:

await db.invitation.deleteMany({
  where: {
    organizationId,
    expiresAt: { lt: new Date() },
  },
});

Transactions

When you need multiple operations to succeed or fail together, wrap them in $transaction(). If any operation throws, all changes roll back automatically—you'll never end up with partially completed data:

await db.$transaction(async (tx) => {
  // Create organization
  const organization = await tx.organization.create({
    data: { name: 'New Organization', slug: 'new-org' },
  });
  // Add owner as member
  await tx.member.create({
    data: {
      organizationId: organization.id,
      userId,
      role: 'owner',
    },
  });
});

Atomic Counter Updates

A common pattern is updating a counter when inserting or deleting related records. Use a transaction with Prisma's increment / decrement operators for atomic changes:

// Upvote: insert vote and increment counter atomically
await db.$transaction(async (tx) => {
  await tx.feedbackVote.create({
    data: {
      id: generateId(),
      feedbackItemId: itemId,
      userId,
    },
  });
  await tx.feedbackItem.update({
    where: { id: itemId },
    data: { voteCount: { increment: 1 } },
  });
});
// Remove vote: delete and decrement atomically
await db.$transaction(async (tx) => {
  await tx.feedbackVote.delete({
    where: { id: existingVoteId },
  });
  await tx.feedbackItem.update({
    where: { id: itemId },
    data: { voteCount: { decrement: 1 } },
  });
});

Transaction Error Handling

Transactions automatically roll back on error. You can catch errors outside the transaction:

try {
  await db.$transaction(async (tx) => {
    await tx.project.create({ data: { ... } });
    await tx.projectMember.create({ data: { ... } });
    // If this throws, the project insert is rolled back
  });
} catch (error) {
  console.error('Transaction failed:', error);
  throw new Error('Failed to create project with members');
}

Raw SQL

Sometimes Prisma's query API isn't enough—maybe you need a complex aggregation, a database-specific feature, or a query that's just easier to write in SQL. The $queryRaw tagged template lets you write raw SQL while still protecting against injection (variables are automatically parameterized):

const result = await db.$queryRaw`
  SELECT
    p.*,
    COUNT(t.id) as task_count
  FROM projects p
  LEFT JOIN tasks t ON t.project_id = p.id
  WHERE p.organization_id = ${organizationId}
  GROUP BY p.id
`;

Never interpolate raw user input into $queryRawUnsafe(). Use the $queryRaw tagged template (which parameterizes values) whenever a query includes external input.

Counting Records

To count records without fetching them, use count(). This is much more efficient than fetching all records and checking .length:

const projectCount = await db.project.count({
  where: { organizationId },
});

Pagination

For large datasets, fetch records in pages using skip and take. Run the data query and count query in parallel with Promise.all() for better performance:

import { db } from '@kit/database';
const PAGE_SIZE = 20;
export async function getPaginatedProjects(
  organizationId: string,
  page: number = 1
) {
  const skip = (page - 1) * PAGE_SIZE;
  const [projects, total] = await Promise.all([
    db.project.findMany({
      where: { organizationId },
      orderBy: { createdAt: 'desc' },
      take: PAGE_SIZE,
      skip,
    }),
    db.project.count({
      where: { organizationId },
    }),
  ]);
  return {
    projects,
    total,
    page,
    pageSize: PAGE_SIZE,
    totalPages: Math.ceil(total / PAGE_SIZE),
  };
}

Multi-tenancy Pattern

In a multi-tenant application, data isolation is critical. Every query that touches tenant data must filter by organizationId. Forgetting this filter is a security vulnerability—users could see other organizations' data:

import { db } from '@kit/database';
// ✅ Good: Scoped to organization
const results = await db.project.findMany({
  where: { organizationId: currentOrganizationId },
});
// ❌ Bad: Not scoped - security risk!
const results = await db.project.findMany();

Performance Tips

Keep these practices in mind as your application grows:

  1. Use indexes — Add database indexes on columns you frequently filter or sort by (like organizationId, createdAt)
  2. Limit results — Always use take when you don't need every record
  3. Select only needed fields — Use select to fetch only the fields you need instead of the full record
  4. Use transactions — Group related operations to ensure data consistency
  5. Avoid N+1 queries — Use include to fetch related data in one query instead of looping and querying for each record
  6. Fetch in loaders — Read data from a route loader (or a createServerFn({ method: 'GET' }) helper) so it runs once on the server per navigation, rather than querying repeatedly from components

Schema Management

Adding New Models

Define the model in the shared Prisma schema at packages/database/src/prisma/schema.prisma:

packages/database/src/prisma/schema.prisma

model Project {
  id             String       @id @default(uuid())
  name           String
  organizationId String       @map("organization_id")
  createdAt      DateTime     @default(now()) @map("created_at")
  updatedAt      DateTime     @updatedAt @map("updated_at")
  organization   Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  @@map("projects")
}

Add the back-relation to the related model (here, Organization):

model Organization {
  // ... existing fields
  projects Project[]
}

Regenerate the Prisma client, then create and apply a migration:

pnpm --filter "@kit/database" prisma:generate
pnpm --filter "@kit/database" prisma:migrate

Prisma will prompt for a migration name; the generated SQL lands in packages/database/src/prisma/migrations/. See the Prisma Migrate documentation for details.

Adding Indexes

Declare indexes with @@index in the model for frequently queried columns:

packages/database/src/prisma/schema.prisma

model Project {
  id             String   @id @default(uuid())
  name           String
  organizationId String   @map("organization_id")
  status         String   @default("active")
  createdAt      DateTime @default(now()) @map("created_at")
  @@index([organizationId])
  @@index([status])
  @@map("projects")
}

Related: Adding Features → | Server Functions →