用於安全編寫原始 SQL 的函式庫

我在 PropelAuth 的職責之一是用各種語言/框架編寫範例應用程式/指南。這確實是我工作中最有趣的部分之一。我開始嘗試不同的堆棧，無論是新的還是舊的，並找出支援客戶的最佳方法。

因此，我最終從頭開始創建了很多專案。每當我開始一個新專案時，我都必須做出一些重要的選擇，而我傾向於花費大量時間的一個決定是：

我應該使用什麼資料庫庫？

對我來說，我喜歡的函式庫是那些我正在寫的程式碼和 SQL 查詢本身之間具有很少抽象層的函式庫。

這樣做的一個原因是實用性——因為我經常切換語言，所以我沒有太多時間來熟練任何特定的 ORM。我過去也從事過包含大量資料科學成分的工作，所以 SQL 是我非常熟悉的東西。

但我也是一個不喜歡「魔法」的開發人員- 所以我避免使用那些我無法輕易判斷生成的SQL 是什麼樣子的庫，或者我覺得我花了所有時間的庫谷歌搜尋「如何加入X ”，然後搜尋“如何在兩個條件下加入X”。

在這篇文章中，我想強調一些我經常使用的函式庫，以及我很高興嘗試的函式庫，它們都試圖最小化我寫的程式碼和 SQL 之間的差異被處決。

我個人最喜歡的：SQLx

我最喜歡的 Rust 箱子之一是 SQLx。

用他們自己的話說：

SQLx 支援 編譯時檢查查詢。然而，它並沒有透過提供 Rust API 或 DSL（特定於網域的語言）來建立查詢來實現這一點。相反，它提供了將常規 SQL 作為輸入並確保它對您的資料庫有效的巨集。其工作原理是，SQLx 在編譯時連接到您的開發資料庫，讓資料庫本身驗證您的 SQL 查詢（並傳回一些資訊）。

換句話說，SQLx 讓您可以寫以下查詢：

