Chapter 05

条件与函数：eq/and/or/sql

where 子句的全部武器库——比较操作符、逻辑组合、SQL 模板、聚合与排序。

5.1 比较操作符

从 drizzle-orm 导入，与 SQL 一一对应：

import {
  eq, ne,                      // = / !=
  gt, gte, lt, lte,            // > >= < <=
  isNull, isNotNull,
  inArray, notInArray,
  between, notBetween,
  like, ilike, notLike,        // ilike 仅 Postgres
  exists, notExists,
} from "drizzle-orm";

eq(users.age, 30)
gt(users.age, 18)
isNull(users.deletedAt)
inArray(users.id, [1, 2, 3])
between(users.age, 18, 65)
like(users.name, "A%")
ilike(users.name, "a%")    // 不区分大小写（PG 专有）

5.2 逻辑组合：and / or / not

import { and, or, not } from "drizzle-orm";

await db.select().from(users).where(
  and(
    gt(users.age, 18),
    or(
      eq(users.role, "admin"),
      ilike(users.email, "%@company.com"),
    ),
    not(isNull(users.verifiedAt)),
  )
);

生成 SQL：

SELECT * FROM users
WHERE (age > $1)
  AND ((role = $2) OR (email ILIKE $3))
  AND (NOT (verified_at IS NULL));

5.3 动态 where：条件选入

常见场景：根据查询参数动态拼 where：

async function searchUsers(filter: { role?: string; search?: string }) {
  const conditions = [];
  if (filter.role) conditions.push(eq(users.role, filter.role));
  if (filter.search) conditions.push(ilike(users.name, `%${filter.search}%`));

  return db.select().from(users).where(
    conditions.length ? and(...conditions) : undefined,
  );
}

undefined 传入 .where() 相当于没有 where——干净优雅。

5.4 sql 模板：未覆盖操作的逃生舱

Drizzle 虽然实现了大部分常用函数，但 SQL 那么多方言特性不可能全有。sql 模板能让你直接写 SQL 片段并保持类型安全 + 自动参数化：

import { sql } from "drizzle-orm";

// 完全动态
await db.select().from(users)
  .where(sql`${users.age} BETWEEN ${minAge} AND ${maxAge}`);

// 引用列（会自动加表前缀 + 防注入）
await db.select().from(users)
  .where(sql`LOWER(${users.email}) = ${email.toLowerCase()}`);

// 整条原生 SQL（用 db.execute）
const r = await db.execute(sql`SELECT version()`);
sql 模板里的变量是参数，不是字符串插值

sql`... ${x} ...` 里的 x 会变成参数化查询 $1——即便 x 是用户输入，也不会造成 SQL 注入。

如果确实要拼标识符（如动态列名），用 sql.identifier("col_name") 或 sql.raw("literal")——后者是不转义的，自己保证安全！

5.5 聚合函数

import { count, countDistinct, sum, avg, min, max } from "drizzle-orm";

const [stats] = await db.select({
  total:        count(),                        // COUNT(*)
  active:       count(users.id),                 // COUNT(id)
  uniqueEmails: countDistinct(users.email),     // COUNT(DISTINCT email)
  totalAge:     sum(users.age),
  avgAge:       avg(users.age),
  minAge:       min(users.age),
  maxAge:       max(users.age),
}).from(users);

sum/avg 对数值列返回 string（精度考虑），要转 number 自己 Number() 或在 SQL 里 cast。

5.6 groupBy / having

const ageGroups = await db
  .select({
    role: users.role,
    total: count(),
    avgAge: avg(users.age),
  })
  .from(users)
  .groupBy(users.role)
  .having(gt(count(), 10));          // 组内 > 10 人

5.7 orderBy / limit / offset

import { asc, desc } from "drizzle-orm";

const recent = await db
  .select()
  .from(posts)
  .orderBy(desc(posts.createdAt), asc(posts.title))
  .limit(20)
  .offset(40);

NULLS FIRST / LAST

.orderBy(sql`${users.deletedAt} ASC NULLS LAST`)

5.8 distinct

// 整行 distinct
const uniq = await db.selectDistinct().from(users);

// 仅选某字段的 distinct
const roles = await db
  .selectDistinct({ role: users.role })
  .from(users);

// Postgres distinct on（每组取一行）
await db.selectDistinctOn([users.role]).from(users).orderBy(users.role, desc(users.createdAt));

5.9 String / Math / Date 函数

常用的已经封装：

import {
  lower, upper, length, concat,
  now, currentDate, currentTimestamp,
} from "drizzle-orm";

await db.select({
  nameLower: lower(users.name),
  nameLen: length(users.name),
  full: concat(users.firstName, sql` `, users.lastName),
  createdDay: sql`date_trunc('day', ${users.createdAt})`.as("day"),
}).from(users);

没有提供的直接用 sql`...` 写——毕竟 SQL 函数有成千上万。

5.10 全文搜索（Postgres）

import { sql } from "drizzle-orm";

await db.select({
  id: posts.id,
  title: posts.title,
  rank: sql<number>`ts_rank(to_tsvector('english', ${posts.body}), websearch_to_tsquery('english', ${q}))`.as("rank"),
})
.from(posts)
.where(sql`to_tsvector('english', ${posts.body}) @@ websearch_to_tsquery('english', ${q})`)
.orderBy(sql`rank DESC`);

5.11 coalesce / case when

await db.select({
  displayName: sql<string>`COALESCE(${users.nickname}, ${users.name})`,
  ageGroup: sql<string>`CASE
      WHEN ${users.age} < 18 THEN 'minor'
      WHEN ${users.age} < 65 THEN 'adult'
      ELSE 'senior'
    END`,
}).from(users);

5.12 小结