query-builder

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

Conditional column selection with spread operator

To conditionally include columns in a select statement, use the spread operator with a ternary or logical AND expression: `await db.select({ id: posts.id, ...(withTitle && { title: posts.title }) }).from(posts);`. This allows columns to be optionally included based on a condition.

getColumns() utility function

The `getColumns()` utility function from 'drizzle-orm' returns an object containing all columns from a table as column references. It can be spread into select objects to include all columns, and can be destructured to exclude specific columns.

Benefits of limit/offset pagination

Benefits of limit/offset pagination include simplicity of implementation and easy page reachability, allowing navigation to any page without saving previous page state.

Deferred join technique for pagination performance

Improve limit/offset pagination performance using the deferred join technique, which performs pagination on a subset of data instead of the entire table. Create a subquery selecting only the id column with pagination applied, then inner join back to the full table.

Dynamic pagination with custom function

Create a custom pagination function using $dynamic() method. Example: function withPagination<T extends PgSelect>(qb: T, orderByColumn: PgColumn | SQL | SQL.Aliased, page = 1, pageSize = 3) { return qb.orderBy(orderByColumn).limit(pageSize).offset((page - 1) * pageSize); }

Drawbacks of limit/offset pagination

Drawbacks of limit/offset pagination include degradation in query performance with increasing offset because the database must scan all rows before the offset to skip them, and inconsistency due to data shifts, which can lead to the same row being returned on different pages or rows being skipped.

Data inconsistency in limit/offset pagination

When rows are deleted or inserted during pagination, offset-based pagination can skip rows or show the same row multiple times on different pages. If a row is deleted before the current offset position, the next page will skip a row that should have been included.

When to use cursor-based pagination instead

If your database experiences frequent insert and delete operations in real time or if you need high performance to paginate large tables, consider using cursor-based pagination instead of limit/offset pagination.

Basic limit/offset pagination syntax for PostgreSQL, MySQL, SQLite, and CockroachDB

To implement limit/offset pagination in PostgreSQL, MySQL, SQLite, or CockroachDB, use the orderBy clause followed by limit and offset methods. Order by is mandatory. The limit method specifies the number of rows to return (page size), and the offset method specifies the number of rows to skip. Example: db.select().from(users).orderBy(asc(users.id)).limit(4).offset(4);

Limit/offset pagination support across databases

Limit/offset pagination is supported in PostgreSQL, MySQL, SQLite, MSSQL, and CockroachDB.

Limit/offset pagination formula

Limit is the number of rows to return (page size) and offset is the number of rows to skip, calculated as ((page number - 1) * page size).

Multi-column ordering for pagination

To order by multiple columns in pagination, pass multiple columns to orderBy. Example: .orderBy(asc(users.firstName), asc(users.id)) orders first by firstName (non-unique) then by id (unique primary key).

Ensure unique column ordering for consistent pagination

For consistent pagination, order by a unique column. If ordering by a non-unique column, also append a unique column to the ordering to prevent inconsistent results.

Deferred join example

Example of deferred join pagination: const sq = db.select({ id: users.id }).from(users).orderBy(users.id).limit(pageSize).offset((page - 1) * pageSize).as('subquery'); await db.select().from(users).innerJoin(sq, eq(users.id, sq.id)).orderBy(users.id);

Select parent rows with children: exists() returns only parent data

Using `exists()` in a where clause returns only the parent row data without any child data. It is useful when you only need to identify which parents have at least one child, without needing the child details.

Select parent rows with at least one related child row using innerJoin

To select parent rows with at least one related child row and retrieve both parent and child data, use the `.innerJoin()` method. The query filters out parent rows that have no related children. Example: `db.select({ user: users, post: posts }).from(users).innerJoin(posts, eq(users.id, posts.userId))` retrieves all users who have at least one post, with their corresponding posts included in the result set.

Select only parent rows with at least one related child using exists()

To select only parent rows without their child data, but with the condition that they have at least one related child row, use a subquery with the `exists()` function. Example: `db.select().from(users).where(exists(sq))` where `sq` is a subquery `db.select({ id: sql\`1\` }).from(posts).where(eq(posts.userId, users.id))`. This returns only parent rows that have at least one child, excluding the child data itself.

