SQL Select

基础查询

从表中选择所有行，包括所有列：

sql

select "id", "name", "age" from "users";

drizzle

const result = await db.select().from(users)
/*
  {
    id: number;
    name: string;
    age: number | null;
  }[]
*/

部分选择（Partial select）

在某些情况下，你可能只想从表中选择部分列。 可以通过在 .select() 方法中提供一个选择对象实现：

sql

select "id", "name" from "users";

drizzle

const result = await db.select({
  field1: users.id,
  field2: users.name,
}).from(users)

const { field1, field2 } = result[0]

就像在 SQL 中一样，除了表的列外，你还可以使用任意表达式作为选择字段：

const result = await db.select({
  id: users.id,
  lowerName: sql<string>`lower(${users.name})`,
}).from(users)

select "id", lower("name") from "users";

通过指定 sql<string>，你告诉 Drizzle 该字段的预期类型是 string。 如果指定错误（例如，将返回为字符串的字段定义为 sql<number>），运行时的值将与预期类型不匹配。 Drizzle 无法根据提供的泛型类型进行任何类型转换，因为这些信息在运行时不可用。

如果需要对返回值进行运行时转换，可以使用 .mapWith() 方法。

条件选择（Conditional select）

你可以根据某些条件构建动态的选择对象：

async function selectUsers(withName: boolean) {
  return db
    .select({
      id: users.id,
      ...(withName ? { name: users.name } : {}),
    })
    .from(users)
}

const users = await selectUsers(true)

去重查询（Distinct select）

可以使用 .selectDistinct() 替代 .select() 来从数据集中检索唯一的行：

sql

select distinct "id", "name" from "users" order by "id", "name";

select distinct "id" from "users" order by "id";

ts

await db.selectDistinct().from(users).orderBy(usersTable.id, usersTable.name)

await db.selectDistinct({ id: users.id }).from(users).orderBy(usersTable.id)

高级查询（Advanced select）

借助 TypeScript，Drizzle API 允许以多种灵活的方式构建查询。

以下是高级部分选择的简单示例，更多详细示例可参考

包含额外字段的选择

import { getTableColumns, sql } from 'drizzle-orm'

await db.select({
  ...getTableColumns(posts), // 获取表中的所有列
  titleLength: sql<number>`length(${posts.title})`, // 添加计算字段
}).from(posts)

排除特定列的选择

import { getTableColumns } from 'drizzle-orm'

const { content, ...rest } = getTableColumns(posts) // 排除 "content" 列
await db.select({ ...rest }).from(posts) // 选择除 "content" 之外的所有列

选择部分列

await db.query.posts.findMany({
  columns: {
    title: true, // 仅选择 "title" 列
  },
})

排除特定列

await db.query.posts.findMany({
  columns: {
    content: false, // 排除 "content" 列
  },
})

过滤（Filters）

可以在 .where() 方法中使用过滤操作符来筛选查询结果：

sql

select "id", "name", "age" from "users" where "id" = 42;
select "id", "name", "age" from "users" where "id" < 42;
select "id", "name", "age" from "users" where "id" >= 42;
select "id", "name", "age" from "users" where "id" <> 42;

drizzle

import { eq, gte, lt, ne } from 'drizzle-orm'

await db.select().from(users).where(eq(users.id, 42))
await db.select().from(users).where(lt(users.id, 42))
await db.select().from(users).where(gte(users.id, 42))
await db.select().from(users).where(ne(users.id, 42))

自定义 SQL 过滤器

所有过滤操作符通过函数实现。你也可以自己编写任意 SQL 过滤器或构建自定义操作符。

示例：

import { sql } from 'drizzle-orm'

function equals42(col: Column) {
  return sql`${col} = 42`
}

await db.select().from(users).where(sql`${users.id} < 42`)
await db.select().from(users).where(sql`${users.id} = 42`)
await db.select().from(users).where(equals42(users.id))
await db.select().from(users).where(sql`${users.id} >= 42`)
await db.select().from(users).where(sql`${users.id} <> 42`)
await db.select().from(users).where(sql`lower(${users.name}) = 'aaron'`)

对应的 SQL 语句：

select "id", "name", "age" from "users" where "id" < 42;
select "id", "name", "age" from "users" where "id" = 42;
select "id", "name", "age" from "users" where "id" = 42;
select "id", "name", "age" from "users" where "id" >= 42;
select "id", "name", "age" from "users" where "id" <> 42;
select "id", "name", "age" from "users" where lower("name") = 'aaron';

