神奇的 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] = @par0 -- 参数: [69]

提供给 sql 参数的任何表和列都会自动映射为其对应的 SQL
语法，表名会进行转义，并且转义后的表名会附加到列名上。

此外，任何动态参数，例如 ${id}，都会映射为 @par0 占位符，
并且相应的值会被移动到一个单独传递给数据库的值数组中。

这种方法可以有效防止任何潜在的 SQL 注入漏洞。

sql<T>

你可以在 Drizzle 中定义一个自定义类型，用于字段需要除 unknown 之外的特定类型的场景。

此功能在部分选择查询中特别有用，可确保所选字段的类型保持一致：

// 未定义 sql<T> 类型
const response: { lowerName: unknown }[] = await db.select({
    lowerName: sql`lower(${usersTable.id})`
}).from(usersTable);

// 定义了 sql<T> 类型
const response: { lowerName: string }[] = await db.select({
    lowerName: sql<string>`lower(${usersTable.id})`
}).from(usersTable);

sql``.mapWith()

对于需要对从数据库驱动传递到 drizzle 的值进行运行时映射的场景，你可以使用 .mapWith()

这个函数接受不同的值，并在运行时对响应进行映射。

你可以复现特定的列映射策略，只要 mapWith 内部使用的接口与 Column 实现的接口相同即可。

import { mssqlTable, int, text } from 'drizzle-orm/mssql-core';

