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: '[email protected]', 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: '[email protected]' }, { name: 'Bob', email: '[email protected]' }]

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, '[email protected]'));
// [{ id: 1, name: 'Alice', email: '[email protected]', 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: '[email protected]',
  age: 34
});

Multiple rows

await db.insert(users).values([
  { name: 'Dana', email: '[email protected]', age: 25 },
  { name: 'Eli', email: '[email protected]', 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: '[email protected]',
  age: 22
}).returning();
// { id: 6, name: 'Fay', email: '[email protected]', 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: '[email protected]' })
  .where(eq(users.id, 1))
  .returning();
// { id: 1, name: 'Alice', email: '[email protected]', 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, '[email protected]'));

Putting It Together

A typical CRUD flow looks like this:

// Create
const [user] = await db.insert(users)
  .values({ name: 'Grace', email: '[email protected]', 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