query-builder

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

CockroachDB update example with FROM clause

Example: Update users table setting cityId from cities table based on city name. Code: `await db.update(users).set({ cityId: cities.id }).from(cities).where(and(eq(cities.name, 'Seattle'), eq(users.name, 'John')));`

CockroachDB update example with returning

Example: Update user name and return the updated ID. Code: `const updatedUserId = await db.update(users).set({ name: "Mr. Dan" }).where(eq(users.name, "Dan")).returning({ updatedId: users.id });`

Delete query example

Delete example: await db.delete(users).where(eq(users.id, 1)) generates DELETE FROM users WHERE users.id = 1

Subquery variable example

Example of subquery in variable: 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));

Compose filters example

Example of independently composed filters: 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));

Update query example

Update example: await db.update(users).set({ email: 'user@gmail.com' }).where(eq(users.id, 1)) generates UPDATE users SET email = 'user@gmail.com' WHERE users.id = 1

Insert query example

Insert example: await db.insert(users).values({ email: 'user@gmail.com' }) generates INSERT INTO users (email) VALUES ('user@gmail.com')

Select with left join example

Example of SQL-like select query with left join: await db.select().from(posts).leftJoin(comments, eq(posts.id, comments.post_id)).where(eq(posts.id, 10)) generates SELECT * FROM posts LEFT JOIN comments ON posts.id = comments.post_id WHERE posts.id = 10

SQL-like syntax for querying databases

Drizzle provides SQL-like syntax for querying databases. If you know SQL, you can use Drizzle with minimal learning curve. The syntax is designed to replicate SQL closely so users know exactly what query will be generated. It supports select, insert, update, delete, aliases, WITH clauses, subqueries, prepared statements, and more.

D1 database operations example

Example showing insert, select, update, and delete operations on Cloudflare D1. The code demonstrates inserting a user object inferred from schema, selecting all users, updating specific user fields with a where clause, and deleting records. Uses eq() from drizzle-orm for filtering.

Query performance in Cloudflare Workers with Durable Objects

For maximum performance, bundle all database interactions within a single Durable Object method call, since database access is fast within a Durable Object instance. Each individual query exposure is a round-trip to the Durable Object instance, making it slower for debugging or multiple operations.

Effect PostgreSQL CRUD operations example

Example showing how to perform insert, select, update, and delete operations with Effect PostgreSQL. Insert uses db.insert(usersTable).values(user), select uses db.select().from(usersTable), update uses db.update(usersTable).set({...}).where(...), and delete uses db.delete(usersTable).where(...). All operations are yielded in an Effect.gen function.

Gel query example with insert, select, update, delete

Example of Gel database operations with Drizzle ORM: insert values with db.insert(users).values(user), select all with db.select().from(users), update with db.update(users).set({age: 31}).where(eq(users.email, user.email)), and delete with db.delete(users).where(eq(users.email, user.email)).

Query database operations in Expo with Drizzle

Use standard Drizzle query methods in Expo: db.delete(table), db.insert(table).values([...]), and db.select().from(table). These can be called within useEffect or other async contexts after migrations complete successfully.

Gel query operations with Drizzle

Drizzle ORM supports insert, select, update, and delete operations on Gel databases. Insert uses db.insert(table).values(object), select uses db.select().from(table), update uses db.update(table).set(values).where(condition), and delete uses db.delete(table).where(condition). The eq operator from drizzle-orm can be used in where clauses.

Supabase query builder driver

When querying a Supabase database with Drizzle ORM, use 'postgres-js' as the driver dialect.

SQLite Cloud basic operations example

Example showing insert, select, update, and delete operations with SQLite Cloud: ```typescript import 'dotenv/config'; import { eq } from 'drizzle-orm'; import { drizzle } from 'drizzle-orm/sqlite-cloud'; import { usersTable } from './db/schema'; async function main() { const db = drizzle(); const user: typeof usersTable.$inferInsert = { name: 'John', age: 30, email: 'john@example.com', }; await db.insert(usersTable).values(user); console.log('New user created!') const users = await db.select().from(usersTable); console.log('Getting all users from the database: ', users) await db .update(usersTable) .set({ age: 31, }) .where(eq(usersTable.email, user.email)); console.log('User info updated!') await db.delete(usersTable).where(eq(usersTable.email, user.email)); console.log('User deleted!') } main(); ```

