Pix Api Data

API permettant d'interroger le datamart avec des requêtes préparées par l'équipe Data.

Le Datamart est la base datawarehouse-data constituée de tables copiées depuis la production de Pix et de dataset produits par l'équipe data à partir de la production également.

L'objectif du projet, initié lors des Tech Days 2023, est d'offrir dans sa première version un service de récupération de données statistiques aux applications Pix (orga / admin) et dans un second temps à des tiers.

Fonctionnement

L'API présente un catalogue de requêtes paramétrables à faire exécuter. Le client authentifié formule une demande d'exécution d'une requête en indiquant son ID et les paramètres qu'il souhaite y injecter. L'API retourne les résultats de l'exécution de cette requête.

Architecture

L'ensemble est constitué d'un serveur Node connecté à deux bases de données :

API_DATABASE: la base de données dédiée à l'API. Elle contient les données utilisateurs ainsi que le catalogue des requêtes exécutables par les utilisateurs. Elle est hébergée au même endroit que l'API. L'adresse de cette base est lue depuis la variable d'environnement DATABASE_API_URL.
DATAMART_DATABASE: la base de données sur laquelle les requêtes demandées par l'utilisateur vont être exécutées. Cette base n'est consultée qu'en lecture uniquement et est externe à l'API. L'adresse de cette base est lue depuis la variable d'environnement DATAMART_DATABASE_URL.

Les bases de données en fonction de l'environnement

Développement

API_DATABASE: serveur PG local via Docker sur une base de développement. Les données proviennent des seeds.
DATAMART_DATABASE: serveur PG local via Docker. Les données proviennent d'un script SQL exécuté au démarrage du container Docker (dans data/sql/init_db.sql).

Tests auto

API_DATABASE: serveur PG local via Docker sur une base de test. Les données proviennent des tests.
DATAMART_DATABASE: serveur PG local via Docker. Les données proviennent d'un script SQL exécuté au démarrage du container Docker (dans data/sql/init_db.sql).

Intégration

API_DATABASE: add-on PG provisionné sur l'application de l'API sur Scalingo. Les données proviennent des seeds ainsi que de l'usage.
DATAMART_DATABASE: idem que API_DATABASE. Le script data/sql/init_db.sql a été exécuté sur cette base afin d'avoir un set de données minimal pour tester.

Production

API_DATABASE: add-on PG provisionné sur l'application de l'API sur Scalingo. Les données proviennent de l'usage.
DATAMART_DATABASE: base externe (a priori datawarehouse-data).

Lancement du serveur en local

Opérations à réaliser

Copier le fichier sample.env en .env.

Puis lancer les commandes suivantes :

docker-compose up -> démarrage des instances Postgres API_DATABASE et DATAMART_DATABASE
npm ci-> installation des packages du projet
npm run db:reset -> reset de la base API_DATABASE contenant les requêtes et les utilisateurs
npm run start -> démarrage du serveur Node

Tests autos

npm run test

Tests manuels

Lancer les requêtes présentes dans le répertoire tests/sample-requests (natif sur WebStorm, nécessite le plugin RestClient sur VSCode). Les réponses doivent correspondre aux commentaires présents en-tête des requêtes. L'exécution d'une requête sur /query nécessite en premier lieu de récupérer un token d'authentification via la route /token, puis de positionner ce token dans l'en-tête (après "Bearer ").

Ajout de requêtes

Une requête est composée :

d'un template SQL paramétrable
de la liste de paramètres associée au template

Les requêtes sont stockées dans la base pix_api_data.

Syntaxe du template SQL

Sans paramètre

Rédiger une requête standard.

Exemple :

SELECT count(*) from data_ref_academies

Avec paramètres

Rédiger une requête ou les valeurs de paramètres sont remplacées par un token du type {{ nom_parametre }} (les espaces dans les accolades sont obligatoires)

Exemple :

SELECT nom, region from data_ref_academies where id = {{ id_academie }}

Le paramètre est ici : id_academie. Il a pour type int et est obligatoire.nécessaire Si le type envoyé ne correspond pas au type défini, l'api renverra une erreur.

Types de paramètres

string
int
date : au format YYYY-MM-DD
date-time : au format YYYY-MM-DD HH:mm:ss
float
boolean : true ou false uniquement (pas de O/N, 0/1)
string-array
int-array
float-array

Caractère obligatoire / facultatif

Il est possible de définir un bloc comme étant facultatif à l'aide de crochets. Le bloc entre crochets ne sera ajouté que si les paramètres sont renseignées dans l'appel.

Exemple :

SELECT nom, region FROM data_ref_academies WHERE 1=1 [[ AND id = {{ id_1 }} ]]

