pg-core/query-api

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

Composing WHERE filters independently

Drizzle allows composing WHERE statements independently before using them in a query. Build an array of SQL filter conditions and pass them to the where clause using the and() function. Example: const filters: SQL[] = []; if (name) filters.push(ilike(products.name, name)); if (category) filters.push(eq(products.category, category)); if (maxPrice) filters.push(lte(products.price, maxPrice)); return db.select().from(products).where(and(...filters));

SQL-like syntax for SELECT with JOIN and WHERE

Drizzle supports SQL-like syntax for querying. To select data with a left join and where clause, use: await db.select().from(posts).leftJoin(comments, eq(posts.id, comments.post_id)).where(eq(posts.id, 10)). This generates the SQL: SELECT * FROM "posts" LEFT JOIN "comments" ON "posts"."id" = "comments"."post_id" WHERE "posts"."id" = 10.

SQL-like syntax query operations supported

Drizzle's SQL-like syntax supports select, insert, update, delete operations, as well as aliases, WITH clauses, subqueries, and prepared statements.

Drizzle Queries API with relational data

Drizzle provides a Queries API for fetching nested, relational data without manual joins or data mapping. To fetch users with their posts using the Queries API, use: const result = await db.query.users.findMany({ with: { posts: true } });. Drizzle always outputs exactly one SQL query, making it suitable for serverless databases without roundtrip performance concerns.

Drizzle SQL-like syntax philosophy

Drizzle is built with SQL-like syntax at its core because SQL is already a well-known language. Unlike other ORMs that abstract away from SQL, Drizzle embraces SQL, resulting in minimal learning curve and full access to SQL's power. If you know SQL, you know Drizzle.

Composing subqueries and using them in main queries

Drizzle allows separating subqueries into different variables and then using them in the main query. Create a subquery using select().from().leftJoin().as('alias_name'), then use it in the main query by joining to the subquery. Example: const subquery = db.select().from(internalStaff).leftJoin(customUser, eq(internalStaff.userId, customUser.id)).as('internal_staff'); const mainQuery = await db.select().from(ticket).leftJoin(subquery, eq(subquery.internal_staff.userId, ticket.staffId));

UPDATE query syntax

To update data in a table, use: await db.update(users).set({ email: 'user@gmail.com' }).where(eq(users.id, 1)). This generates the SQL: UPDATE "users" SET "email" = 'user@gmail.com' WHERE "users"."id" = 1.

DELETE query syntax

To delete data from a table, use: await db.delete(users).where(eq(users.id, 1)). This generates the SQL: DELETE FROM "users" WHERE "users"."id" = 1.

INSERT query syntax

To insert data into a table using SQL-like syntax, use: await db.insert(users).values({ email: 'user@gmail.com' }). This generates the SQL: INSERT INTO "users" ("email") VALUES ('user@gmail.com').

Delete with WITH clause example

const averageAmount = db.$with('average_amount').as(db.select({ value: sql`avg(${orders.amount})`.as('value') }).from(orders)); const result = await db.with(averageAmount).delete(orders).where(gt(orders.amount, sql`(select * from ${averageAmount})`)).returning({ id: orders.id }); This generates a WITH clause that calculates the average amount and deletes orders above that average.

Delete with returning() example

const deletedUser = await db.delete(users).where(eq(users.name, 'Dan')).returning(); const deletedUserId = await db.delete(users).where(eq(users.name, 'Dan')).returning({ deletedId: users.id }); The second example returns a partial result with type { deletedId: number | null }[].

Delete with where clause

To delete rows matching a condition, chain .where() after db.delete(). The example shows await db.delete(users).where(eq(users.name, 'Dan'));

Delete all rows with db.delete()

To delete all rows in a table, call db.delete(tableName) without any where clause. The example shows await db.delete(users);

Delete with WITH clause (CTE)

Use the .with() method to include common table expressions (CTEs) in a delete query. This allows splitting complex queries into smaller subqueries. Call db.$with('cte_name').as(subquery) to define the CTE, then chain .with(cte) before .delete(). The CTE can be referenced in the where clause using sql`` templates.

Delete with returning() to get deleted rows

PostgreSQL supports returning deleted rows with the .returning() method. Call .returning() without arguments to return all columns, or .returning({ field1: table.field1 }) to return specific columns. The resulting type is an array of objects.

Example: chaining dynamic query building with joins