innerJoin filters out parent rows with no related children

When using `.innerJoin()` between parent and child tables, the result set automatically excludes parent rows that have no related children. For example, a user with no posts will not appear in the result set.

exists() function filters parent rows based on child existence

The `exists()` function evaluates a subquery and returns rows where the subquery returns at least one result. This is useful for filtering parent rows based on whether related children exist, without including the child data in the result set.

Select parent rows with children: innerJoin returns parent-child pairs

Using `.innerJoin()` returns a result set where each row contains both parent and child data. If a parent has multiple children, it will appear multiple times in the result set, once for each child.

Toggle boolean column with not() operator

To toggle a boolean column value, use the update().set() method with the not() operator from drizzle-orm. Example: await db.update(table).set({ isActive: not(table.isActive) }).where(eq(table.id, 1)). This generates SQL: update "table" set "is_active" = not "is_active" where "id" = 1.

Update many with different values supported databases

Update many with different values for each row is supported on PostgreSQL, MySQL, SQLite, MSSQL, and CockroachDB.

Update many with different values example

import { SQL, inArray, sql } from 'drizzle-orm'; import { users } from './schema'; const db = drizzle(...); const inputs = [ { id: 1, city: 'New York', }, { id: 2, city: 'Los Angeles', }, { id: 3, city: 'Chicago', }, ]; if (inputs.length === 0) { return; } const sqlChunks: SQL[] = []; const ids: number[] = []; sqlChunks.push(sql`(case`); for (const input of inputs) { sqlChunks.push(sql`when ${users.id} = ${input.id} then ${input.city}`); ids.push(input.id); } sqlChunks.push(sql`end)`); const finalSql: SQL = sql.join(sqlChunks, sql.raw(' ')); await db.update(users).set({ city: finalSql }).where(inArray(users.id, ids));

Update many rows with different values using sql case statement

To update multiple rows with different values in a single request, use the sql operator with a case statement in the .update().set() methods. Build an array of SQL chunks containing case/when/then conditions for each row, then join them and pass to the set() method with a where clause filtering by the affected ids. The inputs array must be checked for emptiness before processing.

Query users by lowercase email across databases

To query users by email with case-insensitive comparison across PostgreSQL, MySQL, and SQLite, use the pattern: db.select().from(users).where(eq(lower(users.email), email.toLowerCase())). The lower function must be imported from the schema file where it was defined. This approach works consistently across all three database systems.

Cosine distance function for vector similarity

The `cosineDistance` function is imported from 'drizzle-orm' and calculates the distance between two vectors. It can be used with the sql operator to compute similarity scores: `sql<number>\`1 - (${cosineDistance(guides.embedding, embedding)})\`` computes a similarity score between 0 and 1, where 1 means identical vectors.

Vector similarity search query example

Example of finding similar guides by vector embedding: ```ts import { cosineDistance, desc, gt, sql } from 'drizzle-orm'; import { generateEmbedding } from './embedding'; import { guides } from './schema'; const findSimilarGuides = async (description: string) => { const embedding = await generateEmbedding(description); const similarity = sql<number>\`1 - (${cosineDistance(guides.embedding, embedding)})\`; const similarGuides = await db .select({ name: guides.title, url: guides.url, similarity }) .from(guides) .where(gt(similarity, 0.5)) .orderBy((t) => desc(t.similarity)) .limit(4); return similarGuides; }; ``` This query generates an embedding for a description, calculates similarity scores against stored embeddings, filters results with similarity above 0.5, and returns the top 4 most similar guides ordered by similarity in descending order.

excluded keyword for upsert in PostgreSQL, Cockroach, and SQLite

The excluded keyword is a special reference that refers to the row that was proposed for insertion but was not inserted due to a conflict. It can be used with the sql operator to update columns to their proposed values.

Multiple row upsert with excluded in PostgreSQL, Cockroach, and SQLite

