How I Automated Database Schema Migrations in a Multi-Tenant Database Model

How I Automated Database Schema Migrations in a Multi-Tenant Database Model

In one of the applications that I’m working on at the moment, there was a requirement to automate the DB migrations into the pipeline. Our database model is based on a multi-tenant database design. That means in the same database, you can find many schemas.

In one of the applications that I’m working on at the moment, there was a requirement to automate the DB migrations into the pipeline. Our database model is based on a multi-tenant database design. That means in the same database, you can find many schemas. The database structure will look something like the following.

diagram of multi-tenant model

Multi-tenant DB model

When there’s a need to do a DB migration, we used to run our migration script against every schema, which was a tedious task.

For example, let’s say that we want to add a new column,column_A to table_1. We should write the following SQL statement to do that.

Alter table table_1 Add Column column_A varchar(255)

Since we use a multi-tenant database design, this same change needs to be done to every schema in the database because table_1 is there in every schema.

db-migration programming jenkins database devops

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Jenkins Is Getting Old — It’s Time to Move On

After using Jenkins on several projects, we say it's time to move on. Jenkins is left behind with his old approach — found out more!

DevOps Basics: What You Should Know

What is DevOps? What are the goals it helps achieves? What are its benefits? This article has answers!

What are the benefits of cloud migration? Reasons you should migrate

To move or not to move? Benefits are multifold when you are migrating to the cloud. Get the correct information to make your decision, with our cloud engineering expertise.

How to Extend your DevOps Strategy For Success in the Cloud?

DevOps and Cloud computing are joined at the hip, now that fact is well appreciated by the organizations that engaged in SaaS cloud and developed applications in the Cloud. During the COVID crisis period, most of the organizations have started using cloud computing services and implementing a cloud-first strategy to establish their remote operations. Similarly, the extended DevOps strategy will make the development process more agile with automated test cases.

DevOps for Databases: Collaboration for Convenience or Practicality?

Devops for Databases look like a perfect match. Database Devops can function like a well-oiled machine that ensures successful implementation of your devops strategy.