/postgres-drizzle

Drizzle Query Patterns

Comprehensive reference for querying PostgreSQL with Drizzle ORM using the SQL-like API (db.select() etc. — identical in 0.x and v1.0). For db.query.* relational queries see RELATIONS.md.

Contents

• Query Operators
• Select Queries
• Ordering & Pagination
• Joins
• Aggregations
• Subqueries
• Insert Operations
• Update Operations
• Delete Operations
• Raw SQL
• Prepared Statements
• Transactions

Query Operators

Imports

import {
  eq,           // =
  ne,           // <>
  gt,           // >
  gte,          // >=
  lt,           // <
  lte,          // <=
  like,         // LIKE (case-sensitive)
  ilike,        // ILIKE (case-insensitive)
  notLike,
  notIlike,
  inArray,      // IN
  notInArray,   // NOT IN
  isNull,
  isNotNull,
  between,
  notBetween,
  and,
  or,
  not,
  exists,
  notExists,
  arrayContains,
  arrayContained,
  arrayOverlaps,
  sql,
} from 'drizzle-orm';

Select Queries

Basic Select

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

// Specific columns
const emails = await db.select({
  id: users.id,
  email: users.email
}).from(users);

// With alias
const result = await db.select({
  identifier: users.id,
  mail: users.email,
}).from(users);

Where Clause

// Single condition
const user = await db
  .select()
  .from(users)
  .where(eq(users.id, userId));

// Multiple conditions (AND)
const activeAdmins = await db
  .select()
  .from(users)
  .where(and(
    eq(users.status, 'active'),
    eq(users.role, 'admin'),
  ));

// OR conditions
const flaggedUsers = await db
  .select()
  .from(users)
  .where(or(
    eq(users.status, 'suspended'),
    gt(users.warningCount, 3),
  ));

// Complex nested conditions
const result = await db
  .select()
  .from(users)
  .where(and(
    eq(users.status, 'active'),
    or(
      eq(users.role, 'admin'),
      gt(users.score, 100),
    ),
  ));

Comparison Operators

// Equality
.where(eq(users.status, 'active'))

// Not equal
.where(ne(users.status, 'deleted'))

// Greater than / less than
.where(gt(users.age, 18))
.where(gte(users.age, 18))
.where(lt(users.age, 65))
.where(lte(users.age, 65))

// Between
.where(between(users.age, 18, 65))
.where(notBetween(products.price, 0, 10))

// Null checks
.where(isNull(users.deletedAt))
.where(isNotNull(users.verifiedAt))

// IN / NOT IN
.where(inArray(users.status, ['active', 'pending']))
.where(notInArray(users.role, ['banned', 'suspended']))

Pattern Matching

// Case-sensitive LIKE
.where(like(users.name, 'John%'))      // Starts with
.where(like(users.name, '%Smith'))     // Ends with
.where(like(users.name, '%John%'))     // Contains

// Case-insensitive ILIKE
.where(ilike(users.email, '%@gmail.com'))

// Negated
.where(notLike(users.name, 'Test%'))
.where(notIlike(users.email, '%spam%'))

Conditional Filters

Build dynamic queries by passing undefined to skip conditions:

interface Filters {
  search?: string;
  categoryId?: string;
  minPrice?: number;
  maxPrice?: number;
}

async function getPosts(filters: Filters) {
  return db
    .select()
    .from(posts)
    .where(and(
      eq(posts.published, true),
      filters.search
        ? ilike(posts.title, `%${filters.search}%`)
        : undefined,
      filters.categoryId
        ? eq(posts.categoryId, filters.categoryId)
        : undefined,
      filters.minPrice !== undefined   // not truthiness: 0 is a valid price
        ? gte(posts.price, filters.minPrice)
        : undefined,
      filters.maxPrice !== undefined
        ? lte(posts.price, filters.maxPrice)
        : undefined,
    ));
}

and()/or() ignore undefined arguments, which is what makes this pattern work. An empty and() is undefined, so .where(undefined) returns all rows.

Ordering & Pagination

Order By

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

// Single column
const newest = await db
  .select()
  .from(posts)
  .orderBy(desc(posts.createdAt));

// Multiple columns
const sorted = await db
  .select()
  .from(users)
  .orderBy(asc(users.lastName), asc(users.firstName));

// Nulls handling
.orderBy(sql`${users.name} NULLS LAST`)

Limit & Offset

// Basic pagination
const page1 = await db
  .select()
  .from(posts)
  .orderBy(desc(posts.createdAt))
  .limit(20)
  .offset(0);

// Page helper
async function getPage(page: number, pageSize: number = 20) {
  return db
    .select()
    .from(posts)
    .orderBy(desc(posts.createdAt))
    .limit(pageSize)
    .offset((page - 1) * pageSize);
}

Cursor-Based Pagination (Better Performance)

