PostgreSQL Database API

Complete API reference for the Drizzle PostgreSQL database client

PgDatabase

The main database client class for PostgreSQL. Created by calling driver-specific functions like drizzle() from drizzle-orm/node-postgres, drizzle-orm/postgres-js, etc.

import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

const db = drizzle(pool);

Query Methods

select()

Creates a SELECT query. Call with no arguments to select all columns, or pass a selection object to specify columns.

// Select all columns
const allCars = await db.select().from(cars);

// Select specific columns
const carsIdsAndBrands = await db.select({
  id: cars.id,
  brand: cars.brand
}).from(cars);

// Select with SQL expressions
const carsWithLowerBrands = await db.select({
  id: cars.id,
  lowerBrand: sql<string>`lower(${cars.brand})`,
}).from(cars);

fields · SelectedFields

Selection object mapping field names to column references or SQL expressions

return · PgSelectBuilder

Query builder for chaining additional methods like .from(), .where(), .orderBy(), etc.

selectDistinct()

Adds DISTINCT to the SELECT query, returning only unique values.

// Select all unique rows
await db.selectDistinct()
  .from(cars)
  .orderBy(cars.id, cars.brand, cars.color);

// Select unique brands
await db.selectDistinct({ brand: cars.brand })
  .from(cars)
  .orderBy(cars.brand);

fields · SelectedFields

Selection object for which columns to select

selectDistinctOn()

PostgreSQL-specific method that adds DISTINCT ON to specify how unique rows are determined.

// Select first row for each unique brand
await db.selectDistinctOn([cars.brand])
  .from(cars)
  .orderBy(cars.brand);

// Select first occurrence of each brand with its color
await db.selectDistinctOn([cars.brand], { 
  brand: cars.brand, 
  color: cars.color 
})
  .from(cars)
  .orderBy(cars.brand, cars.color);

on · (PgColumn | SQLWrapper)[] · required

Array of columns or SQL expressions that define uniqueness

fields · SelectedFields

Selection object for which columns to select

insert()

Creates an INSERT query. Use .values() to specify rows to insert.

// Insert one row
await db.insert(cars).values({ brand: 'BMW' });

// Insert multiple rows
await db.insert(cars).values([
  { brand: 'BMW' },
  { brand: 'Porsche' }
]);

// Insert with returning clause
const insertedCar = await db.insert(cars)
  .values({ brand: 'BMW' })
  .returning();

table · PgTable · required

The table to insert into

return · PgInsertBuilder

Query builder with methods like .values(), .onConflictDoNothing(), .returning(), etc.

update()

Creates an UPDATE query. Use .set() to specify values and .where() to filter rows.

// Update all rows
await db.update(cars).set({ color: 'red' });

// Update with filters
await db.update(cars)
  .set({ color: 'red' })
  .where(eq(cars.brand, 'BMW'));

// Update with returning
const updatedCar = await db.update(cars)
  .set({ color: 'red' })
  .where(eq(cars.id, 1))
  .returning();

table · PgTable · required

The table to update

return · PgUpdateBuilder

Query builder with methods like .set(), .where(), .returning(), etc.

delete()

Creates a DELETE query. Use .where() to specify which rows to delete.

// Delete all rows (use with caution!)
await db.delete(cars);

// Delete with filters
await db.delete(cars).where(eq(cars.color, 'green'));

// Delete with returning
const deletedCar = await db.delete(cars)
  .where(eq(cars.id, 1))
  .returning();

table · PgTable · required

The table to delete from

return · PgDeleteBase

Query builder with methods like .where(), .returning(), etc.

Common Table Expressions (CTEs)

$with()

Defines a CTE (Common Table Expression) for use in subsequent queries.

// Create a subquery
const sq = db.$with('sq').as(
  db.select().from(users).where(eq(users.id, 42))
);

// Use it in the main query
const result = await db.with(sq).select().from(sq);

// Reference SQL values with aliases
const sq2 = db.$with('sq').as(
  db.select({
    name: sql<string>`upper(${users.name})`.as('name'),
  }).from(users)
);

const result2 = await db.with(sq2)
  .select({ name: sq2.name })
  .from(sq2);

alias · string · required

Alias name for the CTE

return · WithBuilder

Object with .as() method to define the CTE query

with()

Incorporates previously defined CTEs into the main query.

const sq = db.$with('sq').as(
  db.select().from(users).where(eq(users.id, 42))
);

const result = await db.with(sq).select().from(sq);

queries · WithSubquery[] · required

One or more CTEs to incorporate

return · object

Object with query methods: select, selectDistinct, selectDistinctOn, update, insert, delete

Advanced Features

execute()

Executes raw SQL queries or SQLWrapper objects.

// Execute raw SQL string
const result = await db.execute('SELECT * FROM users WHERE id = 1');

// Execute SQL template
const result2 = await db.execute(
  sql`SELECT * FROM users WHERE id = ${userId}`
);

query · SQLWrapper | string · required

SQL query to execute

transaction()

Executes queries within a database transaction.

await db.transaction(async (tx) => {
  await tx.insert(users).values({ name: 'John' });
  await tx.insert(posts).values({ title: 'Hello' });
  // Both queries succeed or both fail
});

// With configuration
await db.transaction(async (tx) => {
  // ... queries
}, {
  isolationLevel: 'read committed',
  accessMode: 'read write',
  deferrable: false,
});

transaction · (tx: PgTransaction) => Promise<T> · required

Async function that receives transaction client

config · PgTransactionConfig

Transaction configuration options:

• isolationLevel: 'read uncommitted' | 'read committed' | 'repeatable read' | 'serializable'
• accessMode: 'read only' | 'read write'
• deferrable: boolean

refreshMaterializedView()

Refreshes a PostgreSQL materialized view.

await db.refreshMaterializedView(myMaterializedView);

// With CONCURRENTLY option
await db.refreshMaterializedView(myMaterializedView)
  .concurrently();

view · PgMaterializedView · required

The materialized view to refresh

$count()

Counts rows in a table or view with optional filters.

// Count all rows
const totalUsers = await db.$count(users);

// Count with filter
const activeUsers = await db.$count(
  users,
  eq(users.active, true)
);

source · PgTable | PgViewBase | SQL | SQLWrapper · required

Table, view, or SQL to count rows from

filters · SQL

Filter condition for counting

Relational Queries

When using a schema with relations, the query object provides a type-safe relational query API.

import * as schema from './schema';
const db = drizzle(pool, { schema });

// Query with relations
const usersWithPosts = await db.query.users.findMany({
  with: {
    posts: true,
  },
});

// Nested relations
const result = await db.query.users.findMany({
  with: {
    posts: {
      with: {
        comments: true,
      },
    },
  },
});

Read Replicas

withReplicas()

Configures read replica support for read-write splitting.

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

const primary = drizzle(primaryPool);
const replica1 = drizzle(replica1Pool);
const replica2 = drizzle(replica2Pool);

const db = withReplicas(primary, [replica1, replica2]);

// Read queries go to replicas
await db.select().from(users); // Uses random replica

// Write queries go to primary
await db.insert(users).values({ name: 'John' }); // Uses primary

// Explicitly use primary
await db.$primary.select().from(users);

primary · PgDatabase · required

Primary database connection for write operations

replicas · [PgDatabase, ...PgDatabase[]] · required

Array of replica connections for read operations

getReplica · (replicas: PgDatabase[]) => PgDatabase

Custom function to select which replica to use. Defaults to random selection.