```ts function withFriends<T extends PgSelect>(qb: T) { return qb.leftJoin(friends, eq(friends.userId, users.id)); } let query = db.select().from(users).where(eq(users.id, 1)).$dynamic(); query = withFriends(query); ``` Generic types like PgSelect allow you to modify the result type of the query builder inside functions, such as adding joins. This demonstrates building queries progressively by chaining multiple enhancement functions.

Example: withPagination dynamic query builder function

```ts function withPagination<T extends PgSelect>( qb: T, page: number = 1, pageSize: number = 10, ) { return qb.limit(pageSize).offset((page - 1) * pageSize); } const query = db.select().from(users).where(eq(users.id, 1)); withPagination(query, 1); // ❌ Type error - the query builder is not in dynamic mode const dynamicQuery = query.$dynamic(); withPagination(dynamicQuery, 1); // ✅ OK ``` This example shows a shared function that takes a query builder in dynamic mode and adds LIMIT and OFFSET clauses based on page number and page size.

Query builder dynamic mode type restrictions

Only query builders in dynamic mode can accept methods being called multiple times. Passing a non-dynamic query builder to a function that calls methods multiple times will result in a type error. Once .$dynamic() is called, the query builder enters dynamic mode and can be used in functions that enhance it.

QueryBuilder standalone type for dynamic building

The ...QueryBuilder types (such as PgSelectQueryBuilder) are for usage with standalone query builder instances from 'drizzle-orm/pg-core'. DB query builders are subclasses of these types, so you can use them interchangeably. You can import QueryBuilder and use it to create standalone instances that support dynamic query building.

Dynamic query building with $dynamic()

By default, query builders in Drizzle conform to SQL and restrict most methods to be invoked only once. For example, you cannot call .where() multiple times on a SELECT statement. To enable dynamic query building where methods can be called multiple times, call .$dynamic() on a query builder. This is useful when building queries dynamically, such as in shared functions that enhance query builders.

Dynamic query building generic type parameters

For dynamic query building, use these generic parameter types: PgSelect or PgSelectQueryBuilder for SELECT queries, PgInsert for INSERT queries, PgUpdate for UPDATE queries, and PgDelete for DELETE queries. These types are designed specifically for dynamic query building and can only be used in dynamic mode.

pg_vector selecting distance value example

To select the distance value itself: db.select({ distance: l2Distance(items.embedding, [3,1,2]) }). This translates to SQL: SELECT embedding <-> '[3,1,2]' AS distance FROM items.

pg_vector arithmetic on distance function example

