Deploy Schema Migration with Bytebase API

Hi and welcome to Database DevOps Academy #25!

Each week, we share Database DevOps insights and best practices for modern engineering organizations. If you enjoy this newsletter, we ask you to subscribe and share. ??

In this Issue #25, we are exploring how to deploy database schema migrations through Bytebase API. Enjoy!

Bytebase is a database DevOps and CI/CD Tool for Developer, DBA and Platform Engineering teams. It provides an intuitive GUI to manage database schema changes. On the other hand, for some teams, they may want to integrate Bytebase into their existing DevOps platform. This requires calling the Bytebase API.

In this tutorial, you will begin by creating a schema change within the Bytebase console. Then, you will proceed to run an external sample application that utilizes the API to create a similar change.

By following the step-by-step instructions provided, you will have the opportunity to explore and experiment with the Bytebase API. This will give you a practical understanding of how to incorporate it into your own application.

The API example repository is at https://github.com/bytebase/api-example

Prerequisites

1. Docker installed.
2. Node.js >= v18.

Start Bytebase and prepare the accounts

• Make sure your Docker daemon is running. Copy and paste the commands to start one Bytebase and two MySQL instances via Docker.

docker run --init \
  --name bytebase \
  --restart always \
  --publish 5678:8080 \
  --health-cmd "curl --fail https://localhost:8080/healthz || exit 1" \
  --health-interval 5m \
  --health-timeout 10s \
  --volume ~/.bytebase/data:/var/opt/bytebase \
  bytebase/bytebase:2.12.0 \
  --data /var/opt/bytebase \
  --port 8080        

• Bytebase is now running via Docker, and you can access it via localhost:5678. Register the first admin account which will be granted Workspace Admin.
• Log in as the admin user, click on the gear icon on the top right corner and click on Workspace > Members.
• Turn on Create as service account, fill in with api-example, choose the DBA role that is sufficient for this tutorial and click Add.
• Find the newly created service account in the Active members section and click on Copy Service Key. We will use this token to authenticate the API calls.
• Click Select Project on the top bar, and you'll see there's a sample project called Sample Project. Click on it to enter the project page, there are two databases: hr_prod and hr_test.

Create a schema change in Bytebase Console

Let's first review how to create a schema change from console directly.

• Stay in Bytebase console, go to Sample Project. Click the checkbox for hr_prod and click Edit Schema.
• Switch Raw SQL, paste the following SQL statement and click Preview issue.

CREATE TABLE test_from_console ( id integer NOT NULL );        

• Click Create, after the automatic checks are done, it'll automatically roll out the change. The issue will become Done.

Create a schema change via Bytebase API

Below is an example app demonstrating the following APIs:

• List all projects
• List all database under a project
• Create a schema change issue in the project
• Get the created issue status.

• Go to Bytebase API Example repo and clone it.
• Copy env-template.local to .env.local.Update the variables.NEXT_PUBLIC_BB_URL: https://localhost:5678NEXT_PUBLIC_BB_SERVICE_ACCOUNT: api-exampleNEXT_PUBLIC_BB_SERVICE_KEY: service key copied in Step 2
• Run the following commands to start the sample application.

pnpm i && pnpm dev        

• Open the application in your browser, you'll see the following page.
• Choose Sample Project, then hr_prod, input the following SQL and click Create New Issue.

CREATE TABLE test_from_api ( id integer NOT NULL );        

• The issue will be created and you'll see the following page.
• Click the link View issue xxx in Bytebase[OPEN] to see the issue in Bytebase Console. You'll notice the issue rolls out automatically and becomes Done.
• Go back to the sample application, and click Refresh status. You'll see the status has changed from [OPEN] to [DONE].
• For additional information about the example application, refer to the README in its corresponding GitHub repository.

Summary

Congratulations! You've successfully created a schema change via Bytebase API. In similar ways, you could integrate Bytebase API into your existing DevOps platform to automate your schema change process, and benefit Bytebase features such as SQL Review, Custom Approval and Schema Drift Detection.