所有传递给过滤器操作符和 sql 函数的值会自动参数化。 例如，以下查询：

await db.select().from(users).where(eq(users.id, 42))

会被转换为：

select "id", "name", "age" from "users" where "id" = $1; -- 参数: [42]

---

使用 not 操作符

可以通过 not 操作符反转条件：

import { eq, not, sql } from 'drizzle-orm'

await db.select().from(users).where(not(eq(users.id, 42)))
await db.select().from(users).where(sql`not ${users.id} = 42`)

对应的 SQL 语句：

select "id", "name", "age" from "users" where not ("id" = 42);
select "id", "name", "age" from "users" where not ("id" = 42);

---

组合过滤器

可以使用 and() 和 or() 操作符逻辑组合过滤条件：

使用 and 操作符：

import { and, eq, sql } from 'drizzle-orm'

await db.select().from(users).where(
  and(
    eq(users.id, 42),
    eq(users.name, 'Dan')
  )
)
await db.select().from(users).where(sql`${users.id} = 42 and ${users.name} = 'Dan'`)

对应的 SQL 语句：

select "id", "name", "age" from "users" where "id" = 42 and "name" = 'Dan';

使用 or 操作符：

import { eq, or, sql } from 'drizzle-orm'

await db.select().from(users).where(
  or(
    eq(users.id, 42),
    eq(users.name, 'Dan')
  )
)
await db.select().from(users).where(sql`${users.id} = 42 or ${users.name} = 'Dan'`)

对应的 SQL 语句：

select "id", "name", "age" from "users" where "id" = 42 or "name" = 'Dan';

---

高级过滤

结合 TypeScript，Drizzle API 提供了强大而灵活的方式来组合查询中的过滤条件。

条件过滤

async function searchPosts(term?: string) {
  await db
    .select()
    .from(posts)
    .where(term ? ilike(posts.title, term) : undefined)
}
await searchPosts()
await searchPosts('AI')

动态组合过滤条件

async function searchPosts(filters: SQL[]) {
  await db
    .select()
    .from(posts)
    .where(and(...filters))
}
const filters: SQL[] = []
filters.push(ilike(posts.title, 'AI'))
filters.push(inArray(posts.category, ['Tech', 'Art', 'Science']))
filters.push(gt(posts.views, 200))
await searchPosts(filters)

限制与偏移（Limit & offset）

使用 .limit() 和 .offset() 来为查询添加 limit 和 offset 子句，例如用于实现分页：

sql

select "id", "name", "age" from "users" limit 10;
select "id", "name", "age" from "users" limit 10 offset 10;

drizzle

await db.select().from(users).limit(10)
await db.select().from(users).limit(10).offset(10)

以下是高级分页的简单示例，更多详细示例请查看我们的 limit offset 分页 和游标分页指南。

使用 limit 和 offset 实现分页：

await db
  .select()
  .from(users)
  .orderBy(asc(users.id)) // 排序是必需的
  .limit(4) // 返回的行数
  .offset(4) // 跳过的行数

另一种分页实现方式：

async function getUsers(page = 1, pageSize = 3) {
  await db.query.users.findMany({
    orderBy: (users, { asc }) => asc(users.id),
    limit: pageSize,
    offset: (page - 1) * pageSize,
  })
}
await getUsers()

使用子查询结合分页：

async function getUsers(page = 1, pageSize = 10) {
  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)
}

基于游标的分页：

async function nextUserPage(cursor?: number, pageSize = 3) {
  await db
    .select()
    .from(users)
    .where(cursor ? gt(users.id, cursor) : undefined) // 如果提供游标，获取该游标之后的行
    .limit(pageSize) // 返回的行数
    .orderBy(asc(users.id)) // 排序
}
// 传入上一页最后一行的游标（id）
await nextUserPage(3)

排序（Order By）

使用 .orderBy() 为查询添加 order by 子句，按指定字段对结果进行排序：

sql

select "id", "name", "age" from "users" order by "name";
select "id", "name", "age" from "users" order by "name" desc;

select "id", "name", "age" from "users" order by "name", "name2";
select "id", "name", "age" from "users" order by "name" asc, "name2" desc;

drizzle

import { asc, desc } from 'drizzle-orm'

await db.select().from(users).orderBy(users.name)
await db.select().from(users).orderBy(desc(users.name))

// 按多个字段排序
await db.select().from(users).orderBy(users.name, users.name2)
await db.select().from(users).orderBy(asc(users.name), desc(users.name2))

聚合查询

sql中的聚合查询用于对多行数据执行计算，返回单个汇总值或分组结果。