Nowadays everybody talks about continuous integration, automated test tools, and stuff like that. But almost every time it’s about testing applications. Let’s talk a little about Sqitch, “a database change manager” as they use to call themselves.
Let’s say you’re starting a new project and want to ensure every database change are managed and secured with tests? Just like with an app, you can write tests to ensure everything works and you can have a “revert” method for the changes too. Suppose you are changing a field type and start creating a backup column (fallback ;)), but something goes wrong on the meantime and for some reason, you can’t do a simple rollback… revert it and be happy.
What if your CI says that the application it’s fine and can be released to the production server but “a wild bug appears” and you NEED to revert to a previous version of your service, easy huh? With Docker and one command line, the service is up and running again. But what about the database changes your team did? Just call the revert and be happy :).
Have I mentioned that you can have multiples connection URI at the project and have different environments to apply the changes with a single command?!. Let’s see a few commands to create a simple MySQL database…
Create a folder, initialize GIT in it and initialize the Sqitch project.
mkdir awesomeapp cd awesomeapp git init . sqitch init flipr --uri https://github.com/theory/sqitch-mysql-intro/ --engine mysql
Now you have a ready to go sqitch structure. Now let’s do some basic creates.
sqitch add createcustomeruser -n 'Creation of Customer table'
Add the create table script to the file inside the “deploy” dir.
CREATE TABLE customer ( id INTEGER PRIMARY KEY, name VARCHAR(50) );
Add the verify script, can be a simple select. Add the revert script, guess what’s the command, and after all, you can run the commands below and see the result.
sqitch deploy db:mysql://root@/awesomeapp sqitch verify db:mysql://root@/awesomeapp sqitch revert db:mysql://root@/awesomeapp
This was a quick explanation about Sqitch so go ahead and read more at their docs and tutorials, they are pretty good.