N'interrogez plus votre base PostgreSQL, préférez PostgREST

L'année dernière, j'avais une grosse application développée en Laravel qui nécessitait une base MySQL. En migrant vers PostgreSQL, j'ai découvert PostgREST, qui m'a permis de supprimer complètement les requêtes de mon code.

Ne vous trompez pas : mon code Laravel/PHP lançait des dizaines de requêtes vers la base et, après migration, plus aucune.

Mes tables, mes modèles, mes requêtes SQL : j'ai pu tout retirer de mon code. Mon code PHP a été considérablement allégé et simplifié.

Ce que PostgREST fait pour vous​

Il y a une table todos dans une base PostgreSQL. Aucun contrôleur, aucun fichier de routes, aucun modèle — rien n'a été écrit. Et pourtant :

$ curl http://localhost:3000/todos | jq▋

[
  {
    "id": 1,
    "done": false,
    "task": "finish tutorial 0",
    "due": null
  },
  {
    "id": 2,
    "done": false,
    "task": "pat self on back",
    "due": null
  }
]

Du JSON prêt à consommer, directement issu de la table, sans une seule ligne de code backend. C'est terriblement simple, non ?

Pourquoi ça fonctionne​

• Le schéma de la base est l'API. « PostgREST is a standalone web server that turns your PostgreSQL database directly into a RESTful API. The structural constraints and permission in the database determine the API endpoints and operations » (documentation officielle). Une table devient un endpoint, une colonne devient un champ.
• Les permissions sont la couche de sécurité, et elles sont là où elles doivent être : dans PostgreSQL. Une table users que vous n'accordez jamais au rôle anonyme n'existe tout simplement pas du point de vue de l'API.
• Il n'y a plus rien à maintenir synchronisé. Après ma propre migration, j'ai supprimé 100 % du code déclarant les tables et les champs, les déclarations de relations et les requêtes. Ce qui était une couche de modèles est devenu des URLs.

À nous de jouer​

Pour cet article, créons un dossier temporaire dans votre dossier /tmp : ouvrez une console Linux et lancez mkdir /tmp/postgrest && cd $_.

Étape 1 - Créer et alimenter notre base PostgreSQL​

Nous allons créer un container Docker pour notre base PostgreSQL :

$ docker run --name tutorial -p 5433:5432 

-e POSTGRES_PASSWORD=mysecretpassword 

-d postgres▋

Maintenant, entrons dans notre container PostgreSQL et lançons psql :

$ docker exec -it tutorial psql -U postgres▋

Copiez/collez le code ci-dessous dans votre console. Cela va créer une base appelée api avec une table todos contenant deux enregistrements. Cela va aussi créer un utilisateur web_anon que nous utiliserons avec PostgREST pour interroger nos données :

create schema api;

create table api.todos (
  id serial primary key,?
  done boolean not null default false,
  task text not null,
  due timestamptz?
);

insert into api.todos (task) values
  ('finish tutorial 0'), ('pat self on back');

create role web_anon nologin;?

grant usage on schema api to web_anon;
grant select on api.todos to web_anon;

create role authenticator noinherit login password 'mysecretpassword';?
grant web_anon to authenticator;?

Pour quitter la console postgres, tapez simplement \q.

Étape 2 - Installer et exécuter PostgREST​

PostgREST est un binaire, téléchargez-le en lançant :

curl -o postgrest-v14.16-linux-static-x86-64.tar.xz -L https://github.com/PostgREST/postgrest/releases/download/v14.16/postgrest-v14.16-linux-static-x86-64.tar.xz
tar xJf postgrest-v14.16-linux-static-x86-64.tar.xz && rm -f postgrest-v14.16-linux-static-x86-64.tar.xz▋

Vous avez maintenant un fichier postgrest dans votre dossier.

Il nous faut une configuration : créez un fichier tutorial.conf avec ce contenu :

