Update 12/1/08 –

See comments below, and look for a new blog post from us on a new player which we’ve recently discovered – south:

http://south.aeracode.org/

They even have their own review of the other players, here:

http://south.aeracode.org/wiki/Alternatives

We will post a review on this soon.

Preamble

Following is a review I did recently for one of our Django clients, of six or so of the various tools available to handle database migrations – much like the “Migrations” facilities built in to Ruby on Rails.This list is by no means complete; as there are a lot of players our there – too many – so many, in fact, that no clear winner has emerged.  Read on.

The Reviews

The issue is how to handle schema migration and schema version control in a way that integrates well with the usual Django development workflow.

Here is an excellent Django-centric view on the topic, with proposed solutions and some of the projects currently available:

http://code.djangoproject.com/wiki/SchemaEvolution

I have checked all the references in the article, and some further ones I have found –
here are the candidates for use, and my evaluations:

Django Evolutions

http://code.google.com/p/django-evolution/

• Mature, active, likely candidate for future Django inclusion
• Postgres support the strongest, MySQL weakest
• Establishes a baseline, rather than introspects – stable, conservative approach
• Creates two new tables for the baseline – django_project_version, django_evolution
• Does not help for our current situation
• Builds on RoR / ActiveRecord? ideas, takes one step further

Django Schema Evolution

http://code.google.com/p/django-schemaevolution/

• Based on the above article/link & observations
• actually does introspections, too
• MySQL support the strongest
• Would work well for us, except it faults on current Django SVN release we are using (I filed the issue on Google code for the project).

deseb – Django External Schema Evolution Branch

http://code.google.com/p/deseb/

• Appears to be a current project, with a very active community
• this appears to be a fork of the schema_evolution project, and is also based on the article/link above
• it uses a different approach, doing signatures of the SQL, and
• it does a virtual ‘diff’ on the Model vs the schema –
• almost like a db ‘lint’ utility – I will post the output below or next post
• can optionally generate schema migration scripts to be used in version control and managed deployment scenarios
• at the very least, this is a very useful utility to compare existing Models/schema with the manage.py sqlevolve command (see output below)

BEGIN;
ALTER TABLE `blog_categories` MODIFY COLUMN `id` integer AUTO_INCREMENT;
ALTER TABLE `blog_posts` MODIFY COLUMN `id` integer AUTO_INCREMENT;

ALTER TABLE `tagging_tag` MODIFY COLUMN `id` integer AUTO_INCREMENT;
[lots of similar auto-increment mods clipped for brevity]
ALTER TABLE `django_redirect` MODIFY COLUMN `id` integer AUTO_INCREMENT;
ALTER TABLE `django_session` MODIFY COLUMN `session_key` varchar(40);
ALTER TABLE `django_site` MODIFY COLUMN `id` integer AUTO_INCREMENT;
ALTER TABLE `robots_url` MODIFY COLUMN `id` integer AUTO_INCREMENT;
ALTER TABLE `robots_rule` MODIFY COLUMN `id` integer AUTO_INCREMENT;
ALTER TABLE `wtp_user_profile` MODIFY COLUMN `user_id` integer;
— warning: the following may cause data loss
ALTER TABLE `wtp_user_profile` DROP COLUMN `is_subscribed`;
— end warning
ALTER TABLE `wtp_user_review` MODIFY COLUMN `id` integer AUTO_INCREMENT;
[lots of similar auto-increment mods clipped for brevity]
ALTER TABLE `wtp_product` MODIFY COLUMN `id` integer AUTO_INCREMENT;
UPDATE `wtp_product` SET `is_new_image_url` = ‘t’ WHERE `is_new_image_url` IS NULL;
ALTER TABLE `wtp_product` MODIFY COLUMN `is_new_image_url` bool NOT NULL;
ALTER TABLE `wtp_genre` MODIFY COLUMN `id` integer AUTO_INCREMENT;
[lots of similar auto-increment mods clipped for brevity]
ALTER TABLE `wtp_editorial_review` MODIFY COLUMN `product_id` integer;
UPDATE `wtp_editorial_review` SET `is_published` = ‘f’ WHERE `is_published` IS NULL;
ALTER TABLE `wtp_editorial_review` MODIFY COLUMN `is_published` bool NOT NULL;
ALTER TABLE `wtp_featured_article` MODIFY COLUMN `id` integer AUTO_INCREMENT;
ALTER TABLE `wtp_user_review_feature` MODIFY COLUMN `id` integer AUTO_INCREMENT;
ALTER TABLE `wtp_user_review_feature_rate` MODIFY COLUMN `id` integer AUTO_INCREMENT;
ALTER TABLE `wtp_screenshot_meta` MODIFY COLUMN `product_id` integer;
ALTER TABLE `wtp_screenshot_image` MODIFY COLUMN `id` integer AUTO_INCREMENT;
ALTER TABLE `wtp_product_video` MODIFY COLUMN `product_id` integer;
[lots of similar auto-increment mods clipped for brevity]
COMMIT;

