Ce tutoriel s'appuie sur les logiciels PgModeler, Git et dans une moindre mesure sur DBeaver,
utilisés sous le système d'exploitation Ubuntu

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 finalisés 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_first_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 -d tutorial" < database/1_create_schema_and_first_table.sql

On peut vérifier avec DBeaver ou psql que la base/schéma/table ont bien été créé. Dans la commande ci-dessus, l'option -d tutorial qui permet de préciser la base de données contenant le schéma est importante sinon on retrouve le 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_first_table.sql
git commit -m "init et ajout du schema initial"

On peut faire aussi une copie de 1_create_schema_and_first_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)

Dans PgModeler, on modifie maintenant notre table town et on lui ajoute un attribut canonical_name var_char(255).
On lance le diff de PgModeler pour générer le script de mise à jour de la base :

Dans la fenêtre de diff, on laisse en Input le modèle courant, dans Compare to,
on sélectionne notre serveur local (notre conteneur docker), et la base de donnée définie précédemment tutorial.

et enfin, on définit dans Options le nom du fichier dans lequel le script va être sauver 2_table-town-ajout-canonical-name.sql dans notre capture

Il peut parfois être nécessaire d'utiliser aussi l'onglet Partial diff afin de limiter le diff à un schéma, ses tables/vues/fonctions… rôles :

On doit obtenir un fichier contenant notre modification de table :

-- [ Created objects ] --
-- object: canonical_name | type: COLUMN --
-- ALTER TABLE location.town DROP COLUMN IF EXISTS canonical_name CASCADE;
ALTER TABLE location.town ADD COLUMN canonical_name VARCHAR(255);
-- ddl-end --
 
COMMENT ON COLUMN location.town.canonical_name IS E'pour mémoriser les noms sous forme canonique, càd sans espaces ni accent…';
-- ddl-end --

que l'on applique à notre base de donnée :

docker exec -i tutorial bash -c "psql -U postgres -d tutorial" < 2_table-town-ajout-canonical-name.sql

puis après vérification, on pourra ajouter ce fichier au dépôt :

git add 2_table-town-ajout-canonical-name.sql && git commit -m "feat: ajout attribut canonical_name"