Bibliothèques pour écrire du SQL brut en toute sécurité

L'une de mes responsabilités chez PropelAuth consiste à rédiger des exemples d'applications/guides dans différents langages/frameworks. C’est vraiment l’une des parties les plus amusantes de mon travail. Je peux jouer avec différentes piles, nouvelles et anciennes, et trouver les meilleures façons de soutenir nos clients.

À cause de cela, je finis par créer beaucoup de projets à partir de zéro. Chaque fois que je démarre un nouveau projet, je dois faire quelques choix importants — et une décision sur laquelle j'ai tendance à consacrer beaucoup de temps est :

Quelle bibliothèque DB dois-je utiliser ?

Pour moi, les bibliothèques vers lesquelles je gravite sont celles qui ont le moins de couches d'abstraction entre le code que j'écris et la requête SQL elle-même.

L'une des raisons à cela est simplement pratique : comme je change souvent de langue, je n'ai pas autant de temps pour maîtriser un ORM en particulier. J'ai également occupé des emplois dans le passé qui comportaient de lourdes composantes de science des données, donc SQL est quelque chose avec lequel je suis très à l'aise.

Mais je suis aussi un développeur qui a tendance à détester la « magie » — j'évite donc les bibliothèques dans lesquelles je ne peux pas facilement dire à quoi ressemblera le SQL généré, ou les bibliothèques dans lesquelles j'ai l'impression de passer tout mon temps. en recherchant sur Google "comment rejoindre X" suivi de "comment rejoindre X avec deux conditions".

Dans cet article, je voulais mettre en évidence quelques bibliothèques que j'utilise fréquemment, ainsi que celles que j'ai hâte d'essayer, qui tentent toutes de minimiser la différence entre le code que j'écris et le SQL qui est exécuté.

Mon préféré : SQLx

L'une de mes caisses Rust préférées est SQLx.

Dans leurs propres mots :

SQLx prend en charge requêtes vérifiées au moment de la compilation. Cependant, il ne le fait pas en fournissant une API Rust ou un DSL (langage spécifique au domaine) pour créer des requêtes. Au lieu de cela, il fournit des macros qui prennent du SQL standard en entrée et garantissent qu'il est valide pour votre base de données. La façon dont cela fonctionne est que SQLx se connecte à votre base de données de développement au moment de la compilation pour que la base de données elle-même vérifie (et renvoie des informations sur) vos requêtes SQL.

En d'autres termes, SQLx vous permet d'écrire une requête comme celle-ci :

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

ce qui peut paraître standard, mais lorsque vous compilez votre code, vous obtiendrez une erreur comme celle-ci :

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

Cette même vérification au moment de la compilation s'applique également aux requêtes complexes :

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"

Étant donné que la requête est vérifiée par rapport à la base de données, cela fonctionnera également avec toutes les extensions que vous avez installées.

Pourquoi est-ce cool ?

Ce qui est incroyable, c'est que nous écrivons littéralement du SQL. Mais contrairement à une caisse comme postgres, qui permet également d'écrire du SQL brut, SQLx nous empêche de commettre des erreurs stupides.

Cela a un petit coût : nous avons maintenant une dépendance au moment de la compilation sur une base de données, mais SQLx résout ce problème avec un « mode hors ligne ». Lorsque votre base de données est disponible, vous pouvez générer un fichier avec toutes les requêtes validées, puis dans votre build, SQLx vérifiera ce fichier à la place de la base de données.

Dans ma quête visant à minimiser la différence entre le code que j'écris et le code SQL exécuté, avec SQLx, il n'y a aucune différence ET je n'ai pas eu à sacrifier la sécurité pour l'obtenir.

Générez des interfaces TS pour votre SQL avec PgTyped

Comme c'est souvent le cas dans l'écosystème JavaScript/TypeScript, il existe de nombreuses options ici.

Il existe des options comme Kysely qui génèrent des types TS à partir de votre base de données et fournissent ensuite à la fois un générateur de requêtes et un moyen d'écrire du SQL brut. Il existe Drizzle, qui est un générateur de requêtes, mais son objectif déclaré est de réduire le delta entre le code TS que vous écrivez et le SQL généré. Il existe même un portage SQLx que je n'ai pas encore eu l'occasion d'essayer.

Mais la bibliothèque qui correspond le mieux à ce que je recherche ici est PgTyped. Avec PgTyped, vous définissez vos requêtes dans des fichiers séparés comme ceci :

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

Vous exécutez ensuite une commande npx pgtyped -c config.json qui génère une fonction avec les types appropriés en fonction de votre schéma :

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

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

Vous pouvez appeler cette fonction pour obtenir les résultats de la base de données. Surtout, si votre requête est erronée (disons qu'elle fait référence à une colonne qui n'existe pas), vous obtenez une erreur comme celle-ci :

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

Cela signifie que vous pouvez non seulement écrire du SQL brut en toute sécurité, mais votre code d'application obtient une belle abstraction TS à appeler (ou à simuler dans les tests).

Le plus gros inconvénient de PgTyped est ce problème avec Github : la nullité des types n'est pas respectée, ce qui peut être assez frustrant car cela signifie que vous pouvez raisonnablement transmettre null pour un champ obligatoire. Un autre inconvénient est sa spécificité à Postgres… nous en reparlerons plus tard dans la section « portabilité ».

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.

• Les alternatives incluent des générateurs de requêtes comme jOOQ et Drizzle, où vous écrivez directement du SQL, mais l'écart est encore faible.

• Les considérations lors du choix d'une bibliothèque de base de données incluent la portabilité, la verbosité et la nécessité de requêtes complexes par rapport à de simples opérations CRUD.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!