drizzle-orm/queries

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

v2 orderBy is now an object

In v2, orderBy is simplified to a single object specifying the column and sort direction (asc or desc). Example: db.query.users.findMany({ orderBy: { id: 'asc' } }). This replaces v1's: db._query.users.findMany({ orderBy: (users, { asc }) => [asc(users.id)] })

v2 offset on related objects

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

v2 where clause supports AND, OR, NOT, RAW operators

v2 where clauses support AND, OR, NOT, and RAW operators for complex filtering. AND combines multiple conditions (implicit when listing multiple conditions). OR requires an array of conditions. NOT negates a condition. RAW allows raw SQL: { RAW: (table) => sql`...` }

v2 where clause is now an object not a function

In v2, where clauses in queries are objects instead of callback functions. Simple example: db.query.users.findMany({ where: { age: 15 } }). This replaces v1's: db._query.users.findMany({ where: (users, { eq }) => eq(users.id, 1) })

v2 where filtering operators

v2 where clauses support filtering operators including: gt (greater than), lt (less than), gte, lte, like, ilike (case-insensitive like), eq (equals), and others that were available in v1.

v2 filtering by relations

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

INTERSECT ALL not supported by SingleStore

INTERSECT ALL is not supported by SingleStore.

EXCEPT returns rows from first query not in second query omitting duplicates

EXCEPT is a set operation that for two query blocks A and B, returns all results from A which are not also present in B, omitting any duplicates. In Drizzle ORM, it can be used with the import-pattern using the except() function from 'drizzle-orm/singlestore-core', or with the builder-pattern using the .except() method on a select query.

EXCEPT builder-pattern syntax

The builder-pattern for EXCEPT uses .except() method: db.select({ courseName: depA.projectsName }).from(depA).except(db.select({ courseName: depB.projectsName }).from(depB));

EXCEPT import-pattern syntax

The import-pattern for EXCEPT imports the except() function from 'drizzle-orm/singlestore-core' and passes two select queries as arguments: except(db.select({ courseName: depA.projectsName }).from(depA), db.select({ courseName: depB.projectsName }).from(depB));

EXCEPT ALL not supported by SingleStore

EXCEPT ALL is not supported by SingleStore.

SQL set operations supported in Drizzle for SingleStore

Drizzle ORM supports the following SQL set operations for SingleStore: UNION, UNION ALL, INTERSECT, EXCEPT. The operations INTERSECT ALL and EXCEPT ALL are not supported by SingleStore.

UNION combines results from two queries omitting duplicates

UNION is a set operation that combines all results from two query blocks into a single result, omitting any duplicates. In Drizzle ORM, it can be used with the import-pattern using the union() function from 'drizzle-orm/singlestore-core', or with the builder-pattern using the .union() method on a select query.

UNION builder-pattern syntax

The builder-pattern for UNION uses .union() method on a select query: db.select({ name: users.name }).from(users).union(db.select({ name: customers.name }).from(customers)).limit(10);

UNION import-pattern syntax

The import-pattern for UNION imports the union() function from 'drizzle-orm/singlestore-core' and passes two select queries as arguments: union(db.select({ name: users.name }).from(users), db.select({ name: customers.name }).from(customers)).limit(10);

UNION ALL builder-pattern syntax

The builder-pattern for UNION ALL uses .unionAll() method: db.select({ transaction: onlineSales.transactionId }).from(onlineSales).unionAll(db.select({ transaction: inStoreSales.transactionId }).from(inStoreSales));

UNION ALL combines results from two queries including duplicates

UNION ALL is a set operation that combines all results from two query blocks into a single result, retaining any duplicates. In Drizzle ORM, it can be used with the import-pattern using the unionAll() function from 'drizzle-orm/singlestore-core', or with the builder-pattern using the .unionAll() method on a select query.

UNION ALL import-pattern syntax

The import-pattern for UNION ALL imports the unionAll() function from 'drizzle-orm/singlestore-core' and passes two select queries as arguments: unionAll(db.select({ transaction: onlineSales.transactionId }).from(onlineSales), db.select({ transaction: inStoreSales.transactionId }).from(inStoreSales));

UNION ALL with ORDER BY behaves differently in SingleStore than MySQL

SingleStore parses UNION ALL followed by ORDER BY commands differently from MySQL. In SingleStore, queries with UNION ALL followed by ORDER BY are valid, but in MySQL, they are invalid.

INTERSECT combines rows common to both queries omitting duplicates

INTERSECT is a set operation that combines only those rows which the results of two query blocks have in common, omitting any duplicates. In Drizzle ORM, it can be used with the import-pattern using the intersect() function from 'drizzle-orm/singlestore-core', or with the builder-pattern using the .intersect() method on a select query.

INTERSECT builder-pattern syntax

The builder-pattern for INTERSECT uses .intersect() method: db.select({ courseName: depA.courseName }).from(depA).intersect(db.select({ courseName: depB.courseName }).from(depB));

INTERSECT import-pattern syntax

The import-pattern for INTERSECT imports the intersect() function from 'drizzle-orm/singlestore-core' and passes two select queries as arguments: intersect(db.select({ courseName: depA.courseName }).from(depA), db.select({ courseName: depB.courseName }).from(depB));

Select from subquery

Embed queries into other queries using the subquery API with `.as()` to name the subquery: `const sq = db.select().from(users).where(eq(users.id, 42)).as('sq'); const result = await db.select().from(sq)`. Subqueries can be used anywhere a table can be used, including in joins.

Aggregations with groupBy and having

Use aggregation functions like `sum`, `count`, `avg` with `.groupBy()` and `.having()` for filtering aggregated results, same as in raw SQL. Example: `db.select({ age: users.age, count: sql<number>`cast(count(${users.id}) as int)` }).from(users).groupBy(users.age).having(({ count }) => gt(count, 1))`.

Count function returns decimal in SingleStore

In SingleStore, `count()` returns `decimal`, which is treated as a string value instead of a number. Use `cast(... as int)` to convert to an integer or use `.mapWith(Number)` to cast at runtime. For count aggregation, Drizzle recommends using the `$count` API.

Aggregations with GROUP BY requirement

When selecting using aggregating functions and other columns in one query, you must use the `.groupBy()` clause.

count aggregation helper

The `count()` helper function returns the number of values in an expression. Import from `'drizzle-orm'`. Usage: `db.select({ value: count() }).from(users)` (count all rows) or `db.select({ value: count(users.id) }).from(users)` (count specific column). Equivalent to `sql`count(*)`.mapWith(Number)`.

countDistinct aggregation helper

The `countDistinct()` helper returns the number of non-duplicate values in an expression. Import from `'drizzle-orm'`. Usage: `db.select({ value: countDistinct(users.id) }).from(users)`. Generates SQL: `count(distinct `id`)`.

avg aggregation helper

The `avg()` helper returns the average (arithmetic mean) of all non-null values in an expression. Import from `'drizzle-orm'`. Usage: `db.select({ value: avg(users.id) }).from(users)`. Equivalent to `sql`avg(${users.id})`.mapWith(String)`.

avgDistinct aggregation helper

The `avgDistinct()` helper returns the average (arithmetic mean) of all non-null distinct values in an expression. Import from `'drizzle-orm'`. Usage: `db.select({ value: avgDistinct(users.id) }).from(users)`. Generates SQL: `avg(distinct `id`)`.

sum aggregation helper

The `sum()` helper returns the sum of all non-null values in an expression. Import from `'drizzle-orm'`. Usage: `db.select({ value: sum(users.id) }).from(users)`. Equivalent to `sql`sum(${users.id})`.mapWith(String)`.

sumDistinct aggregation helper

The `sumDistinct()` helper returns the sum of all non-null and non-duplicate values in an expression. Import from `'drizzle-orm'`. Usage: `db.select({ value: sumDistinct(users.id) }).from(users)`. Generates SQL: `sum(distinct `id`)`.

min aggregation helper

The `min()` helper returns the minimum value in an expression. Import from `'drizzle-orm'`. Usage: `db.select({ value: min(users.id) }).from(users)`. Equivalent to `sql`min(${users.id})`.mapWith(users.id)`.

max aggregation helper

The `max()` helper returns the maximum value in an expression. Import from `'drizzle-orm'`. Usage: `db.select({ value: max(users.id) }).from(users)`. Equivalent to `sql`max(${users.id})`.mapWith(users.id)`.

Basic select all rows

To select all rows from a table including all columns, use `.select().from(table)`. The result type is inferred automatically based on the table definition, including columns nullability. Drizzle explicitly lists all columns in the SELECT clause instead of using SELECT *, which guarantees field order in the query result.

Partial select with column subset

To select only a subset of columns, provide a selection object to `.select()` with custom field names mapping to table columns. Example: `db.select({ field1: users.id, field2: users.name }).from(users)`.

Select with arbitrary SQL expressions

You can use arbitrary SQL expressions as selection fields, not just table columns. Use the `sql` function with a type generic to specify the expected return type: `db.select({ id: users.id, lowerName: sql<string>`lower(${users.name})` }).from(users)`.

sql type generic correctness requirement

When using `sql<Type>`, you declare the expected type of the field. If you specify it incorrectly (e.g., use `sql<number>` for a field returned as a string), the runtime value won't match the expected type. Drizzle cannot perform type casts based on the type generic because that information is not available at runtime. Use `.mapWith()` method to apply runtime transformations if needed.

Column alias with .as() method

Starting from `v1.0.0-beta.1`, you can use `.as()` on columns to provide an alias: `db.select({ id: users.id, lowerName: users.name.as('lower') }).from(users)`.

Conditional select with spread operator

You can build a dynamic selection object based on conditions using spread operators: `db.select({ id: users.id, ...(withName ? { name: users.name } : {}) }).from(users)`.

Select distinct rows

Use `.selectDistinct()` instead of `.select()` to retrieve only unique rows from a dataset. Can be combined with partial selection and ordering: `db.selectDistinct().from(users).orderBy(users.id, users.name)` or `db.selectDistinct({ id: users.id }).from(users)`.

Filter operators for WHERE clause

Use filter operators like `eq()`, `lt()`, `gte()`, `ne()` in the `.where()` method to filter query results. Example: `db.select().from(users).where(eq(users.id, 42))`. These operators are parameterized automatically, preventing SQL injection.

Custom filter operators using sql function

You can write arbitrary SQL filters using the `sql` function or build custom operators. Example: `db.select().from(users).where(sql`${users.id} < 42`)`. Custom operators can be created as functions: `function equals42(col: Column) { return sql`${col} = 42`; }`.

Invert conditions with not operator

Use the `not()` operator to invert conditions: `db.select().from(users).where(not(eq(users.id, 42)))` or with sql: `db.select().from(users).where(sql`not ${users.id} = 42`)`.

Combine filters with and operator

Logically combine multiple filter conditions using `and()`: `db.select().from(users).where(and(eq(users.id, 42), eq(users.name, 'Dan')))`.

Combine filters with or operator

Logically combine filter conditions with OR logic using `or()`: `db.select().from(users).where(or(eq(users.id, 42), eq(users.name, 'Dan')))`.

Conditional filter in where clause

You can pass `undefined` to `.where()` to conditionally exclude filters: `db.select().from(posts).where(term ? like(posts.title, term) : undefined)`. This allows dynamic filtering based on parameters.

Array-based conditional filters

Build an array of SQL filter conditions and combine them with `and()`: `const filters: SQL[] = []; filters.push(like(posts.title, 'AI')); db.select().from(posts).where(and(...filters))`.

Order by single or multiple fields

Use `.orderBy()` to sort results. Order by a single field: `db.select().from(users).orderBy(users.name)`. Order by multiple fields: `db.select().from(users).orderBy(users.name, users.name2)`. Use `asc()` for ascending (default) and `desc()` for descending: `db.select().from(users).orderBy(asc(users.name), desc(users.name2))`.

Limit and offset pagination

Use `.limit()` and `.offset()` for limit-offset pagination. `.orderBy()` is mandatory for pagination. Example: `db.select().from(users).orderBy(asc(users.id)).limit(4).offset(4)` returns 4 rows starting from row 5.

Relational query pagination with limit and offset

Using the relational query API for pagination: `db.query.users.findMany({ orderBy: (users, { asc }) => asc(users.id), limit: pageSize, offset: (page - 1) * pageSize })`. This returns paginated results using the query builder API.

Cursor-based pagination approach

Implement cursor-based pagination by filtering rows where the cursor column is greater than the previous cursor value: `db.select().from(users).where(cursor ? gt(users.id, cursor) : undefined).limit(pageSize).orderBy(asc(users.id))`. Pass the ID of the last row from the previous page as the cursor.

Common Table Expressions (CTEs) with WITH clause

Use `.with()` to create CTEs and simplify complex queries with subqueries. Create a CTE: `const sq = db.$with('sq').as(db.select().from(users).where(eq(users.id, 42)))`. Use it in the main query: `db.with(sq).select().from(sq)`.

SingleStore does not support RETURNING in WITH

SingleStore doesn't support native RETURNING, so insert, update, and delete statements that depend on returning rows are not available inside WITH for this dialect.

CTE aliases for arbitrary SQL values

When selecting arbitrary SQL values in a CTE, you must add aliases using `.as()`. Example: `const sq = db.$with('sq').as(db.select({ name: sql<string>`upper(${users.name})`.as('name') }).from(users))`. Without an alias, the field type becomes `DrizzleTypeError` and causes a runtime error.

sql.as() to define field aliases

Use sql.as('alias_name') to explicitly specify an alias for a custom field in select queries. Example: sql`lower(usersTable.name)`.as('lower_name') generates ... `usersTable`.`name` as lower_name ...

sql template for type-safe parameterized queries

Drizzle provides the sql template to write type-safe and parameterized queries that prevent SQL injection. The sql template automatically escapes table and column names and converts dynamic parameters to ? placeholders with values passed separately to the database.

sql template basic usage with db.execute

Import sql from 'drizzle-orm' and use it as a template literal. Tables and columns are automatically mapped to escaped SQL syntax, and dynamic parameters like ${id} become ? placeholders with values in an array. Example: await db.execute(sql`select * from ${usersTable} where ${usersTable.id} = ${id}`) generates select * from `users` where `users`.`id` = ?; --> [69]

sql<T> generic type for custom type definition

Use sql<T> to define a custom type for fields that require a specific type other than unknown. This is a compile-time helper for Drizzle and performs no runtime mapping. Example: sql<string>`lower(${usersTable.name})` ensures the response field is typed as string.

sql.mapWith() for runtime value mapping

Use sql.mapWith() to apply runtime mapping for values passed from the database driver to Drizzle. It accepts a column mapping strategy with the same interface as the Column type, or a custom DriverValueDecoder implementation. Example: sql`...`.mapWith(usersTable.name) or sql``.mapWith({mapFromDriverValue: (value: any) => {...}}) or sql``.mapWith(Number)

Give your agent this brain