async function getPostsAfter(cursor?: string, limit = 20) {
  return db
    .select()
    .from(posts)
    .where(cursor ? lt(posts.id, cursor) : undefined)
    .orderBy(desc(posts.id))
    .limit(limit);
}

Joins

Left Join

const usersWithPosts = await db
  .select()
  .from(users)
  .leftJoin(posts, eq(posts.authorId, users.id));

// Result type: { users: User, posts: Post | null }[]

Inner Join

const usersWithPosts = await db
  .select()
  .from(users)
  .innerJoin(posts, eq(posts.authorId, users.id));

// Only users who have posts

Right Join

const postsWithUsers = await db
  .select()
  .from(posts)
  .rightJoin(users, eq(posts.authorId, users.id));

Full Join

const all = await db
  .select()
  .from(users)
  .fullJoin(posts, eq(posts.authorId, users.id));

Multiple Joins

const fullData = await db
  .select({
    order: orders,
    user: users,
    product: products,
  })
  .from(orders)
  .leftJoin(users, eq(orders.userId, users.id))
  .leftJoin(products, eq(orders.productId, products.id));

Join with Selected Columns

const result = await db
  .select({
    userName: users.name,
    userEmail: users.email,
    postTitle: posts.title,
    postDate: posts.createdAt,
  })
  .from(users)
  .innerJoin(posts, eq(posts.authorId, users.id));

Aggregations

Imports

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

Basic Aggregates

// Count all rows — shorthand
const total = await db.$count(users);                          // number
const active = await db.$count(users, eq(users.status, 'active'));

// Count all rows — explicit
const [{ total }] = await db
  .select({ total: count() })
  .from(users);

// Count with condition
const [{ activeCount }] = await db
  .select({ activeCount: count() })
  .from(users)
  .where(eq(users.status, 'active'));

// Count distinct
const [{ uniqueAuthors }] = await db
  .select({ uniqueAuthors: countDistinct(posts.authorId) })
  .from(posts);

// Sum
const [{ totalRevenue }] = await db
  .select({ totalRevenue: sum(orders.amount) })
  .from(orders);

// Average
const [{ avgPrice }] = await db
  .select({ avgPrice: avg(products.price) })
  .from(products);

// Min / Max
const [{ cheapest, expensive }] = await db
  .select({
    cheapest: min(products.price),
    expensive: max(products.price),
  })
  .from(products);

Group By

const postsByAuthor = await db
  .select({
    authorId: posts.authorId,
    postCount: count(),
    totalViews: sum(posts.views),
  })
  .from(posts)
  .groupBy(posts.authorId);

Having

const prolificAuthors = await db
  .select({
    authorId: posts.authorId,
    postCount: count(),
  })
  .from(posts)
  .groupBy(posts.authorId)
  .having(gt(count(), 10));

Group By with Join

const authorStats = await db
  .select({
    authorName: users.name,
    postCount: count(posts.id),
    totalViews: sum(posts.views),
  })
  .from(users)
  .leftJoin(posts, eq(posts.authorId, users.id))
  .groupBy(users.id, users.name);

Subqueries

Subquery in FROM

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

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

Subquery in WHERE (EXISTS)

// Users who have at least one post
const usersWithPosts = await db
  .select()
  .from(users)
  .where(
    exists(
      db.select().from(posts).where(eq(posts.authorId, users.id))
    )
  );

// Users who have NO posts
const usersWithoutPosts = await db
  .select()
  .from(users)
  .where(
    notExists(
      db.select().from(posts).where(eq(posts.authorId, users.id))
    )
  );

Correlated Scalar Subquery

Self-referencing correlations need a table alias — comparing posts.authorId to itself is always true:

import { alias } from 'drizzle-orm/pg-core';

const p = alias(posts, 'p');

const postsWithAuthorCount = await db
  .select({
    post: posts,
    authorPostCount: sql<number>`(
      SELECT count(*) FROM ${p} WHERE ${p.authorId} = ${posts.authorId}
    )`.as('author_post_count'),
  })
  .from(posts);

Insert Operations

Single Insert

const [newUser] = await db
  .insert(users)
  .values({
    email: 'user@example.com',
    name: 'John Doe',
  })
  .returning();

Multiple Insert

const newUsers = await db
  .insert(users)
  .values([
    { email: 'user1@example.com', name: 'User 1' },
    { email: 'user2@example.com', name: 'User 2' },
    { email: 'user3@example.com', name: 'User 3' },
  ])
  .returning();

Upsert (On Conflict)

// Update on conflict
await db
  .insert(users)
  .values({ email: 'user@example.com', name: 'John' })
  .onConflictDoUpdate({
    target: users.email,
    set: {
      name: 'John Updated',
      updatedAt: new Date(),
    },
  });

// Ignore on conflict
await db
  .insert(users)
  .values({ email: 'user@example.com', name: 'John' })
  .onConflictDoNothing();