TiDB setup step 7 query database

The seventh step is to query the TiDB database using tidb-serverless dialect and DATABASE_URL environment variable.

TiDB setup step 11 query with new field optional

The eleventh step is to query the TiDB database with a new field using tidb-serverless dialect and DATABASE_URL environment variable, which is optional.

Query builder select example with Turso

Example of selecting all records from Turso database: const users = await db.select().from(usersTable);

Query builder delete example with Turso

Example of deleting records from Turso database: await db.delete(usersTable).where(eq(usersTable.email, user.email));

Query builder update example with Turso

Example of updating records in Turso database using eq condition: await db.update(usersTable).set({age: 31}).where(eq(usersTable.email, user.email));

Query builder insert example with Turso

Example of inserting a user record into Turso database: await db.insert(usersTable).values({name: 'John', age: 30, email: 'john@example.com'});

Query PlanetScale MySQL with insert, select, update, delete

Example of querying PlanetScale using Drizzle ORM: import { eq } from 'drizzle-orm'; import { drizzle } from 'drizzle-orm/planetscale-serverless'; Create a db instance with connection config. Use db.insert(table).values(object) for inserts, db.select().from(table) for queries, db.update(table).set(fields).where(condition) for updates, and db.delete(table).where(condition) for deletes. The where clause uses eq() for equality comparison.

Turso Database basic CRUD operations example

Example of inserting, selecting, updating, and deleting records with Drizzle ORM on Turso Database: ```typescript import 'dotenv/config'; import { eq } from 'drizzle-orm'; import { drizzle } from 'drizzle-orm/tursodatabase/database'; import { usersTable } from './db/schema'; async function main() { const db = drizzle(); const user: typeof usersTable.$inferInsert = { name: 'John', age: 30, email: 'john@example.com', }; await db.insert(usersTable).values(user); console.log('New user created!') const users = await db.select().from(usersTable); console.log('Getting all users from the database: ', users) await db .update(usersTable) .set({ age: 31, }) .where(eq(usersTable.email, user.email)); console.log('User info updated!') await db.delete(usersTable).where(eq(usersTable.email, user.email)); console.log('User deleted!') } main(); ```

Basic Drizzle ORM operations example with Vercel Postgres

Example showing insert, select, update, and delete operations using Drizzle ORM with Vercel Postgres: ```typescript import 'dotenv/config'; import { eq } from 'drizzle-orm'; import { drizzle } from 'drizzle-orm/vercel-postgres'; import { usersTable } from './db/schema'; async function main() { const db = drizzle(); const user: typeof usersTable.$inferInsert = { name: 'John', age: 30, email: 'john@example.com', }; await db.insert(usersTable).values(user); console.log('New user created!') const users = await db.select().from(usersTable); console.log('Getting all users from the database: ', users) await db .update(usersTable) .set({ age: 31, }) .where(eq(usersTable.email, user.email)); console.log('User info updated!') await db.delete(usersTable).where(eq(usersTable.email, user.email)); console.log('User deleted!') } main(); ```

Conditional filters with .where() and undefined

To pass a conditional filter in a query, use the .where() method with a ternary operator that returns undefined when the condition is not met. For example: .where(term ? ilike(posts.title, term) : undefined). When undefined is passed, no filter is applied to the query.

Conditional filters supported across all databases

Conditional filters in queries are supported across all Drizzle-supported databases: PostgreSQL, MySQL, SQLite, MSSQL, and CockroachDB.

Create custom filter operators with sql template

Create custom filter operators by defining functions that accept columns and values, then return sql expressions. For example: const lenlt = (column: AnyColumn, value: number) => { return sql`length(${column}) < ${value}`; }. Import AnyColumn from drizzle-orm for type safety. Use the custom operator in .where() just like built-in operators.

Combine multiple conditional filters with and() operator

Use the and() operator to combine multiple conditional filters in a query. Pass ternary expressions that return undefined when conditions are not met: and(term ? ilike(posts.title, term) : undefined, categories.length > 0 ? inArray(posts.category, categories) : undefined, views > 100 ? gt(posts.views, views) : undefined). The and() operator handles undefined values gracefully.

