SQL Select

Drizzle 在保持类型安全和可组合性的同时，为您提供了最接近 SQL 的方式从数据库中获取数据。它本机支持几乎每个方言的查询功能和能力，而它尚不支持的任何功能，都可以由用户通过强大的 sql 操作符添加。

对于以下示例，请假设您有一个像这样定义的 users 表：

PostgreSQL

MySQL

SQLite

import { pgTable, serial, text } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  age: integer('age'),
});

import { mysqlTable, serial, text } from 'drizzle-orm/mysql-core';

export const users = mysqlTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  age: integer('age'),
});

import { sqliteTable, integer, text } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: integer('id').primaryKey(),
  name: text('name').notNull(),
  age: integer('age'),
});

基本和部分选择

选择所有列

选择包括所有列的表中的所有行：

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

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

请注意，结果类型是根据表定义自动推断的，包括列的可空性。

💡

Drizzle 总是在 select 子句中显式列出列，而不是使用 select *。
内部必须这样做，以确保查询结果中字段的顺序，这也通常被认为是一种良好的实践。

部分选择

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

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

const { field1, field2 } = result[0];

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

与 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() 方法。

如果您经常使用表达式，可以将其提取到一个函数中：

import type { Column } from 'drizzle-orm';
import { sql } from 'drizzle-orm';

function lower(col: Column) {
  return sql<string>`lower(${col})`;
}

const result = await db.select({
  id: users.id,
  lowerName: lower(users.name),
}).from(users);

条件选择

您可以根据某些条件拥有动态选择对象：

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

const users = await selectUsers(true);

过滤

您可以使用 .where() 方法中的过滤操作器来过滤查询结果：

import { eq, lt, gte, 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));
...

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;

所有过滤操作符均使用 sql 函数实现。您可以自己使用它来编写任意 SQL 过滤器或构建自己的操作符。您可以参考 Drizzle 提供的操作符是如何实现的。

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'`);

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; -- params: [42]

使用 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`);

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

💡

您可以安全地更改模式、重命名表和列，由于模板插值的作用，这将自动反映在你的查询中，而不是在编写原始 SQL 时硬编码列或表的名称。

组合过滤器

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

import { eq, and, 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'`);

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

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'`);

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

Distinct

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

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

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

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

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

在 PostgreSQL 中，您还可以使用 distinct on 子句指定如何确定唯一行：

await db.selectDistinctOn([users.id]).from(users).orderBy(users.id);
await db.selectDistinctOn([users.name], { name: users.name }).from(users).orderBy(users.name);

select distinct on ("id") "id", "name" from "users" order by "id";
select distinct on ("name") "name" from "users" order by "name";

💡

distinct on 子句仅在 PostgreSQL 中受支持。

Limit & offset

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

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

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

Order By

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

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

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

// order by multiple fields
await db.select().from(users).orderBy(users.name, users.name2);
await db.select().from(users).orderBy(asc(users.name), desc(users.name2));

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;

WITH 子句

💡

请查看如何在 INSERT 语句中使用 WITH 语句 insert、update、delete

使用 with 子句可以通过将复杂的查询拆分为更小的子查询（称为公共表达式）来简化查询：

const sq = db.$with('sq').as(db.select().from(users).where(eq(users.id, 42)));

const result = await db.with(sq).select().from(sq);

with sq as (select "id", "name", "age" from "users" where "id" = 42)
select "id", "name", "age" from sq;

要选择一个 CTE 中的任意 SQL 值作为字段，并在其他 CTE 或主查询中引用它们，您需要为它们添加别名：

const sq = db.$with('sq').as(db.select({ 
  name: sql<string>`upper(${users.name})`.as('name'),
})
.from(users));

const result = await db.with(sq).select({ name: sq.name }).from(sq);

如果不提供别名，字段类型将变为 DrizzleTypeError，您将无法在其他查询中引用它。如果忽略类型错误并尝试仍然使用该字段，您将获得一个运行时错误，因为没有办法在没有别名的情况下引用该字段。

从子查询中选择

就像在 SQL 中一样，您可以使用子查询 API 将查询嵌入到其他查询中：

const sq = db.select().from(users).where(eq(users.id, 42)).as('sq');
const result = await db.select().from(sq);

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

子查询可以在任何可以使用表的地方使用，例如在连接中：

const sq = db.select().from(users).where(eq(users.id, 42)).as('sq');
const result = await db.select().from(users).leftJoin(sq, eq(users.id, sq.id));

select "users"."id", "users"."name", "users"."age", "sq"."id", "sq"."name", "sq"."age" from "users"
  left join (select "id", "name", "age" from "users" where "id" = 42) "sq"
    on "users"."id" = "sq"."id";

迭代器

MySQL

PostgreSQL[WIP]

SQLite[WIP]

如果您需要从查询中返回非常大量的行，并且不希望将它们全部加载到内存中，可以使用 .iterator() 将查询转换为异步迭代器：

const iterator = await db.select().from(users).iterator();

for await (const row of iterator) {
  console.log(row);
}

它也可以与 prepared statements 一起使用：

const query = await db.select().from(users).prepare();
const iterator = await query.iterator();

for await (const row of iterator) {
  console.log(row);
}

聚合

使用 Drizzle，您可以通过使用 sum、count、avg 等函数进行聚合，并通过 .groupBy() 和 .having() 进行分组和筛选，就像在原始 SQL 中一样：

import { gt } from 'drizzle-orm';