Les valeurs des paramètres obligatoires doivent systématiquement être fournies par l'utilisateur de l'API pour exécuter la requête concernée. Ils seront donc systématiquement injectés dans la requête et ne nécessitent aucune précaution particulière dans la rédaction de la requête. Les blocs facultatifs quand à eux, amènent un cas particulier :

Si un bloc facultatif contient plusieurs paramètres, si un paramètre est renseigné, il est nécessaire que chacun des autres paramètres soie renseigné, par exemple dans la requête ci-dessous, si un des deux élements n'est pas renseigné l'API renverra une erreur :

SELECT nom, region FROM data_ref_academies WHERE 1=1 AND [[ (id = {{ id_1 }} OR id = {{ id_2 }}) ]]

Cas particulier des clauses : WHERE ... IN

L'injection d'un tableau via knex oblige l'utilisation du = ANY() à la place du IN. En effet un tableau[1, 2]sera converti en '{1,2}'.

Exemple:

SELECT nom, region FROM data_ref_academies WHERE id = ANY( {{ id_array }} )

Pour le NOT IN il faut passer par la fonction ALL().

Exemple:

SELECT nom, region FROM data_ref_academies WHERE id <> ALL( {{ id_array }} )

Insertion dans la base

Il est possible d'insérer les requêtes et les paramètres associés à la main directement sur la base pix_api_data de la façon suivante:

INSERT INTO catalog_queries(query_id, sql_query, created_at) VALUES (UUID, my_query, my_params);
INSERT INTO catalog_query_params(catalog_query_id, name, type, mandatory) VALUES (UUID, name, param_type, boolean);

⚠️ Cette méthode est néanmoins grande source d'erreurs de saisie.

C'est pourquoi un script a été mis à disposition afin d'accompagner les équipes responsables de l'élaboration du catalogue dans la réalisation et l'insertion de requêtes.

Ce script prend en paramètre un fichier CSV (avec pour séparateur ,) construit de la manière suivante : requete,param1,param2,param3,...,paramN.

Exemple : exemple de fichier csv valide

Il permet de:

Vérifier la correspondance des paramètres présents dans la requête avec ceux fournis dans le CSV
Tester/effectuer, dans le cas où le fichier est valide, l'insertion dans le catalogue des requêtes et de leurs paramètres

Il s'utilise sur un container Scalingo de la manière suivante :

scalingo -a <nom-application-scalingo> run --file ./my_awesome_queries.csv "node build/scripts/prod/add-queries-from-csv.js --file /tmp/uploads/my_awesome_queries.csv"

Le script est assorti d'une option --run laquelle permet de réaliser et de persister l'insertion des requêtes.

scalingo -a <nom-application-scalingo> run --file ./my_awesome_queries.csv "node build/scripts/prod/add-queries-from-csv.js --file /tmp/uploads/my_awesome_queries.csv --run"

Ajout d'un utilisateur

Il est réalisé en ajoutant un enregistrement dans la tables Users en BDD à l'aide des commandes suivantes :

scalingo --app <nom-application-scalingo> run "node build/scripts/prod/add-user.js --username <userName> --label <userLabel> --password <userPassword>"

Utilisation de l'API

Authentification

Il faut récupérer un token en se connectant avec un login/mot de passe valide via un

POST http://DATABASE_API_URL/token

avec en paramètre

{
"username": "dev",
"password": "LeMotDePasseQueL'UtilisateurUtiliseraitDeSonPointDeVue"
}

On récupérera le token dans la réponse

ACCESS_TOKEN=${response.body.data}

Utilisation d'une requête

Sans paramètre utilisé dans la requête

POST http://DATABASE_API_URL:3000/query

Pour l'autorisation:

Authorization: Bearer ACCESS_TOKEN

et en paramètre de post

{
 "queryId": "UUID",
 "params": []
}

Avec paramètre utilisé dans la requête Il n'y a pas besoin de spécifier si les paramètres sont obligatoires voir ici

POST http://DATABASE_API_URL:3000/query

Pour l'autorisation:

Authorization: Bearer ACCESS_TOKEN

et en paramètre de post

{
 "queryId": "UUID",
 "params":[
  {
   "name": "id_list",
   "value": [1, 10, 20, 30]
  }
 ]
}

1024pix/pix-api-data

Pix Api Data

Fonctionnement

Architecture

Les bases de données en fonction de l'environnement

Développement

Tests auto

Intégration

Production

Lancement du serveur en local

Opérations à réaliser

Tests autos

Tests manuels

Ajout de requêtes

Syntaxe du template SQL

Sans paramètre

Avec paramètres

Types de paramètres

Caractère obligatoire / facultatif

Cas particulier des clauses : WHERE ... IN

Insertion dans la base

Ajout d'un utilisateur

Utilisation de l'API

Authentification

Utilisation d'une requête