Manage Databases in Bytebase with Terraform

This tutorial will guide you to use Terraform Bytebase Provider to manage your databases via Terraform.

Terraform is an infrastructure as code tool that lets you build, change, and version infrastructure safely and efficiently. This includes low-level components like compute instances, storage, and networking; and high-level components like DNS entries and SaaS features.

Bytebase is an open-source database DevOps tool, it's the GitLab for managing databases throughout the application development lifecycle. It offers a web-based workspace for Developers and DBAs to collaborate and manage the database change safely and efficiently.

Why Terraform? Although Bytebase provides a GUI for you to manage databases, if you have tens or hundreds of database instances for different environments, instead of repetitive and error-prone manual work, Terraform would definitely save your efforts and prevent mistakes.

Before you start the tutorial, make sure:

Have Docker installed.

Install Terraform#

Follow Terraform installation Guide, we use the mac version in this tutorial.

Install the HashiCorp tap, a repository of all our Homebrew packages.

brew tap hashicorp/tap

Install Terraform with hashicorp/tap/terraform.

brew install hashicorp/tap/terraform

Verify the installation by typing.

terraform -help

Run Bytebase#

In this section, you’ll start Bytebase and follow its onboard guide.

Make sure your docker daemon is running, and then start the Bytebase docker container by typing the following command in the terminal.

docker run --rm --init \
  --name bytebase \
  --publish 8080:8080 --pull always \
  --volume ~/.bytebase/data:/var/opt/bytebase \
  bytebase/bytebase:3.0.1

Type the following commands one by one in the terminal to start two MySQL instances, and they will be mapped to Test and Prod environments later.

docker run --name mysqldtest \
  --publish 3307:3306 \
  -e MYSQL_ROOT_HOST=172.17.0.1 \
  -e MYSQL_ROOT_PASSWORD=testpwd1 \
  mysql/mysql-server:8.0

docker run --name mysqldprod \
  --publish 3308:3306 \
  -e MYSQL_ROOT_HOST=172.17.0.1 \
  -e MYSQL_ROOT_PASSWORD=testpwd1 \
  mysql/mysql-server:8.0

Register admin account DBA. This account will be granted Workspace Admin role. https://www.bytebase.com/docs/concepts/roles-and-permissions

Add an Instance in Bytebase from GUI#

In this section, you'll follow the onboard guide to add an instance in Bytebase.

Follow the onboard guide or click Add instance on home page.
Create an instance for Test Environment with the following configuration. Fill username/password as root/testpwd1
Follow the onboard guide or click New Project on Projects page. Create a project Test with key TEST and click Create.
Follow the onboard guide or click New DB on the project Test page.
Create a database demo, and click Create. This will take you to the issue page, an issue is created. Since it’s for Test environment, it will execute without approval from you. Click Resolve issue, and the issue will be done.

create-db-demo

issue-db-demo

Add Instances via Terraform#

You’ve added an instance for the Test environment in Bytebase by clicking. What if you need to add hundreds of instances. In this section, you’ll witness the process simplification Terraform brings.

Step 1 - Create a Terraform file#

Create a new folder learn-terraform-bytebase and create a blank file main.tf in it.
Go to https://registry.terraform.io/providers/bytebase/bytebase/latest/docs. Click Use Provider, copy and paste the whole code block in the gray box into main.tf. Pay attention to the version.
Follow the document and go to Example Usage. Copy the following provider part and paste it in main.tf.

provider "bytebase" {
 service_account = "<Your Bytebase service account email>"
 service_key     = "<Your Bytebase service account key>"
 url             = "<Your Bytebase external URL>"
}

Step 2 - Add a Terraform account#

Click Settings on the top navigation bar, and click Workspace > Members.
Turn on Create as service account, fill email with prefix tf, and click + Add.
Scroll down, and you can see the newly added account there. Click Copy Service Key.

bb-key-copied

Step 3 - Query to list all resources#

Paste the Service Key, Service Account Email, and URL into main.tf.The file now should look like this:
Paste the following queries after the provider block and save the file. What it does is to list all existing environments and instances and print those out in the terminal.

# List all environment
data "bytebase_environment_list" "all" {}
output "all_environments" {
 value = data.bytebase_environment_list.all
}

# List all instances
data "bytebase_instance_list" "all" {}
output "all_instances" {
 value = data.bytebase_instance_list.all
}

Run terraform init, terraform plan and terraform apply one by one in the terminal. You’ll see the output:

all_environments = {
    "environments" = tolist([
     {
        "environment_tier_policy" = "UNPROTECTED"
        "order" = 0
        "resource_id" = "test"
        "title" = "Test"
    },
    {
        "environment_tier_policy" = "UNPROTECTED"
        "order" = 1
        "resource_id" = "prod"
        "title" = "Prod"
    },
    ])
    "id" = "1673486499"
    "show_deleted" = false
}

As we have two default environments in our Bytebase. Pay attention to resource_id, they are test and prod . environments-test

all_instances = {
    "environment" = "-"
    "id" = "1673486499"
    "instances" = tolist([
        {
        "data_sources" = tolist([
            {"database" = ""
            "host" = "host.docker.internal"
            "port" = "3307"
            "title" = "Admin data source"
            "type" = "ADMIN"
            "username" = "root"
            },
        ])
        "engine" = "MYSQL"
        "environment" = "test"
        "external_link" = ""
        "resource_id" = "instance-e14ae248"
        "title" = "MySQL Test"
        },
    ])
    "show_deleted" = false
}

As we can see, it’s the instance we just added. Follow "title" = "MySQL Test", you'll find "resource_id" = "instance-e14ae248". instance-test

Step 4 - Add instances via Terraform#

Now you have listed all environments and instances you have in Bytebase. Then how to create/update?

Remove the #List all environment and #List all environment blocks, and add the following:

# Define variable name
locals {
    environment_id_test = "test"
    environment_id_prod = "prod"
    instance_id_test = "test-instance"
    instance_id_prod = "prod-instance"
}

# Create a new environment named "Test"
resource "bytebase_environment" "test" {
    resource_id = local.environment_id_test
    title = "Test"
    order = 0
    environment_tier_policy = "UNPROTECTED"
}

# Create another environment named "Prod"
resource "bytebase_environment" "prod" {
    resource_id = local.environment_id_prod
    title = "Prod"
    order = 1
    environment_tier_policy = "UNPROTECTED"
}

# Create a new instance named "MySQL Test TF"
resource "bytebase_instance" "test" {
    resource_id = local.instance_id_test
    environment = bytebase_environment.test.resource_id
    title = "MySQL Test TF"
    engine = "MYSQL"

    # You need to specific the data source
    data_sources {
        title = "admin data source"
        type = "ADMIN"
        username = "root"
        password = "testpwd1"
        host = "host.docker.internal"
        port = "3307"
    }
}

# Create a new instance named "MySQL Prod TF"
resource "bytebase_instance" "prod" {
    resource_id = local.instance_id_prod
    environment = bytebase_environment.prod.resource_id
    title = "MySQL Prod TF"
    engine = "MYSQL"

    # You need to specific the data source
    data_sources {
        title = "admin data source"
        type = "ADMIN"
        username = "root"
        password = "testpwd1"
        host = "host.docker.internal"
        port = "3308"
    }
}

What it does is first to define some variables, and then add four resources:

two environments – Test and Prod
two instances - MySQL Test TF and MySQL Prod TF

Run terraform init, terraform plan and terraform apply one by one in the terminal. You will see this in the terminal.
Go back to Bytebase, and click Environments. There is nothing changed with these two environments.

If you go back to the previous query output

"resource_id" = "test"
"title" = "Test"

"resource_id" = "prod"
"title" = "Prod"

You can see that the resource_id happens to be the same as in the local variables:

environment_id_test = "test"
environment_id_prod = "prod"

Combined with the terminal warning message above:

Warning: Environment already exists
...
Environment test already exists, try to exec the update operation

What happened is that the two existing environments match with the ones terraform declare by resource_id, so the Bytebase provider will attempt to update the environment.

Click Instances. You’ll see there are two instances added.

Why it's different from the environments? If you go back to query output for our existing instance which is added from GUI.

"resource_id" = "instance-e14ae248"

And there are the resource_ids defined in the local variables:

instance_id_test    = "test-instance"
instance_id_prod    = "prod-instance"

The resource_id generated by UI operation instance-e14ae248 can’t match the one defined in terraform, and it’s not possible to adjust to make them match. So for the instances, it adds them instead of updating.

Step 5 - Test if the instances added by Terraform are working#

Click Projects on the top navigation bar, and then click New Project. Create a new project called TestTF.
Click TestTF on the left side bar, and click New DB.
Fill out the form as follows and click Create.

Name: demotf
Environment: Test
Instance: MySQL Test TF

You’ll be redirected to the issue page, and click Resolve issue.
Click Instances on the top navigation bar, then select MySQL Test TF. You’ll see demotf.

Summary and Next#

Now you have learned how to use Terraform to manage your MySQL database environments and instances in Bytebase, for PostgreSQL, you can futher declare database roles. Please check more example usage in GitHub.

If you encounter any problems while trying, welcome to our discord channel.