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.
3 min read
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.
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 ).
In addition to that, She clicks Settings on the top navigation bar and adds labels for different tenants.
DBA Creates the Project
She Creates a new project EmployeeSaaS with Tenant mode selected.
Adela adds stages with tenant labels under the project Deployment Config tab. In our case, it's Prod 1, Prod 2, and Prod 3.
She clicks Preview, the warning says there is no database.
She clicks Transfer in DB.
These four databases are the ones we need.
She transfers them one by one with the corresponding tenant label.
After that, she can now see all those databases on the project home page.
Adela invites Ray and Lucy as developers.
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.
She clicks Alter Schema and selects Tenant.
After clicking Next, the page will redirect to the new issue page.
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.
DBA Approves the issue
It's Adela's turn to approve.
After reviewing the SQL, Adela clicks Approve.
The task on Prod Stage passes. Adela continues to approve Prod 2 Stage and Prod3 Stage.
After tasks on all stages pass with green checkmarks, Adela can click Resolve issue.
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.