Combine multiple conditional filters with or() operator

Use the or() operator to combine multiple conditional filters with OR logic. Similar to and(), the or() operator accepts conditional expressions with undefined values for filters that should not be applied.

Build dynamic filters in an SQL array

Create a variable of type SQL[] to accumulate filters dynamically. Push filter expressions to the array using methods like push(ilike(posts.title, 'AI')). Pass the array to .where() with the spread operator: .where(and(...filters)). This pattern allows combining conditional filters in different parts of the project.

Drizzle filter operators are SQL expressions

Drizzle filter operators are implemented as SQL expressions under the hood. For example, the lt operator is implemented as: const lt = (left, right) => { return sql`${left} < ${bindIfParam(right, left)}`; }. This means you can create similar custom operators using the sql template tag.

count() returns bigint as string in PostgreSQL, MySQL, and Cockroach

In PostgreSQL, MySQL, and Cockroach databases, the count() function returns bigint, which is interpreted as a string by their drivers. You should cast the result to integer for proper type handling. In MySQL, cast to unsigned integer instead.

count() returns integer directly in SQLite and MSSQL

In SQLite and MSSQL databases, the count() result is returned directly as an integer without requiring type casting.

sql operator alternative to count() function

You can use the sql operator as an alternative to count(): await db.select({ count: sql`count(*)`.mapWith(Number) }).from(products); This generates the same SQL as count() but allows explicit runtime type transformation using .mapWith(Number).

count() with joins and grouping for aggregate queries

Use count() with joins and groupBy() to count related rows. Example: await db.select({ country: countries.name, citiesCount: count(cities.id) }).from(countries).leftJoin(cities, eq(countries.id, cities.countryId)).groupBy(countries.id).orderBy(countries.name); This counts cities per country.

count rows supported on all databases

The count rows functionality is supported on PostgreSQL, MySQL, SQLite, MSSQL, and Cockroach databases.

Custom count function with runtime type casting

Create a custom count function that casts the result to integer for PostgreSQL, MySQL, and Cockroach: const customCount = (column?: AnyColumn) => { if (column) { return sql<number>`cast(count(${column}) as integer)`; } else { return sql<number>`cast(count(*) as integer)`; } };

Create custom decrement function for reusable column updates

Define a custom decrement function that accepts an AnyColumn type and an optional value parameter (defaulting to 1). The function returns an sql expression that subtracts the value from the column. Example: const decrement = (column: AnyColumn, value = 1) => { return sql`${column} - ${value}`; }; Then use it in update operations: await db.update(table).set({ counter1: decrement(table.counter1), counter2: decrement(table.counter2, 10), }).where(eq(table.id, 1));

Decrement column value with update().set() and sql operator

To decrement a column value in an update statement, use the update().set() method with the sql operator. Pass an expression that subtracts the desired amount from the column. For example, to decrement a counter by 1: await db.update(table).set({ counter: sql`${table.counter} - 1`, }).where(eq(table.id, 1)); This generates: update "table" set "counter" = "counter" - 1 where "id" = 1;

Decrement supported across all major databases

The decrement pattern using sql operator is supported on PostgreSQL, MySQL, SQLite, MSSQL, and CockroachDB.

Benefits of cursor-based pagination

Cursor-based pagination provides consistent query results with no skipped or duplicated rows due to insert or delete operations. It is more efficient than limit/offset pagination because it does not need to scan and skip previous rows to access the next page.

Cursor-based pagination basic example with query builder

To implement cursor-based pagination with Drizzle's query builder, use the gt() operator to filter rows after the cursor, limit() to set page size, and orderBy() for consistent ordering. Example: await db.select().from(users).where(cursor ? gt(users.id, cursor) : undefined).limit(pageSize).orderBy(asc(users.id)). The cursor should be the id of the last row from the previous page.

Cursor-based pagination with dynamic order direction

To support both ascending and descending order in cursor-based pagination, conditionally apply cursor comparison operators: use gt() for ascending order and lt() for descending order. Example: .where(cursor ? (order === 'asc' ? gt(users.id, cursor) : lt(users.id, cursor)) : undefined).orderBy(order === 'asc' ? asc(users.id) : desc(users.id)).

