This proof of concept demonstrates a method of being able to automatically upgrade and downgrade databases in order to be able to automate changes to them and integrate them with delivery pipelines.
Different environments will have databases that are a different version from one another. By treating the database as a versioned item and having migration scripts from each version to the next, we can take a database of any version and sequentially run the appropriate migration scripts to take it to any other version.
This database deployment script expects upgrade-scripts and downgrade-scripts directories which hold the relevant scripts in order. The database has a table with a row giving the database's current version. This is used along with an argument passed to the script to determine which scripts will be run. Each script in upgrade-scripts has an equivalent script with the same name in downgrade-scripts which acts as the rollback for that migration.
Docker - brew install docker
MySQL Client - brew install mysql
Python3 - brew install python3
Python MySQL Connector - pip3 install mysql-connector-python --user
To check the internal database version (exists when the db is at v1+), you can run
mysql -h 127.0.0.1 -u root -ppassword < queries/db_version.sql
- Start up a MySQL server
docker run --name mysqlserver -p 3306:3306 -e MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=password --detach mysql/mysql-server - Run the database deployment script to version 2
python3 deploy-db.py 2 - Insert test data
mysql -h 127.0.0.1 -u root -ppassword < test-data/test-data.sql - Check the person table contents
mysql -h 127.0.0.1 -u root -ppassword < queries/person_table.sql - Upgrade the database to version 3
python3 deploy-db.py 3 - Check the person table contents
mysql -h 127.0.0.1 -u root -ppassword < queries/person_table.sql - Downgrade the database to version 2
python3 deploy-db.py 2 - Check the person table contents
mysql -h 127.0.0.1 -u root -ppassword < queries/person_table.sql - Upgrade the database to version 3
python3 deploy-db.py 3 - Check the person table contents
mysql -h 127.0.0.1 -u root -ppassword < queries/person_table.sql - Stop the container
docker stop mysqlserver - Remove the container
docker rm mysqlserver