let row = sqlx::query!(r#"
    SELECT enail
    FROM user
    WHERE user_id = ?
"#, user_id)
  .fetch_one(&pool)
  .await?;

這似乎是標準的，但是當你編譯程式碼時，你會得到這樣的錯誤：

error returned from database: column "enail" of relation "user" does not exist

同樣的編譯時檢查也適用於複雜查詢：

SELECT job_id, job_data 
FROM job_queue
WHERE job_status = 'Queued' AND run_at >= NOW()
ORDER BY run_at ASC
FOR UPDATE SKIP LOCKE -- oops
LIMIT 1

error returned from database: syntax error at or near "LOCKE"

由於查詢是根據資料庫進行檢查的，因此這也適用於您安裝的任何擴充功能。

為什麼這麼酷？

令人難以置信的是我們實際上只是在編寫 SQL。但與像 postgres 這樣的套件不同，它還允許您編寫原始 SQL，SQLx 可以防止我們犯下愚蠢的錯誤。

這確實需要付出很小的代價——我們現在對資料庫有編譯時依賴，但 SQLx 透過「離線模式」解決了這個問題。當您的資料庫可用時，您可以產生一個包含所有經過驗證的查詢的文件，然後在您的建置中，SQLx 將檢查該文件而不是資料庫。

當我尋求最小化我編寫的程式碼和執行的 SQL 之間的差異時，使用 SQLx 既沒有差異，而且我不必犧牲安全性來獲得它。

使用 PgTyped 為您的 SQL 產生 TS 介面

正如 JavaScript/TypeScript 生態系統中經常出現的那樣，這裡有很多選項。

像 Kysely 這樣的選項可以從資料庫產生 TS 類型，然後提供查詢產生器和編寫原始 SQL 的方法。 Drizzle 是一個查詢產生器，但它的既定目標是減少您編寫的 TS 程式碼與產生的 SQL 之間的差異。甚至還有一個 SQLx 連接埠我還沒有機會嘗試。

但是最符合我在這裡尋找的函式庫是 PgTyped。使用 PgTyped，您可以在單獨的檔案中定義查詢，如下所示：

/* @name FindEmailById */
SELECT email FROM user WHERE user_id = :userId;

然後執行命令 npx pgtyped -c config.json，它會根據您的架構產生具有正確類型的函數：

export interface IFindEmailByIdParams {
    userId?: string | null;
}

export interface IFindEmailByIdResult {
    email: string
}export const findEmailById = new PreparedQuery< // ...

您可以呼叫該函數從資料庫取得結果。重要的是，如果您的查詢錯誤（假設它引用了不存在的列），您會收到以下錯誤：

Error in query. Details: {
  errorCode: 'errorMissingColumn',
  hint: 'Perhaps you meant to reference the column "user.email".',
  message: 'column "enail" does not exist',
  position: '7'
}

這意味著您不僅可以安全地編寫原始 SQL — 您的應用程式程式碼還可以獲得一個很好的 TS 抽象來呼叫（或在測試中模擬）。

PgTyped 的最大缺點是這個 Github 問題——不考慮類型的可為空性，這可能非常令人沮喪，因為這意味著您可能會合理地為必填字段傳遞 null。另一個缺點是它特定於 Postgres……稍後將在“可移植性”部分詳細介紹。

Prisma recently released TypedSQL — a “a new way to write raw SQL queries in a type-safe way.” They mention that part of the inspiration was both SQLx and PgTyped, so I am excited to try it out!

Something for the Python world: PugSQL

A library I enjoy when I switch to Python is PugSQL (Python). Similar to PgTyped, you create separate SQL files for your queries like this:

-- :name find_email :one
select email from users where user_id = :user_id

which will create a function that you can call:

email = queries.find_email(user_id=42)

The downside (relative to the previous libraries) is these queries aren’t automatically checked for issues. That being said, some tests can surface most (all?) the issues with the query itself — you just need to write them.

If you are feeling fancy, it’s possible to add your own automation which will check the queries. There are ways to verify a query against a DB without running the query — it’s just some additional work. Each query being in its own file makes it a bit easier to automate since you don’t need to go parse out the queries in the first place.

Mark up your interfaces with JDBI

Whenever I talk about how much I liked Dropwizard, I usually get met with blank stares. It’s a bit of a deeper cut in the Java world relative to Spring (either that or normal people don’t discuss Dropwizard at parties).

One of the reasons I liked Dropwizard so much was just because it came with JDBI. That library allowed you to annotate the functions on an interface with SQL queries and it would generate the implementation for you.

public interface UserDAO {
  @SqlQuery("select email from user where user_id = :user_id")
  String fetchEmail(@Bind("user_id") String userId);
}

final UserDAO userDao = database.onDemand(UserDAO.class);

Again though, this would require additional testing to find issues in the queries.

I should also mention that Spring Data JPA does also have the same concept with it’s @Query annotation. It’s been a very long time, but back when I was comparing JDBI and Spring Data JPA - I always felt like Spring was trying to get me to use it’s more magical “function name to sql query” methods. Upon re-reading the docs recently though, I was wrong, and it does mention that you can fallback to @Query pretty frequently.

Other considerations

“Use it sparingly”

If you followed some of the links in this post, you’ll find that some of these libraries don’t advocate for this approach as the primary way to query the database.

TypedSQL describes it as an escape hatch for when querying via their ORM isn’t sufficient. Same for Spring Data JPA which describes it as “fine for a small number of queries”.

This isn’t an unfounded claim — if you go down the path of writing raw SQL for every query, it can be pretty verbose. There are absolutely times where I am making a simple, boring table that’s basically just a key-value store, and the exercise in writing INSERT INTO boring_table VALUES (...) and SELECT * FROM boring_table WHERE ... etc is just a typing exercise.

A library that provides the best of both worlds seems great! The devil is really in the details, as it depends on what you consider to be complex enough to warrant writing raw SQL and how frequently those queries come up.

Portability

One issue with the raw SQL approach is it’s less portable. If you are using an ORM, that ORM often will be compatible with more than just the database you are currently working with.

This can mean small things like running sqlite locally and a different DB in production — or big things like making it easier to migrate your database to something else.

Again, your mileage may vary here — it’s really dependent on how much you care about this.

Use a query builder instead

Going back to the java ecosystem, a popular library is jOOQ. With jOOQ, you aren’t writing raw SQL, but it’s very close:

To me, this is great! My stated goal was just keeping the delta between my code and the generated SQL as little as possible, so query builders like jOOQ or Drizzle do a good job of keeping that delta small.

Not all query builders are made equal here, as I tend to dislike ones like Knex which have a larger delta.

Summary

• Raw SQL libraries like SQLx, PgTyped, and JDBI allow writing SQL directly while providing safety and type checking.

• These libraries aim to minimize the gap between code and executed SQL, with some offering benefits like compile-time checking and generated type interfaces.

• 替代方案包括查詢建構器，例如 jOOQ 和 Drizzle，您可以直接編寫 SQL，但差距仍然很小。

• 選擇資料庫庫時的考慮因素包括可移植性、冗長性以及複雜查詢與簡單 CRUD 操作的需求。

以上是用於安全編寫原始 SQL 的函式庫的詳細內容。更多資訊請關注PHP中文網其他相關文章！