Drizzle — Queries

OWNER: urszula, maxim ALSO_USED_BY: boris, floris, hugo, jasper, marco, stef, arjan LAST_VERIFIED: 2026-03-26 GE_STACK_VERSION: drizzle-orm 0.45.x

---

Overview

Drizzle provides two query modes: the SQL-like query builder and the relational query API. GE uses BOTH — the query builder for complex operations and the relational API for simple reads with nested data. Agents must understand when to use each.

---

Query Builder (SQL-like)

Select

import { db } from '@/lib/db';
import { agents, tasks } from '@/drizzle/schema';
import { eq, and, gt, desc, sql, inArray } from 'drizzle-orm';

// Simple select
const allAgents = await db.select().from(agents);

// With conditions
const activeAgents = await db
  .select()
  .from(agents)
  .where(eq(agents.status, 'active'));

// Multiple conditions
const criticalTasks = await db
  .select()
  .from(tasks)
  .where(and(
    eq(tasks.status, 'running'),
    eq(tasks.priority, 'critical'),
    gt(tasks.createdAt, new Date('2026-01-01'))
  ))
  .orderBy(desc(tasks.createdAt))
  .limit(50);

CHECK: Agent is writing a SELECT query. IF: The query needs joins, aggregations, or subqueries. THEN: Use the query builder (db.select().from()). IF: The query is a simple read with nested relations. THEN: Consider the relational API (db.query.table.findMany()).

Partial Selects

// Select specific columns only
const agentNames = await db
  .select({
    id: agents.id,
    name: agents.name,
    role: agents.role,
  })
  .from(agents);

CHECK: Agent is selecting from a table with JSONB or many columns. IF: Only a few columns are needed. THEN: Use partial select. Reduces data transfer and memory.

ANTI_PATTERN: db.select().from(largeTable) when only 2 columns are needed. FIX: Always use partial select for tables with 10+ columns or JSONB data.

---

Joins

Inner Join

const tasksWithAgents = await db
  .select({
    taskId: tasks.id,
    taskName: tasks.task,
    agentName: agents.name,
    agentRole: agents.role,
  })
  .from(tasks)
  .innerJoin(agents, eq(tasks.agentId, agents.id))
  .where(eq(tasks.status, 'completed'));

Left Join

const agentsWithTasks = await db
  .select({
    agentId: agents.id,
    agentName: agents.name,
    taskCount: sql<number>`count(${tasks.id})::int`,
  })
  .from(agents)
  .leftJoin(tasks, eq(agents.id, tasks.agentId))
  .groupBy(agents.id, agents.name);

CHECK: Agent is joining tables. IF: Every row in the left table MUST have a match. THEN: Use innerJoin. IF: Rows without matches should still appear (with nulls). THEN: Use leftJoin.

ANTI_PATTERN: Using multiple sequential queries instead of a join. FIX: If you query table A, then loop over results to query table B — that is N+1. Use a join.

---

Subqueries

import { sql } from 'drizzle-orm';

// Subquery as a derived table
const activeTaskCounts = db
  .select({
    agentId: tasks.agentId,
    count: sql<number>`count(*)::int`.as('count'),
  })
  .from(tasks)
  .where(eq(tasks.status, 'running'))
  .groupBy(tasks.agentId)
  .as('active_counts');

const agentsWithLoad = await db
  .select({
    id: agents.id,
    name: agents.name,
    activeCount: activeTaskCounts.count,
  })
  .from(agents)
  .leftJoin(activeTaskCounts, eq(agents.id, activeTaskCounts.agentId));

EXISTS subquery

import { exists } from 'drizzle-orm';

const agentsWithTasks = await db
  .select()
  .from(agents)
  .where(
    exists(
      db.select({ x: sql`1` })
        .from(tasks)
        .where(eq(tasks.agentId, agents.id))
    )
  );

---

Aggregations

import { sql, count, sum, avg, min, max } from 'drizzle-orm';

// Built-in aggregates
const taskStats = await db
  .select({
    status: tasks.status,
    total: count(),
  })
  .from(tasks)
  .groupBy(tasks.status);

// Custom SQL aggregates
const dailyStats = await db
  .select({
    day: sql<string>`date_trunc('day', ${tasks.createdAt})`.as('day'),
    total: count(),
    completed: sql<number>`count(*) filter (where ${tasks.status} = 'completed')::int`,
  })
  .from(tasks)
  .groupBy(sql`date_trunc('day', ${tasks.createdAt})`)
  .orderBy(sql`day desc`)
  .limit(30);

---

Relational Query API

The relational API requires relations to be defined in the schema. READ_ALSO: wiki/docs/stack/drizzle/schema-design.md (Relations section)

// Simple find
const agent = await db.query.agents.findFirst({
  where: eq(agents.id, 'urszula'),
});

// With nested relations
const agentWithLearnings = await db.query.agents.findFirst({
  where: eq(agents.id, 'urszula'),
  with: {
    learnings: {
      orderBy: (learnings, { desc }) => [desc(learnings.learnedAt)],
      limit: 10,
    },
  },
});

// Find many with filtering and relations
const teamAgents = await db.query.agents.findMany({
  where: eq(agents.team, 'alfa'),
  with: {
    tasks: {
      where: eq(tasks.status, 'running'),
    },
  },
  orderBy: (agents, { asc }) => [asc(agents.name)],
});

CHECK: Agent is using db.query.table.findMany() with with:. IF: Relations are not defined for the table. THEN: The with will silently return empty arrays. Define relations first.

