Database Operations

Learn how to query and mutate data using Drizzle ORM.

Perform type-safe database operations with Drizzle ORM.

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:

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

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:

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:

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:

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:

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:

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:

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:

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:

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:

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

Conditional Delete

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

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:

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

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

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:

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:

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:

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