How to implement MySQL database migration feature

Ahad, 30 Jun 2013, 12:21 am0

Basic idea of database migration is database schema, which is organized into versions, need to be upgraded or downgraded from previous to current version. Refer to the diagram below, a simple database to store a blog post:

On initial release (version 1), the application only have 4 columns – id, title, content & created. When new version come out (version 2), developer added two new columns – updated & cover_img.

For each version change, there must be upgrade() and downgrade() function. upgrade() is to upgrade the schema from previous to current version, while downgrade() is to revert changes to the previous version. Here’s how the upgrade & downgrade SQL queries going to be:

upgrade version 0 to 1

CREATE TABLE IF NOT EXISTS tbl_post (
id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
content TEXT NOT NULL,
created DATETIME,
PRIMARY KEY (id)
)

downgrade version 1 to 0

-- DROP TABLE tbl_post

upgrade version 1 to 2

ALTER TABLE tbl_post ADD (
updated DATETIME,


cover_img VARCHAR(255) NOT NULL
)

downgrade version 2 to 1

ALTER TABLE tbl_post
DROP updated,
DROP cover_img

To implement database versioning to support database migration, developer need to create a table to store information about the current database version that is in effect. It could be as simple as this table: