PostgreSQL Query Builders

Select Query Builder

PgSelectBuilder

Builds SELECT queries with PostgreSQL-specific features.

const result = await db.select()
  .from(users)
  .where(eq(users.active, true))
  .orderBy(users.name)
  .limit(10);

from()

Specifies which table or subquery to select from.

// From a table
db.select().from(users)

// From a subquery
const sq = db.select().from(users).as('sq');
db.select().from(sq)

// From multiple tables (cross join)
db.select().from(users, posts)

source

PgTable | Subquery | PgView

required

Table, view, or subquery to select from

where()

Adds WHERE clause conditions.

import { eq, and, or, gt, lt } from 'drizzle-orm';

// Single condition
db.select().from(users).where(eq(users.id, 1))

// Multiple conditions with AND
db.select().from(users).where(
  and(
    eq(users.active, true),
    gt(users.age, 18)
  )
)

// OR conditions
db.select().from(users).where(
  or(
    eq(users.role, 'admin'),
    eq(users.role, 'moderator')
  )
)

condition

SQL | undefined

required

SQL condition expression

orderBy()

Adds ORDER BY clause.

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

// Single column ascending
db.select().from(users).orderBy(users.name)

// Descending
db.select().from(users).orderBy(desc(users.createdAt))

// Multiple columns
db.select().from(users).orderBy(
  asc(users.lastName),
  desc(users.firstName)
)

// Using SQL
db.select().from(users).orderBy(sql`${users.name} COLLATE "C"`)

columns

(PgColumn | SQL)[]

required

Columns or SQL expressions to order by

limit()

Limits the number of returned rows.

db.select().from(users).limit(10)

limit

number

required

Maximum number of rows to return

offset()

Skips a number of rows before returning results.

// Pagination
const page = 2;
const pageSize = 10;

db.select()
  .from(users)
  .limit(pageSize)
  .offset(page * pageSize)

offset

number

required

Number of rows to skip

groupBy()

Groups results by columns.

import { count } from 'drizzle-orm';

db.select({
  role: users.role,
  count: count(),
})
  .from(users)
  .groupBy(users.role)

columns

(PgColumn | SQL)[]

required

Columns to group by

having()

Filters grouped results.

import { count, gt } from 'drizzle-orm';

db.select({
  role: users.role,
  count: count(),
})
  .from(users)
  .groupBy(users.role)
  .having(({ count }) => gt(count, 5))

condition

SQL | (selected: SelectedFields) => SQL

required

Condition to filter groups

PostgreSQL-Specific Select Features

distinctOn()

PostgreSQL DISTINCT ON clause for selecting distinct rows based on specific columns.

// Get latest post for each user
db.selectDistinctOn([posts.userId], {
  userId: posts.userId,
  title: posts.title,
  createdAt: posts.createdAt,
})
  .from(posts)
  .orderBy(posts.userId, desc(posts.createdAt))

columns

(PgColumn | SQL)[]

required

Columns to determine uniqueness

Note: When using DISTINCT ON, the ORDER BY must start with the same columns.

for()

Adds locking clauses for concurrent access control.

// FOR UPDATE - lock rows for update
const user = await db.select()
  .from(users)
  .where(eq(users.id, 1))
  .for('update');

// FOR SHARE - lock rows but allow other shared locks
await db.select()
  .from(users)
  .where(eq(users.id, 1))
  .for('share');

// With specific tables and options
await db.select()
  .from(users)
  .leftJoin(posts, eq(users.id, posts.userId))
  .for('update', { of: users, noWait: true });

type

'update' | 'no key update' | 'share' | 'key share'

required

Lock type:

'update': Full row lock
'no key update': Lock without blocking foreign key checks
'share': Shared lock (prevents updates)
'key share': Shared lock on key only

config

object

Lock options:

of: Table to lock (for joins)
noWait: Fail immediately if row is locked
skipLocked: Skip locked rows

Join Operations

innerJoin()

db.select()
  .from(users)
  .innerJoin(posts, eq(users.id, posts.userId))

leftJoin()

db.select({
  user: users,
  post: posts,
})
  .from(users)
  .leftJoin(posts, eq(users.id, posts.userId))

rightJoin()

db.select()
  .from(users)
  .rightJoin(posts, eq(users.id, posts.userId))

fullJoin()

db.select()
  .from(users)
  .fullJoin(posts, eq(users.id, posts.userId))

All join methods accept:

table

PgTable | Subquery

required

Table or subquery to join

SQL

required

Join condition

Insert Query Builder

PgInsertBuilder

Builds INSERT queries with PostgreSQL-specific features.

values()

Specifies values to insert.

// Insert single row
await db.insert(users).values({
  name: 'John',
  email: '[email protected]',
});

// Insert multiple rows
await db.insert(users).values([
  { name: 'John', email: '[email protected]' },
  { name: 'Jane', email: '[email protected]' },
]);

values

InferInsertModel | InferInsertModel[]

required

Single object or array of objects to insert

onConflictDoNothing()

PostgreSQL ON CONFLICT DO NOTHING clause.

// Ignore conflicts on any constraint
await db.insert(users)
  .values({ email: '[email protected]', name: 'John' })
  .onConflictDoNothing();

// Ignore conflicts on specific column(s)
await db.insert(users)
  .values({ email: '[email protected]', name: 'John' })
  .onConflictDoNothing({ target: users.email });

config.target

PgColumn | PgColumn[]

Specific column(s) that trigger the conflict

onConflictDoUpdate()

PostgreSQL ON CONFLICT DO UPDATE clause for upserts.

import { sql } from 'drizzle-orm';

