使用 Golang 构建可维护的 SQL 查询

任何使用 SQL 查询的应用程序都可以受益于使用查询生成器来提高代码的可读性、可维护性和安全性。事实上，Golang 中有许多不同的库可以做到这一点。在 Vaunt，我们尝试了许多不同的选择，最后决定自己创建一个。最终，我们想要一些安全的东西，并提供变量替换来防止 SQL 注入，同时仍然可读并且能够有条件语句。因此，我们创建了一个名为 tqla 的新库，并于去年年底发布并宣布。您可以在这篇文章中阅读更多相关信息。

在构建 tqla 之前，我们主要使用 Squirrel 来构建 SQL 查询逻辑——我们强烈推荐它。我们仍然在某些领域使用 Squirrel，但已逐渐开始用 tqla 替换和实现新的查询构建逻辑。我们发现在许多实例中，tqla 提高了我们维护代码和修复使用其他语句生成器时遇到的问题的能力。

现实世界用例

在 Vaunt，我们最近进行了从 CockroachDB 到 TiDB 的数据库迁移。虽然 CockroachDB 高性能且可靠，但我们最终决定添加到我们的技术堆栈中以支持 OLAP 数据库。这样做的需要是支持我们对开源社区洞察产品的分析工作量。为了保持较小的技术足迹，我们决定继续使用 TiDB 并利用该数据库的 HTAP 架构。 

CockroachDB 与 PostgreSQL 很大程度上兼容，我们的许多 SQL 查询都使用 PostgreSQL 语法。要切换到 TiDB，我们必须更改一些表并更新查询以使用 MySQL 语法。在迁移过程中的一些位置，我们发现我们不正确地使用条件查询构建语句，并且缺乏适当的测试来发现语句生成不正确。

示范

在 Squirrel 的自述文件中，有一个示例说明如何使用条件查询构建来更新具有可选过滤器的语句：

if len(q) > 0 {
    users = users.Where("name LIKE ?", fmt.Sprint("%", q, "%"))
}

这是一个真实但简化的示例，说明我们如何更新其中一个查询以有条件连接表并添加可选过滤器：

psql := squirrel.StatementBuilder.PlaceholderFormat(squirrel.Question)

statementBuilder := psql.Select(`i.id`).
From("vaunt.installations i").
Where(`entity_name = ?`, name)

if len(provider) > 0 {
    statementBuilder.Where(`provider = ?`, provider)
}

if len(repo) > 0 {
    statementBuilder.Join(`repositories as r on JSON_CONTAINS(i.repositories, CONCAT('["', r.id, '"]'))`)
    statementBuilder.Where(`r.name = ?`, repo)
}

你能发现代码的问题吗？如果没有，请不要担心——在我们运行测试之前，我们自己的代码审查也会忽略这一点。 

这里的问题是我们忘记使用构建器函数的结果更新语句构建器。例如，提供者条件过滤器应改为：

if len(provider) > 0 {
    statementBuilder = statementBuilder.Where(`provider = ?`, provider)
}

这是一个相对简单的错误，可以通过足够的测试用例轻松发现，但由于它不是技术上无效的代码，因此可能需要一些时间才能立即意识到发生了什么。

此设置的另一个可读性问题是条件连接与初始 select 语句是分开的。我们可以重新组织构建器，将每个部分放在它应该去的地方，但这需要多次重复的条件语句检查，并且仍然会遇到一些可读性问题。

使用tqla

上面使用 Squirrel 的演示已被重写，tqla 中的等效项如下所示：

t, err := tqla.New(tqla.WithPlaceHolder(tqla.Question))
if err != nil {
    return nil, err
}

query, args, err := t.Compile(`
    SELECT i.id
    FROM vaunt.installations as i
    {{ if .Repo }}
    JOIN vaunt.repositories as r on JSON_CONTAINS(i.repositories, CONCAT('["', r.id, '"]'), '$')
    {{ end }}
    WHERE entity_name = {{ .Name}}
    {{ if .Provider }}
    AND i.provider = {{ .Provider }}
    {{ end }}
    {{ if .Repo }}
    AND r.name = {{ .Repo }}
    {{ end }}
    `, data)
if err != nil {
    return nil, err
}

如您所见，tqla 的模板语法使得合并条件子句变得非常简单。 Tqla 自动用指定的占位符替换我们设置的变量，并提供我们可以与 sql 驱动程序一起使用来执行语句的参数。

与 Squirrel 类似，这种语句构建方法很容易测试，因为我们可以创建不同的数据对象集来传递给模板构建器并验证输出。

您可以看到，我们可以轻松地将查询的条件部分添加到最适合的位置。例如，这里我们在 FROM 语句之后直接有一个条件 JOIN，尽管我们仍然有多个条件检查，但它并没有使模板过于复杂。

自定义功能

另一个有助于提高 sql 构建器可维护性的不错的 tqla 功能是能够定义我们可以在模板中使用的自定义函数来抽象一些转换逻辑。

下面是我们如何使用函数将 Golang 的 time.Time 值转换为 sql.NullTime 的示例，以便我们无需事先转换即可对数据对象进行插入：

funcs := template.FuncMap{
    "time": func(t time.Time) sql.NullTime {
        if t.IsZero() {
            return sql.NullTime{Valid: false}
        }
        return sql.NullTime{Time: t, Valid: true}
    },
}

t, err := tqla.New(tqla.WithPlaceHolder(tqla.Question), tqla.WithFuncMap(funcs))
if err != nil {
    return err
}

通过在 tqla funcs 映射中定义此函数，我们现在可以通过向其提供来自数据对象（即 time.Time 字段）的参数来在查询模板中自由使用它。我们甚至可以在同一模板中使用不同字段多次调用此函数。

Here is a simplified example:

statement, args, err := t.Compile(`
    INSERT INTO events
        (name, created_at, merged_at, closed_at)
    VALUES ( 
        {{ .Name }},
        {{ time .CreatedAt }},
        {{ time .MergedAt }},
        {{ time .ClosedAt }}
    )`, eventData)

Conclusion

In conclusion, we believe that using tqla can help improve the maintainability of query building logic while offering some powerful utility for creating dynamic queries. The simplicity of the template structure allows for clean code readability and can make it faster to debug any potential errors.

We made tqla open source to share this library in hopes that it provides a good option for other users wanting a simple, maintainable, and secure way to build sql queries in many different types of applications.

If you are interested, please check out the repository and give it a star if it helps you in any way. Feel free to make any feature requests or bug reports!

We are always open to receiving feedback and contributions.

To stay in the loop on future development, follow us on X or join our Discord!

以上是使用 Golang 构建可维护的 SQL 查询的详细内容。更多信息请关注PHP中文网其他相关文章！