How do you version/track changes to SQL tables?

Question

When working in a team of developers, where everyone is making changes to local tables, and development tables, how do you keep all the changes in sync? A central log file where everyone keeps their sql changes? A wiki page to track alter table statements, individual .sql files that the devs can run to bring their local db's to the latest version? I've used some of these solutions, and I'm tyring to get a good solid solution together that works, so I'd appreciate your ideas.

Answer 1

I use a code-based Database Migration tool and keep the migration code in source control.

By using timestamps as version numbers, any number of devs are mostly free to add migrations as they please and we can run the Migration tool against any copy of our database confidently.

I used to use SQL Scripts under version control, but find the code-based approach much, much easier to work with due to them all being in one logical "spot" and being able to execute all needed scripts with a single command.

Answer 2

I don't do it myself, but some developers maintain a collection of SQL scripts under source control which, when executed, can recreate the database tables for testing purposes, and build up an empty database for production purposes.

The same technique can be used to version the database at the customer site, when fields or tables need to be added or removed, or data transformations need to take place.

Answer 3

Build scripts under version control and continuous integration to verify them

One approach that worked for me was to have each developer work with their own schema which they can do what they like with. Their schema was destructible and populated with test data taken from a version controlled set of scripts that all developers contributed to.

The nightly continuous integration build took the latest version of all the scripts and attempted to build a cohesive test database from them. The application then had a series of integration and functional tests run against it to verify that the current schema was in line with the current release candidate.

Before starting down this road, there was a pretty solid database design in place and a DBA was always keeping an eye on things to prevent developers going mad with denormalisation and other horrors.

Version control helped immensely here because changes to the scripts were immediately obvious. We also made use of a database VERSION table to identify the overall state of the database. This was a simple integer sequence and was not linked to any particular application.

Overall, it worked well and meant that developers ceased to be fearful of changing the persistence tiers because they could always roll back their own schemas without impacting others.

Answer 4

If you are in a MS shop, Visual Studio 2010 has some nice database version control tools, which can also generate change/deployment scripts based on the differences between two databases.

Answer 5

Along with keeping schemata and other SQL scripts under version control, another handy practice is to maintain a 'schema version' table in the actual DB.

create table schema_migrations (
    `appliedAt` timestamp not null default CURRENT_TIMESTAMP,
    `migrationCode` varchar(256) not null,
    `extraNotes` varchar(256),
    primary key (`migrationCode`)
)

Answer 6

Doctrine has an awesome Migration tool: http://www.doctrine-project.org/documentation/manual/1_2/en/migrations, best part is that they are autogenerated or can be easily coded by hand.

Answer 7

The approach that I use is to provide one table for parameters. This table will have one name/value pair for the version that the database is on. This gives me two benefits: I have a way of verifying a database only fix has been applied through the application, and I can use that value for my SQL scripts.

The SQL script will create new tables, alter columns, and whatever work is needed on the database to promote the script from the version before. Ideally I would also have a rollback script, but most of the time I don't.

BTW, this whole approach has been automated as part of Ruby on Rails, complete with the rollback scripts. I like the idea of that, but not all frameworks do that. When I'm not using Ruby on Rails, I use the approach outlined above.