Bibliotheken zum sicheren Schreiben von Roh-SQL

Eine meiner Aufgaben bei PropelAuth ist das Schreiben von Beispiel-Apps/Anleitungen in verschiedenen Sprachen/Frameworks. Es ist wirklich einer der unterhaltsamsten Teile meines Jobs. Ich kann mit verschiedenen neuen und alten Stacks herumspielen und herausfinden, wie wir unsere Kunden am besten unterstützen können.

Aus diesem Grund erstelle ich am Ende viele Projekte von Grund auf. Immer wenn ich ein neues Projekt beginne, muss ich ein paar wichtige Entscheidungen treffen – und eine Entscheidung, mit der ich oft viel Zeit verbringe, ist:

Welche DB-Bibliothek soll ich verwenden?

Für mich sind die Bibliotheken, die ich bevorzuge, diejenigen, die so wenig Abstraktionsebenen zwischen dem Code, den ich schreibe, und der SQL-Abfrage selbst haben.

Ein Grund dafür ist einfach die Praktikabilität – da ich häufig die Sprache wechsle, habe ich nicht so viel Zeit, mich fließend in ein bestimmtes ORM einzuarbeiten. Ich hatte in der Vergangenheit auch Jobs, die stark datenwissenschaftliche Komponenten enthielten, daher ist SQL etwas, mit dem ich mich sehr wohl fühle.

Aber ich bin auch ein Entwickler, der „Magie“ nicht mag – deshalb meide ich Bibliotheken, in denen ich nicht leicht sagen kann, wie das generierte SQL aussehen wird, oder Bibliotheken, in denen ich das Gefühl habe, dass ich meine ganze Zeit verbringe Googeln Sie „wie man einen Join in X durchführt“, gefolgt von „wie man einen Join in X mit zwei Bedingungen durchführt“.

In diesem Beitrag wollte ich einige Bibliotheken hervorheben, nach denen ich häufig greife, sowie solche, die ich gerne ausprobieren möchte, und die alle versuchen, den Unterschied zwischen dem Code, den ich schreibe, und dem SQL, das ich schreibe, zu minimieren ausgeführt.

Mein persönlicher Favorit: SQLx

Eine meiner Lieblings-Rust-Kisten ist SQLx.

In ihren eigenen Worten:

SQLx unterstützt überprüfte Abfragen zur Kompilierungszeit. Dies geschieht jedoch nicht durch die Bereitstellung einer Rust-API oder DSL (domänenspezifische Sprache) zum Erstellen von Abfragen. Stattdessen werden Makros bereitgestellt, die reguläres SQL als Eingabe verwenden und sicherstellen, dass es für Ihre Datenbank gültig ist. Dies funktioniert so, dass SQLx zur Kompilierungszeit eine Verbindung zu Ihrer Entwicklungsdatenbank herstellt, damit die Datenbank selbst Ihre SQL-Abfragen überprüft (und einige Informationen dazu zurückgibt).

Anders ausgedrückt: Mit SQLx können Sie eine Abfrage wie diese schreiben:

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

was vielleicht normal erscheint, aber wenn Sie Ihren Code kompilieren, erhalten Sie eine Fehlermeldung wie diese:

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

Die gleiche Überprüfung zur Kompilierungszeit gilt auch für komplexe Abfragen:

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"

Da die Abfrage mit der Datenbank abgeglichen wird, funktioniert dies auch mit allen von Ihnen installierten Erweiterungen.

Warum ist das cool?

Das Unglaubliche daran ist, dass wir buchstäblich nur SQL schreiben. Aber im Gegensatz zu einer Kiste wie Postgres, mit der Sie auch rohes SQL schreiben können, verhindert SQLx, dass wir dumme Fehler machen.

Dies ist zwar mit geringen Kosten verbunden – wir haben jetzt eine Abhängigkeit zur Kompilierungszeit von einer Datenbank, aber SQLx behebt dieses Problem mit einem „Offline-Modus“. Wenn Ihre Datenbank verfügbar ist, können Sie eine Datei mit allen validierten Abfragen generieren, und dann prüft SQLx in Ihrem Build diese Datei anstelle der Datenbank.

In meinem Bestreben, den Unterschied zwischen dem von mir geschriebenen Code und dem ausgeführten SQL zu minimieren, gibt es mit SQLx keinen Unterschied UND ich musste dafür keine Abstriche bei der Sicherheit machen.

Generieren Sie TS-Schnittstellen für Ihr SQL mit PgTyped

Wie so oft im JavaScript/TypeScript-Ökosystem gibt es hier viele Optionen.

Es gibt Optionen wie Kysely, die TS-Typen aus Ihrer Datenbank generieren und dann sowohl einen Abfrage-Builder als auch eine Möglichkeit zum Schreiben von Roh-SQL bereitstellen. Es gibt Drizzle, einen Abfrage-Builder, dessen erklärtes Ziel jedoch darin besteht, das Delta zwischen dem von Ihnen geschriebenen TS-Code und dem generierten SQL zu verringern. Es gibt sogar einen SQLx-Port, den ich noch nicht ausprobieren konnte.

Aber die Bibliothek, die am besten zu dem passt, was ich hier suche, ist PgTyped. Mit PgTyped definieren Sie Ihre Abfragen in separaten Dateien wie folgt:

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

Sie führen dann den Befehl npx pgtyped -c config.json aus, der eine Funktion mit den richtigen Typen basierend auf Ihrem Schema generiert:

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

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

Sie können diese Funktion aufrufen, um die Ergebnisse aus der Datenbank abzurufen. Wichtig: Wenn Ihre Abfrage falsch ist (sagen wir, sie verweist auf eine Spalte, die nicht existiert), erhalten Sie eine Fehlermeldung wie diese:

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

Das bedeutet, dass Sie nicht nur Roh-SQL sicher schreiben können, sondern Ihr Anwendungscode auch eine schöne TS-Abstraktion erhält, die er aufrufen (oder in Tests simulieren) kann.

Der größte Nachteil von PgTyped ist dieses Github-Problem – die NULL-Zulässigkeit von Typen wird nicht respektiert, was ziemlich frustrierend sein kann, da es bedeutet, dass Sie für ein Pflichtfeld vernünftigerweise NULL übergeben können. Ein weiterer Nachteil ist, dass es spezifisch für Postgres ist … mehr dazu später im Abschnitt „Portabilität“.

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.

• Alternativen umfassen Abfrageersteller wie jOOQ und Drizzle, bei denen Sie direkt SQL schreiben, aber die Lücke ist immer noch gering.

• Zu den Überlegungen bei der Auswahl einer DB-Bibliothek gehören Portabilität, Ausführlichkeit und die Notwendigkeit komplexer Abfragen im Vergleich zu einfachen CRUD-Operationen.

Das obige ist der detaillierte Inhalt vonBibliotheken zum sicheren Schreiben von Roh-SQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!