Dariusz on Software

Manual schema migrations, single database instance

This approach assumes you are using database administration tool to manually change your database schema to reflect application. Typically there's one single "master" database that has "official" schema and is used for tests and (possibly cloned version) for production. It's the way most novice programmers attack the problem. This approach is simple and strighforward, but does't have more benefits. Problems that may occure:

• It's problematic to develop in paralell on branches: you need to change schema but other developers will see your changes and may break their code
• schema migration is chaotic - it's very easy to forget add some field on production database

Migrations by series of SQL scripts, manual apply

Another, more advanced method, is to use series of SQL files that will ALTER database and optionally UPDATE some fields to reflect migration. You can order those scripts by prepending date and make all developers manually path their databases. Benefits:

• Arbitrary schema modifications can be implementaed this way

Some problems to notice:

• It's still very easy to forget applying some patches (or applying them in incorrect order) making your env broken
• Additional work is needed to prepare scripts

Migrations by series of SQL scripts, applied automatically

A variation of above method. SQL migrations scripts are recorded (by name) after apply in special database table. They are applied automatically (system lists special upgrade/*.sql directory) and already applied patches are silently ignored. Benefits:

• Automatic retest of whole sequence of patches (load old database dump and run upgrade)
• Any compicated schema migrations could be implemented this way
• Automatic order of execution
• Exclude "double execution" problem (patch names recorded)

This solution is used by us and works perfectly for 6 years of continuous development (400 patches applied so far!).

Automatic schema migrations for ORM

ORM (Object Relational Mappers) have few possibilities to detect current database schema and compare it with existing model. Then migrations can be applied automatically on database to make it work with application. Examples:

• Django Evolution - fill a gap in basic Django (where only new tables are inserted
• Deseb - Django External Schema Evolution Branch

Using Django Evolution is very simple:

./manage.py evolve --execute --hint --noinput

Similar for Deseb:

./manage.py evolvedb --noinput

Which approach should I choose?

I suggest to start with Django Evolution (or others ORM equivalent) then if migrations become more advanced switch to registered, applied automatically SQL scripts. This way you will have speed at the initialisation of a project and later - more confidence with manually created SQL patches.