Example showing multiple row upsert using the excluded keyword: ```ts import { sql } from 'drizzle-orm'; import { users } from './schema'; const values = [ { id: 1, lastLogin: new Date(), }, { id: 2, lastLogin: new Date(Date.now() + 1000 * 60 * 60), }, { id: 3, lastLogin: new Date(Date.now() + 1000 * 60 * 120), }, ]; await db .insert(users) .values(values) .onConflictDoUpdate({ target: users.id, set: { lastLogin: sql.raw(`excluded.${users.lastLogin.name}`) }, }); ``` This produces the SQL: ```sql insert into users ("id", "last_login") values (1, '2024-03-15T22:29:06.679Z'), (2, '2024-03-15T23:29:06.679Z'), (3, '2024-03-16T00:29:06.679Z') on conflict ("id") do update set last_login = excluded.last_login; ```

Custom function for updating multiple columns on conflict in PostgreSQL, Cockroach, and SQLite

Example showing a custom buildConflictUpdateColumns function for updating specific columns on conflict: ```ts import { SQL, getColumns, sql } from 'drizzle-orm'; import { PgTable } from 'drizzle-orm/pg-core'; import { SQLiteTable } from 'drizzle-orm/sqlite-core'; import { CockroachTable } from "drizzle-orm/cockroach-core"; import { users } from './schema'; const buildConflictUpdateColumns = < T extends PgTable | CockroachTable | SQLiteTable, Q extends keyof T['_']['columns'] >( table: T, columns: Q[], ) => { const cls = getColumns(table); return columns.reduce((acc, column) => { const colName = cls[column].name; acc[column] = sql.raw(`excluded.${colName}`); return acc; }, {} as Record<Q, SQL>); }; const values = [ { id: 1, lastLogin: new Date(), active: true, }, { id: 2, lastLogin: new Date(Date.now() + 1000 * 60 * 60), active: true, }, { id: 3, lastLogin: new Date(Date.now() + 1000 * 60 * 120), active: true, }, ]; await db .insert(users) .values(values) .onConflictDoUpdate({ target: users.id, set: buildConflictUpdateColumns(users, ['lastLogin', 'active']), }); ```

values() function for MySQL upsert

The values() function in MySQL refers to the value of a column that would be inserted if a duplicate-key conflict had not occurred. It is used with the sql operator.

Multiple row upsert with values() in MySQL

Example showing multiple row upsert using the values() function in MySQL: ```ts import { sql } from 'drizzle-orm'; import { users } from './schema'; const values = [ { id: 1, lastLogin: new Date(), }, { id: 2, lastLogin: new Date(Date.now() + 1000 * 60 * 60), }, { id: 3, lastLogin: new Date(Date.now() + 1000 * 60 * 120), }, ]; await db .insert(users) .values(values) .onDuplicateKeyUpdate({ set: { lastLogin: sql`values(${users.lastLogin})`, }, }); ``` This produces the SQL: ```sql insert into users (`id`, `last_login`) values (1, '2024-03-15 23:08:27.025'), (2, '2024-03-15 00:08:27.025'), (3, '2024-03-15 01:08:27.025') on duplicate key update last_login = values(last_login); ```

Custom function for updating multiple columns on conflict in MySQL

Example showing a custom buildConflictUpdateColumns function for updating specific columns on conflict in MySQL: ```ts import { SQL, getColumns, sql } from 'drizzle-orm'; import { MySqlTable } from 'drizzle-orm/mysql-core'; import { users } from './schema'; const buildConflictUpdateColumns = <T extends MySqlTable, Q extends keyof T['_']['columns']>( table: T, columns: Q[], ) => { const cls = getColumns(table); return columns.reduce((acc, column) => { acc[column] = sql`values(${cls[column]})`; return acc; }, {} as Record<Q, SQL>); }; const values = [ { id: 1, lastLogin: new Date(), active: true, }, { id: 2, lastLogin: new Date(Date.now() + 1000 * 60 * 60), active: true, }, { id: 3, lastLogin: new Date(Date.now() + 1000 * 60 * 120), active: true, }, ]; await db .insert(users) .values(values) .onDuplicateKeyUpdate({ set: buildConflictUpdateColumns(users, ['lastLogin', 'active']), }); ``` This produces the SQL: ```sql insert into users (`id`, `last_login`, `active`) values (1, '2024-03-16 15:23:28.013', true), (2, '2024-03-16 16:23:28.013', true), (3, '2024-03-16 17:23:28.013', true) on duplicate key update last_login = values(last_login), active = values(active); ```

