Knex QueryBuilder 中动态替换表和连接的 Schema

本文探讨在 Knex QueryBuilder 中动态管理和替换已添加的表及连接的数据库 Schema。由于 Knex 默认不提供直接访问和修改 QueryBuilder 内部已添加连接的方法，本文介绍一种利用 toString() 将查询转换为 SQL 字符串，通过字符串替换注入动态 Schema，再使用 knex.raw() 重新构建可执行查询的解决方案，尤其适用于多租户或动态数据库环境。

在构建复杂的数据库查询时，我们常常会遇到需要根据不同环境或业务逻辑动态调整数据库 Schema 的情况。例如，在多租户应用中，可能需要针对每个租户的数据库实例，将相同的查询逻辑应用到不同的 Schema 下。Knex QueryBuilder 提供了 withSchema() 方法来为主 from 子句指定 Schema，但对于已经通过 join 方法添加的连接，Knex 并没有直接提供 API 来回溯并修改这些连接的 Schema。这意味着一旦 join 被添加，其 Schema 就被固定，无法通过 QueryBuilder 的标准方法进行动态调整。

这种限制对于需要构建通用查询模板，然后为每个租户或数据库动态应用其特定 Schema 的场景构成了挑战。直接访问 QueryBuilder 内部的连接列表进行修改并非 Knex 的设计意图，因此我们需要一种变通的方法来解决这个问题。

核心思路：利用 SQL 字符串操作实现动态 Schema 注入

解决上述问题的核心思路是：

1. 构建通用查询模板：创建一个不包含具体 Schema 的基础查询，使用一个明确的占位符来表示 Schema 位置。
2. 转换为 SQL 字符串：利用 Knex QueryBuilder 的 toString() 方法将构建好的查询转换为原始的 SQL 字符串。
3. 字符串替换：对 SQL 字符串执行替换操作，将占位符替换为目标 Schema 名称。
4. 重新构建查询：使用 knex.raw() 方法将修改后的 SQL 字符串重新包装成一个可执行的 Knex 查询对象。

这种方法允许我们在运行时动态地为整个查询（包括主表和所有连接表）注入指定的 Schema，而无需重新构建整个查询。

实现步骤与示例

下面我们将通过一个具体的代码示例来演示如何实现这一策略。

1. 初始化 Knex 实例

首先，我们需要一个 Knex 实例。这里以 MySQL 为例，但该方法适用于任何 Knex 支持的数据库。

const knex = require("knex")({ client: "mysql" });

2. 定义基础查询模板

创建一个基础查询，其中所有需要动态应用 Schema 的表名前都使用一个特定的占位符（例如 #）。这个占位符将被后续的字符串替换操作识别并替换。

Lateral App

整理归类论文

85

查看详情

const readOnlyQuery = knex
  .select("*")
  .from("#.users as u")
  .leftJoin("#.pets as p", "u.id", "p.idUser")
  .where("u.id", 1);

注意：为了防止在后续操作中意外修改 readOnlyQuery 对象，建议使用 Object.freeze() 方法将其冻结。

Object.freeze(readOnlyQuery); // 防止对模板查询的意外修改

3. 实现动态 Schema 注入函数

创建一个辅助函数 buildQueryWithSchema，它接受一个 Knex QueryBuilder 对象和一个 Schema 字符串作为参数。该函数将执行 SQL 字符串转换、替换和重新构建的逻辑。

/**
 * 返回一个新的 Knex QueryBuilder 对象，其查询中包含给定的 Schema。
 * @param {import('knex').QueryBuilder} queryBuilder - 基础查询模板。
 * @param {String} schema - 要注入的 Schema 名称。
 * @returns {import('knex').Raw} - 包含指定 Schema 的 Knex Raw 查询对象。
 */
function buildQueryWithSchema(queryBuilder, schema) {
  // 1. 将 QueryBuilder 转换为 SQL 字符串
  const sqlString = queryBuilder.toString();

  // 2. 替换占位符。注意这里的反引号是针对 MySQL 的，其他数据库可能不同。
  // 我们将 "`#`" 替换为 "`yourSchema`"
  const modifiedSqlString = sqlString.replaceAll("`#`", "`" + schema + "`");

  // 3. 使用 knex.raw() 重新构建查询
  return knex.raw(modifiedSqlString);
}

4. 测试与验证

现在，我们可以使用 buildQueryWithSchema 函数为不同的 Schema 生成查询。

// 为 "public" Schema 生成查询
const queryBuilderSchemaPublic = buildQueryWithSchema(readOnlyQuery, "public");
console.log("Public Schema Query:", queryBuilderSchemaPublic.toString());

// 为 "private" Schema 生成查询
const queryBuilderSchemaPrivate = buildQueryWithSchema(readOnlyQuery, "private");
console.log("Private Schema Query:", queryBuilderSchemaPrivate.toString());

输出结果示例：

Public Schema Query: select * from `public`.`users` as `u` left join `public`.`pets` as `p` on `u`.`id` = `p`.`idUser` where `u`.`id` = 1
Private Schema Query: select * from `private`.`users` as `u` left join `private`.`pets` as `p` on `u`.`id` = `p`.`idUser` where `u`.`id` = 1

从输出可以看出，Schema 名称已成功地应用到 from 子句中的 users 表和 leftJoin 子句中的 pets 表。

重要注意事项

1. SQL 注入风险：
   • 极度重要！ knex.raw() 方法会直接执行传入的 SQL 字符串，不进行任何参数绑定或转义。如果 schema 参数来源于不可信的用户输入，这将导致严重的 SQL 注入漏洞。
   • 防范措施：确保 schema 参数始终来自应用程序内部的安全配置或经过严格验证的白名单列表，绝不能直接使用未经净化的用户输入。
2. 数据库方言兼容性：
   • SQL 字符串的格式会因数据库类型（如 MySQL、PostgreSQL、SQL Server）而异。例如，MySQL 使用反引号 (`) 来引用标识符，而 PostgreSQL 使用双引号 (" )。
   • 在 replaceAll 方法中，占位符的匹配模式 (#) 和替换字符串的引用方式 (" + schema + "") 需要根据您使用的数据库类型进行调整。
3. 可读性与维护性：
   • 虽然这种方法解决了特定问题，但它通过字符串操作绕过了 Knex QueryBuilder 的抽象层。这可能降低代码的可读性，并使调试变得稍微复杂。
   • 应仅在确实需要动态操作 Schema 且 Knex 现有 API 无法满足时才采用此方法。
4. 性能考量：
   • toString() 和 knex.raw() 的组合会带来轻微的性能开销，因为它涉及字符串的生成、操作和解析。对于大多数应用场景，这种开销可以忽略不计。但在极端高并发或对性能要求极高的场景下，需要进行基准测试。
5. 占位符选择：
   • 选择一个在正常 SQL 语句中不会出现的独特字符串作为占位符，以避免意外替换。例如，# 或 __SCHEMA__ 都是不错的选择。

总结

尽管 Knex QueryBuilder 没有提供直接修改已添加连接 Schema 的 API，但通过结合 toString()、字符串替换和 knex.raw()，我们可以有效地实现动态 Schema 注入。这种方法在构建多租户应用或需要灵活处理数据库 Schema 的复杂查询场景中非常有用。然而，在使用此方法时，务必牢记 SQL 注入的风险，并确保所有动态注入的 Schema 名称都经过严格的安全验证，以保障应用程序的安全性。

以上就是Knex QueryBuilder 中动态替换表和连接的 Schema的详细内容，更多请关注php中文网其它相关文章！