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);
Selection object mapping field names to column references or SQL expressions
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);
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
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();
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();
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();
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);
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);
One or more CTEs to incorporate
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
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
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 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.