pg-core/query-api

254 notes in this subject, read out of this brain and free to use. This is page 4 of 5.

Select from subquery with as()

Create a subquery using .as('alias') and use it in another select. For example, const sq = db.select().from(users).where(eq(users.id, 42)).as('sq'); const result = await db.select().from(sq) generates 'select "id", "name", "age" from (select "id", "name", "age" from "users" where "users"."id" = 42) "sq"'.

Subqueries work in joins and other query contexts

Subqueries can be used anywhere a table can be used, such as in joins. For example, const sq = db.select().from(users).where(eq(users.id, 42)).as('sq'); const result = await db.select().from(users).leftJoin(sq, eq(users.id, sq.id)) generates a left join with the subquery.

groupBy() for aggregations

Use .groupBy() to group results for aggregation functions. For example, db.select({ age: users.age, count: sql<number>`cast(count(${users.id}) as int)` }).from(users).groupBy(users.age) groups by age and counts users in each group.

comment() method for query tracking metadata

The .comment() method adds sqlcommenter-formatted comments to queries for metadata attachment, query tracking, debugging and database traffic control. Comments are appended to the end of each query as SQL comments.

comment() method available on select, insert, update, delete

The .comment() method is available on select, insert, update and delete queries in Drizzle.

comment() with string parameter

Pass a raw string as a comment to .comment(). The string is appended as-is in SQL comment syntax. Example: db.select().from(users).comment("my_first_tag") produces /*my_first_tag*/ at the end of the query.

comment() with object key-value pairs

Pass an object with key-value pairs to .comment() for structured tags. The object is converted to sqlcommenter format with URL-encoded values. Example: db.select().from(users).comment({ priority: 'high', category: 'analytics' }) produces /*priority='high',category='analytics'*/ in the SQL.

comment() with insert query example

Example of using .comment() with insert: db.insert(users).values({ name: 'Dan' }).comment({ operation: 'seed' }) produces insert into "users" ("name") values ('Dan') /*operation='seed'*/

comment() with update query example

Example of using .comment() with update: db.update(users).set({ name: 'Dan' }).where(eq(users.id, 1)).comment({ operation: 'update' }) produces update "users" set "name" = 'Dan' where "users"."id" = 1 /*operation='update'*/

comment() with delete query example

Example of using .comment() with delete: db.delete(users).where(eq(users.id, 1)).comment({ operation: 'cleanup' }) produces delete from "users" where "users"."id" = 1 /*operation='cleanup'*/

comment() cannot be used after prepare()

The .comment() method cannot be called after a statement has been prepared with .prepare(). Prepared statements compile the SQL query once and reuse it across executions, so the query string is fixed at preparation time and cannot be modified afterwards, including appending comments.

comment() must be called before prepare()

To use .comment() with a prepared statement, call .comment() before calling .prepare(). Example: db.select().from(users).comment({ key: 'val' }).prepare() is correct, while p.comment({ key: 'val' }).execute() after prepare() will not work.

Object values in comment() are URL-encoded

When passing an object to .comment(), all values are converted to strings and URL-encoded in the resulting SQL comment. Example: { trace: true, route: '/api/users', version: 2 } produces /*route='%2Fapi%2Fusers',trace='true',version='2'*/ where / is encoded as %2F.

sql.mapWith() runtime value mapping

