Simon Willison’s Weblog

What tools and techniques are used for relational database version control (structure and data)?

24th December 2012

My answer to What tools and techniques are used for relational database version control (structure and data)? on Quora

The term you are looking for is database migrations (sometimes called database change scripts).

The basic concept is pretty straight forward: you set up a table in the database that records which change scripts have already been applied. When you need to make a change (adding a column, adding a table, denormalising some data for performance reasons, adding an index etc) you write a change script that applies the change—in raw SQL or in another programming language, depending on how your migration system is set up.

These change scripts (let’s call them migrations from here) are numbered so they can be applied in the correct order. Then you run a command which checks for scripts that have not yet been applied and runs them in the correct order—then records that they have been run to the relevant database table.

The setup I’ve described above is a pretty good start. Some systems let you have reversible migrations: each migration includes instructions for reversing its effect (removing the index that was added, moving data back to its old location) which lets you run a command to revert back to a previous database state. In practise this is a nice-to-have but not essential: many migrations are by their nature irreversible, but it can make development faster if you can easily try out and then revert a database structure change within your development environment.

Really clever migration systems can even introspect your database, figure out what has changed and attempt to generate the migration scripts automatically! South, the most popular migration system for Django, does this with surprisingly good results for many cases.

If you’re interested in learning more, it’s worth reading through the South documentation: http://south.readthedocs.org/en/...