Beanstalk & database versioning

Leading on from my earlier post about Development environments & source control, I got a question asking about database versioning and how that fits in to the whole system. One of the joys of using Beanstalk is the ability to have it call a webhook post-deployment. Meaning that you can call a certain script or page on your site after the deployment has finished.

The basics

The way this’ll work is as follows:

  1. Create a file called ‘dbchanges.sql’, which should contain all changes required for the database.
  2. Create a file called ‘dbupdate.php’. This file will self-delete when it has ran, so as to stop it being run again, but ultimately, it will take your SQL & run it on your SQL server for you.
  3. Configure a web hook to run the dbupdate.php file on Post-Deployment.

The SQL

The main thing to ensure is that when you run your SQL, if you are spanning multiple databases, to not select the databse when you connect. However, you should instead database prefix your tables. So, instead of table `site`, you would use something like `my`.`site`, where the table `site` is within database `my`.

Also, ensure that your SQL queries end with a semi-colon to stop the SQL failing & ultimately breaking everything.

The PHP

Of course, this can be written in any language that you want that is capable of database interactions, so save the hate on PHP. Ultimately all that the script needs to do is connect to the db server, run the SQL, disconnect, delete the ‘dbchanges.sql’ file, and then self-delete the ‘dpupdate.php’ file. Of course, a check should be put in place to double check that the SQL file exists.

There is a lot of scope here for what the PHP script does. One of the main things that I’d make it do is back up your database before running the SQL changes, this means that you have a failsafe incase everything fails. For this to run, you need to find out/make sure you’re able to execute the system() function, many hosts disable this due to what it opens up.

Within this, we’ll be using mysqldump which takes a series of parameters to let us specify:

  • SQL host (-h)
  • SQL username (-u)
  • SQL password (-p)
  • SQL database (or SQL table)

As a quick example, if I want to back up the `site` database to a file within the /home/user/ directory, I’d use something along the lines of the following code:

<?php
system(mysqldump -h localhost -u dbuser -pdbpass site > /home/user/sqlbac.sql);
?>

There’s a lot of scope to what the method can do, so I suggest taking a quick read of the mysqldump documentation.

That’s a wrap

While a very basic summary of what I’d do, I didn’t want to write all of the code for everyone, as ultimately people might be using a database class or some other crazy wacked out way to connect to their database. The logic is there, and if you need any help, don’t hesitate to shoot a comment below.