# Database Operations

> Query and mutate data with type-safe Prisma ORM operations in your Next.js Prisma kit application.

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

---

Query and mutate PostgreSQL data with Prisma ORM - type-safe database operations with compile-time validation, relation handling, and transaction support.

The Next.js Prisma kit uses Prisma ORM for all database operations. Prisma generates a type-safe 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.

Prisma ORM is a type-safe database client that generates TypeScript types from your schema. It replaces raw SQL with an intuitive API while preserving the ability to write raw queries when needed.

## 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 projects = 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 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 projects = 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 efficiently:

```typescript
const result = await db.project.createMany({
  data: [
    { name: 'Project 1', organizationId },
    { name: 'Project 2', organizationId },
  ],
});

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

### With Relations

Create records with related data in a single operation:

```typescript
const organization = await db.organization.create({
  data: {
    id: crypto.randomUUID(),
    name: 'New Organization',
    slug: 'new-org',
    createdAt: new Date(),
    members: {
      create: {
        id: crypto.randomUUID(),
        userId,
        role: 'owner',
        createdAt: new Date(),
      },
    },
  },
  include: { members: true },
});
```

## Update Data

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

### Update by ID

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

### Conditional Update

Update multiple records matching criteria with `updateMany()`:

```typescript
const result = await db.project.updateMany({
  where: {
    organizationId,
    lastActivity: { lt: oneMonthAgo },
  },
  data: { status: 'archived' },
});

console.log(`Archived ${result.count} projects`);
```

### Partial Update

Only 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

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

### Conditional Delete

Delete multiple records matching criteria:

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

## Transactions

When multiple operations must succeed or fail together, use `$transaction()`:

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

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

## Raw SQL

For complex queries that are easier in SQL, use `$queryRaw`:

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

## Counting Records

Use `count()` for efficient counting without fetching records:

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

## Pagination

For large datasets, use `skip` and `take` for pagination:

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

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

// Good: Scoped to organization
const projects = await db.project.findMany({
  where: { organizationId: currentOrganizationId },
});

// Bad: Not scoped - security risk!
const allProjects = await db.project.findMany();
```

## Common Pitfalls

- **Forgetting organization scope**: Every tenant query must filter by `organizationId`. Without it, users can read other organizations' data.
- **N+1 queries**: Fetching records in a loop causes one query per iteration. Use `include` to fetch related data in a single query.
- **Missing indexes**: Queries on unindexed columns cause full table scans. Add indexes for `organizationId`, `createdAt`, and any column you filter by.
- **Unbounded queries**: `findMany()` without `take` returns all records. Always limit results, especially in production.
- **Mixing tx and db**: Inside `$transaction()`, use `tx` (the transaction client), not `db`. Using `db` bypasses the transaction.
- **Ignoring `null`**: `findUnique()` and `findFirst()` can return `null`. Always handle the missing-record case.

## Performance Tips

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
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
6. **Cache with React's `cache()`** - Wrap loaders to prevent duplicate queries during a single request

{% faq
   title="Frequently Asked Questions"
   items=[
     {"question": "When should I use findUnique vs findFirst?", "answer": "Use findUnique when querying by a unique field (id, email). Use findFirst for non-unique fields or when you only want one result from a potential many."},
     {"question": "How do I handle optional relations?", "answer": "When including optional relations, the result can be null. Check for existence before accessing: if (org.subscription) { ... }"},
     {"question": "Should I use select or include?", "answer": "Use include to fetch the full related record. Use select to fetch specific fields (reduces data transfer). Select is more precise but requires listing every field you need."},
     {"question": "How do transactions work with errors?", "answer": "If any operation inside $transaction() throws, all operations roll back. The error propagates to your catch block. No partial commits."},
     {"question": "Can I use raw SQL for everything?", "answer": "Yes, but you lose type safety and need to handle SQL injection manually. Use $queryRaw for complex queries, but prefer the Prisma API for standard operations."}
   ]
/%}

## Related

- [Development Guide Overview](./overview) - Full development patterns guide
- [Database Schema](../database/schema) - Schema definition and migrations
- [Adding Features](./adding-features) - Step-by-step feature tutorial

---

**Back to:** [Development Guide Overview](./overview)