pg-core/query-api

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

Many-to-one relationship query example

For a many-to-one relationship where users belong to cities: const result = await db.select().from(cities).leftJoin(users, eq(cities.id, users.cityId)). This joins cities with users where each city can have multiple users, and returns all cities with their associated users (or null if a user is not found in LEFT JOIN).

CROSS JOIN syntax and return type

Use db.select().from(users).crossJoin(pets) to perform a CROSS JOIN. Neither table's fields are nullable in the result type. Example generates SQL select ... from "users" cross join "pets" and returns { users: {...}; pets: {...} }[].

JOIN types available in Drizzle

Drizzle ORM has APIs for INNER JOIN [LATERAL], FULL JOIN, LEFT JOIN [LATERAL], RIGHT JOIN, and CROSS JOIN [LATERAL].

Type inference with sql operator in partial select during JOINs

When using the sql operator for partial selection fields or aggregations during JOINs, explicitly specify the type including null if the field comes from a nullable table. Use sql<type | null> for proper result type inference. Example: sql<string | null>`upper(${pets.name})` when selecting from a nullable joined table, versus sql<string>`upper(${pets.name})` for non-nullable tables.

Many-to-many relationship query example

For a many-to-many relationship with a junction table: await db.select().from(usersToChatGroups).leftJoin(users, eq(usersToChatGroups.userId, users.id)).leftJoin(chatGroups, eq(usersToChatGroups.groupId, chatGroups.id)).where(eq(chatGroups.id, 1)). This queries a junction table and joins both related tables to fetch users in a specific chat group.

Partial select with JOINs

Use .select({ field1: table1.column, field2: table2.column }) with a join to select only specific fields and flatten the response type. Drizzle automatically infers the return type based on the select structure. For LEFT JOINs and FULL JOINs, fields from the nullable table(s) will have nullable types in the result.

Nested select object syntax with JOINs

Use nested select objects to make a whole joined table nullable instead of making all its individual fields nullable. Example: await db.select({ userId: users.id, pet: { id: pets.id, name: pets.name } }).from(users).fullJoin(pets, eq(users.id, pets.ownerId)) returns { userId: number | null; pet: { id: number; name: string } | null }[] instead of having id and name each individually nullable.

CROSS JOIN LATERAL syntax and return type

Use db.select().from(users).crossJoinLateral(subquery) to perform a CROSS JOIN LATERAL. Example: const subquery = db.select().from(pets).where(gte(users.age, 16)).as('userPets'); const result = await db.select().from(users).crossJoinLateral(subquery) generates SQL select ... from "users" cross join lateral (select ... from "pets" where "users"."age" >= 16) "userPets" and returns { users: {...}; userPets: {...} }[].

Aggregating JOIN results in JavaScript

Drizzle ORM returns name-mapped results from the driver without changing the structure. Use JavaScript array operations like reduce() to aggregate and transform join results. Example use case: transforming a many-to-one join result with multiple rows per parent into a single object with an array of children, by reducing rows and grouping by parent ID.

eq operator - equal to

The eq operator tests if a value equals another value or column. Import from drizzle-orm. Example: eq(table.column, 5) generates SQL 'column' = 5. Can compare column to value or column to column: eq(table.column1, table.column2) generates SQL 'column1' = 'column2'.

notBetween operator - value not between two values

The notBetween operator tests if a value is not between two values. Import from drizzle-orm. Example: notBetween(table.column, 2, 7) generates SQL 'column' NOT BETWEEN 2 AND 7.

Filter operators import from drizzle-orm

All filter and conditional operators are imported from 'drizzle-orm' package. Example: import { eq, ne, gt, gte, ... } from 'drizzle-orm';

arrayOverlaps operator - array has any common elements

The arrayOverlaps operator tests that a column or expression contains any elements of the list passed as the second argument. Import from drizzle-orm. Example: arrayOverlaps(posts.tags, ['Typescript', 'ORM']) generates SQL 'tags' && {Typescript,ORM}.

arrayContained operator - array contained in list

The arrayContained operator tests that the list passed as the second argument contains all elements of a column or expression. Import from drizzle-orm. Example: arrayContained(posts.tags, ['Typescript', 'ORM']) generates SQL 'tags' <@ {Typescript,ORM}.

or operator - one or more conditions true