Preserve existing column value on upsert in MySQL

To update all columns except a specific one in MySQL, use sql to reference the existing column value: ```ts import { sql } from 'drizzle-orm'; import { users } from './schema'; const data = { id: 1, name: 'John', email: 'john@email.com', age: 29, }; await db .insert(users) .values(data) .onDuplicateKeyUpdate({ set: { ...data, email: sql`${users.email}` }, }); ``` This produces the SQL: ```sql insert into users (`id`, `name`, `email`, `age`) values (1, 'John', 'john@email.com', 29) on duplicate key update id = 1, name = 'John', email = email, age = 29; ```

MySQL upsert with onDuplicateKeyUpdate

Use the .onDuplicateKeyUpdate() method to implement upsert queries in MySQL. MySQL automatically determines the conflict target based on the primary key and unique indexes.

MySQL upsert single row example

Example showing single row upsert in MySQL: ```ts await db .insert(users) .values({ id: 1, name: 'John' }) .onDuplicateKeyUpdate({ set: { name: 'Super John' } }); ``` This produces the SQL: ```sql insert into users (`id`, `first_name`) values (1, 'John') on duplicate key update first_name = 'Super John'; ```

Preserve existing column value on upsert in PostgreSQL, Cockroach, and SQLite

To update all columns except a specific one, use sql to reference the existing column value: ```ts import { sql } from 'drizzle-orm'; import { users } from './schema'; const data = { id: 1, name: 'John', email: 'john@email.com', age: 29, }; await db .insert(users) .values(data) .onConflictDoUpdate({ target: users.id, set: { ...data, email: sql`${users.email}` }, }); ``` This produces the SQL: ```sql insert into users ("id", "name", "email", "age") values (1, 'John', 'john@email.com', 29) on conflict ("id") do update set id = 1, name = 'John', email = email, age = 29; ```

setWhere clause for conditional upsert in PostgreSQL, Cockroach, and SQLite

The setWhere property can be used in onConflictDoUpdate to add a WHERE clause that controls when the update occurs. Example: ```ts import { or, sql } from 'drizzle-orm'; import { products } from './schema'; const data = { id: 1, title: 'Phone', price: '999.99', stock: 10, lastUpdated: new Date(), }; const excludedPrice = sql.raw(`excluded.${products.price.name}`); const excludedStock = sql.raw(`excluded.${products.stock.name}`); await db .insert(products) .values(data) .onConflictDoUpdate({ target: products.id, set: { price: excludedPrice, stock: excludedStock, lastUpdated: sql.raw(`excluded.${products.lastUpdated.name}`) }, setWhere: or( sql`${products.stock} != ${excludedStock}`, sql`${products.price} != ${excludedPrice}` ), }); ``` This produces the SQL: ```sql insert into products ("id", "title", "stock", "price", "last_updated") values (1, 'Phone', 10, '999.99', '2024-04-29T21:56:55.563Z') on conflict ("id") do update set stock = excluded.stock, price = excluded.price, last_updated = excluded.last_updated where (stock != excluded.stock or price != excluded.price); ```

Composite primary key upsert in PostgreSQL, Cockroach, and SQLite

Example showing upsert with multiple target columns (composite primary key): ```ts import { sql } from 'drizzle-orm'; import { inventory } from './schema'; await db .insert(inventory) .values({ warehouseId: 1, productId: 1, quantity: 100 }) .onConflictDoUpdate({ target: [inventory.warehouseId, inventory.productId], set: { quantity: sql`${inventory.quantity} + 100` }, }); ``` This produces the SQL: ```sql insert into inventory ("warehouse_id", "product_id", "quantity") values (1, 1, 100) on conflict ("warehouse_id","product_id") do update set quantity = quantity + 100; ```

Upsert support across databases

Upsert queries are supported in PostgreSQL, MySQL, SQLite, and Cockroach. MSSQL does not support the ON CONFLICT DO UPDATE syntax.

PostgreSQL, Cockroach, and SQLite upsert with onConflictDoUpdate

