# Database Operations

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

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

---

Perform type-safe database operations with [Drizzle ORM](https://orm.drizzle.team/docs/overview).

Drizzle provides a SQL-like query builder that feels familiar if you know SQL, while giving you full TypeScript type safety. Every query is validated at compile time. If you reference a column that doesn't exist or pass the wrong type, TypeScript catches it before your code runs.

## Query Data

Drizzle's query builder uses a chainable API. You start with `db.select()`, specify the table with `.from()`, and add conditions with `.where()`. The result is always a Promise that resolves to an array of records.

### Find First

When you need a single record, query with `.limit(1)` and destructure the first element. If no record matches, `user` will be `undefined`:

```typescript
import { eq } from 'drizzle-orm';
import { db, user } from '@kit/database';

const [userRecord] = await db
  .select()
  .from(user)
  .where(eq(user.email, 'user@example.com'))
  .limit(1);
```

### Find Many

For lists of records, add sorting with `.orderBy()` and pagination with `.limit()`. The `desc()` helper sorts in descending order (newest first):

```typescript
import { eq, desc } from 'drizzle-orm';
import { db, project } from '@kit/database';

const results = await db
  .select()
  .from(project)
  .where(eq(project.organizationId, organizationId))
  .orderBy(desc(project.createdAt))
  .limit(10);
```

### With Joins

Joins let you fetch related data in a single query. Use `leftJoin()` when the related record might not exist (you'll get `null` for missing relations), or `innerJoin()` when you only want records that have the relation:

```typescript
import { eq } from 'drizzle-orm';
import { db, organization, member, user } from '@kit/database';

const results = await db
  .select()
  .from(organization)
  .leftJoin(member, eq(member.organizationId, organization.id))
  .leftJoin(user, eq(user.id, member.userId))
  .where(eq(organization.id, organizationId));
```

### Complex Queries

Drizzle provides operators that map directly to SQL. Combine them with `and()` or `or()` to build complex filters:

```typescript
import { and, eq, gt, like } from 'drizzle-orm';
import { db, project } from '@kit/database';

const results = await db
  .select()
  .from(project)
  .where(
    and(
      eq(project.organizationId, organizationId),
      gt(project.createdAt, startDate),
      like(project.name, '%search%')
    )
  );
```

## Insert Data

Use `db.insert()` to create new records. The `.returning()` method is important—without it, the insert succeeds but returns nothing. With it, you get the newly created record including any database-generated values like IDs or timestamps.

### Single Insert

Insert a single record and get it back:

```typescript
const [projectRecord] = await db
  .insert(project)
  .values({
    name: 'New Project',
    organizationId,
  })
  .returning();
```

### Multiple Inserts

Pass an array to `.values()` to insert multiple records in a single query. This is much faster than inserting one at a time:

```typescript
const newProjects = await db
  .insert(project)
  .values([
    { name: 'Project 1', organizationId },
    { name: 'Project 2', organizationId },
  ])
  .returning();
```

### With Default Values

If your schema defines default values (like `defaultNow()` for timestamps or a default ID generator), you can omit those fields. The database fills them in automatically:

```typescript
const [user] = await db
  .insert(user)
  .values({
    email: 'user@example.com',
    // id, createdAt use defaults from schema
  })
  .returning();
```

## Update Data

Updates use `db.update()` with `.set()` to specify new values. Always include a `.where()` clause—without it, you'll update every row in the table.

### Update by ID

The most common pattern: update a specific record by its ID. Remember to manually update the `updatedAt` timestamp if your schema has one:

```typescript
const [updatedProject] = await db
  .update(project)
  .set({
    name: 'Updated Name',
    updatedAt: new Date(),
  })
  .where(eq(project.id, projectId))
  .returning();
```

### Conditional Update

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

```typescript
await db
  .update(project)
  .set({ status: 'archived' })
  .where(
    and(
      eq(project.organizationId, organizationId),
      lt(project.lastActivity, oneMonthAgo)
    )
  );
```

### Partial Update

When you only want to update some fields, pass a partial object. Fields not included in `.set()` remain unchanged:

```typescript
const updates: Partial<Project> = {
  name: 'New Name',
  // Other fields remain unchanged
};

await db
  .update(project)
  .set(updates)
  .where(eq(project.id, projectId));
```

## Delete Data

Deletes permanently remove records. Like updates, always include a `.where()` clause to avoid deleting everything.

### Delete by ID

Remove a specific record:

```typescript
await db
  .delete(project)
  .where(eq(project.id, projectId));
```

### Conditional Delete

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

```typescript
await db
  .delete(invitation)
  .where(
    and(
      eq(invitation.organizationId, organizationId),
      lt(invitation.expiresAt, new Date())
    )
  );
```

## Transactions

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

```typescript
await db.transaction(async (tx) => {
  // Create organization
  const [org] = await tx
    .insert(organization)
    .values({ name: 'New Organization', slug: 'new-org' })
    .returning();

  // Add owner as member
  await tx.insert(member).values({
    organizationId: org.id,
    userId,
    role: 'owner',
  });
});
```

### Atomic Counter Updates

A common pattern is updating a counter when inserting or deleting related records. Use transactions with raw SQL for atomic increments:

```typescript
import { sql } from 'drizzle-orm';

// Upvote: insert vote and increment counter atomically
await db.transaction(async (tx) => {
  await tx.insert(feedbackVote).values({
    id: generateId(),
    feedbackItemId: itemId,
    userId,
  });

  await tx
    .update(feedbackItem)
    .set({ voteCount: sql`${feedbackItem.voteCount} + 1` })
    .where(eq(feedbackItem.id, itemId));
});

// Remove vote: delete and decrement atomically
await db.transaction(async (tx) => {
  await tx
    .delete(feedbackVote)
    .where(eq(feedbackVote.id, existingVoteId));

  await tx
    .update(feedbackItem)
    .set({ voteCount: sql`${feedbackItem.voteCount} - 1` })
    .where(eq(feedbackItem.id, itemId));
});
```

### 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.insert(project).values({ ... });
    await tx.insert(projectMember).values({ ... });
    // 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 Drizzle's query builder 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 `sql` template tag lets you write raw SQL while still protecting against injection (variables are automatically parameterized):

```typescript
import { sql } from 'drizzle-orm';

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

## Counting Records

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

```typescript
import { count } from 'drizzle-orm';

const [{ count: projectCount }] = await db
  .select({ count: count() })
  .from(project)
  .where(eq(project.organizationId, organizationId));
```

## Pagination

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

```typescript
import { eq, desc, count } from 'drizzle-orm';
import { db, project } from '@kit/database';

const PAGE_SIZE = 20;

export async function getPaginatedProjects(
  organizationId: string,
  page: number = 1
) {
  const skip = (page - 1) * PAGE_SIZE;

  const [projectList, [{ total }]] = await Promise.all([
    db
      .select()
      .from(project)
      .where(eq(project.organizationId, organizationId))
      .orderBy(desc(project.createdAt))
      .limit(PAGE_SIZE)
      .offset(skip),
    db
      .select({ total: count() })
      .from(project)
      .where(eq(project.organizationId, organizationId)),
  ]);

  return {
    projects: projectList,
    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 { eq } from 'drizzle-orm';
import { db, project } from '@kit/database';

// ✅ Good: Scoped to organization
const results = await db
  .select()
  .from(project)
  .where(eq(project.organizationId, currentOrganizationId));

// ❌ Bad: Not scoped - security risk!
const results = await db.select().from(project);
```

## 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 `.limit()` when you don't need every record
3. **Select only needed fields** — Instead of `select()` (which fetches all columns), use `select({ id: project.id, name: project.name })` to fetch only what you need
4. **Use transactions** — Group related operations to ensure data consistency
5. **Avoid N+1 queries** — Use joins 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 Tables

Define the table in `packages/database/src/schema/`:

```typescript {% title="packages/database/src/schema/project.ts" %}
import { pgTable, text, timestamp } from 'drizzle-orm/pg-core';
import { organization } from './core';

export const project = pgTable('project', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  organizationId: text('organization_id')
    .notNull()
    .references(() => organization.id, { onDelete: 'cascade' }),
  createdAt: timestamp('created_at').notNull().defaultNow(),
  updatedAt: timestamp('updated_at').notNull().defaultNow(),
});
```

Export from `packages/database/src/schema/schema.ts`:

```typescript
export * from './core';
export * from './project';
```

Generate and apply migrations:

```bash
pnpm --filter "@kit/database" drizzle:generate
pnpm --filter "@kit/database" drizzle:migrate
```

### Adding Indexes

Define indexes in the table definition for frequently queried columns:

```typescript
import { index, pgTable, text, timestamp } from 'drizzle-orm/pg-core';

export const project = pgTable(
  'project',
  {
    id: text('id').primaryKey(),
    name: text('name').notNull(),
    organizationId: text('organization_id').notNull(),
    status: text('status').default('active'),
    createdAt: timestamp('created_at').notNull().defaultNow(),
  },
  (table) => [
    index('project_organization_id_idx').on(table.organizationId),
    index('project_status_idx').on(table.status),
  ]
);
```

---

**Related:** [Adding Features →](./adding-features) | [Server Functions →](./server-functions)