The or operator combines conditions where one or more must return true. Import from drizzle-orm. Example: or(gt(table.column, 5), lt(table.column, 7)) generates SQL ('column' > 5 OR 'column' < 7).

and operator - all conditions true

The and operator combines conditions where all must return true. Import from drizzle-orm. Example: and(gt(table.column, 5), lt(table.column, 7)) generates SQL ('column' > 5 AND 'column' < 7).

not operator - negate condition

The not operator negates a condition so it must return false. Import from drizzle-orm. Example: not(eq(table.column, 5)) generates SQL NOT ('column' = 5).

notIlike operator - case insensitive non-match

The notIlike operator tests if a string column does not match a pattern, case insensitive. Import from drizzle-orm. Example: notIlike(table.column, '%llo wor%') generates SQL 'column' NOT ILIKE '%llo wor%'.

ilike operator - case insensitive pattern match

The ilike operator tests if a string column matches a pattern, case insensitive. Import from drizzle-orm. Example: ilike(table.column, '%llo wor%') generates SQL 'column' ILIKE '%llo wor%'.

like operator - case sensitive pattern match

The like operator tests if a string column matches a pattern, case sensitive. Import from drizzle-orm. Example: like(table.column, '%llo wor%') generates SQL 'column' LIKE '%llo wor%'.

ne operator - not equal to

The ne operator tests if a value is not equal to another value or column. Import from drizzle-orm. Example: ne(table.column, 5) generates SQL 'column' <> 5. Can compare column to value or column to column: ne(table.column1, table.column2) generates SQL 'column1' <> 'column2'.

between operator - value between two values

The between operator tests if a value is between two values inclusive. Import from drizzle-orm. Example: between(table.column, 2, 7) generates SQL 'column' BETWEEN 2 AND 7.

notInArray operator - value not in list

The notInArray operator tests if a column value is not in an array of values or a subquery result. Import from drizzle-orm. Example with array: notInArray(table.column, [1, 2, 3, 4]) generates SQL 'column' NOT IN (1, 2, 3, 4). Example with subquery: notInArray(table.column, db.select({data: table2.column}).from(table2)) generates SQL 'column' NOT IN (SELECT "table2"."column" FROM table2).

inArray operator - value in list

The inArray operator tests if a column value is in an array of values or a subquery result. Import from drizzle-orm. Example with array: inArray(table.column, [1, 2, 3, 4]) generates SQL 'column' IN (1, 2, 3, 4). Example with subquery: inArray(table.column, db.select({data: table2.column}).from(table2)) generates SQL 'column' IN (SELECT "table2"."column" FROM table2).

isNotNull operator - value is not null

The isNotNull operator tests if a column or expression is not null. Import from drizzle-orm. Example: isNotNull(table.column) generates SQL 'column' IS NOT NULL.

arrayContains operator - array contains all elements

The arrayContains operator tests that a column or expression contains all elements of the list passed as the second argument. Import from drizzle-orm. Example with array literal: arrayContains(posts.tags, ['Typescript', 'ORM']) generates SQL 'tags' @> {Typescript,ORM}. Example with subquery: arrayContains(posts.tags, db.select({tags: posts.tags}).from(posts).where(eq(posts.id, 1))) generates SQL 'tags' @> (SELECT 'tags' FROM posts WHERE 'id' = 1).

notExists operator - subquery does not exist

The notExists operator tests whether a subquery returns no rows. Import from drizzle-orm. Example: notExists(db.select().from(table2)) generates SQL NOT EXISTS (SELECT * FROM table2). Used with db.select().from(table).where(notExists(query)).

exists operator - subquery exists

The exists operator tests whether a subquery returns any rows. Import from drizzle-orm. Example: exists(db.select().from(table2)) generates SQL EXISTS (SELECT * FROM table2). Used with db.select().from(table).where(exists(query)).

lte operator - less than or equal to

The lte operator tests if a value is less than or equal to another value or column. Import from drizzle-orm. Example: lte(table.column, 5) generates SQL 'column' <= 5. Can compare column to value or column to column: lte(table.column1, table.column2) generates SQL 'column1' <= 'column2'.

gte operator - greater than or equal to