db-uri = "postgres://authenticator:mysecretpassword@localhost:5433/postgres"?
db-schemas = "api"
db-anon-role = "web_anon"?

server-port = 3000?

Lançons maintenant un container Docker pour PostgREST :

$ ./postgrest tutorial.conf▋

D'autres requêtes​

À l'étape 1, nous avons créé et alimenté notre base PostgreSQL ; à l'étape 2, nous avons installé et configuré PostgREST pour utiliser cette base. À partir de maintenant, on peut l'utiliser directement comme n'importe quelle API.

Chaque requête est composée d'une partie fixe (l'URL de notre serveur PostgREST), à savoir http://localhost:3000, suivie de la requête elle-même. Pour récupérer le contenu d'une table, mentionnez simplement son nom — c'est l'appel http://localhost:3000/todos montré en haut de cet article.

Pour des raisons esthétiques, j'utilise ici | jq (vous pouvez retirer cette partie si vous voulez). Consultez mon article L'utilitaire jq pour Linux pour en savoir plus sur jq.

Utiliser un filtre​

Vous pouvez utiliser des filtres en tapant p.ex. ? suivi d'un nom de champ et d'un critère. Pour obtenir uniquement l'enregistrement dont l'id vaut 2, voici comment faire :

$ curl http://localhost:3000/todos?id=eq.2 | jq
▋

Et si vous souhaitez faire une recherche full text pour retrouver un contenu à partir d'une valeur, voici comment faire :

$ curl http://localhost:3000/todos?task=fts.tutorial | jq
▋

Dans l'exemple ci-dessus, on recherche tous les todos dont le champ task contient le mot tutorial.

Ci-dessous, une requête sur le champ done qui doit être vrai.

$ curl http://localhost:3000/todos?done=is.true | jq
▋

Ci-dessous, nous demandons uniquement les champs id et task :

$ curl http://localhost:3000/todos?select=id,task | jq
▋

Depuis le frontend​

Comme il s'agit de simples endpoints HTTP, votre frontend les consomme directement. En JavaScript, avec axios :

const todos = axios.create({
    baseURL: 'http://localhost:3000/todos',
    headers: {
      'Accept': 'application/json'
    }
})

