Perpustakaan untuk menulis SQL mentah dengan selamat

Salah satu tanggungjawab saya di PropelAuth ialah menulis contoh aplikasi / panduan dalam pelbagai bahasa / rangka kerja. Ia benar-benar salah satu bahagian yang paling menyeronokkan dalam pekerjaan saya. Saya dapat bermain-main dengan tindanan yang berbeza, baharu dan lama serta memikirkan cara terbaik untuk menyokong pelanggan kami.

Disebabkan itu, saya akhirnya mencipta banyak projek dari awal. Setiap kali saya memulakan projek baharu, terdapat beberapa pilihan penting yang perlu saya buat — dan satu keputusan yang saya cenderung untuk menghabiskan banyak masa ialah:

Pustaka DB apakah yang patut saya gunakan?

Bagi saya, perpustakaan yang saya minati ialah perpustakaan yang mempunyai sedikit lapisan abstraksi antara kod yang saya tulis dan pertanyaan SQL itu sendiri.

Salah satu sebab untuk ini hanyalah kepraktisan — memandangkan saya sering menukar bahasa, saya tidak mempunyai banyak masa untuk fasih dalam mana-mana ORM tertentu. Saya juga pernah mempunyai pekerjaan pada masa lalu yang mempunyai komponen sains data yang berat kepada mereka, jadi SQL adalah sesuatu yang saya sangat selesa.

Tetapi saya juga seorang pembangun yang cenderung tidak menyukai "sihir" — jadi saya mengelakkan perpustakaan yang saya tidak dapat mengetahui dengan mudah rupa SQL yang dijana, atau perpustakaan yang saya rasa seperti menghabiskan sepanjang masa saya googling "bagaimana untuk menyertai dalam X" diikuti dengan "cara untuk menyertai dalam X dengan dua syarat".

Dalam siaran ini, saya ingin menyerlahkan beberapa perpustakaan yang sering saya capai, serta perpustakaan yang saya teruja untuk mencuba, yang semuanya mencuba dan meminimumkan perbezaan antara kod yang saya tulis dan SQL yang dilaksanakan.

Kegemaran peribadi saya: SQLx

Salah satu peti Rust kegemaran saya ialah SQLx.

Dalam perkataan mereka sendiri:

SQLx menyokong kompilasi pertanyaan disemak masa. Walau bagaimanapun, ia tidak melakukan ini dengan menyediakan API Rust atau DSL (bahasa khusus domain) untuk membina pertanyaan. Sebaliknya, ia menyediakan makro yang mengambil SQL biasa sebagai input dan memastikan ia sah untuk pangkalan data anda. Cara ini berfungsi ialah SQLx menyambung ke DB pembangunan anda pada masa penyusunan untuk memastikan pangkalan data itu sendiri mengesahkan (dan mengembalikan beberapa maklumat pada) pertanyaan SQL anda.

Dengan kata lain, SQLx membolehkan anda menulis pertanyaan seperti ini:

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

yang mungkin kelihatan standard, tetapi apabila anda menyusun kod anda, anda akan mendapat ralat seperti ini:

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

Semakan masa kompilasi yang sama ini juga digunakan untuk pertanyaan kompleks:

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"

Memandangkan pertanyaan disemak terhadap pangkalan data, ini juga akan berfungsi dengan mana-mana sambungan yang telah anda pasang.

Kenapa ini hebat?

Perkara yang luar biasa tentang ini ialah kami hanya menulis SQL. Tetapi tidak seperti peti seperti postgres, yang juga membolehkan anda menulis SQL mentah, SQLx menghalang kami daripada membuat kesilapan bodoh.

Ini datang dengan kos yang kecil — kami kini mempunyai kebergantungan masa kompilasi pada pangkalan data, tetapi SQLx menanganinya dengan "mod luar talian." Apabila DB anda tersedia, anda boleh menjana fail dengan semua pertanyaan yang disahkan, dan kemudian dalam binaan anda, SQLx akan menyemak fail ini dan bukannya pangkalan data.

Dalam usaha saya untuk meminimumkan perbezaan antara kod yang saya tulis dan dan SQL yang dilaksanakan, dengan SQLx kedua-duanya tiada perbezaan DAN saya tidak perlu mengorbankan keselamatan untuk mendapatkannya.

Jana antara muka TS untuk SQL anda dengan PgTyped

Seperti yang sering berlaku dalam ekosistem JavaScript/TypeScript, terdapat banyak pilihan di sini.

Terdapat pilihan seperti Kysely yang menjana jenis TS daripada pangkalan data anda dan kemudian menyediakan kedua-dua pembina pertanyaan dan cara untuk menulis SQL mentah. Terdapat Drizzle, yang merupakan pembina pertanyaan, tetapi matlamat dinyatakan ialah mengurangkan delta antara kod TS yang anda tulis dan menjana SQL. Malah terdapat port SQLx yang saya belum berpeluang mencuba.

Tetapi perpustakaan yang paling sesuai dengan apa yang saya cari di sini ialah PgTyped. Dengan PgTyped, anda mentakrifkan pertanyaan anda dalam fail berasingan seperti:

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

Anda kemudian menjalankan perintah npx pgtyped -c config.json yang menjana fungsi dengan jenis yang betul berdasarkan skema anda:

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

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

Anda boleh memanggil fungsi itu untuk mendapatkan hasil daripada DB. Yang penting, jika pertanyaan anda salah (katakan ia merujuk kepada lajur yang tidak wujud), anda mendapat ralat seperti ini:

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

Ini bermakna anda bukan sahaja boleh menulis SQL mentah dengan selamat — kod aplikasi anda mendapat abstraksi TS yang bagus untuk dipanggil (atau mengejek dalam ujian).

Kelemahan terbesar kepada PgTyped ialah isu Github ini — kebolehbatalan jenis tidak dihormati, yang boleh mengecewakan kerana ini bermakna anda mungkin lulus secara munasabah untuk medan yang diperlukan. Kelemahan lain adalah khusus untuk Postgres… lebih lanjut mengenainya kemudian dalam bahagian "mudah alih".

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.

• Alternatif termasuk pembina pertanyaan seperti jOOQ dan Drizzle, di mana anda menulis SQL secara langsung, tetapi jurangnya masih kecil.

• Pertimbangan semasa memilih perpustakaan DB termasuk kemudahalihan, verbositi dan keperluan untuk pertanyaan kompleks berbanding operasi CRUD mudah.

Atas ialah kandungan terperinci Perpustakaan untuk menulis SQL mentah dengan selamat. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!