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)

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')
  )
)

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"`)

(PgColumn | SQL)[]

required

Columns or SQL expressions to order by

limit()

Limits the number of returned rows.

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

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)

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)

(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))

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))

(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 });

'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

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:

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: 'john@example.com',
});

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

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: 'john@example.com', name: 'John' })
  .onConflictDoNothing();

// Ignore conflicts on specific column(s)
await db.insert(users)
  .values({ email: 'john@example.com', name: 'John' })
  .onConflictDoNothing({ target: users.email });

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: 'john@example.com', 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: 'john@example.com', name: 'John' })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: 'John Updated' },
    where: sql`${users.active} = true`,
  });

PgColumn | PgColumn[]

required

Column(s) that trigger the conflict

object

required

Values to update on conflict

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 });

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: 'john@example.com' })
  .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));

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, 'user@example.com'));

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'`)
    )
  );

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 });

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;

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 = ${'john@example.com'}`
);

// 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.

​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

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