Sous le capot (passez cette section si vous voulez juste l'utiliser)​

Permissions requises​

En utilisant PostgREST, vous exposez vos tables et vos enregistrements via une API RESTful. Naturellement, il existe un système de permissions qui permet de définir ce qui est accessible (p.ex. une table users restera secrète) et ce qui peut être fait (p.ex. un utilisateur n'aura qu'un accès en lecture, un autre un accès en lecture-écriture).

OpenAPI​

PostgREST est conforme à OpenAPI. Il est donc possible de documenter automatiquement ses routes avec l'image Docker Swagger UI.

Cela signifie qu'en lançant curl http://localhost:3000 (l'URL de PostgREST), vous obtenez la liste de toutes les tables auxquelles vous avez accès (avec votre clé d'accès). Votre base devient ainsi ouverte au monde (encore une fois, uniquement ce que vous avez autorisé avec les bonnes permissions).

Arrêter PostgREST​

Retournez dans la console où vous avez démarré PostgREST et appuyez sur CTRL-C pour l'arrêter.

Si vous avez démarré PostgreSQL plus haut, vous pouvez l'arrêter et le supprimer avec docker container stop tutorial ; docker container rm tutorial.

Illustration de quelques appels​

Citizens​

• Obtenir la liste de tous les citizens : clear ; curl http://127.0.0.1:3000/citizens | jq
• Uniquement le citizen ID 69 : clear ; curl http://127.0.0.1:3000/citizens?id=eq.69 | jq
• Uniquement le citizen ID 69, avec id, firstname et lastname : clear ; curl http://127.0.0.1:3000/citizens?select=id,first_name,last_name&id=eq.69 | jq
• Obtenir Jean, mais seulement celui qui parle néerlandais (language ID 2) (c'est-à-dire un WHERE avec deux conditions) : clear ; curl http://127.0.0.1:3000/citizens?first_name=fts.Jean&language_id=eq.2 | jq

Workers​

• Obtenir la liste de tous les workers, les cinq premiers : clear ; curl http://127.0.0.1:3000/workers?select=id,email&limit=5 | jq

• Obtenir la liste de tous les workers, les cinq suivants : clear ; curl http://127.0.0.1:3000/workers?select=id,email&limit=5&offset=5 | jq

• Ordre inverse, obtenir les 10 derniers : clear ; curl http://127.0.0.1:3000/workers?select=id,email&limit=10&offset=0&order=id.desc | jq

• Obtenir le worker id 15 : clear ; curl http://127.0.0.1:3000/workers?id=eq.15 | jq — comme on le voit, la sortie est un tableau avec un seul enregistrement

• Obtenir le worker id 15 - sans tableau : clear ; curl http://127.0.0.1:3000/workers?id=eq.15 -H "Accept: application/vnd.pgrst.object+json" | jq (c'est ici plus simple et plus logique pour le frontend)

Utiliser une inner join​

• Obtenir la liste des workers avec leurs first_name et last_name (limité au worker ID 73) : clear ; curl http://127.0.0.1:3000/workers?select=id,email,citizens(first_name,last_name)&id=eq.73 | jq
• Ajouter le code de langue : clear ; curl http://127.0.0.1:3000/workers?select=id,email,citizens(first_name,last_name,language_id),languages(code)&id=eq.73 | jq

Levels​

• Obtenir la liste de tous les levels : clear ; curl http://127.0.0.1:3000/levels | jq
• Obtenir la liste des levels dont l'ID est supérieur à 10, uniquement ID et code : clear ; curl http://127.0.0.1:3000/levels?select=id,code&id=gt.10 | jq

Translations​

• Obtenir la liste des traductions contenant le mot Technical : clear ; curl http://127.0.0.1:3000/translations?value=fts.Technical | jq
• Obtenir la liste des traductions commençant par le mot Technical, sans tenir compte de la casse : clear ; curl http://127.0.0.1:3000/translations?value=ilike.technical* | jq

Generic profiles​

• Obtenir la liste des generic profiles actifs : clear ; curl http://127.0.0.1:3000/generic_profiles?active=is.true | jq
• et renommer le champ code en GenericProfileCode : curl http://127.0.0.1:3000/generic_profiles?select=id,GenericProfileCode:code&active=is.true | jq

Format de sortie​

• Obtenir la liste en JSON : clear ; curl http://127.0.0.1:3000/workers -H "Accept: application/json"
• Obtenir la liste en CSV : clear ; curl http://127.0.0.1:3000/workers -H "Accept: text/csv"
• Obtenir la liste en TEXT - ici, il faut ne retourner qu'une seule valeur : clear ; curl http://127.0.0.1:3000/workers?select=email&id=eq.15 -H "Accept: text/plain"

Casting​

• Obtenir l'ID et l'email du worker 15 : clear ; curl http://127.0.0.1:3000/workers?select=id,email&id=eq.75 | jq
• Caster l'ID en chaîne : clear ; curl http://127.0.0.1:3000/workers?select=id::text,email&id=eq.75 | jq

Conclusion​

Un container, un binaire et un fichier .conf, et votre base de données répond en HTTP. Ce que je trouve remarquable, ce n'est pas la rapidité de la mise en place, c'est ce qui disparaît ensuite : les modèles, les déclarations de relations, les appels au query builder — tout un layer dont le seul rôle était de décrire, dans votre langage, quelque chose que PostgreSQL savait déjà.

Oracle a son équivalent, décrit dans Transformer une base Oracle en OpenData avec Oracle REST Data Services. Et pour regarder la base derrière l'API, Adminer, pgadmin or phpmyadmin reste bien pratique.