await db.select({
  age: users.age,
  count: sql<number>`cast(count(${users.id}) as int)`,
})
  .from(users)
  .groupBy(users.age);

await db.select({
  age: users.age,
  count: sql<number>`cast(count(${users.id}) as int)`,
})
  .from(users)
  .groupBy(users.age)
  .having(({ count }) => gt(count, 1));

select "age", cast(count("id") as int)
  from "users"
  group by "age";

select "age", cast(count("id") as int)
  from "users"
  group by "age"
  having cast(count("id") as int) > 1;

💡

cast(... as int) 是必需的，因为 PostgreSQL 中 count() 返回 bigint，MySQL 中返回 decimal，它们被视为字符串值而不是数字。或者，您可以使用 .mapWith(Number) 在运行时将值转换为数字。

聚合助手函数

Drizzle 提供了一组封装的 sql 函数，这样您就不需要为应用程序中的常见情况编写 sql 模板

💡

请记住，聚合函数通常与 SELECT 语句的 GROUP BY 子句一起使用。因此，如果您正在使用聚合函数和其他列在一个查询中选择，请务必使用 .groupBy 子句

count

返回 expression 中的值的数量。

import { count } from 'drizzle-orm'

await db.select({ value: count() }).from(users);
await db.select({ value: count(users.id) }).from(users);

select count("*") from "users";
select count("id") from "users";

// It's equivalent to writing
await db.select({ 
  value: sql`count('*'))`.mapWith(Number) 
}).from(users);

await db.select({ 
  value: sql`count(${users.id})`.mapWith(Number) 
}).from(users);

countDistinct

返回 expression 中的非重复值的数量。

import { countDistinct } from 'drizzle-orm'

await db.select({ value: countDistinct(users.id) }).from(users);

select count(distinct "id") from "users";

// It's equivalent to writing
await db.select({ 
  value: sql`count(${users.id})`.mapWith(Number) 
}).from(users);

avg

返回 expression 中所有非空值的平均值（算术平均值）。

import { avg } from 'drizzle-orm'

await db.select({ value: avg(users.id) }).from(users);

select avg("id") from "users";

// It's equivalent to writing
await db.select({ 
  value: sql`avg(${users.id})`.mapWith(String) 
}).from(users);

avgDistinct

返回 expression 中所有非空值的平均值（算术平均值）。

import { avgDistinct } from 'drizzle-orm'

await db.select({ value: avgDistinct(users.id) }).from(users);

select avg(distinct "id") from "users";

// It's equivalent to writing
await db.select({ 
  value: sql`avg(distinct ${users.id})`.mapWith(String) 
}).from(users);

sum

返回 expression 中所有非空值的总和。

import { sum } from 'drizzle-orm'

await db.select({ value: sum(users.id) }).from(users);

select sum("id") from "users";

// It's equivalent to writing
await db.select({ 
  value: sql`sum(${users.id})`.mapWith(String) 
}).from(users);

sumDistinct

返回 expression 中所有非空且不重复的值的总和。

import { sumDistinct } from 'drizzle-orm'

await db.select({ value: sumDistinct(users.id) }).from(users);

select sum(distinct "id") from "users";

// It's equivalent to writing
await db.select({ 
  value: sql`sum(distinct ${users.id})`.mapWith(String) 
}).from(users);

max

返回 expression 中的最大值。

import { max } from 'drizzle-orm'

await db.select({ value: max(users.id) }).from(users);

select max("id") from "users";

// It's equivalent to writing
await db.select({ 
  value: sql`max(${expression})`.mapWith(users.id) 
}).from(users);

min

返回 expression 中的最小值。

import { min } from 'drizzle-orm'

await db.select({ value: min(users.id) }).from(users);

select min("id") from "users";

// It's equivalent to writing
await db.select({ 
  value: sql`min(${users.id})`.mapWith(users.id) 
}).from(users);

更高级的示例：

const orders = sqliteTable('order', {
  id: integer('id').primaryKey(),
  orderDate: integer('order_date', { mode: 'timestamp' }).notNull(),
  requiredDate: integer('required_date', { mode: 'timestamp' }).notNull(),
  shippedDate: integer('shipped_date', { mode: 'timestamp' }),
  shipVia: integer('ship_via').notNull(),
  freight: numeric('freight').notNull(),
  shipName: text('ship_name').notNull(),
  shipCity: text('ship_city').notNull(),
  shipRegion: text('ship_region'),
  shipPostalCode: text('ship_postal_code'),
  shipCountry: text('ship_country').notNull(),
  customerId: text('customer_id').notNull(),
  employeeId: integer('employee_id').notNull(),
});

const details = sqliteTable('order_detail', {
  unitPrice: numeric('unit_price').notNull(),
  quantity: integer('quantity').notNull(),
  discount: numeric('discount').notNull(),
  orderId: integer('order_id').notNull(),
  productId: integer('product_id').notNull(),
});


db
  .select({
    id: orders.id,
    shippedDate: orders.shippedDate,
    shipName: orders.shipName,
    shipCity: orders.shipCity,
    shipCountry: orders.shipCountry,
    productsCount: sql<number>`cast(count(${details.productId}) as int)`,
    quantitySum: sql<number>`sum(${details.quantity})`,
    totalPrice: sql<number>`sum(${details.quantity} * ${details.unitPrice})`,
  })
  .from(orders)
  .leftJoin(details, eq(orders.id, details.orderId))
  .groupBy(orders.id)
  .orderBy(asc(orders.id))
  .all();

Become a Gold Sponsor