A compelling job of teams which build web applications is to consider and build the database design. In an ideal world, clients know exactly what they want down to the T, requirements are defined and locked up, and the key thrown away in the ocean. The development team analyzes the requirements, drafts up the database design and begins the work, never to change the database design.

But that’s a world we don’t live in. We know that clients and customers want more and more. We need to keep making changes to the software as we go along with our lives. This is why some smart people came up with version control systems and Github is so popular.

But what should one do about database structures? How do you track changes to the numerous tables, columns and data added and dropped? Moreover, how do multiple teams share the changes they’ve made over distributed codebase and communicate these changes with each other?

Migrations come to the rescue.

Good web development teams understand the importance migrations play in development and deployment life-cycles. Think of migrations as an audit trail of changes made to the database – a ledger of sorts. Except this ledger not only tracks changes, but also executes them.

A programmer, after creating the initial database design, goes in and generates scripts which define the tables and fields that are to be created. They are in-fact writing into this ledger that they want a table added to store certain information. In normalized database design, multiple tables could be used to store information. Therefore this ledger holds all details about the table names for each, the fields in each table and the order in which they have to be created.

data migrations

Once the ledger is prepared with all the entries, we then run a program that reads the ledger and goes and creates the tables with their description in the database automagically. No need to export sql files and share on email and ask people to import the database and change entrees. It just takes a few seconds to set this up and saves a huge overhead down the line.

If changes need to be made, such as adding new fields or removing existing ones or adding new tables. The ledger is updated and the desired changes are appended. Keep in mind, that the old instructions are not removed, new ones are added which may change old instructions and are executed in the order. When the program to execute the changes is run again, the system automatically starts from the last change that was run.

The ledger itself can be versioned on Github or SVN, and that makes deployment of database changes across environments a breeze. The techies have name for this ledger and they call it Migrations.

Almost all major application development frameworks such as CakePHP, Symfony, Ruby, Django have support for migrations. Good application development teams use migrations because it saves them so much time.