Batch Change
Bytebase allows you to change a collection of databases in a single workflow. These databases usually have a homogenous structure while belong to different development environments, geographic locations, SaaS tenants or data centers. Bytebase supports three most typical batch change scenarios:
- Change databases from multiple environments
- Change databases from multiple tenants
- Change databases from database groups
While multi-environment change also supports multiple databases in one environment, the main difference between these two is that for multi-tenant change, all the databases in the project have identical schemas.
Change databases from multiple environments
A development pipeline usually consists of multiple environments. Each environment has its own isolated database to store the respective data. A schema change will be propagated from each databases following the development pipeline. You may have multiple databases in one stage as well.
In Bytebase, you can select multiple databases from different environments to apply the database change.
Then Bytebase will then create an issue to track the multi-database changes. You may roll out changes one database after another or batch rollout databases at the same stage.
Change databases from multiple tenants
Bytebase allows you to change a collection of databases with identical schemas, these databases are often referred as tenant databases.
Scenarios
Typical scenarios of tenant databases are:
- A Software as a Service (SaaS) provider provides separate database instances for each of its customers (aka. tenants) alongside their application deployments.
- An internal platform team provides multi-region database deployments (e.g. US, EU), and have separate database instances in different deployment environments (e.g. Staging, Prod).
It is often desired to apply schema changes to databases across all tenants since these databases are homogeneous, but in a staged rollout fashion (aka. canary deployment) to minimize the risk of breaking all deployments.
You should consider using tenant databases when there are multiple database instances alongside multiple deployments for the same application. For example, a software company offers medical record storage services for its customers, hospitals. Each hospital is considered as a tenant, and each tenant has to store their patient data in its own database for regulation or privacy purposes. This feature allows updating database schema for all tenants in a simple and consistent way. Other use cases include multi-location databases for supporting highly-available services where each location is a tenant.
Let's take the hospital example to follow the steps below.
Prerequisites
- You have a running Bytebase with
Test
andProd
environments. - You have at least two instances of the same database type, one for
Test
environment and others forProd
environment.
Create a Project in Tenant Mode
Tenant projects empowers you to:
- Roll out schema changes and data updates to mutiple tenant databases by their environments, tenant labels or any combination of them.
- Progressively roll out through different stages, and only proceed to the next stage when all of rollouts in the current stage are successful.
- When there is a new tenant database created, it will inherit the same schema structures.
Create Databases with Tenant Labels
Within the project, click New DB to create four databases as following and then click Rollout and Resolve one by one:
hospital_test
forTest
environment with empty Tenant fieldhospital_prod_1
forProd
environment withh1
in Tenant fieldhospital_prod_2
forProd
environment withh2
in Tenant fieldhospital_prod_3
forProd
environment withh3
in Tenant field
In real life case, another way is to click Transfer in DB to transfer in your existing databases and then go into a specific database to edit the Tenant.
Adjust Deployment Configuration
Within the project, click Databases tab and you'll see the default deployment pipeline preview.
Scroll down and you will see the default deployment config.
Adjust the config deployment to the following by specifying Tenant. Besides the two default stages by environments, an extra stage for canary testing is added.
Scroll up and you will see the new pipeline preview.
Alter Schema for Tenant Databases
- Within the project, click Alter Schema. You'll see the popup.
- Paste the following scripts into the Raw SQL, and click Preview issue.
-
You'll be redirect to new issue preview page. Click Create, the issue with the configured pipeline will be created. SQL will be the same for all the tenant databases. Click Approve and Rollout if needed one database after another.
-
When it comes to stage with multiple databases, you may choose to Rollout current stage to rollout all databases under that stage.
-
Once the issue is completed, all tenant databases will have the same updated schema version.
Add a New Database
Within a tenant project, if you add a new database, it will automatically inherit the identical schemas from others.
- Click New DB, and create
hospital_prod_4
forProd
environment withh4
in Tenant field. - Go to view database
hospital_prod_4
, you'll see thetm1
table is already there.
Enforce Database Name with Template
If you want to enforce tenant databases to follow a nanme pattern, you can use the tenant database template. For example, suppose the template is {{DB_NAME}}__{{TENANT}}
. If the base database name is hospital_prod
, and there are 3 tenants h1
, h2
, h3
, the database name for the respective tenants can be enforced as hospital_prod__h1
, hospital_prod__h2
, hospital_prod__h3
.
-
Within the project, click Databases tab and you'll see the Tenant Database Name Template section. Click Edit, input
{{DB_NAME}}__{{TENANT}}
and click Update. -
Click New DB, if the database name doesn't match the template
{{DB_NAME}}__{{TENANT}}
, there will be an error. e.g.
- ❌
hospital_prod_h5
- separator should have two underscores instead of one. - ❌
hospital_prod__5
- tenant name should beh5
instead of5
. - ✅
hospital_prod__h5
GitOps
You can further adopt GitOps to batch change tenant databases. Head over to the doc Batch Change Tenant Databases for more details.
Change databases from database groups
If you are responsible for managing horizontally partitioned databases that are distributed across multiple data centers worldwide, applying database changes from database groups empowers you to manage these databases effectively and easily.
Follow the steps below to navigate through the process.
Create a Database Group
- Within a tenant project, click New database group in the Database Groups tab.
- Fill in the required information. This includes details such as database group name, environment and rules for filtering the desired databases.
- Click Save, you'll see the newly created
hotel_global
database group as below.
Create a Table Group
- Within a tenant project, click New table group in the Database Groups tab.
- Fill in the required information.
- Click Save to create the newly created
booking_global
table group.
Navigate to the details page of the hotel_global
database group, you'll see the booking_global
table group is already there.
Batch Alter Schema All Tables in a Table Group
- Within a tenant project, click Alter Schema.
- Click Munaul selection and select Database Group to locate he
hotel_global
database group. - Select the
hotel_global
database group.
- Enter the desired SQLs in the Raw SQL input box. For this example, enter the following SQL into the Raw SQL input box.
- Click Preview issue to review the issue details on the issue details page.
- Click Create to finalize the alter schema issue creation.
- Click Rollout current stage to apply the schema change.
Let's examine the change history of physical tables in the booking_global
table group with the following screenshots, you can see the newly added 'status' column in each physical table.