生の SQL を安全に作成するためのライブラリ

PropelAuth での私の責任の 1 つは、さまざまな言語/フレームワークでサンプル アプリ/ガイドを作成することです。それは本当に私の仕事の中で最も楽しい部分の一つです。新しいものから古いものまで、さまざまなスタックを試してみて、お客様をサポ​​ートする最善の方法を見つけ出すことができます。

そのため、多くのプロジェクトをゼロから作成することになります。新しいプロジェクトを開始するときは常に、いくつかの重要な選択をしなければなりません。そして、私が多くの時間を費やす傾向がある決定の 1 つは次のとおりです。

どの DB ライブラリを使用すればよいですか?

私にとって、私が惹かれるライブラリは、私が書いているコードと SQL クエリ自体の間に抽象化の層が少ないものです。

その理由の 1 つは、単なる実用性です。私は頻繁に言語を切り替えるため、特定の ORM を流暢に理解する時間があまりありません。私も過去に大量のデータ サイエンス コンポーネントを含む仕事に就いていたため、SQL は非常に使いやすいものです。

しかし、私は「魔法」を嫌う傾向にある開発者でもあります。そのため、生成された SQL がどのようなものになるのか簡単に判断できないライブラリや、自分の時間を費やしていると感じるライブラリは避けています。 「X に参加する方法」をグーグルで検索し、続いて「2 つの条件で X に参加する方法」を検索します。

この投稿では、私が頻繁に利用するいくつかのライブラリと、試してみたいと思っているライブラリに焦点を当てたいと思いました。これらはすべて、私が作成したコードと SQL との違いを最小限に抑えるよう努めています。実行されました。

私の個人的なお気に入り: SQLx

私のお気に入りの Rust クレートの 1 つは SQLx です。

彼ら自身の言葉で:

SQLx は コンパイル時にチェックされるクエリ をサポートします。ただし、クエリを構築するための Rust API や DSL (ドメイン固有言語) を提供することでこれを行うわけではありません。代わりに、通常の SQL を入力として受け取り、それがデータベースに対して有効であることを確認するマクロが提供されます。これがどのように機能するかというと、コンパイル時に SQLx が開発 DB に接続して、データベース自体に 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 を書いているだけだということです。しかし、生の SQL を書くこともできる postgres のようなクレートとは異なり、SQLx は愚かな間違いを防ぐことができます。

これには多少のコストがかかります。コンパイル時にデータベースに依存することになりますが、SQLx は「オフライン モード」でこれに対処します。 DB が使用可能な場合は、検証されたすべてのクエリを含むファイルを生成できます。その後、ビルドで SQLx がデータベースの代わりにこのファイルをチェックします。

私が書いたコードと実行される SQL の間の違いを最小限に抑えるという私の探求において、SQLx を使用すると違いがなく、それを得るために安全性を犠牲にする必要もありませんでした。

PgTyped を使用して SQL の TS インターフェイスを生成します

JavaScript/TypeScript エコシステムではよくあることですが、ここにはたくさんのオプションがあります。

データベースから TS タイプを生成し、クエリ ビルダーと生の SQL を記述する方法の両方を提供する Kysely のようなオプションがあります。 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< // ...

その関数を呼び出して DB から結果を取得できます。重要なのは、クエリが間違っている場合 (存在しない列を参照しているとしましょう)、次のようなエラーが表示されることです:

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 可能性が尊重されません。これは、必須フィールドに合理的に null を渡す可能性があることを意味するため、かなりイライラする可能性があります。もう 1 つの欠点は、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 を直接記述しますが、その差はまだ小さいです。

• DB ライブラリを選択する際の考慮事項には、移植性、冗長性、複雑なクエリの必要性と単純な CRUD 操作の必要性が含まれます。

以上が生の SQL を安全に作成するためのライブラリの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。