使用 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中文網其他相關文章！