How To: Simple database migrations with Phing and DbDeploy
Introduction
This How To will introduce some simple database migrations to your PHP application. Ruby on Rails is a popular web application framework, that provides a method of migrating (upgrading) the applications database programatically, keeping the database schema essentially version controlled. This allows individual developers to update their working databases and the databases on testing, staging or production machines to be updated with new versions of applications. The CakePHP framework has recently developed a migrations library simliar to rails, but this article focuses on using seperate tools to run database migrations, a build tool called Phing, along with a method for creating database migrations, dbdeploy.
Install Phing
I always use the beta or release candidate of phing and for the purposes of this article I suggest you do too. The best way to download and install phing is using PEAR. This can be done on Linux or Windows assuming you have the pear script in your PATH with three shell commands.
shell> pear channel-discover pear.phing.info shell> pear config-set preferred_state beta shell> pear install phing/phing
Example Application structure
As an example, we’re going to develop a simple application with the following directory structure.
example/ |-- db/ | `-- deltas/ |-- deploy/ | `-- scripts/ |-- library/ `-- public/
The db directory contains sql files for using and manipulating our database and
the deploy directory contains our build scripts that set the migrations in motion. The library directory contains our application code and the public folder will contain scripts and files accessible directly from the web, but will not be the focus of this article.
Build scripts
This section shows you how to develop the build scripts that will run the database migrations. The first file we need to create is a simple configuration file and should be fairly self explanatory. The file is written as key=value, lines beginning with a # are comments. Open your editor and save the following text as deploy/build.properties.
# Property files contain key/value pairs #key=value # This dir must contain the local application build.dir=../ # Credentials for the database migrations db.host=localhost db.user=user db.pass=password db.name=example # paths to programs progs.mysql=/usr/bin/mysql
The next file we are going to create is the deploy/build.xml file. This is the file that tells Phing what we want it to do. I’m not going to go into too much detail describing each part of the build file, there are some comments, but you should consult the Phing Documentation for further details and enhancements.
<?xml version="1.0" ?>
<project name="PurpleMonkey" basedir="." default="build">
<!-- Sets the DSTAMP, TSTAMP and TODAY properties -->
<tstamp/>
<!-- Load our configuration -->
<property file="./build.properties" />
<!-- create our migration task -->
<target name="migrate" description="Database Migrations">
<!-- load the dbdeploy task -->
<taskdef name="dbdeploy" classname="phing.tasks.ext.dbdeploy.DbDeployTask"/>
<!-- these two filenames will contain the generated SQL to do the deploy and roll it back-->
<property name="build.dbdeploy.deployfile" value="deploy/scripts/deploy-${DSTAMP}${TSTAMP}.sql" />
<property name="build.dbdeploy.undofile" value="deploy/scripts/undo-${DSTAMP}${TSTAMP}.sql" />
<!-- generate the deployment scripts -->
<dbdeploy
url="mysql:host=${db.host};dbname=${db.name}"
userid="${db.user}"
password="${db.pass}"
dir="${build.dir}/db/deltas"
outputfile="${build.dir}/${build.dbdeploy.deployfile}"
undooutputfile="${build.dir}/${build.dbdeploy.undofile}" />
<!-- execute the SQL - Use mysql command line to avoid trouble with large files or many statements and PDO -->
<exec
command="${progs.mysql} -h${db.host} -u${db.user} -p${db.pass} ${db.name} < ${build.dbdeploy.deployfile}"
dir="${build.dir}"
checkreturn="true" />
</target>
</project>
That’s essentially all the magic we need. Now we just need to create our database.
Writing dbdeploy delta scripts
We haven’t actually created our database, so rather than create it the traditional way, we will actually use the migrations to create the initial schema. We’ve not actually decided what our example application does yet, but seeing as most tutorials make blogs, why don’t we give that a bash. We’ll start simple, one table with three columns called post.
| Field | Type | Comment |
|---|---|---|
| title | VARCHAR(255) | The title of our post |
| time_created | DATETIME | The time we created our post |
| content | MEDIUMTEXT | The content of our post |
Dbdeploy works by creating numbered delta files. Each delta files contains simple SQL to both deploy the change and roll it back. The basic layout of a delta file is like so.
--// -- Run SQL to do the changes --//@UNDO -- RUN SQL to undo the changes --//
We are creating our initial schema, so put the following content in db/deltas/1-create_initial_schema.sql
--//
CREATE TABLE `post` (
`title` VARCHAR(255),
`time_created` DATETIME,
`content` MEDIUMTEXT
);
--//@UNDO
DROP TABLE `post`;
--//
Migrating the database
We are one step away from running our first migration. To keep track of the current version of the database, dbdeploy requires a table in the database. This is the only time we will have to interact with the mysql client directly.
shell> mysql -hlocalhost -uroot -ppassword example mysql> CREATE TABLE changelog ( change_number BIGINT NOT NULL, delta_set VARCHAR(10) NOT NULL, start_dt TIMESTAMP NOT NULL, complete_dt TIMESTAMP NULL, applied_by VARCHAR(100) NOT NULL, description VARCHAR(500) NOT NULL ); mysql> ALTER TABLE changelog ADD CONSTRAINT Pkchangelog PRIMARY KEY (change_number, delta_set);
We are now ready to run our first migration and create the initial schema for our application.
shell>cd deploy shell>phing migrate
All being well, we now have a posts table in our database. But what about an author for our blog posts? We’ll have to add another table and a foreign key from the post table to author table. To do this we create another delta, we call this one db/deltas/2-create_author_and_link_to_post.sql
--//
CREATE TABLE `author` (
`author_id` INT(10) unsigned auto_increment,
`name` VARCHAR(255),
PRIMARY KEY (`author_id`)
);
ALTER TABLE `post` ADD `author_id` INT(10) unsigned NULL;
--//@UNDO
ALTER TABLE `post` DROP `author_id`;
DROP TABLE `author`;
--//
Run our migrations again.
shell> cd deploy shell> phing migrate
Conclusion
That’s pretty much it, you’ve seen how to create database deltas and use them to migrate your database, if you can’t be bothered to copy and paste things to try for yourself, download the example application.
There are plenty of caveats when it comes to version controlling databases, especially if you branch and merge your application code, some are detailed in the dbdeploy documentation
This tutorial is probably incomplete or wrong in plenty of ways, if you think you have something to point out, please leave your comments below
Tags: database, dbdeploy, how to, migrations, phing, PHP, version control
April 20th, 2008 at 6:50 pm
[...] Marshal as a writeup on using dbdeploy with Phing on his blog. It’s well worth a [...]
April 24th, 2008 at 12:38 am
[...] I use phing for automating my build process so I was very interested in these two articles. PHPUnit test and Code Coverage Statistics with phing and Simple Database migrations with phing and dbdeploy. [...]
May 11th, 2008 at 9:11 pm
[...] Phing is a project build tool and is a PHP port of the popular Java program ant. Phing can be used to automate builds, database migration, deployment and configuration of code. Documentation. Database migrations with phing [...]
May 15th, 2008 at 9:08 pm
[...] Lets start with build.xml. When you run phing in the command line without specifying the build file, it looks for a file named build.xml by default. Lets see an example build.xml file: The DBDeploy code comes with much thanks to Dave Marshall’s post [...]
May 21st, 2008 at 1:37 pm
[...] Simple database migrations with Phing and DbDeploy [...]
June 1st, 2008 at 10:00 pm
[...] Simple database migrations with Phing and DbDeploy Posted by phpimpact Filed in Databases, Frameworks, PHP, Programming [...]
June 1st, 2008 at 10:02 pm
[...] Simple database migrations with Phing and DbDeploy Posted by phpimpact Filed in Databases, PHP, Programming, Tools [...]
June 22nd, 2008 at 3:47 pm
[...] with an optional task for making revisions to a database called DbDeployTask. Dave Marshall wrote a nice article about it. After testing this task, I came to the conclusion that the technique used is not very effective if [...]
July 23rd, 2008 at 5:53 pm
[...] the rsync over ssh and one-line ssh techinques above you can do more complicated tasks like DBDeploy on a live server that denies external access to its databases (PROTIP: rsync the db folder, [...]
July 30th, 2008 at 11:44 am
Your setup SQL has an error – you have a comma after the content MEDIUMTEXT column, which shouldn’t be there.
Also, do you have any idea how to instruct dbdeploy to undo a migration? I cannot find any documentation on how to do that, which sort of makes the library pointless.
July 30th, 2008 at 2:20 pm
Thanks for the heads up Marlin, will fix it.
DbDeploy itself doesn’t actual do(or undo) the migrations, it simply generates SQL scripts to do so. Whenever you generate a migration script, it also generates the undo script, but I don’t think there’s functionality to generate the undo from version 10 to version 6 for example.
August 14th, 2008 at 11:19 am
Dave, does Phing support roll-backs for deployments/tasks that will use the “build.dbdeploy.undofile” property you defined?
Or is it necessary to write another task called “roolback” that will run SQL from undofile?
PS: Seems like wrong permissions for example application (403).
August 14th, 2008 at 11:36 am
@Evgeniy
You have to write another task.
When we deploy code to our servers, we generally check out a new version of the code and symlink to it (capistrano style), then migrate the database. Our migrate task places the build.dbdeploy.undofile in the current folder with the code, so effectively I can rollback each deployment we do.
For developers, we just use the file manually.
Thanks for the heads up, permissions fixed.
I’m hoping to write a more in depth article, including rolling back and also incorporating a way of maintaining different codelines, just need to find the time :)
January 12th, 2009 at 6:31 pm
Not sure which version of Phing you were using when writing this post, but i had trouble using your sample delta files. The resulting deployXXX.sql and undoXXX.sql files were truncated sql impossible to execute, and looking into the DbDeployTask.php class i realised that the “undo” comment was missing a . Here what i shopuld be “– //@UNDO” (instead of –//@UNDO).
Also – and this might be due to different versions of mysql – the “–//” caused the sql not to work, so i just removed them.
Here the resulting delta (only pasting the first one):
# CREATE TABLE `post` (
# `title` VARCHAR(255),
# `time_created` DATETIME,
# `content` MEDIUMTEXT
# );
#
# — //@UNDO
#
# DROP TABLE `post`;
January 12th, 2009 at 6:39 pm
Dave,
have you been creating any new useful task in your build.xml file? Like an undo task? :)
Also, i wanted to know if you were still satisfied with this dbdeploy task almost a year after writing this post?
And finally, what sort of data do you handle with it? Because i’m about to introduce dbdeploy to my team, but i would like to keep DML (data) and DLL (structure) changes separated so i can have for instance, dev-only deltas, and dev+prod deltas and i’m not sure if there is a best practice for these issues. I’m thinking of using 2 separate directories which would both have a corresponding “deltaSet” (the use of this field is not well documented anywhere though). Any comment/idea would be more than welcome! ;)
January 12th, 2009 at 11:18 pm
@Kevin
Regarding the space between the dash and the slashes, we learned this the hard way about two months ago! The sql standard says that there should be a space after the double dash, somebody reported it as a bug and gave the phing guys a patch, I think it came in 2.3.2?
See: http://phing.info/trac/ticket/288
—–
I’m still very happy with phing, even for some very simple tasks, it’s nice and neat.
Some of our tasks:
We have a task that puts our app in and out of maintenance mode.
We have a task to copy our dojo folder to dojo-src, then run the dojo build scripts to create a compact version for quicker downloads
config
We have a example config file with placeholders, a task then takes the config file and uses the reflexive task to set the values we need.
We fetch our code from subversion, placing it in a release folder and setting symlinks to point to the file data, similar to the way capistrano does things.
PHPDoc, PHPUnit and PHP-Codesniffer run tasks.
—–
For data we tend to only include very base data in the deltas, such as standing lists like job titles.
We’re setup ready to use a second delta set, in the event a maintenance branch needs a delta and the trunk has already created new deltas, but we haven’t done so yet. I’ve tested it and it seems to work well enough, but I wont be 100% till I’ve pushed something into our app with it.
Having the seperate changesets should work well enough for you.
/ddl/01-create-person-table.sql
/dml/01-add-admin-user.sql
/dml-dev/01-add-test-persons.sql
Your production box just doesn’t run the dml-dev folder. I like the idea too, you could really get a nice test dataset in the dml-dev folder, ready for developers to crack on with what they should be doing. We actually have a data masking program, that we run on a copy of the production database to generate test data. It seems to work pretty well, and we get realistic enough data, just with a few strange names ;)
I *will* get round to writing an updated post on phing!
February 4th, 2009 at 2:55 pm
Using the example provided, the SQL statements never got parsed. The correct example SQL should be:
– //@DEPLOY
CREATE TABLE `post` (
`title` VARCHAR(255),
`time_created` DATETIME,
`content` MEDIUMTEXT
);
– //@UNDO
DROP TABLE `post`;
– //
February 4th, 2009 at 3:04 pm
In my last reply, there should be two ‘-’ signs in the example SQL, but I guess this is filtered out in one way or the other…
March 17th, 2009 at 9:40 pm
Thank you, I missed the @DEPLOY in my first attempts.
May 8th, 2009 at 1:42 am
Can DBDeploy pass the schema name (database name) that it is working on to the db update scripts that it’s applying? For mysql, if you want to check if a column exists before adding one to a table you need to query the INFORMATION_SCHEMAS table and filter for the current database in use. Thanks!
SELECT * FROM information_schema.COLUMNS
WHERE COLUMN_NAME=’colname’ AND TABLE_NAME=’tablename’ AND TABLE_SCHEMA=’dbname’
June 1st, 2009 at 2:26 pm
@Norm
The standard doens’t specify that at the moment, but it would lose some of it’s benefits if it did. Having executable SQL in the deploy file is good news.
July 28th, 2009 at 3:15 pm
this is a good example of what is going wrong in the so called “php scene” – totally primitive examples, only the first thought, second thought and consequences NOT even mentioned, imitating good already existing solutions in a bad and primitive way, AND still providing errors in so little and primitive examples – on top of it all this by a so called “certified zend engeneer”… what you do here is bad propaganda on the lowest level – do you understand, that your posting is like a parody of itself?
July 28th, 2009 at 3:47 pm
Sorry ;)
December 4th, 2009 at 10:40 am
[...] Here’s a good tutorial you need to read on how to get you started: http://www.davedevelopment.co.uk/2008/04/14/how-to-simple-database-migrations-with-phing-and-dbdeplo... [...]