Versionner le développement d'une base de données PostgreSQL

Lors de l'utilisation d'un framework, l'utilisation d'un ORM, comme Doctrine avec Symfony ou Eloquent avec Laravel, facilite cette démarche. Cette page précise la démarche en l'absence de framework, par exemple pour la mise en œuvre d'un micro-services ou web-services simplement basé sur postgres/postgis et postgREST, cf. le tuto PostgREST

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

Besoin

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”.

Définitions des créations/modifications de la base

À 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.

Versionnement

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.

Déploiement en "production

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

Voir aussi

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

Mise en pratique

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 public, 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).

Il est intéressant de cliquer sur le bouton Objects en bas à droite de PgModeler, ce qui permet de visualiser toute la structure du modèle et d'accéder aux propriétés de la base, du schéma, des rôles…

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)