sql.mapWith() applies runtime mapping to values from the database driver. It accepts either a column reference (which replicates the column's mapping strategy) or a custom DriverValueDecoder implementation. Example: sql`...`.mapWith(usersTable.name) maps the result using the same strategy as the text column. Alternative: sql``.mapWith({ mapFromDriverValue: (value: any) => { /* mapping */ }, }) or sql``.mapWith(Number)

sql template import

The sql template is imported from 'drizzle-orm': import { sql } from 'drizzle-orm'

sql template basic usage with parameterization

The sql template allows writing parameterized queries with automatic escaping. Tables and columns are mapped to escaped SQL syntax. Dynamic parameters like ${id} are converted to $1, $2 placeholders, and values are passed in a separate array to prevent SQL injection. Example: sql`select * from ${usersTable} where ${usersTable.id} = ${id}` generates: select * from "users" where "users"."id" = $1; --> [69]

sql<T> custom type definition

sql<T> allows defining a custom return type for sql expressions to replace the default 'unknown' type. This is purely a TypeScript helper with no runtime mapping. It is useful in partial select queries to ensure consistent typing. Example: sql<string>`lower(${usersTable.id})` ensures the result is typed as string instead of unknown.

sql.as() field aliasing

sql.as('alias_name') explicitly defines an alias for a custom sql field. Example: sql`lower(${usersTable.name})`.as('lower_name') generates: ... "users"."name" as lower_name ...

sql.raw() unescaped raw SQL

sql.raw() includes raw SQL statements without any processing or escaping. It does not create parameterized values or escape tables/columns. Example: sql.raw(`select * from users where id = ${12}`) generates: select * from users where id = 12 (no parameterization). Contrast with sql`select * from users where id = ${12}` which generates: select * from users where id = $1; --> [12]

sql.raw() within sql template

sql.raw() can be used inside the sql function to include unescaped raw strings directly. Example: sql`select * from ${usersTable} where id = ${sql.raw(12)}` generates: select * from "users" where id = 12 (the raw value is not parameterized, contrasting with sql`select * from ${usersTable} where id = ${12}` which generates: select * from "users" where id = $1; --> [12])

sql.fromList() concatenate SQL chunks

sql.fromList() combines multiple SQL chunks (array of SQL parts) into a single SQL statement. This is useful for aggregating chunks with custom business logic before concatenating. Example: const finalSql: SQL = sql.fromList(sqlChunks) where sqlChunks contains multiple sql` ` template results, generating: select * from users where id = $1 or id = $2 or id = $3 or id = $4 or id = $5; --> [0, 1, 2, 3, 4]

sql.join() concatenate with custom separator

sql.join(chunks, separator) concatenates SQL chunks using a specified separator (any string or character). This provides flexibility in formatting and delimiting chunks. Example: const finalSql: SQL = sql.join(sqlChunks, sql.raw(' ')) joins chunks with spaces, generating: select * from users where id = $1 or id = $2 or id = $3 or id = $4 or id = $5; --> [0, 1, 2, 3, 4]

sql.append() dynamically add chunks

sql.append() adds a new SQL chunk to an existing SQL object, effectively concatenating them. This allows incremental construction of SQL queries with custom logic. Example: const finalSql = sql`select * from users`; finalSql.append(sql` where `); finalSql.append(sql`id = ${0}`); generates: select * from users where id = $1 or id = $2 or id = $3 or id = $4 or id = $5; --> [0, 1, 2, 3, 4]

sql.empty() initialize blank SQL object

sql.empty() creates a blank SQL object that can be built incrementally by appending chunks. After initialization, all sql template features like parameterization, composition, and escaping are available. Example: const finalSql = sql.empty(); finalSql.append(sql`select * from users`); allows constructing queries incrementally.

PgDialect.sqlToQuery() convert sql to string and params

PgDialect().sqlToQuery() converts a sql template to a database-specific query string and parameter array. The dialect must be specified because different databases have varying parameterization and escaping syntax. Example: import { PgDialect } from 'drizzle-orm/pg-core'; const pgDialect = new PgDialect(); pgDialect.sqlToQuery(sql`select * from ${usersTable} where ${usersTable.id} = ${12}`) generates: select * from "users" where "users"."id" = $1; --> [ 12 ]

sql in SELECT partial queries

sql can be used in partial SELECT queries to retrieve specific custom fields. Use sql<T> for type safety, sql.mapWith() for runtime value mapping, and sql.as() for aliasing. Example: await db.select({ id: usersTable.id, lowerName: sql<string>`lower(${usersTable.name})`, aliasedName: sql<string>`lower(${usersTable.name})`.as('aliased_column'), count: sql<number>`count(*)`.mapWith(Number) }).from(usersTable) generates: select "id", lower("name"), lower("name") as "aliased_column", count(*) from "users";

sql in WHERE clause

sql can be used in WHERE clauses for filtering when Drizzle's built-in expressions are insufficient. This enables using database-specific expressions and extensions. Example: const id = 77; await db.select().from(usersTable).where(sql`${usersTable.id} = ${id}`) generates: select * from "users" where "users"."id" = $1; --> [ 77 ]

sql fulltext search example

sql can implement full-text search in WHERE clauses using database-specific operators. Example: const searchParam = "Ale"; await db.select().from(usersTable).where(sql`to_tsvector('simple', ${usersTable.name}) @@ to_tsquery('simple', ${searchParam})`) generates: select * from "users" where to_tsvector('simple', "users"."name") @@ to_tsquery('simple', '$1'); --> [ "Ale" ]

sql in ORDER BY clause

sql can be used in ORDER BY clauses for specific ordering functionality not available in Drizzle. Example: await db.select().from(usersTable).orderBy(sql`${usersTable.id} desc nulls first`) generates: select * from "users" order by "users"."id" desc nulls first;

sql in GROUP BY and HAVING clauses

sql can be used in GROUP BY and HAVING clauses for specific grouping and filtering functionality. Example: await db.select({ projectId: usersTable.projectId, count: sql<number>`count(${usersTable.id})`.mapWith(Number) }).from(usersTable).groupBy(sql`${usersTable.projectId}`).having(sql`count(${usersTable.id}) > 300`) generates: select "project_id", count("id") from "users" group by "users"."project_id" having count("users"."id") > 300

Transaction return value

Transactions can return values. The value returned from the async function passed to db.transaction() becomes the resolved value of the transaction promise.

Transaction with return value example

Example showing transaction that returns a value: const db = drizzle(...) const newBalance: number = await db.transaction(async (tx) => { await tx.update(accounts).set({ balance: sql`${accounts.balance} - 100.00` }).where(eq(users.name, 'Dan')); await tx.update(accounts).set({ balance: sql`${accounts.balance} + 100.00` }).where(eq(users.name, 'Andrew')); const [account] = await tx.select({ balance: accounts.balance }).from(accounts).where(eq(users.name, 'Dan')); return account.balance; });

Transactions with relational queries

Transactions work with relational queries. Use tx.query within a transaction to execute relational query operations with the same transaction semantics.

PostgreSQL transaction configuration

PostgreSQL transactions support dialect-specific configuration passed as a second argument to db.transaction(). The PgTransactionConfig interface has three optional properties: isolationLevel (string), accessMode (string), and deferrable (boolean).

PgTransactionConfig isolation levels

The isolationLevel option in PgTransactionConfig accepts four values: "read uncommitted", "read committed", "repeatable read", or "serializable". This is optional.

PgTransactionConfig access mode

The accessMode option in PgTransactionConfig accepts two values: "read only" or "read write". This is optional.

PgTransactionConfig deferrable option

The deferrable option in PgTransactionConfig is a boolean that controls transaction deferrability. This is optional.

Transaction config example

Example showing PostgreSQL-specific transaction configuration: await db.transaction( async (tx) => { await tx.update(accounts).set({ balance: sql`${accounts.balance} - 100.00` }).where(eq(users.name, "Dan")); await tx.update(accounts).set({ balance: sql`${accounts.balance} + 100.00` }).where(eq(users.name, "Andrew")); }, { isolationLevel: "read committed", accessMode: "read write", deferrable: true, } );

Relational query transaction example

Example showing transaction with relational queries: const db = drizzle({ schema }) await db.transaction(async (tx) => { await tx.query.users.findMany({ with: { accounts: true } }); });

Transaction basic usage

Drizzle ORM provides a transaction API through db.transaction(). Pass an async function that receives a transaction object (tx) as parameter. All database operations on tx within this function are grouped as a single logical unit that commits or rolls back together.

Transaction example with transfer

Example showing how to run multiple update statements within a transaction: const db = drizzle(...) await db.transaction(async (tx) => { await tx.update(accounts).set({ balance: sql`${accounts.balance} - 100.00` }).where(eq(users.name, 'Dan')); await tx.update(accounts).set({ balance: sql`${accounts.balance} + 100.00` }).where(eq(users.name, 'Andrew')); });

Nested transactions with savepoints

Drizzle ORM supports savepoints through nested transactions. Within an outer transaction, call tx.transaction() to create a nested transaction. Changes in nested transactions can be rolled back independently while still participating in the outer transaction.

Nested transaction example

Example showing nested transaction with savepoint: const db = drizzle(...) await db.transaction(async (tx) => { await tx.update(accounts).set({ balance: sql`${accounts.balance} - 100.00` }).where(eq(users.name, 'Dan')); await tx.update(accounts).set({ balance: sql`${accounts.balance} + 100.00` }).where(eq(users.name, 'Andrew')); await tx.transaction(async (tx2) => { await tx2.update(users).set({ name: "Mr. Dan" }).where(eq(users.name, "Dan")); }); });

Transaction rollback method

Call tx.rollback() within a transaction to trigger an exception that rolls back the entire transaction. This is used when business logic conditions require undoing all changes.

Transaction rollback example

Example showing conditional transaction rollback based on business logic: const db = drizzle(...) await db.transaction(async (tx) => { const [account] = await tx.select({ balance: accounts.balance }).from(accounts).where(eq(users.name, 'Dan')); if (account.balance < 100) { tx.rollback() } await tx.update(accounts).set({ balance: sql`${accounts.balance} - 100.00` }).where(eq(users.name, 'Dan')); await tx.update(accounts).set({ balance: sql`${accounts.balance} + 100.00` }).where(eq(users.name, 'Andrew')); });

Update values are parameterized automatically

All values provided to .set() are parameterized automatically in update queries. For example, await db.update(users).set({ name: 'Mr. Dan' }).where(eq(users.name, 'Dan')) is translated to SQL with parameters, not string interpolation, preventing SQL injection.

Undefined values ignored in update set, null values must be explicit

In update operations, undefined values in the set object are ignored. To set a column to null, you must pass null explicitly. For example, await db.update(users).set({ name: null }).where(eq(users.name, 'Dan')) sets name to null.

Use sql template for SQL expressions in update set

You can pass SQL expressions as values in the update set object using the sql template tag. For example, await db.update(users).set({ updatedAt: sql`NOW()` }).where(eq(users.name, 'Dan')) sets updatedAt to the result of the SQL NOW() function.

Update with returning clause

PostgreSQL update queries support a .returning() clause to retrieve updated rows. For example, await db.update(users).set({ name: 'Mr. Dan' }).where(eq(users.name, 'Dan')).returning({ updatedId: users.id }) returns the id of the updated user.

Update with common table expressions (WITH clause)

Update queries support WITH clauses to define common table expressions (CTEs) that simplify complex queries. Define a CTE using db.$with('name').as(...), then use it in an update query with db.with(cte_name).update(...).set(...).where(...).returning(...).

Update with FROM clause joins other tables

PostgreSQL update queries support a .from() clause to allow columns from other tables to appear in the WHERE condition and update expressions. For example, await db.update(users).set({ cityId: cities.id }).from(cities).where(and(eq(cities.name, 'Seattle'), eq(users.name, 'John'))) updates users by joining with cities.

Update FROM with table aliases

In update queries with FROM clauses, you can alias the joined table using the alias() function. For example, const c = alias(cities, 'c'); await db.update(users).set({ cityId: c.id }).from(c) aliases the cities table as 'c' in the FROM clause.

Update FROM returning columns from joined tables

PostgreSQL allows returning columns from joined tables in update queries. For example, await db.update(users).set({ cityId: cities.id }).from(cities).returning({ id: users.id, cityName: cities.name }) returns both the user id and the city name from the joined cities table.

Update example with parameterization

await db.update(users).set({ name: 'Mr. Dan' }).where(eq(users.name, 'Dan')) is translated to: update "users" set "name" = $1 where "users"."name" = $2; -- params: ['Mr. Dan', 'Dan']

Update with sql NOW() example

await db.update(users).set({ updatedAt: sql`NOW()` }).where(eq(users.name, 'Dan')) sets updatedAt to the current timestamp using the SQL NOW() function.

Update with returning example

const updatedUserId = await db.update(users).set({ name: 'Mr. Dan' }).where(eq(users.name, 'Dan')).returning({ updatedId: users.id }) returns [{ updatedId: number | null }].

Update with WITH clause example

const averagePrice = db.$with('average_price').as(db.select({ value: sql`avg(${products.price})`.as('value') }).from(products)); const result = await db.with(averagePrice).update(products).set({ cheap: true }).where(lt(products.price, sql`(select * from ${averagePrice})`)).returning({ id: products.id }) generates: with "average_price" as (select avg("price") as "value" from "products") update "products" set "cheap" = true where "products"."price" < (select * from "average_price") returning "id"

Update FROM example

await db.update(users).set({ cityId: cities.id }).from(cities).where(and(eq(cities.name, 'Seattle'), eq(users.name, 'John'))) generates: update "users" set "city_id" = "cities"."id" from "cities" where (("cities"."name" = 'Seattle') and ("users"."name" = 'John'))

Update FROM with alias example

const c = alias(cities, 'c'); await db.update(users).set({ cityId: c.id }).from(c) generates: update "users" set "city_id" = "c"."id" from "cities" "c"

Update FROM with returning from joined table example

const updatedUsers = await db.update(users).set({ cityId: cities.id }).from(cities).returning({ id: users.id, cityName: cities.name }) generates: update "users" set "city_id" = "cities"."id" from "cities" returning "users"."id", "cities"."name"

Give your agent this brain