yadsel – Yet Another Database Schema Evolution Library

(Formerly dbMigrations)

http://code.google.com/p/yadsel/

Reasonably active project, last updated April 2008, some / sparse community participation –

• Bills itself as an “Active Record Migrations like project”
• Full and wide db support, and Django support
• Full schema inspection, ddl/uml support, etc
• Overkill for what we need
• Lots of docs, but no ‘Getting Started’ guide, as is customary, unlike (All1) the other candidates
• Not installed and evaluated

dupdater.py — Django project evolution utility

http://www.methods.co.nz/django/dupdater.html

Basically a version management & deployment tool, to give you the very basics of the RoR ActiveRecord? functionality – versioned back-and-forth scripts.

• Simple python utility script, no Django installation required
• Does one thing, and does it well – no introspection, etc
• Active, last updated Apr 08
• Immune to the Django 1.0 migration problems
• We should evaluate this further, it looks good

Great for:

• Keeping track of your SQL db alterations
• Keeping them under version control
• Allowing them to move forward and backward
• Managing deployment in various environments – Production, Staging, Development, Sandbox.

This is simple and elegant, we may want to use this along with deseb for ‘db lint’ and to help generate the updates.

dbmigration — Simple schema migration for Django

http://www.aswmc.com/dbmigration/

Currently at v0.4, and about a a year old – does not appear to have a lot of traction, although it’s widely quoted and linked to.

• Also patterns itself after the back-and-forth AR-style scripts
• Uses a ‘migrations’ directory per app (rather than per project, like AR / RoR)
• Decries auto-update integrated migration tools like Django-evolution and Django-schemaevolution
• Supports migrations written in SQL or Python.
• Not installed or evaluated

Recommendations and Conclusions

• Use deseb for its ‘DB Lint’ functionality (at the very least, on dev)
• (Possibly de-fang its update capabilities)
• Evaluate and likely use dupdater for deployment & SQL version management
• Install/Leave django-migrations installed, to see how it tracks the baseline
• Keep a close eye on django-migrations moving forward, to see if we can start using it, and how it tracks the baseline

j

---

UPDATE 09/02/08 10:03:37 PDT dupdater findings: ¶

• dupdater is at version 0.6, and feels at least this rough-around-the-edges
• After using it on sandbox for a while, I can’t recommend it at this time.

However, the workflow that this tries to mimic/achieve, ie, a directory of changes, numbered, which are applied in order, is a good one – We can just do this manually with our /sql/ subdirs.

There is even some support for auto-application of sqls in the app /sql/ subdirs, by the manage.py interface, if we ever choose to use it.

Updated Recommendations and Conclusions ¶

• Use deseb for its ‘DB Lint’ functionality (at the very least, on dev & sandbox)
• (Possibly de-fang its update capabilities)
• Use a /sql/ directory of numbered .sql files for the db updates
• Install/Leave django-migrations installed (at least on sandbox)
• Keep a close eye on django-migrations moving forward, to evaluate when to start using it and how it tracks the baseline

j

---

UPDATE 09/07/08 12:14:24 PDT

FYI, as of 9/7/08, deseb works fine for us in our current environment, but has not yet been updated to Django 1.0.  You will have to remove it if you are migrating to Django 1.0+.

j