How to use Bytebase to coordinate multi-tenancy database schema change

Photo by Jason Richard on Unsplash

How to use Bytebase to coordinate multi-tenancy database schema change

DBA's guide to managing databases with identical schemas.

Adela's photo
Adela
·May 5, 2022·

3 min read

Table of contents

  • DBA Prepares the Configuration
  • DBA Creates the Project
  • Developer Creates the SQL issue
  • DBA Approves the issue

If you haven't set up Bytebase for your team, please refer to How to start using Bytebase for team database collaboration

We already learn the basic SQL review process in Bytebase in How to use Bytebase to facilitate SQL review process (UI workflow)

In this article, we'll put ourselves in the shoes of a SaaS-like system if we have a database schema change and want to apply it to all tenants with the exact change without any mistake.

DBA Prepares the Configuration

Based on the configuration in How to start using Bytebase for team database collaboration, we have two instances mapping to Test and Prod environments. We log in as Adela (The Workspace DBA) again.

image.png

For multi-tendency, we need more.

Adela adds two more instances, and for each of the four instances, she creates a new database employeeSaaS for a multi-tendency SaaS system (it's precisely the same database as employee in Import sample database into AWS RDS MySQL using Shell & Workbench ).

image.png

In addition to that, She clicks Settings on the top navigation bar and adds labels for different tenants.

image.png

DBA Creates the Project

She Creates a new project EmployeeSaaS with Tenant mode selected.

image.png

Adela adds stages with tenant labels under the project Deployment Config tab. In our case, it's Prod 1, Prod 2, and Prod 3.

image.png

image.png

image.png

image.png

She clicks Preview, the warning says there is no database.

image.png

She clicks Transfer in DB.

image.png

These four databases are the ones we need.

image.png

She transfers them one by one with the corresponding tenant label.

image.png

After that, she can now see all those databases on the project home page.

image.png

Adela invites Ray and Lucy as developers.

image.png

Developer Creates the SQL issue

Now we switch to Ray - the project developer. Since Ray is the developer of Project EmployeeSaaS, she can view project information.

image.png

She clicks Alter Schema and selects Tenant.

image.png

After clicking Next, the page will redirect to the new issue page.

image.png

She fills in SQL, assigns the issue to Adela for review, and clicks Create. The SQL runs automatically and successfully in the Test stage since it requires no manual approval.

image.png

DBA Approves the issue

It's Adela's turn to approve.

image.png

After reviewing the SQL, Adela clicks Approve.

image.png

image.png

The task on Prod Stage passes. Adela continues to approve Prod 2 Stage and Prod3 Stage.

image.png

After tasks on all stages pass with green checkmarks, Adela can click Resolve issue.

image.png

image.png

When she goes back to the project home page, she can see a string of the same code under each database. It's the migration version code.

image.png

image.png

 
Share this