Shifting Bits by Patrick Altman

Introduction

Back in July I discussed how I built a tool to help manage database changes across a team and through deployment for our Django based project.

And yes, I am aware of other approaches/projects tackling this same problem. None of them seemed to work how I wanted them to as they all seemed to focus on trying to keep you from writing the raw sql yourself.

I am comfortable with writing SQL and for something that will execute automatically against production databases, I prefer to have that control in knowing exactly what will get executed. Yes, many of these tools have options to allow me to preview the generated SQL, but it still gave me the feeling of being boxed in.

For your reference, here are the ones I reviewed:

• South
• dmigrations
• django-evolution
• deseb
• yadsel
• schema-evolution

So I abstracted out the script and it's assumptions about our environment to function as a management command in a stand alone reusable app.

My Approach

It's extremely simple and is based purely on executing SQL statements in order based on it's file naming scheme. Basically, you create a folder somewhere in your project -- I call mine "db". Within this folder you put the changes to the database into SQL scripts named using a convention that will list them in order. I use:

YYYYMMDD-##.sql

This allows members of the team to know at a glance when the script was created and gives some reasonable avoidance of name collisions. There have been instances where we've had a name collision (I try to push in a commit that has 20090224-01.sql added in my commit index and it already exists in the remote branch, so I just move the file to 20090224-02.sql, commit, pull and then push again).

To sync up your database with changes from the rest of the team you would just execute:

./manage.py upgradedb --execute

If you wanted to just see the output that will be executed you can run it without the --execute flag to just print out the contents to standard output:

./manage.py upgradedb

By default, it looks for a folder with scripts in your current working directory called "db". If your scripts are located elsewhere, just use the --path parameter:

./manage.py upgradedb --execute --path /path/to/db/folder

Where to Get It

I have released v0.1 of the app as it has the bare minimum now to be usable. But there are handful of other additions that I think will make it generally more useful. I do welcome any and all feedback on this project, but want to restate this isn't meant as a replacement or competition for the other migration projects listed above. I have reviewed all of them, and none fit how I preferred to work.

I also welcome any patches, so head over to the project and fork it!

I figured since they really didn't fit my needs and/or what I wanted to do, then there might be others in the same boat and might find what I have done to be useful.