Ce tutoriel s'appuie sur les logiciels PgModeler, DBeaver et Git.

le but est de définir un processus pour définir des modifications (tables, vues, fonctions, rôles…) de bases de données, les versionner, les porter sur le serveur de “production”.

À partir de PgModeler, il est facile de réaliser un export initial, ou si la base existe déjà un import pour définir le modèle PgModeler (fichier XML .dbm également versionnable), puis avec son outils Diff, de générer des scripts SQL de modifications des tables et vues entre le modèle dans PgModeler que l'on a modifié et la base de donnée dans l'environnement de développement, sur laquelle il faut porter ses modifications du modèle.

Pour les fonctions, on pourra utiliser un outil graphique comme DBeaver afin de définir et tester les functions nécessaire, puis une fois crées, à nouveau avec PgModeler, générer le script de mise à jour en réalisant un Diff entre la base et le modèle.

Les scripts SQL définis à l'étape précédentes, étant des fichiers texte, peuvent être ajoutés/commité au versionnement du projet, avec git.
Il peut-être intéressant de versionner les différentes étapes de migration (pour application au fur et à mesure sur le serveur de prod), mais aussi un fichier contenant le tout qui pourra être utile pour un nouveau développeur ou pour un nouveau serveur.

Une fois le développement et les tests finaliser localement, il suffit de les exécuter sur le serveur de production, soit via DBeaver s'il a accès à la base de données, soit via la CLI… par exemple :

docker exec -i nom_du_conteneur /bin/bash -c "psql -U mon_db_user -d nom_de_la_base" < mon_chemin/mon_script_de_migration.sql

• emaj développé par Dalibo
• yoyo logiciel Python qui permet de générer, appliquer ou enlever (roolback) des modifications mémorisées sous forme de scripts python ou sql
• flywaydb logiciel en Java… ne semble pas générer les migrations mais seulement gérer les migrations

Nous allons reprendre en partie le tutoriel sur postgREST, en réalisant la modélisation et génération des scripts de migration avec PgModeler, le versionnement avec git, la visualisation avec DBeaver (ou en CLI avec psql)

Tout d'abord, on crée un conteneur docker pour disposer d'un serveur Postgis :

docker run --name tutorial -p 5433:5432 -e POSTGRES_PASSWORD=mysecretpassword -d postgis/postgis:13-3.1-alpine

Dans PgModeler, on définit un nouveau modèle, par un clic-droit puis Properties, on peut définir le nom de la base de donnée, j'ai mis tutorial et owner postgres,
puis par un clic-droit puis new→Database object→Schema on crée le schéma en spécifiant son nom (et éventuellement un commentaire) et owner postgres.

On crée la table town via un clic-droit puis new→Schema object→table et on y ajouter les attributs town_id et name
On ajoute ensuite le rôle web_anon : clic-droit puis new→Database object→Role
On peut évidemment sauvegarder notre début de modèle.

Et on génère notre premier script SQL, toujours avec PgModeler en cliquant sur le bouton Export puis on sélectionne SQL file et on saisit un nom de fichier, par exemple 1_create_schema_and_fist_table (l'extension .sql est automatiquement ajoutée) On peut éditer ce script (avec votre éditeur de code préféré, pour ma part Vim) et vérifié un peu ce qui a été généré, par exemple pour la table, j'ai :

CREATE TABLE location.town (
	town_id serial,
	name VARCHAR(255)
 
);
COMMENT ON TABLE location.town IS E'gestion des villes';
COMMENT ON COLUMN location.town.name IS E'nom courant de la ville/commune';

On applique ce script :

docker exec -i tutorial /bin/bash -c "psql -U postgres" < database/1_create_schema_and_fist_table.sql

On peut vérifier avec DBeaver ou psql que la base/schéma/table ont bien été créé. TODO pourquoi je retrouve mon schéma dans la base POSTGRES et non tutorial ??

On commence notre versionnement :

git init # inutile si associé à un projet existant qui est déja versionné
git add 1_create_schema_and_fist_table.sql
git commit -m "init et ajout du schema initial"

On peut faire aussi une copie de 1_create_schema_and_fist_table.sql vers tutorial_location.sql. Les fichiers 1_, 2_ (ou un préfixe de date AAAAMMJJ) ne contiendront que les scripts de migration (et n'évolueront donc pas), en revanche le fichier tutorial_location.sql pourra recevoir régulièrement un export complet de la base et servir ainsi à un nouveau développeur (qui pourrait repartir d'un dump de sauvegarde pour avoir des données avec)