Aller au contenu

API Rest (Service web) avec PostgreSQL

Voici comment migrer les modèles de vos API de MySQL vers une BD PostgreSQL:

PostgreSQL et Devilbox

Devilbox à déjà une instance de PostgreSQL (pgsql-1) qui est lancée quand on le démarre, pratique si on veut effectuer des tests. Elle est accessible via le port 5432 en localhost.

Par défaut le nom d'utilisateur est postgres et le mot de passe est vide. Il y a aussi une base de données appelée postgres où vous allez créer vos table.

À l'aide de votre IDE préféré créez une connexion vers l'instance PostgreSQL. Dans DBeaver, quand vous créer une nouvelle connexion vous devez choisir PostgreSQL au lieu de MariaDB. Vous devrez surement installer des pilotes, juste à suivre les indications.

postgres_01.png

Exemple avec DBeaver

Migration des données MySQL vers une BD PostgreSQL

Le plus simple à notre niveau est de refaire un script sql de création de tables. Les instructions et les types de données sont sensiblement les mêmes à quelques exceptions près. Voici par exemple la création de la table pokemon qu'on a utilisée à l'exercice 04, adaptée à PostgreSQL.

create_table_pokemon.sql
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
DROP TABLE IF EXISTS public.pokemon;
CREATE TABLE public.pokemon (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(50),
    type_primaire VARCHAR(50),
    type_secondaire VARCHAR(50),
    pv INTEGER,
    attaque INTEGER,
    defense INTEGER
);

Dans PostgreSQL il existe un concept de schemas qu'on ne couvrira pas (dans l'exemple public est le nom du schema). Le seul autre changement qu'on peut remarquer ici est au niveau du champ id qui est de type SERIAL, c'est l'équivalent du champs auto-increment.

Manuel

Connexion depuis un projet Express.js

Nous allons conserver la même structure de projet qu'on a utilisé jusqu'à maintenant. Dans le fichier .env ajoutez les entrées suivantes :

.env
PG_USER="postgres"
PG_HOST="localhost"
PG_DATABASE="postgres"
PG_PASSWORD=""
PG_PORT=5432

Les valeurs ci-dessus correspondent à une connexion à l'instance PostgreSQL de Devilbox.

Ensuite installez le module pg avec npm. Documentation du module: https://node-postgres.com/. Il supporte une utilisation asynchrone (Promises - async/await).

npm install pg

Maintenant créez un nouveau fichier nommé db_pg.js dans le répertoire ./src/config et copiez le code suivant :

db_pg.js
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
import pg from 'pg'
import dotenv from 'dotenv';
dotenv.config();

let params = { 
  user: process.env.PG_USER,
  host: process.env.PG_HOST,
  database: process.env.PG_DATABASE,
  password: process.env.PG_PASSWORD,
  port: process.env.PG_PORT
}

// SSL est desactivé par default, mais on peut l'activer en ajoutant PG_SSL=true 
//   dans le .env
// Nécessaire pour se connecter à la BD PostgreSQL sur render.com de l'extérieur
if (process.env.PG_SSL) {
  params.ssl = {
    rejectUnauthorized: false
  }
}

const pool = new pg.Pool(params);
export default pool;

Le fonctionnement est similaire à ce qu'on faisait avec MySQL, on créé un pool de connexion qui va être utilisé à chaque fois qu'on veut faire une requête.

La dernière chose à faire est d'utiliser le fichier db_pg.js au lieu de db.js dans les modèles.

script.js
1
2
3
4
5
// Ancienne connexion à MySQL
// const sql = require("../config/db");
// Nouvelle connexion à PostGreSQL

import pool from "../config/db_pg.js";

Modification des requêtes préparées

La syntaxe pour passer des paramêtres à une requête préparée avec le module PG est un peu différente, vous allez devoir les modifier. Au lieu d'utiliser le caractère ? pour indiquer un paramêtre vous devez utiliser le signe de dollar suivi d'un nombre incrémenté à partir de 1.

const requete = 'SELECT id, nom, type_primaire, type_secondaire, pv, attaque, defense FROM public.pokemon WHERE type_primaire LIKE $1;';
// Exemple d'un INSERT - attention PostgreSQL ne retourne pas de valeurs sur INSERT par defaut - il faut préciser si on veut avoir l'id (ou indiquer RETURNING *; par avoir tous les champs)
const requete = 'INSERT INTO pokemon (nom, type_primaire, type_secondaire, pv, attaque, defense) VALUES ($1, $2, $3, $4, $5, $6) RETURNING id;';

Vous devez aussi modifier la façon dont vous récupérez les résultats. Auparavant quand on lançait la requête avec sql.query on recevait un objet resultat qui contenait un tableau de toutes les lignes du résultat de la requête. Avec PG vous devez faire resultat.rows pour obtenir les résultats.

model.js
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
const trouverUnPokemon = async (id) => {
    const requete = 'SELECT id, nom, type_primaire, type_secondaire, pv, attaque, defense FROM pokemon WHERE id = $1';
    const parametres = [id];

    try {
        // Toujours pool.query() avec PostgreSQL - requêtes préparées utilisent un autre mécanisme
        const resultat = await pool.query(requete, parametres);
        // PostgreSQL:  resultats dans la propriété "rows"
        return resultat.rows;
    } catch (erreur) {
        console.error(`Erreur ${erreur.code} : ${erreur.message}`);
        throw erreur; // On relance l'erreur
    }
}

Remarquez aussi que dans l'erreur, sqlState est remplacé par code et sqlMessage par message.

Et voilà! Vous êtes maintenant prêt à utiliser PostgreSQL.