Problem statement

Ensuring Consistent Database Changes Across All Environments

Introduction

As developers, we often are challenged by moving changes swiftly to production. Usually, the challenges involved are understanding the problem, designing an efficient solution and typing it out (coding).

Consider a large team (More than 8 developers) in a fast-paced environment where changes move quickly and all environments (developer machines, production and non-production environments) must always have the same code changes. A typical production/application will most often have changes around frontend, backend and also database. [I'm excluding changes to infrastructure as they are less often].

Backend and frontend changes usually happen via code and when the latest branch is pulled/deployed, all developers/environments get the latest version of the code. This is simple enough. Additionally, given the fact that the code is usually version-controlled, it's easy to switch back to previous versions if necessary.

The outlier for this is "changes" to the database.

The problem

Assume you are a developer and you need to increase the column width from 255 to 500. The simplest way to do this, run a SQL script and share the script with everyone on your team. Provide a rollback script in case something fails. While this works okay, problems will start creeping in when you have more environments and even more developers.

1. Either we will forget to run this script in some environment

2. One or more developers fail to follow the instructions and miss running the script.

3. Version Controler and traceability become tedious.

Enough reasons for the database in some environments to become inconsistent. Trust me, this is a state you never want to be in.

My Experience

While working for a Fortune 100 company, the practice was to create an RFC (Request for change) with enough lead-time, attach the update and rollback scripts and provide detailed instructions for some DBA team to execute it. I'm not even going to talk about the approval process and the pain around it. Well, justifiable given the complexity and business criticality. The application was mostly on .Net and SQL Server.

How did this solve the problem:

1. All developers were connecting to a common database on some remote server.

2. So, as long as changes were implemented on that one common server, everything went well.

SAAS Product with MERN

In other SAAS projects, where I played with MERN stack, I have enjoyed playing with a wonderful library called migrate where changes to db were also done in as code (Hello nodejs my old friend) and the package would automatically take care of implementing the changes whenever developers pull the code.

How did this solve the problem:

1. Developer would implement the change via a migration script (It's just javascript code (node) with mongoose) and commit that script to codebase.

2. The migrate package, on every server start, checks for such new script and if any executes them once and marks them as executed. This way, once a change is executed, it won't run again.

3. Once the code goes to develop or master, whoever pulls the code, step 2 gets repeated.

This is a very clean solution that I've used so far.

SAAS Product with Java Springboot

I'm now working on a Java Springboot application with React & Postgres. The challenge of keeping the database state consistent is back. I stumbled upon Liquibase ( and also flyway but didn't like it) and we love using it right now.

How did this solve the problem:

1. the developer would implement the change via an XML file.

2. The xml file contains the actual script (SQL statements) to be implemented and commits that xml file to the codebase.

3. Luquibase, like migrate looks for new scripts on every server start and will take care of running the script if it's not run already.

4. Once the code goes to develop or master, whoever pulls the code, step 3 gets repeated.

It's probably a good idea to write another blog and explain how migrate and Liquibase works with an example.

---

Conclusion

Keeping database changes consistent across multiple environments is not an easy task. Fortunately, tools like migrate and/or liquibase comes in handy ensuring consistency and also making the database changes version-controlled.

---

Thank You

If you have reached so far, congratulations. Please feel free to share your thoughts via the comments.

---

Let's Connect

Feel free to connect with me on LinkedIn and Twitter if you have any questions.

Until Next time!