// Update on conflict
await db.insert(users)
  .values({ email: '[email protected]', name: 'John', score: 10 })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: 'John Updated', score: sql`${users.score} + 1` },
  });

// With WHERE clause
await db.insert(users)
  .values({ email: '[email protected]', name: 'John' })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: 'John Updated' },
    where: sql`${users.active} = true`,
  });

config.target

PgColumn | PgColumn[]

required

Column(s) that trigger the conflict

config.set

object

required

Values to update on conflict

config.where

SQL

Condition for when to perform update

returning()

Returns inserted rows.

// Return all columns
const inserted = await db.insert(users)
  .values({ name: 'John' })
  .returning();

// Return specific columns
const result = await db.insert(users)
  .values({ name: 'John' })
  .returning({ id: users.id, name: users.name });

fields

SelectedFields

Columns to return (returns all if omitted)

overridingSystemValue()

Allows inserting values into generated identity columns.

await db.insert(users)
  .overridingSystemValue()
  .values({ id: 1, name: 'John' });

Update Query Builder

PgUpdateBuilder

Builds UPDATE queries.

set()

Specifies columns and values to update.

import { sql } from 'drizzle-orm';

// Update with values
await db.update(users)
  .set({ name: 'John Updated', email: '[email protected]' })
  .where(eq(users.id, 1));

// Update with SQL expressions
await db.update(products)
  .set({ 
    stock: sql`${products.stock} - 1`,
    updatedAt: sql`now()`,
  })
  .where(eq(products.id, 1));

values

Partial<InferInsertModel>

required

Object with column names and new values

where()

Filters which rows to update.

await db.update(users)
  .set({ active: false })
  .where(eq(users.email, '[email protected]'));

returning()

Returns updated rows.

const updated = await db.update(users)
  .set({ name: 'John Updated' })
  .where(eq(users.id, 1))
  .returning();

Delete Query Builder

PgDeleteBase

Builds DELETE queries.

where()

Specifies which rows to delete.

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

// Multiple conditions
await db.delete(users)
  .where(
    and(
      eq(users.active, false),
      lt(users.lastLogin, sql`now() - interval '1 year'`)
    )
  );

condition

SQL

required

Condition for which rows to delete

returning()

Returns deleted rows.

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

Prepared Statements

All query builders support .prepare() for creating reusable prepared statements.

import { placeholder } from 'drizzle-orm';

// Create prepared statement
const prepared = db.select()
  .from(users)
  .where(eq(users.id, placeholder('id')))
  .prepare('get_user_by_id');

// Execute with parameters
const user1 = await prepared.execute({ id: 1 });
const user2 = await prepared.execute({ id: 2 });

name

string

Name for the prepared statement

Batch Operations

Execute multiple queries in a single round trip.

const results = await db.batch([
  db.select().from(users),
  db.select().from(posts),
  db.insert(logs).values({ message: 'Batch executed' }),
]);

// results is an array with results from each query
const [usersResult, postsResult, insertResult] = results;

queries

Query[]

required

Array of queries to execute

Refresh Materialized View

PgRefreshMaterializedView

Refreshes PostgreSQL materialized views.

// Basic refresh
await db.refreshMaterializedView(myMaterializedView);

// Concurrent refresh (requires unique index)
await db.refreshMaterializedView(myMaterializedView).concurrently();

concurrently()

Enables concurrent refresh (PostgreSQL REFRESH MATERIALIZED VIEW CONCURRENTLY).

await db.refreshMaterializedView(myView).concurrently();

Note: Requires a unique index on the materialized view.

Raw SQL

Execute raw SQL queries.

import { sql } from 'drizzle-orm';

// Select with raw SQL
const result = await db.execute(
  sql`SELECT * FROM users WHERE email = ${'[email protected]'}`
);

// Raw SQL in queries
const users = await db.select({
  id: users.id,
  upperName: sql<string>`upper(${users.name})`.as('upper_name'),
}).from(users);

Query Builder Utility

QueryBuilder

Low-level query builder used internally.

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

const qb = new QueryBuilder();

Generally not used directly - use database instance methods instead.

PostgreSQL

MySQL

SQLite

Core APIs

Drizzle Kit

​Select Query Builder

​PgSelectBuilder

​from()

​where()

​orderBy()

​limit()

​offset()

​groupBy()

​having()

​PostgreSQL-Specific Select Features

​distinctOn()

​for()

​Join Operations

​innerJoin()

​leftJoin()

​rightJoin()

​fullJoin()

​Insert Query Builder

​PgInsertBuilder

​values()

​onConflictDoNothing()

​onConflictDoUpdate()

​returning()

​overridingSystemValue()

​Update Query Builder

​PgUpdateBuilder

​set()

​where()

​returning()

​Delete Query Builder

​PgDeleteBase

​where()

​returning()

​Prepared Statements

​Batch Operations

​Refresh Materialized View

​PgRefreshMaterializedView

​concurrently()

​Raw SQL

​Query Builder Utility

​QueryBuilder

Build docs developers (and LLMs) love

Select Query Builder

PgSelectBuilder

from()

where()

orderBy()

limit()

offset()

groupBy()

having()

PostgreSQL-Specific Select Features

distinctOn()

for()

Join Operations

innerJoin()

leftJoin()

rightJoin()

fullJoin()

Insert Query Builder

PgInsertBuilder

values()

onConflictDoNothing()

onConflictDoUpdate()

returning()

overridingSystemValue()

Update Query Builder

PgUpdateBuilder

set()

where()

returning()

Delete Query Builder

PgDeleteBase

where()

returning()

Prepared Statements

Batch Operations

Refresh Materialized View

PgRefreshMaterializedView

concurrently()

Raw SQL

Query Builder Utility

QueryBuilder