MySQL – How to Track Database Schema Changes

MySQLschemaversion control

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

Best Answer

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).

…

Related Question