神奇的 sql 操作符 🪄
当使用 ORM 库时，有时候使用提供的 ORM 语法编写特定查询可能会变得具有挑战性。在这种情况下，您可以使用原始查询，即构建原始字符串形式的查询。然而，原始查询通常缺乏类型安全性和查询参数化的优势。
为了解决这个问题，许多库引入了 sql 模板的概念。这个模板允许您编写更具类型安全性和参数化的查询，增强代码的安全性和灵活性。Drizzle 是一个功能强大的 ORM 库，也支持 sql 模板。
使用 Drizzle 的 sql 模板，您可以进一步定制查询。如果使用库的查询构建器编写整个查询时遇到困难，您可以在 Drizzle 查询的特定部分中选择性地使用 sql 模板。这种灵活性使您能够在部分 SELECT 语句、WHERE 子句、ORDER BY 子句、HAVING 子句、GROUP BY 子句甚至关系查询构建器中使用 sql 模板。
通过利用 Drizzle 的 sql 模板的功能，您可以保持类型安全和查询参数化的优势，同时实现所需的查询结构和复杂性。这使您能够在应用程序中创建更健壮和易于维护的代码。
`sql“ 模板
在其他 ORM 中，您可能会遇到最常见的用法之一是直接使用 sql 查询作为原始查询。
import { sql } from 'drizzle-orm'
const id = 69;
await db.execute(sql`select * from ${usersTable} where ${usersTable.id} = ${id}`)这将生成以下查询
select * from "users" where "users"."id" = $1; --> [69]传递给 sql 参数的任何表和列将自动映射为它们对应的 SQL 语法，其中表名被转义为带引号的名称，并将转义后的表名附加到列名后面。
另外，任何动态参数，如 ${id}，将映射到 $1 占位符，并且对应的值将被移动到一个值数组中，该数组被单独传递给数据库。
通过这种方式，有效地防止了任何潜在的 SQL 注入漏洞。
sql<T>
请注意，sql<T> 不会执行任何运行时映射。使用 sql<T> 定义的类型
只是 Drizzle 的一个辅助类型。重要的是要理解没有可行的办法
动态确定确切的类型，因为 SQL 查询可以非常灵活和可定制。
您可以在 Drizzle 中定义自定义类型，在需要除 unknown 以外的特定类型的字段的地方使用该类型。
这个功能在部分选择查询中特别有用，确保所选字段的类型一致：
// 没有定义 sql<T> 类型
const response: { id: unknown }[] = await db.select({
lowerName: sql`lower(${usersTable.id})`
}).from(usersTable);
// 定义了 sql<T> 类型
const response: { id: string }[] = await db.select({
lowerName: sql<string>`lower(${usersTable.id})`
}).from(usersTable);sql``.mapWith()
当您需要将从数据库驱动程序传递的值运行时映射到 Drizzle 时，可以使用 .mapWith()。
该函数接受不同的值，将在运行时映射响应。
只要 mapWith 中的接口是由 Column 实现的相同接口，就可以复制特定的列映射策略。
const usersTable = pgTable('users', {
id: serial('id').primaryKey(),
name: text('name').notNull(),
});
// 在运行时，这些值会按照在 drizzle 中映射 `text` 列的方式进行映射
sql`...`.mapWith(usersTable.name);您还可以为 DriverValueDecoder 接口传递自己的实现：
sql``.mapWith({
mapFromDriverValue: (value: any) => {
const mappedValue = value;
// 应用您想要的映射
return mappedValue;
},
});
// 或者
sql``.mapWith(Number);sql``.as<T>()
在某些情况下，确定要使用的自定义字段的名称可能有些困难。在您需要显式指定用于选择的字段的别名时，可能会遇到这种情况。在处理复杂查询时，这可能非常有用。
为了解决这些情况，我们引入了一个有用的 .as('alias_name') 辅助方法，允许您显式定义别名。通过使用这个功能，您可以为字段提供清晰而有意义的名称，使您的查询更直观和可读。
sql`lower(usersTable.name)`.as('lower_name')... "usersTable"."name" as lower_name ...sql.raw()
在某些情况下，您可能不需要从输入创建参数化值，也不需要将表/列映射为转义字符形式。相反，您可能只想生成原始查询。为此，我们提供了 sql.raw() 函数。
sql.raw() 函数允许您在查询中包含原始的 SQL 语句，而无需进行任何额外的处理或转义。当您有预先构建的 SQL 语句或需要直接将复杂或动态 SQL 代码合并到查询中时，这可能很有用。
sql.raw(`select * from users where id = ${12}`);
// vs
sql`select * from users where id = ${12}`;select * from users where id = 12;
--> vs
select * from users where id = $1; --> [12]您还可以在 sql 函数内部使用 sql.raw()，从而可以将任何原始字符串插入到查询中，而无需通过主要的 sql 模板函数对其进行转义。
通过在 sql 函数内部使用 sql.raw()，您可以将未转义的原始字符串直接合并到查询中。当您具有特定的 SQL 代码或表达式，而模板函数的自动转义或修改不能对其进行处理时，这可能特别有用。
sql`select * from ${usersTable} where id = ${12}`;
// vs
sql`select * from ${usersTable} where id = ${sql.raw(12)}`;select * from "users" where id = $1; --> [12]
--> vs
select * from "users" where id = 12;sql.fromList()
sql 模板生成 SQL 块，即将被连接在查询和参数之后应用到数据库或查询的 SQL 的数组。
在某些情况下，您可能需要使用自定义业务逻辑将这些块聚合到一个数组中，然后将它们连接成一个可以传递给数据库或查询的单个 SQL 语句。对于这种情况，fromList 函数非常有用。
fromList 函数允许您将多个 SQL 块组合成一个单独的 SQL 语句。您可以使用它根据特定需求对个别的 SQL 部分进行聚合和连接，从而获得一个统一的 SQL 查询，可以执行。
const sqlChunks: SQL[] = [];
sqlChunks.push(sql`select * from users`);
// some logic
sqlChunks.push(sql` where `);
// some logic
for (let i = 0; i < 5; i++) {
sqlChunks.push(sql`id = ${i}`);
if (i === 4) continue;
sqlChunks.push(sql` or `);
}
const finalSql: SQL = sql.fromList(sqlChunks)select * from users where id = $1 or id = $2 or id = $3 or id = $4 or id = $5; --> [0, 1, 2, 3, 4]sql.join()
实际上，sql.join 函数与 fromList 辅助函数具有相似的作用。然而，它在处理 SQL 块之间的空格或指定连接 SQL 块的自定义分隔符方面提供了额外的灵活性。
使用 sql.join，您可以使用指定的分隔符将 SQL 块连接在一起。该分隔符可以是任何您想要在块之间插入的字符串或字符。
当您对 SQL 块的格式或分隔要求具有特定要求时，这将非常有用。通过指定自定义分隔符，您可以在最终的 SQL 查询中实现所需的结构和格式。
const sqlChunks: SQL[] = [];
sqlChunks.push(sql`select * from users`);
// some logic
sqlChunks.push(sql`where`);
// some logic
for (let i = 0; i < 5; i++) {
sqlChunks.push(sql`id = ${i}`);
if (i === 4) continue;
sqlChunks.push(sql`or`);
}
const finalSql: SQL = sql.join(sqlChunks, sql.raw(' '));select * from users where id = $1 or id = $2 or id = $3 or id = $4 or id = $5; --> [0, 1, 2, 3, 4]sql.append()
如果您已经使用 sql 模板生成了 SQL，通过使用 append 函数直接将新块添加到生成的 SQL 中，可以实现与 fromList 相同的功能。
通过使用 append 函数，您可以动态地向现有的 SQL 字符串添加其他 SQL 块，有效地将它们连接在一起。这使您可以将自定义逻辑或业务规则纳入到将块聚合到最终的 SQL 查询中。
const finalSql = sql`select * from users`;
// some logic
finalSql.append(sql` where `);
// some logic
for (let i = 0; i < 5; i++) {
finalSql.append(sql`id = ${i}`);
if (i === 4) continue;
finalSql.append(sql` or `);
}select * from users where id = $1 or id = $2 or id = $3 or id = $4 or id = $5; --> [0, 1, 2, 3, 4]sql.empty()
通过使用 sql.empty()，您可以从空的 SQL 对象开始，并根据需要动态地向其附加 SQL 块。这使您可以逐步构建 SQL 查询，应用自定义逻辑或条件来确定每个块的内容。
一旦使用 sql.empty() 初始化了 SQL 对象，您就可以利用 sql 模板的完整功能范围，如参数化、组合和转义。这使您能够以灵活和可控的方式构建 SQL 查询，根据特定要求进行调整。
const finalSql = sql.empty();
// some logic
finalSql.append(sql`select * from users`);
// some logic
finalSql.append(sql` where `);
// some logic
for (let i = 0; i < 5; i++) {
finalSql.append(sql`id = ${i}`);
if (i === 4) continue;
finalSql.append(sql` or `);
}select * from users where id = $1 or id = $2 or id = $3 or id = $4 or id = $5; --> [0, 1, 2, 3, 4]将 sql 转换为字符串和参数
在之前的所有示例中，您观察到了 TypeScript 中与生成的 SQL 输出一起使用 SQL 模板语法的用法。
如果需要获取从 SQL 模板生成的查询字符串和相应参数，您必须指定要为其生成查询的数据库方言。不同的数据库对参数化和转义有不同的语法，因此选择适当的方言非常重要。
选择了方言之后，可以使用相应实现的功能将 SQL 模板转换为所需的查询字符串和参数格式。这确保与您正在使用的特定数据库系统的兼容性。
import { PgDialect } from 'drizzle-orm/pg-core';
const pgDialect = new PgDialect();
pgDialect.sqlToQuery(sql`select * from ${usersTable} where ${usersTable.id} = ${12}`);select * from "users" where "users"."id" = $1; --> [ 12 ]import { MySqlDialect } from 'drizzle-orm/mysql-core';
const mysqlDialect = new MySqlDialect();
mysqlDialect.sqlToQuery(sql`select * from ${usersTable} where ${usersTable.id} = ${12}`);select * from `users` where `users`.`id` = ?; --> [ 12 ]import { SQLiteSyncDialect } from 'drizzle-orm/sqlite-core';
const sqliteDialect = new SQLiteSyncDialect();
sqliteDialect.sqlToQuery(sql`select * from ${usersTable} where ${usersTable.id} = ${12}`);select * from "users" where "users"."id" = ?; --> [ 12 ]sql select
您还可以在部分选择查询中使用 sql 功能。部分选择查询允许您检索表中的特定字段或列，而不是获取整行数据。
有关部分选择查询的更详细信息，可以参考 核心 API 文档。
从表中选择不同的自定义字段
下面是对 sql<T>、sql``.mapWith() 和 sql``.as<T>() 的用法的示例。
import { sql } from 'drizzle-orm'
import { usersTable } from 'schema'
await db.select({
id: usersTable.id,
lowerName: sql<string>`lower(${usersTable})`,
aliasedName: sql<string>`lower(${usersTable})`.as('aliased_column'),
count: sql<number>`count(*)`.mapWith(Number)
}).from(usersTable)select `id`, lower(`name`), lower(`name`) as `aliased_column`, count(*) from `users`;sql in where
的确，在 sql 模板中，提供了一组可用的表达式，您可以在其中使用。然而，数据库通常具有更广泛的表达式可用，包括通过扩展或其他方式提供的表达式。
为了确保灵活性并让您能够使用 Drizzle 不支持的任何表达式，您可以直接使用 sql 函数编写 SQL 模板。这样，您就可以充分利用 SQL 的全部功能，并结合任何表达式或特定于目标数据库的功能。
通过使用 sql 模板，您不仅限于 Drizzle 中预定义的表达式。相反，您可以表达复杂的查询，并且可以整合任何底层数据库系统提供的支持的表达式。
使用 sql 过滤 id
import { sql } from 'drizzle-orm'
import { usersTable } from 'schema'
const id = 77
await db.select()
.from(usersTable)
.where(sql`${usersTable.id} = ${id}`)select * from "users" where "users"."id" = $1; --> [ 77 ]高级全文搜索 where 语句
import { sql } from 'drizzle-orm'
import { usersTable } from 'schema'
const searchParam = "Ale"
await db.select()
.from(usersTable)
.where(sql`to_tsvector('simple', ${usersTable.name}) @@ to_tsquery('simple', ${searchParam})`)select * from "users" where to_tsvector('simple', "users"."name") @@ to_tsquery('simple', '$1'); --> [ "Ale" ]sql in orderBy
确实可以在 ORDER BY 子句中使用 sql 模板，当您需要特定的顺序功能而 Drizzle 不支持时，但又不想使用原始 SQL。
import { sql } from 'drizzle-orm'
import { usersTable } from 'schema'
await db.select().from(usersTable).orderBy(sql`${usersTable.id} desc nulls first`)select * from "users" order by "users"."id" desc nulls first;sql in having 和 groupBy
确实可以在 HAVING 和 GROUP BY 子句中使用 sql 模板，当您需要特定的聚合功能而 Drizzle 不支持时，但又不想使用原始 SQL。
import { sql } from 'drizzle-orm'
import { usersTable } from 'schema'
await db.select({
projectId: usersTable.projectId,
count: sql<number>`count(${usersTable.id})`.mapWith(Number)
}).from(usersTable)
.groupBy(sql`${usersTable.projectId}`)
.having(sql`count(${usersTable.id}) > 300`)select "project_id", count("users"."id") from users group by "users"."project_id" having count("users"."id") > 300; 
