When you develop software with a SQL database, there are code changes that requires database changes as well. It could be database schema changes (e.g. adding a new column) or data fixes (e.g. changing all existing user phone number format). While it is common to use version control to manage code changes, there still many development teams not managing the database changes.

Database Migration concept

1. every database changes will be documented incrementally, as small as possible.

When there is a code change that requires several database table schema to be updated, database change script should be split into
multiple scripts.

For example, when there is code change that requires changing 2 database tables plus patching existing data, there should be at least 3 scripts:

  • add_discounted_amount_to_products.sql
  • add_total_amount_to_orders.sql
  • patch_products_default_discounted_amount.sql

2. database change script should include unique sequence number or timestamp

For example, files could be named using sequence number:

  • 001_add_discounted_amount_to_products.sql
  • 002_add_total_amount_to_orders.sql
  • 003_patch_products_default_discounted_amount.sql

or using timestamp:

  • 20170517080001_add_discounted_amount_to_products.sql
  • 20170517081501_add_total_amount_to_orders.sql
  • 20170517083601_patch_products_default_discounted_amount.sql

With these in place, the time dependency of database changes can be illustrated. It also enables change conflict induced by different conflicting changes from different developers.

3. all database changes are tracked in version control

With all database changes script in version control, you can keep every development database, staging database and production database in sync. It also allow flexibility to have different versions of schema in different development environment.

4. store current schema version number in database

Each database instance should store their own version number (and also "migrated" database change versions, but let's do go too depth for now).

How would that be useful ? Take previous file naming as an example. In the version control, there are 3 database change scripts:

  • 20170517080001_add_discounted_amount_to_products.sql
  • 20170517081501_add_total_amount_to_orders.sql
  • 20170517083601_patch_products_default_discounted_amount.sql

When one of the developer machines get the latest source code and found out it's current schema version is only 20170517081501, then it knows there are database changes haven't been applied yet and apply those to catch up the latest schema version.

Database Migration tools

Here listed some of the database migration tools commonly used in different programming language / framework:

RubyOnRails
http://guides.rubyonrails.org/active_record_migrations.html

Django
https://docs.djangoproject.com/en/1.11/topics/migrations/

Node.js
https://github.com/db-migrate/node-db-migrate

Java
https://flywaydb.org/

http://www.liquibase.org/

In my opinion, RubyOnRails data migration workflow and tools is the best among all the programming frameworks. If you are interested in this topic, spend some time to go through the RubyOnRails migration document. Enjoy !