query-api

314 notes in this subject, read out of this brain and free to use. This is page 6 of 6.

sumDistinct() aggregate function

The sumDistinct() aggregate function calculates the sum of distinct values in a column. Example: await db.select({ value: sumDistinct(users.id) }).from(users);. Equivalent SQL: sql`sum(distinct ${users.id})`.mapWith(String)

max() aggregate function

The max() aggregate function returns the maximum value in a column. Example: await db.select({ value: max(users.id) }).from(users);. Equivalent SQL: sql`max(${users.id})`.mapWith(users.id)

min() aggregate function

The min() aggregate function returns the minimum value in a column. Example: await db.select({ value: min(users.id) }).from(users);. Equivalent SQL: sql`min(${users.id})`.mapWith(users.id)

Aggregate functions require GROUP BY with other columns

When selecting using aggregating functions together with other columns in one query, the .groupBy clause must be used.

Example: WITH DELETE using CTE

Example showing how to use WITH clause with DELETE: define a CTE with db.$with('average_amount').as(), then use .with(averageAmount).delete(orders).where() to delete rows based on the CTE result. The resulting SQL uses WITH clause to define the CTE before the DELETE statement.

.if() function for conditional WHERE expressions

The .if() function can be added to WHERE expressions to conditionally apply filters. In the example, a greater-than comparison gt(posts.views, views).if(views > 100) applies the filter only when views is greater than 100.

onConflictDoUpdate splitWhere into targetWhere and setWhere

In Drizzle ORM v0.30.8, the Postgres .onConflictDoUpdate method was updated to split the where clause into targetWhere and setWhere clauses. The targetWhere clause is used in the ON CONFLICT condition, while setWhere is used in the SET clause to conditionally update values. This supports both where cases in the on conflict clause.

onConflictDoUpdate example with targetWhere clause

Example of using targetWhere in .onConflictDoUpdate to conditionally apply conflict resolution based on a target condition: await db.insert(employees).values({ employeeId: 123, name: 'John Doe' }).onConflictDoUpdate({ target: employees.employeeId, targetWhere: sql`name <> 'John Doe'`, set: { name: sql`excluded.name` } });

onConflictDoUpdate example with setWhere clause

Example of using setWhere in .onConflictDoUpdate to conditionally update values only when a set condition is met: await db.insert(employees).values({ employeeId: 123, name: 'John Doe' }).onConflictDoUpdate({ target: employees.employeeId, set: { name: 'John Doe' }, setWhere: sql`name <> 'John Doe'` });

onConflictDoNothing where clause generation fixed

In Drizzle ORM v0.30.8, a bug was fixed in the Postgres .onConflictDoNothing method where the where clause was being placed in the wrong location in the generated SQL query.

onConflictDoUpdate targetWhere and setWhere fields in SQLite

In Drizzle v0.30.9, the `.onConflictDoUpdate()` method in SQLite was updated to use separate `targetWhere` and `setWhere` fields instead of a single `where` field. The `targetWhere` field specifies a condition for the conflict detection on the target, while `setWhere` specifies a condition for when to apply the set operation. Both fields accept SQL expressions.

onConflictDoUpdate setWhere example in SQLite

await db.insert(employees) .values({ employeeId: 123, name: 'John Doe' }) .onConflictDoUpdate({ target: employees.employeeId, set: { name: 'John Doe' }, setWhere: sql`name <> 'John Doe'` });

Column alias using .as() method

In v1.0.0-beta.2, you can add column aliases directly in queries using the .as() method: db.select({ age: users.age.as('ageOfUser'), id: users.id.as('userId') }).from(users).orderBy(asc(users.id.as('userId')))

Fixed $onUpdate handling of SQL values

v1.0.0-beta.2 fixed $onUpdate not handling SQL values properly (fixes GitHub issue #2388).

Give your agent this brain