How can a group track database schema changes?

Question

What version control methodologies help teams of people track database schema changes?

Answer 1

just a couple of minutes ago I was checking this: A table that should exist in all projects with a database, seems simple enough to put in practice, check it out:

It’s called schema_version (or migrations, or whatever suits you) and its purpose is to keep track of structural or data changes to the database. A possible structure (example in MySQL) is:

create table schema_version (
     `when` timestamp not null default CURRENT_TIMESTAMP,
     `key` varchar(256) not null,
     `extra` varchar(256),
     primary key (`key`)
) ENGINE=InnoDB;

insert into schema_version(key, extra) values ('001', 'schema version');

Whether you add this table from the beggining of the project or just after you’ve deployed the first version to a staging or production server is up to you.

Whenever you need to execute an SQL script to change the database structure or perform a data migration you should be adding a row in that table as well. And do that via an insert statement at the begining or end of that script (which is committed to the project’s code repository).

…

Answer 2

I think the best method is to have the database generated as part of your build process. Keep all of the scripts in source control with the rest of the code, and everyone is responsible for their own environments.

Failing that, RedGate has a tool to integrate source control into SSMS and SQL Compare is useful for comparing/synchronizing MS SQL Server schemas. Visual Studio Database Edition also has a built-in schema compare tool.

Another SO question lead me to Migrator Dot Net which I am going to begin investigating during my copious free time. It looks like a good method, but it might be more of a time/overhead investment than you are willing to make.

Answer 3

eiefai already mentioned A table that should exist in all projects with a database. This is a great blog post, but IMO it only goes part of the way to a working solution for database revision control. I think any attempt to "answer" this question in the real world needs to consider some of the other information about VCS and databases:

• The Agile Data methodology
• The "Database Refactoring" book
• Martin Fowler's article on "Evolutionary Database Design"
• The Liquibase tool
• A decent video with Scott Ambler presenting his thoughts on "Agile Databases"

Answer 4

There are a couple different angles to approach this question, I think. The "tool-first" angle, I believe, is going to vary based on platform and personal preference. Case in point: I'm using a Database Project in MS Visual Studio, but I'm not sure that this is a great solution for MySQL. I also know people who are pretty sold on their favorite tools from Redgate, Erwin, Embarcadero, etc.

There's also a "process-first" angle for this question, which will (hopefully) be revisited on this site in subsequent questions. The keystones in this process are getting your schema under source control and managing changes such that you can apply schema changes from version "x" to version "y" pretty much on demand.

A definitive answer to this topic is going to end up looking like a book, so it's probably worth starting out by referencing one: Redgate recently published a free ebook called "The Red Gate Guide to SQL Server Team-based Development", and while there's plenty in there to be debated, it's a pretty good place to start debating, IMO. Contrary to the name, much of the material in this book is general enough to apply to any DB (not just SQL Server) and any tool set (not just Redgate). If you haven't seen this already, it's definitely worth a skim, at least.

Finally, it's probably worth linking in the "legacy answer" from stackoverflow.

Answer 5

SchemaCrawler is my tool to produce a text file with all of database schema objects. I designed this text output to be both human-readable, as well as diff-able against similar output from another server.

In practice, what I have found is that outputting a text file of the database schema is useful, when done as part of the build. This way, you can check the text file into your source code control system, and have a version history of how your schema has evolved over time. SchemaCrawler is designed to automate this too, from the command-line.