Batch Change Tenant Databases

To practice GitOps with tenant databases, you need to enable the VCS Integration for your selected project, then use the VCS repository instead of our UI to organize and propose schema and data changes.

Migration scripts

Migration scripts are stored in the VCS repository for a GitOps-enabled tenant project, and use the file path to provide important migration information, including environment, database, version, type, and description. These information are captured through the File path template.

Use SQL files

Typically, SQL files are used as migration scripts for both schema (DDL) and data (DML) changes.

For example, to add a new table for all databases in the project, you can use the default File path template ({{VERSION}}##{{TYPE}}##{{DESCRIPTION}}.sql), the file path of the SQL file would look like bytebase/0001##ddl##add-company-table.sql with the following content:

CREATE TABLE company (
  id INT PRIMARY KEY     NOT NULL,
  name           TEXT    NOT NULL,
  address        CHAR(50)
);

Where bytebase/ is the Base directory, depending on your configuration.

Use YAML manifest

Using SQL files as migration scripts has the limitation of only being able to target a single database, to make a data change across an arbitrary set of databases.

A YAML manifest follows the same file path convention as defined by the File path template but using .yml instead of .sql as the file extension:

-bytebase/0003##dml##insert-companies.sql
+bytebase/0003##dml##insert-companies.yml

Below is the file content for inserting a new row to the database supermarket:

databases:
  - name: supermarket
statement: |
  INSERT INTO company (id, name, address) VALUES (1, 'Bytebase', '1 DevOps street');

Both SQL files and YAML manifests can co-exist and be used on per-migration basis depending on your needs. After several migrations, your VCS repository for storing migration scripts could look like follows:

bytebase/0001##ddl##add-company-table.sql
bytebase/0002##ddl##add-payout-table.sql
bytebase/0003##dml##insert-companies.yml
bytebase/0003##ddl##insert-payouts.sql

To target multiple databases, add more entries to the databases section:

databases:
  - name: supermarket
  - name: supermarket_east
  - name: supermarket_west
statement: |
  INSERT INTO company (id, name, address) VALUES (1, 'Bytebase', '1 DevOps street');