Cursor-based pagination with multiple columns

For ordering by non-unique and non-sequential columns, use multiple columns in the cursor object. Use or() to check if the primary column is greater, or if equal then check if the secondary column is greater. Example: .where(cursor ? or(gt(users.firstName, cursor.firstName), and(eq(users.firstName, cursor.firstName), gt(users.id, cursor.id))) : undefined).orderBy(asc(users.firstName), asc(users.id)). Pass cursor as an object with all ordering columns like { id: 2, firstName: 'Alex' }.

Cursor-based pagination with non-sequential primary key

When using non-sequential primary keys like UUIDv4, add a sequential column (such as created_at) and use multiple columns in the cursor. Compare the sequential column first, then the primary key as tiebreaker: or(gt(users.createdAt, cursor.createdAt), and(eq(users.createdAt, cursor.createdAt), gt(users.id, cursor.id))). Order by both columns: orderBy(asc(users.createdAt), asc(users.id)).

Cursor-based pagination requirements

For correct cursor-based pagination ordering and cursor comparison, the cursor column must be unique and sequential. Without these properties, use multiple columns for cursor comparison to ensure consistent pagination results.

Drawbacks of cursor-based pagination

Cursor-based pagination has limitations: inability to directly navigate to a specific page and increased complexity of implementation, especially when adding more columns to the sort order which requires more filters in the where clause.

Cursor-based pagination supported databases

Cursor-based pagination is supported in Drizzle ORM on PostgreSQL, MySQL, SQLite, MSSQL, and CockroachDB.

Query full-text search using @@ operator

Use the @@ operator with sql template to query a tsvector column. The query pattern is: sql`${posts.searchColumn} @@ to_tsquery('language', ${searchTerm})`. The to_tsquery function parses the search term into a query format.

Example: Full-text search query with @@ operator

This example queries the posts table for rows where the bodySearch column matches a search term using PostgreSQL's @@ operator and to_tsquery function.

Create custom increment function with AnyColumn type

Import AnyColumn from 'drizzle-orm' to create a reusable increment function. Define a function like const increment = (column: AnyColumn, value = 1) => { return sql`${column} + ${value}`; }; Then use it in update().set() to increment one or more columns by custom amounts: update(table).set({ counter1: increment(table.counter1), counter2: increment(table.counter2, 10) }).where(...).

Increment column value supported databases

Incrementing column values with sql expressions is supported on PostgreSQL, MySQL, SQLite, MSSQL, and Cockroach databases.

Increment column value with update().set()

To increment a column value in Drizzle ORM, use the update().set() method with the sql template tag. The syntax is: update(table).set({ columnName: sql`${table.columnName} + 1` }).where(...). This generates SQL like: UPDATE "table" SET "counter" = "counter" + 1 WHERE "id" = 1.

Select all columns with .select()

To include all columns in a query, use the `.select()` method without arguments: `await db.select().from(posts);`. This returns all columns from the table with their proper types.

Select specific columns with .select()

To include only specific columns, pass an object to `.select()` with the columns you want: `await db.select({ title: posts.title }).from(posts);`. The result type includes only the selected columns.

Include all columns plus extras with getColumns()

To select all columns plus additional computed columns, use the `getColumns()` utility function from 'drizzle-orm' combined with spread operator: `await db.select({ ...getColumns(posts), titleLength: sql<number>\`length(${posts.title})\` }).from(posts);`. This includes all table columns and the extra computed column.

Exclude columns with getColumns()

To select all columns except specific ones, use `getColumns()` utility with destructuring to exclude unwanted columns: `const { content, ...rest } = getColumns(posts); await db.select({ ...rest }).from(posts);`. This selects all columns except 'content'.

Include or exclude columns with joins

When using joins, you can selectively include columns from different tables. Exclude columns using destructuring with `getColumns()`, include specific columns by referencing them directly, or include all columns from a table by passing the table reference: `await db.select({ postId: posts.id, comment: { ...rest }, user: users }).from(posts).leftJoin(comments, eq(posts.id, comments.postId)).leftJoin(users, eq(users.id, posts.userId));`

Give your agent this brain