# Database Operations

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

*Canonical: https://makerkit.dev/docs/tanstack-prisma/development-guide/database-operations*

---

Perform type-safe database operations with [Prisma ORM](https://www.prisma.io/docs).

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

| Operation | Method |
|-----------|--------|
| Get one | `findUnique()`, `findFirst()` |
| Get many | `findMany()` |
| Create | `create()`, `createMany()` |
| Update | `update()`, `updateMany()` |
| Delete | `delete()`, `deleteMany()` |
| Count | `count()` |
| 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`:

```typescript
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:

```typescript
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:

```typescript
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:

```typescript
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:

```typescript
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

```typescript
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:

```typescript
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:

```typescript
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:

```typescript
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:

```typescript
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:

```typescript
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:

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

### Conditional Delete

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

```typescript
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:

```typescript
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:

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

```typescript
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):

```typescript
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`:

```typescript
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:

```typescript
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:

```typescript
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`:

```prisma {% title="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`):

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

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

```bash
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](https://www.prisma.io/docs/orm/prisma-migrate) for details.

### Adding Indexes

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

```prisma {% title="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 →](./adding-features) | [Server Functions →](./server-functions)