04 - Queries

📋 Jump to Takeaways

Drizzle gives you a type-safe query builder that maps directly to SQL. Every query method chains naturally and returns typed results. No magic, no hidden behavior.

The Select Pattern

All reads start with db.select().from(). This returns every row and column from a table.

import { db } from './db';
import { users } from './schema';

const allUsers = await db.select().from(users);
// [{ id: 1, name: 'Alice', email: 'alice@example.com', age: 28 }, ...]

To grab specific columns, pass an object to select().

const names = await db.select({ name: users.name, email: users.email }).from(users);
// [{ name: 'Alice', email: 'alice@example.com' }, { name: 'Bob', email: 'bob@example.com' }]

Filtering with Where

Chain .where() to filter rows. Import comparison operators from drizzle-orm.

import { eq } from 'drizzle-orm';

const alice = await db.select().from(users).where(eq(users.email, 'alice@example.com'));
// [{ id: 1, name: 'Alice', email: 'alice@example.com', age: 28 }]

Insert

Use db.insert().values() to add rows. Pass a single object or an array.

Single row

await db.insert(users).values({
  name: 'Charlie',
  email: 'charlie@example.com',
  age: 34
});

Multiple rows

await db.insert(users).values([
  { name: 'Dana', email: 'dana@example.com', age: 25 },
  { name: 'Eli', email: 'eli@example.com', age: 31 }
]);

Returning inserted data

Add .returning() to get the created rows back. This avoids a second query.

const [newUser] = await db.insert(users).values({
  name: 'Fay',
  email: 'fay@example.com',
  age: 22
}).returning();
// { id: 6, name: 'Fay', email: 'fay@example.com', age: 22 }

Update

Use db.update().set().where(). Always include .where() or you update every row.

await db.update(users)
  .set({ age: 29 })
  .where(eq(users.name, 'Alice'));

Get the updated row back with .returning().

const [updated] = await db.update(users)
  .set({ email: 'alice.new@example.com' })
  .where(eq(users.id, 1))
  .returning();
// { id: 1, name: 'Alice', email: 'alice.new@example.com', age: 29 }

Delete

Use db.delete().where(). Same rule: always include .where().

await db.delete(users).where(eq(users.id, 3));

❌ Forgetting .where() deletes everything:

// This deletes ALL users
await db.delete(users);

✅ Always filter:

await db.delete(users).where(eq(users.email, 'charlie@example.com'));

Putting It Together

A typical CRUD flow looks like this:

// Create
const [user] = await db.insert(users)
  .values({ name: 'Grace', email: 'grace@example.com', age: 27 })
  .returning();

// Read
const found = await db.select().from(users).where(eq(users.id, user.id));

// Update
await db.update(users).set({ age: 28 }).where(eq(users.id, user.id));

// Delete
await db.delete(users).where(eq(users.id, user.id));

Debugging Queries

To see every SQL query Drizzle runs, enable the built-in logger:

export const db = drizzle(client, { logger: true });
// Logs: Query: select * from "users" where "active" = $1 -- params: [true]

To inspect a single query without executing it, use .toSQL():

const query = db.select().from(users).where(eq(users.active, true));
console.log(query.toSQL());
// { sql: 'select * from "users" where "active" = $1', params: [true] }

// Execute it when ready
const results = await query;

Key Takeaways

db.select().from(table) reads rows; pass an object to select() for specific columns
db.insert(table).values() accepts a single object or an array for batch inserts
db.update(table).set().where() modifies rows; always include .where()
db.delete(table).where() removes rows; omitting .where() deletes everything
.returning() gives back affected rows without a second query