Appliquer les principes de CI/CD aux migrations SQL avec Jenkins et un peu de bash
Écrire des tests unitaires ou fonctionnels sur du code est quasiment devenu un standard. Avoir un serveur qui automatise la vérification de ces tests et qui déploie le code est l'objectif accomplis de beaucoup d'équipes de développement. Malgré cela, ces bonnes pratiques sont rarement mise en place pour les migrations de schéma de base de donnée.
J'ai donc entrepris d'utiliser ces bonnes pratiques pour les migrations SQL que je trouve très chronophage. L'idée est d'abandonner la demande d’exécution d'un alter table via un slack, un mail ou un ticket en créant un repository git spécifique, l'utilisation de Pull Request et un peu de bash dans Jenkins.
Organisation du repository Git
La structure du repository est simple au premier niveau on trouve un dossier par nom de bdd et au deuxième niveau un dossier par environnement. L'idée est d'écrire chaque migration dans un fichier sql.|-db1 | |-preprod | | |-20181001_migrations1.sql | | |-20181015_migrations2.sql | |-prod | |-db2 | |-preprod | |-prod
Un fichier de migration en peut exister que dans un seul environnement, une fois exécuté sur l'environnement de preprod, il est déplacer dans le dossier prod pour y être exécuté. Chaque ajout, modification de migrations se fait via une branche dans Git et une pull request.
Tester une requête SQL
J'ai longtemps cherché un linter SQL pour détecter en amont les erreurs, malheureusement il ne semble pas y en avoir. Du coup pour tester une requête SQL je n'ai rien trouvé de mieux que la jouer. Bien entendu je veux la jouer sur un environnement de test et pas directement sur l'environnement cible.
Première contrainte: le temps. Si pour tester une requête il me faut 3h car la table fait plusieurs millions de ligne, mon process ne sera jamais utilisé. Etant donné que je n'ai jamais rencontré le cas où un ALTER TABLE échoue à cause des données déjà présentes dans la table, mon environnement de test n'a besoin que de la structure de la bdd existante, ce qui va rendre chaque test très rapide.
Ma deuxième contrainte est de garder un environnement de test le plus proche de l'environnement cible. J'ai donc décidé de faire un dump de la structure de l'environnement cible pour chaque test.
Le script est exécuté par jenkins de façon automatique dès qu'il y a un changement qui est poussé sur l'origine du repository.
#!/usr/bin/env bash set -euo pipefail DEV_HOST="preprod" DEV_USER="root" DEV_PASSWD="ulrich" PROD_HOST="moncode.net" PROD_USER="migration" PROD_PASSWD="something" PROD_DEST="/home/migration" GIT_DIFF=$(git diff @^1 @ --name-only --no-renames -M) for FILE in $GIT_DIFF; do if [ -f "$FILE" ] then DB=$(echo "$FILE" | awk -F "/" '{print $1}') ENV=$(echo "$FILE" | awk -F "/" '{print $2}') SANDBOX="jenkins_sandbox_""$DB" echo "Test for $ENV environnement" if [ "$ENV" == "preprod" ] then #first delete sandbox echo "Reset sandbox" mysql -h "$DEV_HOST" -u"$DEV_USER" -p"$DEV_PASSWD" -e "DROP DATABASE IF EXISTS $SANDBOX;" #then create new sandbox with actual schema mysql -h "$DEV_HOST" -u"$DEV_USER" -p"$DEV_PASSWD" -e "CREATE DATABASE $SANDBOX;" mysqldump -h "$DEV_HOST" -u"$DEV_USER" -p"$DEV_PASSWD" -d "$DB" > "$SANDBOX.sql" mysql -h "$DEV_HOST" -u"$DEV_USER" -p"$DEV_PASSWD" "$SANDBOX" < "$SANDBOX.sql" #then try migration echo "Apply migration $FILE" mysql -h "$DEV_HOST" -u"$DEV_USER" -p"$DEV_PASSWD" "$SANDBOX" < "$FILE" elif [ "$ENV" == "prod" ] then #first delete sandbox echo "Reset sandbox" ssh user@production "mysql -h $PROD_HOST -u$PROD_USER -p$PROD_PASSWD -e 'DROP DATABASE IF EXISTS $SANDBOX;'" #then create new sandbox with actual schema ssh user@production "mysql -h $PROD_HOST -u$PROD_USER -p$PROD_PASSWD -e 'CREATE DATABASE $SANDBOX;'" ssh user@production "mysqldump -h $PROD_HOST -u$PROD_USER -p$PROD_PASSWD -d $DB > $PROD_DEST$SANDBOX.sql" ssh user@production "mysql -h $PROD_HOST -u$PROD_USER -p$PROD_PASSWD $SANDBOX < $PROD_DEST$SANDBOX.sql" ssh user@production "rm $PROD_DEST$SANDBOX.sql" FILEONLY=$(echo "$FILE" | awk -F "/" '{print $3}') scp "$FILE" user@production:"$PROD_DEST" QUERY="mysql -h $PROD_HOST -u$PROD_USER -p$PROD_PASSWD $SANDBOX < $PROD_DEST$FILEONLY" ssh user@production "$QUERY" ssh user@production "rm $PROD_DEST$FILEONLY" fi fi done
En haut du script on retrouve les login, mot de passe pour se connecter aux différents environnement, bien entendu il faut adapter cette section aux règles de sécurité que vous utilisez.
J'utilise la commande git diff pour connaitre la liste des fichiers de migration à tester. En découpant les noms de fichiers par / j'obtiens le nom de la bdd et son environnement. Pour l'exemple, je considère que le serveur MySQL de preprod est accessible depuis le serveur Jenkins. Par contre ce n'est pas le cas pour la production. Je passe donc les commandes à travers ssh.
Si une commande échoue, le script s'arrête et retourne le code de sortie de la commande qui a échoué, ce qui permet d'avoir le build Jenkins en erreur.
Support de Percona toolkit
Si la base de donnée devient grosse, il est intéressant d'utiliser pt-online-schema-change pour appliquer les alter table. Pour ce faire, j'utilise des fichiers avec l'extension .pt; ces fichiers sont des script bash dans lesquels je définis 2 variables, une pour le nom de la table et l'autre pour la modification à faire.
#!/usr/bin/env bash PT_TABLE="user" PT_ALTER="ADD COLUMN active tinyint(1) NOT NULL DEFAULT 0, ADD COLUMN avatar varchar(126) DEFAULT NULL"
J'inclus les fichiers au runtime pour insérer les variables dans la commande pt-online-schema-change qui comporte déjà beaucoup d'option. Ce qui me donne:
#!/usr/bin/env bash set -euo pipefail [...] if [ "$ENV" == "preprod" ] then [...] #then try migration if [ $(echo "$FILE" | grep -c ".sql") == 1 ] then echo "Apply migration $FILE" mysql -h "$DEV_HOST" -u"$DEV_USER" -p"$DEV_PASSWD" "$SANDBOX" < "$FILE" elif [ $(echo "$FILE" | grep -c ".pt") == 1 ] then source "$FILE" pt-online-schema-change --alter "$PT_ALTER" D=$SANDBOX,t=$PT_TABLE --host="$DEV_HOST" --user="$DEV_USER" --password="$DEV_PASSWD" --dry-run fi elif [ "$ENV" == "prod" ] then [...] if [ $(echo "$FILE" | grep -c ".sql") == 1 ] then FILEONLY=$(echo "$FILE" | awk -F "/" '{print $3}') scp "$FILE" user@production:"$PROD_DEST" QUERY="mysql -h $PROD_HOST -u$PROD_USER -p$PROD_PASSWD $SANDBOX < $PROD_DEST$FILEONLY" ssh user@production "$QUERY" ssh user@production "rm $PROD_DEST$FILEONLY" elif [ $(echo "$FILE" | grep -c ".pt") == 1 ] then source "$FILE" QUERY="pt-online-schema-change --alter \"$PT_ALTER\" D=$SANDBOX,t=$PT_TABLE --host=$PROD_HOST --user=$PROD_USER --password=$PROD_PASSWD --dry-run" ssh user@production "$QUERY" fi fi [...]
Déployer une requête SQL
L'idée est de lancer le script de déploiement uniquement après un merge sur la branche master (ou stable selon vos conventions). Le script est quasiment le même que pour un test sauf que cette fois plus de base de test et j'utilise l'option --execute pour la commande pt-online-schema-change.
#!/usr/bin/env bash set -euo pipefail DEV_HOST="preprod" DEV_USER="root" DEV_PASSWD="ulrich" PROD_HOST="moncode.net" PROD_USER="migration" PROD_PASSWD="something" PROD_DEST="/home/migration" GIT_DIFF=$(git diff @^1 @ --name-only --no-renames -M) for FILE in $GIT_DIFF; do if [ -f "$FILE" ] then DB=$(echo "$FILE" | awk -F "/" '{print $1}') ENV=$(echo "$FILE" | awk -F "/" '{print $2}') echo "Run for $ENV environnement" if [ "$ENV" == "preprod" ] then if [ $(echo "$FILE" | grep -c ".sql") == 1 ] then echo "Apply migration $FILE" mysql -h "$DEV_HOST" -u"$DEV_USER" -p"$DEV_PASSWD" "$DB" < "$FILE" elif [ $(echo "$FILE" | grep -c ".pt") == 1 ] then source "$FILE" pt-online-schema-change --alter "$PT_ALTER" D=$DB,t=$PT_TABLE --host="$DEV_HOST" --user="$DEV_USER" --password="$DEV_PASSWD" --execute fi elif [ "$ENV" == "prod" ] then if [ $(echo "$FILE" | grep -c ".sql") == 1 ] then FILEONLY=$(echo "$FILE" | awk -F "/" '{print $3}') scp "$FILE" user@production:"$PROD_DEST" QUERY="mysql -h $PROD_HOST -u$PROD_USER -p$PROD_PASSWD $DB < $PROD_DEST$FILEONLY" ssh user@production "$QUERY" ssh user@production "rm $PROD_DEST$FILEONLY" elif [ $(echo "$FILE" | grep -c ".pt") == 1 ] then source "$FILE" QUERY="pt-online-schema-change --alter \"$PT_ALTER\" D=$DB,t=$PT_TABLE --host=$PROD_HOST --user=$PROD_USER --password=$PROD_PASSWD --execute" ssh user@production "$QUERY" fi fi fi done
J'utilise cette procédure depuis quelques mois sur différents projets et j'y ai trouvé beaucoup d'avantage. Les aller retour avec le demandeur en cas d'erreur sont plus simple, le demandeur a très vite le retour du test et peut corriger sa migration et les échanges via la pull request permettent des discussions à plusieurs, et de garder des traces de ces discussions. De plus il est aisé de savoir sur quel environnement se trouve la migration .
Un autre point intéressant, si dans vos process vous faites régulièrement des synchronisation de bdd depuis la production, le repository git peut être utilisé pour rejouer de façon automatique les migrations par environnement.
Attention, pour garder les script compréhensible le plus possible je n'ai géré que 2 environnements mais surtout j'ai réduis la sécurité au plus simple. Ce n'est pas forcément idéal, selon la configuration de Jenkins, les droits sur les scripts et beaucoup d'autres paramètres les mots de passe peuvent être exposé. Soyez attentifs à ce sujet en fonction de vos besoins et je vous conseilles d'utiliser un utilisateur spécifique dans MySQL ou MariaDB.
Ajouter un commentaire