ANTI_PATTERN: Using relational API for complex joins or aggregations. FIX: The relational API issues multiple SQL queries under the hood. For performance-critical paths, use the query builder with explicit joins.

---

Transactions

const result = await db.transaction(async (tx) => {
  const [client] = await tx
    .insert(clients)
    .values({
      name: 'Acme Corp',
      status: 'active',
    })
    .returning();

  await tx
    .insert(projects)
    .values({
      clientId: client.id,
      name: 'Website Rebuild',
      status: 'draft',
    });

  return client;
});

CHECK: Agent is writing multiple related mutations. IF: The operations must succeed or fail together. THEN: Wrap them in db.transaction().

CHECK: Agent is writing a transaction. IF: The transaction includes a long-running operation (API call, file I/O). THEN: Move the external call OUTSIDE the transaction. Long transactions hold locks and cause contention. READ_ALSO: wiki/docs/stack/postgresql/pitfalls.md (idle transactions)

---

Insert

// Single insert with returning
const [newTask] = await db
  .insert(tasks)
  .values({
    agentId: 'urszula',
    task: 'Implement user auth',
    status: 'pending',
    priority: 'high',
  })
  .returning();

// Batch insert
await db.insert(learnings).values([
  { agentId: 'urszula', content: 'Learning 1' },
  { agentId: 'urszula', content: 'Learning 2' },
]);

---

Upsert (Insert or Update)

await db
  .insert(agents)
  .values({
    id: 'urszula',
    name: 'Urszula',
    role: 'Senior Full-Stack Developer',
    team: 'alfa',
    syncedAt: new Date(),
  })
  .onConflictDoUpdate({
    target: agents.id,
    set: {
      name: 'Urszula',
      role: 'Senior Full-Stack Developer',
      team: 'alfa',
      syncedAt: new Date(),
    },
  });

CHECK: Agent is implementing an upsert pattern. IF: Using select-then-insert (check if exists, then insert or update). THEN: STOP. Use onConflictDoUpdate instead. Select-then-insert has race conditions under concurrent access.

ANTI_PATTERN: Select-then-insert pattern for upserts. FIX: Always use onConflictDoUpdate. This is a GE hard rule. READ_ALSO: wiki/docs/stack/drizzle/pitfalls.md

---

Update

await db
  .update(agents)
  .set({
    status: 'active',
    lastActiveAt: new Date(),
  })
  .where(eq(agents.id, 'urszula'));

---

Delete

// Soft delete (preferred in GE)
await db
  .update(clients)
  .set({ status: 'inactive', updatedAt: new Date() })
  .where(eq(clients.id, clientId));

// Hard delete (rare — use only for cleanup tasks)
await db
  .delete(learnings)
  .where(and(
    eq(learnings.agentId, 'test-agent'),
    lt(learnings.learnedAt, cutoffDate),
  ));

CHECK: Agent is deleting records. IF: The table is client-facing or audit-relevant. THEN: Use soft delete (status change). NEVER hard delete client data. IF: The table is internal/ephemeral (metrics, temp data). THEN: Hard delete is acceptable.

---

Raw SQL Escape Hatch

import { sql } from 'drizzle-orm';

// Type-safe raw SQL
const result = await db.execute(
  sql`SELECT * FROM agents WHERE team = ${teamName} AND status = 'active'`
);

// In a select
const custom = await db
  .select({
    id: agents.id,
    daysSinceActive: sql<number>`
      extract(day from now() - ${agents.lastActiveAt})::int
    `,
  })
  .from(agents);

CHECK: Agent is using sql template. IF: Interpolating user input. THEN: Use sql tagged template (it parameterizes automatically). NEVER use string concatenation for SQL.

ANTI_PATTERN: sql.raw(\WHERE name = '${userInput}'`). FIX:sql`WHERE name = ${userInput}`` — Drizzle parameterizes tagged templates.

---

Performance Patterns

Batch Operations

// Batch insert (Drizzle handles chunking)
const values = items.map(item => ({
  agentId: item.agent,
  content: item.content,
}));
await db.insert(learnings).values(values);

Cursor-Based Pagination (GE standard)

const pageSize = 50;
const results = await db
  .select()
  .from(tasks)
  .where(and(
    eq(tasks.status, 'completed'),
    cursor ? gt(tasks.createdAt, cursor) : undefined,
  ))
  .orderBy(tasks.createdAt)
  .limit(pageSize + 1); // Fetch one extra to know if there's a next page

const hasMore = results.length > pageSize;
const page = results.slice(0, pageSize);

ANTI_PATTERN: Using OFFSET-based pagination. FIX: OFFSET re-scans all skipped rows. Use cursor-based pagination. READ_ALSO: wiki/docs/stack/postgresql/performance.md

---

GE-Specific Conventions

1. Always returning() on inserts and updates when you need the result.
2. Cursor pagination over OFFSET for any list endpoint.
3. Transactions for multi-table mutations. No exceptions.
4. onConflictDoUpdate for all upserts. No select-then-insert.
5. Partial selects for tables with 10+ columns.
6. Soft delete for client-facing data.

---

Cross-References

READ_ALSO: wiki/docs/stack/drizzle/index.md READ_ALSO: wiki/docs/stack/drizzle/schema-design.md READ_ALSO: wiki/docs/stack/drizzle/pitfalls.md READ_ALSO: wiki/docs/stack/postgresql/indexing.md READ_ALSO: wiki/docs/stack/postgresql/performance.md