Drizzle Query Builder

Drizzle Query Builder

Compose type-safe SQL with Drizzle's fluent query builder for select, insert, update, and delete

When to Use

• Writing SQL-like queries with full type safety in TypeScript
• Performing joins, subqueries, and aggregations
• Building dynamic queries with conditional clauses
• When you need more control than the relational query API provides

Instructions

1. Select — query rows with the SQL-like builder:

import { eq, desc } from 'drizzle-orm';

const allUsers = await db.select().from(users);

const activeUsers = await db
  .select()
  .from(users)
  .where(eq(users.isActive, true))
  .orderBy(desc(users.createdAt))
  .limit(20);

2. Select specific columns:

const names = await db
  .select({
    id: users.id,
    name: users.name,
  })
  .from(users);
// Type: { id: string; name: string | null }[]

3. Joins — inner, left, right, full:

const result = await db
  .select({
    userName: users.name,
    postTitle: posts.title,
  })
  .from(users)
  .innerJoin(posts, eq(users.id, posts.authorId))
  .where(eq(posts.published, true));

4. Left join preserves all left rows (right columns may be null):

const result = await db
  .select({
    user: users,
    latestPost: posts,
  })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId));
// Type: { user: User; latestPost: Post | null }[]

5. Insert — single or multiple rows:

await db.insert(users).values({
  email: 'alice@example.com',
  name: 'Alice',
});

// Bulk insert
await db.insert(users).values([
  { email: 'bob@example.com', name: 'Bob' },
  { email: 'carol@example.com', name: 'Carol' },
]);

// Insert with returning
const [newUser] = await db
  .insert(users)
  .values({ email: 'dave@example.com', name: 'Dave' })
  .returning();

6. Upsert with onConflictDoUpdate:

await db
  .insert(users)
  .values({ email: 'alice@example.com', name: 'Alice Updated' })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: 'Alice Updated' },
  });

7. Update:

await db.update(users).set({ name: 'New Name', updatedAt: new Date() }).where(eq(users.id, userId));

// Update with returning
const [updated] = await db
  .update(users)
  .set({ isActive: false })
  .where(eq(users.id, userId))
  .returning();

8. Delete:

await db.delete(posts).where(eq(posts.authorId, userId));

const [deleted] = await db.delete(users).where(eq(users.id, userId)).returning();

9. Subqueries:

import { sql } from 'drizzle-orm';

const subquery = db
  .select({
    authorId: posts.authorId,
    postCount: sql<number>`count(*)`.as('post_count'),
  })
  .from(posts)
  .groupBy(posts.authorId)
  .as('post_counts');

const result = await db
  .select({
    name: users.name,
    postCount: subquery.postCount,
  })
  .from(users)
  .leftJoin(subquery, eq(users.id, subquery.authorId));

10. Aggregations:

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

const stats = await db
  .select({
    totalPosts: count(),
    avgViews: avg(posts.viewCount),
  })
  .from(posts)
  .where(eq(posts.published, true));

Details

The Drizzle query builder mirrors SQL syntax closely. Every method returns a new query object (immutable), and the final result is obtained by awaiting the query.

Two query APIs: Drizzle offers the SQL-like builder (db.select().from()) and the relational query API (db.query.table.findMany()). Use the SQL-like builder for joins, aggregations, and complex queries. Use the relational API for simple CRUD with nested includes.

Type inference: The query builder infers result types from the selected columns. Selecting { name: users.name } returns { name: string | null }[]. Left joins correctly type nullable columns.

.returning() support: Available on PostgreSQL and SQLite. MySQL does not support RETURNING — use insertId from the result instead.

Prepared statements:

const prepared = db
  .select()
  .from(users)
  .where(eq(users.id, sql.placeholder('id')))
  .prepare();

const user = await prepared.execute({ id: userId });

Prepared statements are compiled once and reused, reducing parsing overhead for repeated queries.

Trade-offs:

• SQL-like syntax is explicit but more verbose than the relational API for simple includes
• Joins require manual column selection — no automatic "include everything" like the relational API
• Subqueries must be aliased with .as() — forgetting this causes TypeScript errors

Source

https://orm.drizzle.team/docs/select

Process

1. Read the instructions and examples in this document.
2. Apply the patterns to your implementation, adapting to your specific context.
3. Verify your implementation against the details and edge cases listed above.

Harness Integration

• Type: knowledge — this skill is a reference document, not a procedural workflow.
• No tools or state — consumed as context by other skills and agents.

Success Criteria

• The patterns described in this document are applied correctly in the implementation.
• Edge cases and anti-patterns listed in this document are avoided.