Use the .onConflictDoUpdate() method to implement upsert queries in PostgreSQL, Cockroach, and SQLite. The method accepts an object with target (the column(s) to check for conflicts) and set (the columns to update).

PostgreSQL, Cockroach, and SQLite upsert single row example

Example showing single row upsert: ```ts import { users } from './schema'; const db = drizzle(...); await db .insert(users) .values({ id: 1, name: 'John' }) .onConflictDoUpdate({ target: users.id, set: { name: 'Super John' }, }); ``` This produces the SQL: ```sql insert into users ("id", "name") values (1, 'John') on conflict ("id") do update set name = 'Super John'; ```

SQL-like syntax INSERT example

To insert a row 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').

SQL-like syntax DELETE example

To delete rows using SQL-like syntax, use: await db.delete(users).where(eq(users.id, 1)). This generates the SQL: DELETE FROM [users] WHERE [users].[id] = 1.

Drizzle SQL-like queries embrace SQL semantics

Drizzle's SQL-like syntax is designed to be familiar to those who know SQL. Unlike other ORMs that abstract away SQL, Drizzle is SQL-like at its core, minimizing the learning curve and providing full access to SQL power.

SQL-like queries support diverse operations

With SQL-like syntax, you can perform select, insert, update, delete, as well as using aliases, WITH clauses, subqueries, prepared statements, and more.

Compose WHERE filters independently

Drizzle allows composing WHERE statement filters independently from the main query. You can build an array of SQL filter conditions conditionally and then pass them to the query using the and() function, such as: return db.select().from(products).where(and(...filters));

Separate subqueries into variables

Drizzle allows partitioning queries by separating subqueries into different variables before using them in the main query. You can build a subquery with .as('alias'), then use it in the main query with leftJoin or other operations.

Drizzle queries can be composed and partitioned

With Drizzle, queries can be composed and partitioned in any way needed. You can compose filters independently from the main query, separate subqueries or conditional statements, and much more.

SQL-like query syntax for SELECT

Drizzle supports SQL-like syntax for querying. A SELECT query with a LEFT JOIN and WHERE clause can be written as: 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 UPDATE example

To update rows using SQL-like syntax, 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.

Generic dynamic query builders support type transformation

Generic query builder types like MsSqlSelect in dynamic mode allow you to modify the result type of the query builder inside helper functions, for example by adding a join operation.

Enable dynamic mode with $dynamic() method

To build queries dynamically and allow multiple invocations of the same method, call .$dynamic() on a query builder. This enables a special 'dynamic' mode that removes the restriction of invoking methods only once.

Raw SQL queries with db.execute

Use db.execute() with parameterized SQL for raw queries. Example: const result = await db.execute(sql`select * from ${users} where ${users.id} = ${userId}`);

Print SQL query with toSQL()

Call .toSQL() on a query to get the generated SQL statement. Example: db.select({ id: users.id, name: users.name }).from(users).groupBy(users.id).toSQL()

getColumns for typed column exclusion

Use getColumns(table) to get a typed column map object. Destructure to exclude specific fields, then spread the rest into a select query. Example: const { password, role, ...rest } = getColumns(users); await db.select({ ...rest }).from(users);

Standalone Query Builder for MSSQL

Create a QueryBuilder instance without a database connection: new QueryBuilder(). Build queries with select(), from(), and other methods, then use .toSQL() to get { sql, params }.

ne operator - not equal comparison

The ne operator checks if a value is not equal to another value. It can compare a column to a literal value or compare two columns. Example: ne(table.column, 5) generates WHERE [table].[column] <> 5. When comparing columns: ne(table.column1, table.column2) generates WHERE [table].[column1] <> [table].[column2].

eq operator - equality comparison

The eq operator checks if a value equals another value. It can compare a column to a literal value or compare two columns. Example: eq(table.column, 5) generates WHERE [table].[column] = 5. When comparing columns: eq(table.column1, table.column2) generates WHERE [table].[column1] = [table].[column2].

Filter operators parameterization

All values provided to filter operators and to the sql function are parameterized automatically. For example, db.select().from(users).where(eq(users.id, 42)) is translated to select [id], [name], [age] from [users] where [users].[id] = @par0 with params: [42]. This prevents SQL injection.

Give your agent this brain