const usersTable = mssqlTable('users', {
    id: int().primaryKey().identity(),
    name: text().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')

... [users].[name] as [lower_name] ...

sql.raw()

在某些情况下，你可能不需要根据输入创建参数化值，也不需要将表/列映射为转义后的形式。
相反，你可能只是想按原样生成查询。对于这种情况，我们提供了 sql.raw() 函数。

sql.raw() 函数允许你在查询中包含原始 SQL 语句，而无需进行任何额外处理或转义。
当你已经预先构造好 SQL 语句，或者需要将复杂或动态的 SQL 代码直接纳入查询时，这会很有用。

sql.raw(`select * from users where id = ${12}`);
// 与之相比
sql`select * from users where id = ${12}`;

select * from users where id = 12;
--> 与之相比
select * from users where id = @par0; --> [12]

你也可以在 sql 函数中使用 sql.raw()，从而可以包含任何原始字符串，而无需通过主 sql 模板函数对其进行转义。

通过在 sql 函数内部使用 sql.raw()，你可以将未转义的原始字符串直接纳入查询中。
当你有某些特定的 SQL 代码或表达式需要保持不变，不受模板函数自动转义或修改影响时，这尤其有用。

sql`select * from ${usersTable} where id = ${12}`;
// 与之相比
sql`select * from ${usersTable} where id = ${sql.raw(12)}`;

select * from [users] where id = @par0; --> [12]
--> 与之相比
select * from [users] where id = 12;

sql.fromList()

sql 模板会生成 sql 片段，这些片段是 SQL 部分组成的数组，在对数据库或 Drizzle 中的查询应用 SQL 后，它们会被拼接到查询和参数中。

在某些场景下，你可能需要使用自定义业务逻辑将这些片段聚合到一个数组中，然后将它们拼接成一个可以传递给数据库或查询的单个 SQL 语句。 对于这种情况，fromList 函数会非常有用。

fromList 函数允许你将多个 SQL 片段组合成一个单独的 SQL 语句。 你可以根据自己的具体需求对各个 SQL 部分进行聚合和拼接，进而得到一个可执行的统一 SQL 查询。

const sqlChunks: SQL[] = [];

sqlChunks.push(sql`select * from users`);

// 一些逻辑

sqlChunks.push(sql` where `);

// 一些逻辑

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 = @par0 or id = @par1 or id = @par2 or id = @par3 or id = @par4; --> [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`);

// 一些逻辑

sqlChunks.push(sql`where`);

// 一些逻辑

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 = @par0 or id = @par1 or id = @par2 or id = @par3 or id = @par4; --> [0, 1, 2, 3, 4]

sql.append()

如果你已经使用 sql 模板生成了 SQL，那么你可以通过使用 append 函数直接向已生成的 SQL 中添加一个新的块，从而实现与 fromList 相同的行为。

通过使用 append 函数，你可以动态地向现有的 SQL 字符串中添加额外的 SQL 块，实际上就是把它们连接在一起。这使你能够将自定义逻辑或业务规则纳入到最终 SQL 查询的块聚合过程中。

const finalSql = sql`select * from users`;

// 一些逻辑

finalSql.append(sql` where `);

// 一些逻辑

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 = @par0 or id = @par1 or id = @par2 or id = @par3 or id = @par4; --> [0, 1, 2, 3, 4]

sql.empty()

通过使用 sql.empty()，你可以从一个空白的 SQL 对象开始，然后根据需要动态地向其中追加 SQL 片段。这使你能够逐步构建 SQL 查询，并通过自定义逻辑或条件来决定每个片段的内容。

一旦你使用 sql.empty() 初始化了 SQL 对象，就可以利用 sql 模板特性的完整功能，例如参数化、组合和转义。
这使你能够以灵活且可控的方式构建 SQL 查询，
以适应你的特定需求。

const finalSql = sql.empty();

// 一些逻辑

finalSql.append(sql`select * from users`);

// 一些逻辑

finalSql.append(sql` where `);

// 一些逻辑

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 = @par0 or id = @par1 or id = @par2 or id = @par3 or id = @par4; --> [0, 1, 2, 3, 4]

将 sql 转换为字符串和参数

在前面的所有示例中，你已经观察到了在 TypeScript 中使用 SQL 模板语法以及 生成的 SQL 输出。

如果你需要获取从 SQL 模板生成的查询字符串及其对应参数， 你必须指定你打算为其生成查询的数据库方言。不同的数据库在参数化和转义方面有 不同的语法，因此选择合适的方言至关重要。

一旦你选择了方言，就可以利用相应实现提供的功能， 将 SQL 模板转换为所需的查询字符串和参数格式。这可以确保与你正在使用的特定数据库系统 兼容。

import { MsSqlDialect } from 'drizzle-orm/mssql-core';

const mssqlDialect = new MsSqlDialect();
mssqlDialect.sqlToQuery(sql`select * from ${usersTable} where ${usersTable.id} = ${12}`);

select * from [users] where [users].[id] = @par0; --> [ 12 ]

sql 选择

你也可以在部分选择查询中使用 sql 功能。部分选择查询允许你从表中检索特定字段或列，而不是获取整行。

有关部分选择查询的更详细信息，你可以参考 Core API 文档中的 Core API docs。

从表中选择不同的自定义字段

在这里你可以看到 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.name})`,
    aliasedName: sql<string>`lower(${usersTable.name})`.as('aliased_column'),
    count: sql<number>`count(*)`.mapWith(Number) 
}).from(usersTable)

select [id], lower([name]), lower([name]) as [aliased_column], count(*) from [users];

sql 中的 where

确实，Drizzle 提供了一组可在 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] = @par0; --> [ 77 ]

高级全文搜索 where 语句

import { sql } from 'drizzle-orm'
import { usersTable } from './schema'

const searchParam = "%Ale%"

await db.select()
        .from(usersTable)
        .where(sql`lower(${usersTable.name}) like lower(${searchPattern})`)

select * from [users] where lower([users].[name]) like lower(@par0); --> [ "%Ale%" ]

orderBy 中的 sql

当你需要某些 Drizzle 中没有提供的特定排序功能，但又不想直接使用原始 SQL 时，sql 模板确实可以用于 ORDER BY 子句。

import { sql } from 'drizzle-orm'
import { usersTable } from './schema'

await db.select().from(usersTable).orderBy(sql`${usersTable.id} desc`)

select * from [users] order by [users].[id] desc;

sql 在 having 和 groupBy 中

当你需要某些特定的排序功能，而 Drizzle 中并不支持时，sql 模板确实可以用于 HAVING 和 GROUP BY 子句，同时你又不希望退回到原始 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([id]) from [users] group by [users].[project_id] having count([users].[id]) > 300;