// Composite key conflict
await db
  .insert(usersToGroups)
  .values({ userId, groupId })
  .onConflictDoNothing({
    target: [usersToGroups.userId, usersToGroups.groupId],
  });

Insert from Select

await db
  .insert(archivedPosts)
  .select()
  .from(posts)
  .where(lt(posts.createdAt, oneYearAgo));

Update Operations

Basic Update

await db
  .update(users)
  .set({ status: 'active' })
  .where(eq(users.id, userId));

Update with Returning

const [updated] = await db
  .update(users)
  .set({
    status: 'active',
    updatedAt: new Date(),
  })
  .where(eq(users.id, userId))
  .returning();

Increment/Decrement

// Increment
await db
  .update(posts)
  .set({ views: sql`${posts.views} + 1` })
  .where(eq(posts.id, postId));

// Decrement with floor
await db
  .update(products)
  .set({ stock: sql`GREATEST(${products.stock} - 1, 0)` })
  .where(eq(products.id, productId));

Conditional Update

await db
  .update(users)
  .set({
    status: sql`CASE WHEN ${users.score} > 100 THEN 'gold' ELSE 'silver' END`,
  })
  .where(eq(users.role, 'member'));

Delete Operations

Basic Delete

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

Delete with Returning

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

Soft Delete

await db
  .update(users)
  .set({ deletedAt: new Date() })
  .where(eq(users.id, userId));

Delete with Subquery

// Delete inactive users who have no posts
await db
  .delete(users)
  .where(and(
    eq(users.status, 'inactive'),
    notExists(
      db.select().from(posts).where(eq(posts.authorId, users.id))
    ),
  ));

Raw SQL

SQL Template

import { sql } from 'drizzle-orm';

// In select
const result = await db
  .select({
    id: users.id,
    fullName: sql<string>`${users.firstName} || ' ' || ${users.lastName}`,
  })
  .from(users);

// In where
.where(sql`${users.email} ~* ${pattern}`)  // PostgreSQL regex

// Typed raw query
const rows = await db.execute<{ id: string; name: string }>(
  sql`SELECT id, name FROM users WHERE status = 'active'`
);

Note on db.execute() result shape: with postgres.js the result is the row array itself; with node-postgres it's a pg result object — read result.rows.

SQL Operators

// JSON operators
.where(sql`${events.data}->>'type' = 'purchase'`)
.where(sql`${events.data} @> '{"status": "active"}'::jsonb`)

// Array operators
.where(sql`${posts.tags} @> ARRAY['typescript']`)

// Full-text search
.where(sql`to_tsvector('english', ${posts.content}) @@ plainto_tsquery('english', ${searchTerm})`)

Prepared Statements

Improve performance by preparing queries once. Caveat: named server-side prepared statements break behind transaction-mode poolers (PgBouncer < 1.21, Supavisor) — see PERFORMANCE.md.

// Prepare
const getUserById = db
  .select()
  .from(users)
  .where(eq(users.id, sql.placeholder('id')))
  .prepare('get_user_by_id');

// Execute multiple times
const user1 = await getUserById.execute({ id: 'uuid-1' });
const user2 = await getUserById.execute({ id: 'uuid-2' });

// Prepared insert
const createUser = db
  .insert(users)
  .values({
    email: sql.placeholder('email'),
    name: sql.placeholder('name'),
  })
  .returning()
  .prepare('create_user');

const newUser = await createUser.execute({
  email: 'user@example.com',
  name: 'John',
});

Transactions

Basic Transaction

const result = await db.transaction(async (tx) => {
  const [user] = await tx.insert(users).values({ email, name }).returning();
  await tx.insert(profiles).values({ userId: user.id, bio: '' });
  return user;
});

Use tx for every statement inside the callback. A query on db runs on a different connection outside the transaction and will not roll back.

Nested Transactions (Savepoints)

await db.transaction(async (tx) => {
  await tx.insert(users).values({ ... });

  try {
    await tx.transaction(async (tx2) => {
      // Creates savepoint
      await tx2.insert(riskyTable).values({ ... });
      // If this throws, only tx2 is rolled back
    });
  } catch (e) {
    // Handle savepoint rollback
  }

  // Outer transaction continues
  await tx.insert(logs).values({ ... });
});

Rollback

await db.transaction(async (tx) => {
  const [user] = await tx.insert(users).values({ ... }).returning();

  const balance = await checkBalance(user.id);
  if (balance < 0) {
    tx.rollback();  // Throws to abort entire transaction
  }

  await tx.insert(orders).values({ userId: user.id, ... });
});

Transaction Isolation

await db.transaction(async (tx) => {
  // ...
}, {
  isolationLevel: 'serializable',  // read committed, repeatable read, serializable
  accessMode: 'read write',        // read only, read write
});