Manage Databases 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.

You can find the complete Terraform code example on GitHub. This tutorial will help you get started with Bytebase and Terraform.

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#

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.6.0

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 \
-p 3307:3306 \
-e MYSQL_ROOT_PASSWORD=testpwd1 \
-d mysql:8.0

docker run --name mysqldtest \
-p 3308:3306 \
-e MYSQL_ROOT_PASSWORD=testpwd1 \
-d mysql:8.0

Add an Instance in Bytebase from GUI#

In this section, you'll add an instance in Bytebase.

Login as admin, click Instances on the left sidebar, and click + Add Instance on the top right.
Create an instance for Test Environment with the following configuration. Fill username/password as root/testpwd1
Click Projects on the left sidebar, and click + New Project on the top right. Create a project Test and click Create.
Go into Test project, click Database >Databases on the left sidebar, and click + New DB on the top right.
Create a database demo, and click Create. This will take you to the issue page, an issue is created.

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 example configuration, 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#

Go to IAM & Admin > Users & Groups, click + Add User.
Choose Service Account as the Type, fill in the Email with tf@service.bytebase.com, choose Workspace DBA as Roles, and click Confirm.
Copy the Service Key for later use.

Step 3 - Query to list all resources#

Paste the Service Key, Service Account Email, and URL into main.tf.

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 like this:

all_environments = {
   [
       {
           environment_tier_policy = "UNPROTECTED"
           name                    = "environments/test"
           order                   = 0
           resource_id             = "test"
           title                   = "Test"
         },
       {
           environment_tier_policy = "UNPROTECTED"
           name                    = "environments/prod"
           order                   = 1
           resource_id             = "prod"
           title                   = "Prod"
         },
     ]
     id = "xxxx"
     show_deleted = false
}

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

all_instances = {
"id" = "1744624330"
"instances" = tolist([
   {
      "activation" = false
      "data_sources" = toset([
      {
         "database" = ""
         "external_secret" = tolist([])
         "host" = "host.docker.internal"
         "id" = "bb67b4b8-40c6-4ac6-a170-5f673183c759"
         "password" = ""
         "port" = "3307"
         "ssl_ca" = ""
         "ssl_cert" = ""
         "ssl_key" = ""
         "type" = "ADMIN"
         "username" = "root"
      },
      ])
      "engine" = "MYSQL"
      "engine_version" = "8.0.36"
      "environment" = "environments/test"
      "external_link" = ""
      "maximum_connections" = 0
      "name" = "instances/mysql-test"
      "resource_id" = "mysql-test"
      "sync_interval" = 0
      "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" = "mysql-test".

bb-instances

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:

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

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

resource "bytebase_instance" "test" {
depends_on = [
   bytebase_environment.test
]
resource_id = "mysql-test"
environment = bytebase_environment.test.name
title       = "MySQL test"
engine      = "MYSQL"
activation  = false

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

resource "bytebase_instance" "prod" {
depends_on = [
   bytebase_environment.prod
]
resource_id = "mysql-prod"
environment = bytebase_environment.prod.name
title       = "MySQL prod"
engine      = "MYSQL"
activation  = false

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

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

two environments – Test and Prod
two instances - MySQL test and MySQL prod

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.
Click Instances on the left sidebar, and you will see the two instances we just added.
Click into one instance, scroll down and click Test Connection. It should be successful.

Things to Pay Attention To#

Operations in Bytebase that rely on issues are not supported by Terraform. This includes:

Create a database
Executing DDL/DML statements

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.