To perform arithmetic on a distance function: db.select({ innerProduct: sql`(${innerProduct(items.embedding, [3,1,2])}) * -1` }).from(items). This translates to SQL: SELECT (embedding <#> '[3,1,2]') * -1 AS inner_product FROM items.

pg_vector subquery distance example

To use a subquery as the comparison value: const subquery = db.select({ embedding: items.embedding }).from(items).where(eq(items.id, 1)); db.select().from(items).orderBy(l2Distance(items.embedding, subquery)).limit(5). This translates to SQL: SELECT * FROM items ORDER BY embedding <-> (SELECT embedding FROM items WHERE id = 1) LIMIT 5.

pg_vector ordering by distance example

To order query results by vector distance: db.select().from(items).orderBy(l2Distance(items.embedding, [3,1,2])). This translates to SQL: SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5 when combined with .limit(5).

Custom pg_vector distance function implementation

To implement a custom pg_vector distance function, use the sql template operator. The function signature is: export function functionName(column: SQLWrapper | AnyColumn, value: number[] | string[] | TypedQueryBuilder<any> | string): SQL. For arrays, convert to JSON with JSON.stringify(). This pattern allows replicating any pg_vector operator by changing the operator symbol.

jaccardDistance helper function for pg_vector queries

The jaccardDistance() helper function from drizzle-orm computes Jaccard distance between a column and a value. It accepts a column and a string. It generates the <%> operator. Example: jaccardDistance(table.column, '101') produces table.column <%> '101'.

hammingDistance helper function for pg_vector queries

The hammingDistance() helper function from drizzle-orm computes Hamming distance between a column and a value. It accepts a column and a string. It generates the <~> operator. Example: hammingDistance(table.column, '101') produces table.column <~> '101'.

cosineDistance helper function for pg_vector queries

The cosineDistance() helper function from drizzle-orm computes cosine distance between a column and a value. It accepts a column and a number array, string array, TypedQueryBuilder, or string. It generates the <=> operator. Example: cosineDistance(table.column, [3, 1, 2]) produces table.column <=> '[3, 1, 2]'.

innerProduct helper function for pg_vector queries

The innerProduct() helper function from drizzle-orm computes inner product between a column and a value. It accepts a column and a number array, string array, TypedQueryBuilder, or string. It generates the <#> operator. Example: innerProduct(table.column, [3, 1, 2]) produces table.column <#> '[3, 1, 2]'.

l1Distance helper function for pg_vector queries

The l1Distance() helper function from drizzle-orm computes L1 distance between a column and a value. It accepts a column and a number array, string array, TypedQueryBuilder, or string. It generates the <+> operator. Example: l1Distance(table.column, [3, 1, 2]) produces table.column <+> '[3, 1, 2]'.

getTableConfig to inspect table metadata

Call getTableConfig(table) to retrieve table metadata including columns, indexes, foreignKeys, checks, primaryKeys, name, and schema. Example: const { columns, indexes, foreignKeys, checks, primaryKeys, name, schema } = getTableConfig(table);

getColumns to extract typed column map

Use getColumns(table) to get an object with all columns as properties. This is useful for excluding specific columns via destructuring. Example: const { password, role, ...rest } = getColumns(users); await db.select({ ...rest }).from(users);

Execute raw parametrized SQL with db.execute

Use db.execute() with sql template string for raw parametrized SQL queries. Use ${} to safely interpolate table and column references. Example: const statement = sql`select * from ${users} where ${users.id} = ${userId}`; const result = await db.execute(statement);

Enable query logging with logger option

Pass { logger: true } to the drizzle() constructor to enable default query logging. Example: const db = drizzle(process.env.DB_URL, { logger: true });

Custom logger with DefaultLogger and LogWriter

Create a custom LogWriter by implementing the LogWriter interface with a write(message: string) method. Pass it to DefaultLogger via { writer: new MyLogWriter() }, then pass the logger instance to drizzle(). Example: const logger = new DefaultLogger({ writer: new MyLogWriter() }); const db = drizzle(process.env.DB_URL, { logger });

Custom logger implementation

Implement the Logger interface with a logQuery(query: string, params: unknown[]): void method. Pass the instance to drizzle() via the logger option. Example: class MyLogger implements Logger { logQuery(query: string, params: unknown[]): void { console.log({ query, params }); } }

pgTableCreator for multi-project schemas

Use pgTableCreator((name) => customFormat(name)) to customize table names across multiple projects sharing one database. Pass a function that receives the table name and returns a formatted name. Example: const pgTable = pgTableCreator((name) => `project1_${name}`);

InferSelectModel and InferInsertModel type helpers

Use InferSelectModel<T> and InferInsertModel<T> to infer TypeScript types from a pgTable schema. Alternatively, use typeof table.$inferSelect and typeof table.$inferInsert. These helpers extract the select and insert models that match the table's column definitions.

Use is() for Drizzle type checking instead of instanceof

Import is from 'drizzle-orm' and use it to type-check Drizzle objects. Example: if (is(value, Column)) { /* value is narrowed to Column */ }

Standalone QueryBuilder without database instance

Import QueryBuilder from 'drizzle-orm/pg-core' and instantiate it without a database connection. Use it to build queries and call .toSQL() to get { sql, params }. Example: const qb = new QueryBuilder(); const query = qb.select().from(users).where(eq(users.name, 'Dan')); const { sql, params } = query.toSQL();

Mock database instance with drizzle.mock()

Call drizzle.mock({ relations }) from 'drizzle-orm/node-postgres' to create a typed database object without a real PostgreSQL connection. Useful for testing. Example: const db = drizzle.mock({ relations });

Print SQL query with toSQL() method

Call .toSQL() on any query to get the generated SQL string and parameters without executing it. Example: const query = db.select({ id: users.id, name: users.name }).from(users).groupBy(users.id).toSQL();

Insert multiple rows

To insert multiple rows at once, pass an array of objects to `.values()`: `await db.insert(users).values([{ name: 'Andrew' }, { name: 'Dan' }]);`

Insert parameterization in Drizzle

All values provided to `.values()` are parameterized automatically. For example, `await db.insert(users).values({ name: 'Andrew' })` is translated to `insert into "users" ("id", "name") values (default, $1)` with params `['Andrew']`. This prevents SQL injection.

Insert with SELECT using SQL template tag

To insert rows using a custom SQL SELECT query via template tag: `await db.insert(employees).select(sql`select "users"."id" as "id", "users"."name" as "name" from "users" where "users"."role" = 'employee'`);` or with a callback: `await db.insert(employees).select(() => sql`select "users"."id" as "id", "users"."name" as "name" from "users" where "users"."role" = 'employee'`);`

Insert with SELECT query using query builder

To insert rows from a SELECT statement using a query builder directly: `await db.insert(employees).select(db.select({ id: users.id, name: users.name }).from(users).where(eq(users.role, 'employee'))).returning({ id: employees.id, name: employees.name });`

WITH clause in insert statements

Insert statements support the `with` clause to use common table expressions (CTEs) and simplify complex queries. Example: `const userCount = db.$with('user_count').as(db.select({ value: sql`count(*)`.as('value') }).from(users)); const result = await db.with(userCount).insert(users).values([{ username: 'user1', admin: sql`((select * from ${userCount}) = 0)` }]).returning({ admin: users.admin });`

onConflictDoUpdate with composite keys

For composite indexes or composite primary keys, pass an array of columns to the `target` option: `await db.insert(users).values({ firstName: 'John', lastName: 'Doe' }).onConflictDoUpdate({ target: [users.firstName, users.lastName], set: { firstName: 'John1' } });`

onConflictDoUpdate with where clauses

The `onConflictDoUpdate` method supports two where clause options. Use `targetWhere` to add a where clause as part of the conflict target (for partial indexes): `targetWhere: sql`name <> 'John Doe'``. Use `setWhere` to add a where clause as part of the update clause: `setWhere: sql`name <> 'John Doe'``.

onConflictDoUpdate syntax

Use `.onConflictDoUpdate()` to update the row if there is a conflict. Specify the target column and the set clause: `await db.insert(users).values({ id: 1, name: 'Dan' }).onConflictDoUpdate({ target: users.id, set: { name: 'John' } });`

onConflictDoNothing syntax

Use `.onConflictDoNothing()` to cancel an insert if there is a conflict. Can optionally specify a conflict target: `await db.insert(users).values({ id: 1, name: 'John' }).onConflictDoNothing();` or `await db.insert(users).values({ id: 1, name: 'John' }).onConflictDoNothing({ target: users.id });`

Insert with returning

After inserting a row in PostgreSQL, you can return the inserted row using `.returning()` for full return or `.returning({ insertedId: users.id })` for partial return. Examples: `await db.insert(users).values({ name: "Dan" }).returning();` or `await db.insert(users).values({ name: "Partial Dan" }).returning({ insertedId: users.id });`

Insert with SELECT query using callback

To insert rows from a SELECT statement using a callback function: `await db.insert(employees).select(() => db.select({ id: users.id, name: users.name }).from(users).where(eq(users.role, 'employee')));` or `await db.insert(employees).select((qb) => qb.select({ id: users.id, name: users.name }).from(users).where(eq(users.role, 'employee')));`

Infer insert type from table

Use `typeof users.$inferInsert` to get the type for inserting into a particular table. This enables type-safe insert operations, for example: `type NewUser = typeof users.$inferInsert;`

Basic insert syntax

To insert a row in Drizzle ORM, use `await db.insert(users).values({ name: 'Andrew' })`. This generates the SQL `insert into "users" ("id", "name", "age") values (default, 'Andrew', default)`.

INNER JOIN syntax and return type

Use db.select().from(users).innerJoin(pets, eq(users.id, pets.ownerId)) to perform an INNER JOIN. Neither table's fields are nullable in the result type. Example generates SQL select ... from "users" inner join "pets" on "users"."id" = "pets"."owner_id" and returns { users: {...}; pets: {...} }[].

RIGHT JOIN syntax and return type

Use db.select().from(users).rightJoin(pets, eq(users.id, pets.ownerId)) to perform a RIGHT JOIN. The left table fields are nullable in the result type. Example generates SQL select ... from "users" right join "pets" on "users"."id" = "pets"."owner_id" and returns { users: {...} | null; pets: {...} }[].

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

JOIN types available in Drizzle

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

Give your agent this brain