Database Migration — Writing Scripts & Best Practices

Database migration on a production database is never simple. Depending on the volume of requests, some teams schedule database migration to off hours.

Can you imagine being able to roll back granular changes to the schema as you write your code?

There are even standalone database migration software libraries like Flyway and Liquibase. Before I even start, let me first explain what a DB migration is all about.

What is Database Migration?

Migration, in the context of enterprise and web-based applications means moving from one platform to another. Database Migrations are particularly complicated as you have all the challenges of changing your software platform where some old features are missing or behave differently.

Using Migration Scripts in Database Deployments

Migrations are the version control for your database, allowing your team to easily modify and share the application’s database schema. Basically, you affect the way the data gets stored! This alteration can be as simple as adding or removing a column to a table, or a complex refactoring task such as splitting tables or changing column properties.

Migration scripts are of two types:

• Automated Migration Script — this is a script generated by a synchronization tool such as SQL Compare and normally checked, and perhaps altered, by a developer or DBA.
• Manual Migration Script — this is a script written by a database programmer. Sometimes it is impossible for a tool to create a migration script that will preserve the data in the correct place. Although it knows what the ‘before’ and ‘after’ versions of the database look like, there is sometimes ambiguity regarding the correct transition path.

The classic example is that of renaming a table from A to B. We know it’s the same table with a different name, but a tool would likely perform the transition by dropping A, and any data it contains, and creating B !! Ouch!! :-(

Things to consider — Bad Practice VS Good Practice

1. Know your system

What comes as the top priority for migration is understanding what your system is all about & what it needs ! Yes, without knowing why the system is built for, having DB migrations written would only add junks.

Lets say, we had written a migration script yesterday to add a column & then we write another script today to remove the column :-) Please avoid!

2. Add Guards to Scripts

Guard Clauses are a good way of ensuring that Murphy’s law will have no consequences.

Every migration script needs a way to check the preconditions in the environment in which it will run to make sure the script has the right and that it is idempotent, meaning that running it repeatedly will have no further effect. For this purpose, we include in every script a guard clause that will, for example, check to see whether the migration task has already been accomplished before continuing to run it.

IF EXISTS ( SELECT 1 FROM information_schema.Tables WHERE table_schema = 'MySchema' AND TABLE_NAME = 'MyTableName' )
    PRINT 'the table exists'
ELSE
    PRINT 'The table does not exists'

3. Choose One Database Migration Tool and Stick to It

I want to warn against making unnecessary changes to the tool-set simply because it’s cool. Recall that database migration scripts are dependent on the tool you use to generate them. There’s no proper standards for these scripts. So, either you are locked into your database migration tool, or you’re locked into your choice of database — or in some cases, even both.There are better things to do with your time than spin your wheels needlessly.

4. Prefer `ORM APIs` to `Raw` SQL statements

There is always a reason why we go for ORMs. So why not make the best use of it? Say, if we prefer TypeORM, below would be the bad & good practice.

Bad Practice

alter table `MyTableName` add column `MyColumnName` `MyDataType`

Good Practice

await queryRunner.addColumn(`MyTableName` new TableColumn({ 
    name:"MyColumnName",
    type:"MyDataType"
})

5. `Do not` deploy the code first

Do not deploy the code first that write to new columns, and then run the migration. It will only result in errors as the code might try to access non-existing column(s).

6. Ensure the new changes work before you drop anything

Run `drop` or `rename` columns only after you are fully satisfied that the new changes are not breaking anything.

7. Use `Feature Flags` whenever possible

Adding Feature flags, is an upcoming best practice to decouple deployment from exposure. These flags can be used to implement a variety of features, such as user-based opt-in, A/B testing, incremental rollback, and the graceful sun-setting of features.

The simplest implementation of a feature flag is an “if/else” statement and a Boolean variable sourced from the environment.

if enabled {
   newFeature()
} else {
   oldFeature()
}

Conclusion

Developers need all the tools they can get to make their lives easier. Database migration is one of those must-have tools for the developer’s toolbox. I foresee that employing database migrations will evolve from a development best practice to a development standard practice. People will look at you funny for not using database migration at all. Still, it’s important to keep in mind how database migrations can backfire on you, particularly for hard-to-reverse schema changes.

Hence, be absolutely certain of those changes before you make them go live.