The gte operator tests if a value is greater than or equal to another value or column. Import from drizzle-orm. Example: gte(table.column, 5) generates SQL 'column' >= 5. Can compare column to value or column to column: gte(table.column1, table.column2) generates SQL 'column1' >= 'column2'.

SQL-like select with join example

SQL-like queries in Drizzle allow selecting data with joins. Example: await db.select().from(countries).leftJoin(cities, eq(cities.countryId, countries.id)).where(eq(countries.id, 10))

Queries API example with relational data

The Queries API allows fetching relational nested data from the database. Example: const result = await db.query.users.findMany({ with: { posts: true } });

Prepared statements reduce query overhead

Drizzle provides a thin TypeScript layer on top of SQL with almost zero overhead. To achieve actual zero overhead, prepared statements can be used. When a query runs on the database, the query builder configurations are concatenated to an SQL string, that string and params are sent to the database driver, and the driver compiles the SQL query to binary SQL executable format and sends it to the database. With prepared statements, SQL concatenation happens once on the Drizzle ORM side, and then the database driver can reuse the precompiled binary SQL instead of parsing the query each time. This has extreme performance benefits on large SQL queries.

Placeholder with SQL expressions in prepared statements

Placeholders can be used within SQL expressions and template literals. Example: .where(sql`lower(${customers.name}) like ${sql.placeholder('name')}`).prepare('p2') followed by await p2.execute({ name: '%an%' })

sql.placeholder() for dynamic values in prepared statements

The sql.placeholder() API allows embedding dynamic runtime values into prepared statements. This prevents re-preparation of the query when parameter values change. Parameters are passed as an object to execute(). Example: .where(eq(customers.id, sql.placeholder('id'))).prepare('p1') followed by await p1.execute({ id: 10 })

Prepared statement syntax with prepare()

To create a prepared statement in Drizzle, call the prepare() method on a query with a statement name string, then call execute() on the result to run the prepared query. The statement can be executed multiple times. Example: const prepared = db.select().from(customers).prepare('statement_name'); followed by await prepared.execute();

withReplicas basic setup with multiple replicas

Create a primary database connection and one or more read replica connections, then pass them to withReplicas(primaryDb, [replica1, replica2, ...]) to get a db instance that automatically routes queries.

withReplicas function for read replicas

The withReplicas() function in Drizzle allows you to manage SELECT queries from read replicas while performing create, delete, and update operations on a primary database instance. It automatically routes read operations to replicas and write operations to the primary.

withReplicas weighted random replica selection example

You can implement weighted probability selection for replicas. For example, to give the first replica a 70% chance and the second a 30% chance: const db = withReplicas(primaryDb, [read1, read2], (replicas) => { const weight = [0.7, 0.3]; let cumulativeProbability = 0; const rand = Math.random(); for (const [i, replica] of replicas.entries()) { cumulativeProbability += weight[i]!; if (rand < cumulativeProbability) return replica; } return replicas[0]! });

withReplicas custom replica selection logic

You can pass a third argument to withReplicas that is a function accepting the replicas array and returning a selected replica. This allows you to implement custom weighted selection, round-robin, or any other custom logic for choosing which replica to use.

$primary key forces primary database for read operations

Use the $primary key on the db instance to force a read operation to use the primary database instead of a replica. For example: await db.$primary.select().from(usersTable)

withReplicas automatic routing behavior

When using withReplicas, SELECT queries are automatically routed to read replicas while DELETE, UPDATE, and INSERT operations are routed to the primary database instance.

Relational Queries v2: defineRelations replaces separate relations objects

In Relational Queries v2, all relations are defined in a single place using defineRelations() instead of creating separate relations objects for each table. The defineRelations function takes a schema and a callback that receives an 'r' parameter providing autocomplete for all tables and relation functions (one, many, through). This is then passed to drizzle() via the relations option.

Relational Queries v2: offset on related objects

v2 supports offset and limit on related objects when using with(): db.query.posts.findMany({ limit: 5, offset: 2, with: { comments: { offset: 3, limit: 3 } } }). This was not supported in v1.

Relational Queries v2: query-builders/query imports updated

Import paths for query builders have been updated: RelationalQueryBuilder and PgRelationalQuery are now imported from 'drizzle-orm/pg-core/query-builders/query' with v2 alternatives.

Relational Queries v2: filtering by relations

v2 supports filtering by related data directly in where clauses. Example: db.query.usersTable.findMany({ where: { id: { gt: 10 }, posts: { content: { like: 'M%' } } } }) returns users with ID > 10 who have at least one post starting with 'M'.

Relational Queries v2: defineRelationsPart for splitting relations definition

Relations can be split into multiple parts using defineRelationsPart() for better organization. Each part is defined like defineRelations() but returns an object that can be spread into the main relations: { ...relations, ...part }. The parts are then merged when passed to drizzle().

Relational Queries v2: RAW filter for complex SQL

Use RAW in where clauses for custom SQL: { RAW: (table) => sql`${table.age} BETWEEN 25 AND 35` }. This allows complex filtering that cannot be expressed with standard operators, including JSONB operations and other PostgreSQL-specific syntax.

defineRelations syntax: from and to replace fields and references

In v2 relations, the 'from' and 'to' parameters replace v1's 'fields' and 'references'. Both from and to accept either a single value or an array: r.one.users({ from: r.posts.authorId, to: r.users.id }) or r.one.users({ from: [r.posts.authorId], to: [r.users.id] }).

Relational Queries v2: orderBy is now an object

In v2, orderBy is simplified to an object format: db.query.users.findMany({ orderBy: { id: 'asc' } }). Replaces v1's callback syntax of (users, { asc }) => [asc(users.id)].

Relational Queries v2: where clause is now an object

In v2, the where clause in findMany() is now a simple object instead of a callback. Example: db.query.users.findMany({ where: { age: 15 } }). Supports AND, OR, NOT operators and filtering operators like eq, gt, like, ilike, and RAW for custom SQL expressions.

Relational Queries v2: predefined filters in relations using where

Relations can now include a 'where' option to predefined filters. Example: r.many.users({ from: r.groups.id.through(r.usersToGroups.groupId), to: r.users.id.through(r.usersToGroups.userId), where: { verified: true } }). This filters related data automatically when querying.

Relational Queries v2: through for many-to-many relations

The new 'through' method simplifies many-to-many relations. Instead of querying through a junction table explicitly, you use: r.many.groups({ from: r.users.id.through(r.usersToGroups.userId), to: r.groups.id.through(r.usersToGroups.groupId) }). This allows querying junction tables implicitly without manual mapping.

Relational Queries v2: relationName renamed to alias

The relationName parameter in v1 has been renamed to alias in v2. Example: r.one.users({ from: r.posts.authorId, to: r.users.id, alias: 'author_post' }).

Relational Queries v2: optional parameter for one relations

The optional: false parameter at the relation level makes the related key required in the TypeScript type. This should be used when you are certain that the related entity will always exist. Example: r.one.posts({ from: r.users.id, to: r.posts.authorId, optional: false }).

Relational Queries v2: many relations without requiring one relation

In v2, you can define a many relation without needing to define a corresponding one relation on the other side. For example, r.many.posts({ from: r.users.id, to: r.posts.authorId }) defines a many relation from users to posts without requiring a one relation on the posts side.

Relational Queries v2: removed v1 types and entities

The following v1 types and functions have been removed: Relations, TableRelationsKeysOnly, ExtractTableRelationsFromSchema, ExtractRelationsFromTableExtraConfigSchema, getOperators, FindTableByDBName, RelationalSchemaConfig, RelationConfig, extractTablesRelationalConfig, relations, createOne, createMany, NormalizedRelation, normalizeRelation, createTableRelationsHelpers, and TableRelationsHelpers.

Querying with relations using findMany and with

Query relational data using db.query.tableName.findMany({ with: { relationName: true } }). This fetches the table records along with their related data defined in the relations. For example, await db.query.users.findMany({ with: { groups: true } }) returns users with their associated groups array.

Relational queries findMany and findFirst methods

Drizzle provides .findMany() and .findFirst() APIs for relational queries. findMany() returns an array of results. findFirst() returns a single result and adds LIMIT 1 to the query.

Relational queries require callback parameter for table references

Inside relational queries, references to a table's columns must go through the callback parameter, not through the imported table object. This applies to every clause that accepts a callback: orderBy, where.RAW, extras, and subqueries inside extras. The callback exposes the aliased table for the current query scope, which is required for correct SQL generation in nested or self-referential queries.

Give your agent this brain