Drizzle Query Patterns

This page catalogs the Drizzle ORM patterns used in the Trovella codebase. All examples assume tx (or ctx.db in tRPC handlers) is a tenant-scoped transaction from withTenantContext.

Select with Conditions

Use eq, and, desc, asc from drizzle-orm for typed, composable conditions:

import { and, desc, eq } from "drizzle-orm";

// Single condition
const [plan] = await ctx.db
  .select()
  .from(researchPlan)
  .where(eq(researchPlan.id, input.planId))
  .limit(1);

// Multiple conditions
const conditions = [eq(planAuditLog.planId, input.planId)];
if (input.eventType) {
  conditions.push(eq(planAuditLog.eventType, input.eventType));
}

const logs = await ctx.db
  .select()
  .from(planAuditLog)
  .where(and(...conditions))
  .orderBy(desc(planAuditLog.createdAt))
  .limit(input.limit)
  .offset(input.offset);

The dynamic conditions pattern (building an array and spreading into and()) is used throughout the research plan and AI logs routers when filters are optional.

Relational Queries

Drizzle's relational query API (ctx.db.query.*) supports with for eager loading related records:

const members = await ctx.db.query.member.findMany({
  with: {
    user: {
      columns: { id: true, name: true, email: true, image: true },
    },
  },
});

Use findFirst for single-record lookups:

const memberRecord = await ctx.db.query.member.findFirst({
  where: and(eq(member.userId, ctx.session.user.id), eq(member.organizationId, ctx.organizationId)),
});

The relational API requires that the Drizzle instance was created with the schema (which @repo/db does in client.ts).

Column Selection

Select specific columns to reduce data transfer:

const org = await ctx.db.query.organization.findFirst({
  where: eq(organization.id, ctx.organizationId),
  columns: { type: true },
});

Or with the select builder for computed/aggregated columns:

const rows = await ctx.db
  .select({
    sourceTable: documentChunk.sourceTable,
    count: count(),
  })
  .from(documentChunk)
  .groupBy(documentChunk.sourceTable);

Insert

Standard single-row insert:

await tx.insert(researchPlan).values({
  id: planId,
  organizationId,
  userId,
  name,
  researchQuestion,
  status: "planning",
  createdBySession: sessionId ?? null,
  outputFormattingNotes: outputFormattingNotes ?? null,
});

Multi-row insert (used in seed scripts, but also valid in application code):

await db.insert(user).values([
  { id: "user_1", name: "Alice", ... },
  { id: "user_2", name: "Bob", ... },
]);

For tenant-scoped inserts, include the organizationId in the values. RLS will reject the insert if the organization_id does not match the session variable.

Update with Returning

Use .returning() to get the updated row back without a separate SELECT:

const [updated] = await ctx.db
  .update(organization)
  .set({
    ...(input.name !== undefined && { name: input.name }),
    ...(input.slug !== undefined && { slug: input.slug }),
  })
  .where(eq(organization.id, ctx.organizationId))
  .returning();

if (!updated) {
  throw new TRPCError({ code: "NOT_FOUND" });
}

The conditional spread pattern (...(input.name !== undefined && { name: input.name })) allows partial updates where only the provided fields are changed.

Soft Delete

The PAT module uses a soft-revoke pattern -- setting a timestamp instead of deleting the row:

const [updated] = await db
  .update(personalAccessToken)
  .set({ revokedAt: new Date() })
  .where(
    and(
      eq(personalAccessToken.id, tokenId),
      eq(personalAccessToken.userId, userId),
      isNull(personalAccessToken.revokedAt),
    ),
  )
  .returning({ id: personalAccessToken.id });

The isNull(personalAccessToken.revokedAt) condition prevents revoking an already-revoked token.

Hard Delete

await ctx.db.delete(member).where(eq(member.id, input.memberId));

RLS ensures this only affects rows in the current tenant. A cross-tenant delete silently affects zero rows (the row is invisible).

Raw SQL

Use tx.execute() for queries that cannot be expressed with the Drizzle builder:

// Aggregate subquery
const avgResult = await ctx.db.execute<{ avg: number }>(
  sql`SELECT round(avg(cnt)) AS avg
      FROM (SELECT count(*) AS cnt FROM ${planStep} GROUP BY ${planStep.planId}) sub`,
);
const avgStepsRow = avgResult.rows[0];

The sql template tag from drizzle-orm handles parameter interpolation. Table references like ${planStep} resolve to the table's SQL name.

Vector Search with Raw SQL

pgvector queries require raw SQL because Drizzle does not have native vector operators:

const vectorStr = `[${queryEmbedding.join(",")}]`;

const result = await tx.execute(sql`
  SELECT id, source_table, source_id, embedded_text,
         (embedding <#> ${vectorStr}::halfvec(1536)) * -1 AS similarity
  FROM document_chunk
  WHERE source_table = ${sourceTable}
  ORDER BY embedding <#> ${vectorStr}::halfvec(1536)
  LIMIT ${limit}
`);

The <#> operator computes negative inner product distance. Multiplying by -1 converts it to a similarity score (higher = more similar).

Count Queries

For pagination, run a parallel count query:

const [items, [totalRow]] = await Promise.all([
  ctx.db
    .select()
    .from(researchArtifact)
    .where(where)
    .orderBy(desc(researchArtifact.createdAt))
    .limit(input.limit)
    .offset(input.offset),
  ctx.db.select({ total: count() }).from(researchArtifact).where(where),
]);

return { items, total: totalRow?.total ?? 0 };

See Performance -- Parallel Queries for when this is safe inside a transaction.

Aggregation with SQL Functions

const rows = await ctx.db
  .select({
    totalCalls: count(),
    totalInputTokens: sum(aiUsage.inputTokens),
    totalOutputTokens: sum(aiUsage.outputTokens),
    totalCost: sum(aiUsage.estimatedCost),
    avgLatency: sql<number>`round(avg(${aiUsage.latencyMs}))`,
    errorCount: sql<number>`count(*) filter (where ${aiUsage.stopReason} = 'error')`,
  })
  .from(aiUsage);

Use sql<Type> for inline SQL expressions with type annotations. The FILTER (WHERE ...) clause is PostgreSQL-specific and useful for conditional aggregation without CASE expressions.

Group By with Ordering

const rows = await ctx.db
  .select({
    model: aiUsage.model,
    calls: count(),
    cost: sum(aiUsage.estimatedCost),
  })
  .from(aiUsage)
  .groupBy(aiUsage.model)
  .orderBy(desc(count()));

Date Truncation

For time-series aggregation:

const truncFn = sql`date_trunc('day', ${aiUsage.createdAt})`;

const rows = await ctx.db
  .select({
    period: truncFn,
    calls: count(),
    inputTokens: sum(aiUsage.inputTokens),
  })
  .from(aiUsage)
  .groupBy(truncFn)
  .orderBy(truncFn);