🚀 Announcing Bytebase 3.0 - DevSecOps for Databases

Automate Database Change with Jira

Estimated: 45 mins

In the previous tutorial, we have set up a manual database change workflow with Jira and Bytebase. In this tutorial, we will show you how to automate the process by leveraging Jira and Bytebase Webhook and API. You need to finish the previous tutorial first.

Bytebase is an open-source database DevSecOps solution for Developer, Security, DBA, and Platform Engineering teams. The GitLab for database DevSecOps.

Here is what you will achieve by the end of this tutorial:

auto-jira

Prerequisites

Workflow Overview

Setup

  1. Configure the environment variables and run the jira demo app
  2. Setup Jira Webhook and API
  3. Setup Bytebase Webhook and API

Change process

  1. (Jira) Developer creates a Jira Database Change issue filling the summary, SQL, database, and description fields, the status is Todo.
  2. (Jira Webhook -> Bytebase API) Once the Jira issue is created, Jira webhook will trigger Bytebase API to create a corresponding issue.
  3. (Bytebase API -> Jira API) Once the Bytebase issue is created, the success response will trigger Jira API to set Jira issue with the Bytebase issue link and change the status to In Progress.
  4. (Bytebase) DBA goes to Bytebase to roll out the database change.
  5. (Bytebase Webhook -> Jira API) Once the Bytebase issue rolls out and becomes Done, Bytebase Webhook will trigger Jira API to set Jira issue status to Done.

auto-jira

Setup

Configure the environment variables and run the jira demo app.

  1. Go to the jira folder of the api-example repository, and copy env-template.local file as .env.local. Replace the placeholders with yours.

    NEXT_PUBLIC_JIRA_BASE_URL=https://xxxxxx.atlassian.net
NEXT_PUBLIC_JIRA_EMAIL=xxx@xxxx.com
NEXT_PUBLIC_JIRA_API_TOKEN=xxxxxxx
NEXT_PUBLIC_BB_HOST=https://xxxxxxx
NEXT_PUBLIC_BB_SERVICE_ACCOUNT=xxxx@service.bytebase.com
NEXT_PUBLIC_BB_SERVICE_KEY=bbs_xxxxxx

  2. Run pnpm i and pnpm run dev, you can run the demo app locally with localhost:xxxx. However, the app need to listen to webhook from Jira and Bytebase, so you need to make the app network accessible from both. By using ngrok or vscode ports, you can acheive this.

    wm-empty

Jira webhook: To trigger when Jira issue is created or updated

  1. Go to Jira, click Settings and then System settings in the dropdown menu.

    jira-go-setting

  2. Click WebHooks on the left sidebar, and then click + Create a WebHook.

    jira-create-webhook

  3. Fill in the URL, remember to append /api/receive-jira-issue-webhook to your base URL for the demo jira app, select Issue created and updated, and click Create.

    jira-webhook-details

Bytebase API: To create a Bytebase issue

  1. Go to Bytebase, click IAM & Admin > Users & Groups on the left sidebar, and then click +Add User.

    bb-add-user

  2. Choose Service Account as User Type, fill in the Email, give it Workspace DBA role, and then click Create. Copy the API Token to the .env.local file.

    bb-new-user

  1. Go to Atlassian Account >Security > API tokens to generate an API token. Copy the API Token to the .env.local file.

    jira-api-tokens

Bytebase Webhook: To trigger when Bytebase issue is set to Done

  1. Go in to the project, click Integration > Webhooks on the left sidebar and click Add A Webhook.

    bb-add-webhook

  2. Choose Custom as Destination, fill in the Webhook URL, remember to append /api/receive-bb-issue-webhook to your base URL for the demo jira app, select Issue status change as Triggering activities and click Create.

    bb-new-webhook

Change process

Step 1 (Jira): Create a database change issue

  1. You act as a developer, now go to the Jira project to create a Database Change issue, fill in the fields summary, SQL, database, and description, and click Create. Here's the screenshot of the issue.

    jira-todo

  2. View the jira app demo, you will see there's a jira webhook received with Todo status.

    wm-todo

Step 2 (Jira Webhook -> Bytebase API) Once the Jira issue is created, Jira Webhook will trigger Bytebase API to create a corresponding issue

Go to the Bytebase project and find the issue which is waiting to rollout.

bb-to-rollout

It's because the jira webhook trigger Bytebase API to create an issue there. The logic is in src/api/receive-jira-issue-webhook/route.ts.

  1. When it receive the trigger, it checks if the issue type is Database Change. and then if the webhook event is issue_created.

    const body: JiraWebhookPayload = await request.json();

   const issueType = body.issue.fields.issuetype.name;
   if (issueType !== 'Database Change') {
      return Response.json({ error: 'Not a Database Change issue' }, { status: 400 });
   }

   const issueKey = body.issue.key;
   ...
   let bytebaseIssueLink = body.issue.fields.customfield_10039;

   f (body.webhookEvent === "jira:issue_created" && body.issue_event_type_name === "issue_created") {
      ...

  2. If both are true, via Bytebase API, it will try to match the Jira's project key with Bytebase's project key to make sure they're the same. then it will try to match the Jira's database with the database belonging to that matching Bytebase project.

    ...
   // Find matching Bytebase project
   const matchingProject = allProjectData.projects.find((project: BytebaseProject) => project.key === projectKey);
   if (!matchingProject) {
         return Response.json({ error: 'No matching Bytebase project found' }, { status: 400 });
   }
   // Fetch databases for the matching project
   const databasesData = await fetchData(`${process.env.NEXT_PUBLIC_BB_HOST}/v1/${matchingProject.name}/databases`, token);

   // Find matching database
   const matchingDatabase = databasesData.databases.find((db: BytebaseDatabase) => db.name.split('/').pop() === database);
   if (!matchingDatabase) {
         return Response.json({ error: 'No matching Bytebase database found' }, { status: 400 });
   }

   // Create Bytebase issue
   const result = await createBBIssueWorkflow(matchingProject.name, matchingDatabase, sqlStatement, summary, description, issueKey);

  3. Only if the project and database are both matched, it will create a Bytebase issue.

    // Create Bytebase issue
const result = await createBBIssueWorkflow(
  matchingProject.name,
  matchingDatabase,
  sqlStatement,
  summary,
  description,
  issueKey,
);

    which internally involves four steps:

    const sheetData = await createSheet(project, database, SQL);

const planData = await createPlan(project, database, sheetData.name);

const issueData = await createIssue(
  project,
  database,
  planData.name,
  summary,
  description,
  jiraIssueKey,
);

const rolloutData = await createRollout(project, planData.name);

  1. If you go back to Jira, you'll see the Jira issue status becomes In Progress with Bytebase url link filled.

    jira-in-progress

  2. View the jira app demo, it's updated, too.

    wm-in-progress

The logic is still in src/api/receive-jira-issue-webhook/route.ts.

  1. Once the Bytebase issue is created via API, the demo app will parse the Bytebase issue link.

    if (result.success && result.issueLink) {
  bytebaseIssueLink = result.issueLink;
  parsedData.bytebaseIssueLink = bytebaseIssueLink;

  try {
    // Update Jira issue with Bytebase link and set status to "In Progress"
    await updateJiraIssueAfterBBIssueCreated(issueKey, bytebaseIssueLink);
  } catch (error) {
    return Response.json(
      {
        error: 'Failed to update Jira issue',
        details: error instanceof Error ? error.message : String(error),
      },
      { status: 500 },
    );
  }
}

  2. Then call the Jira API to update Bytebase issue link field and change the status from Todo to In Progress.

    ...
   await updateJiraIssueAfterBBIssueCreated(issueKey, bytebaseIssueLink);

    Here we need to call two Jira APIs:

    • /rest/api/3/issue/${issueKey} to update Bytebase Link
    • /rest/api/3/issue/${issueKey}/transitions to change the status

Step 4 (Bytebase) DBA goes to Bytebase to roll out the database change.

  1. You now act as DBA, go to Bytebase to roll out the database change.

    bb-done

  2. Once change is rolled out, Bytebase will record the change in the database Change History.

    bb-history

  3. You can also click View change to view the change diff.

    bb-diff

Step 5 (Bytebase Webhook -> Jira API) Once the Bytebase issue rolls out and becomes Done, Bytebase Webhook will trigger Jira API to set Jira issue status as Done.

  1. Once the issue has rolled out in Bytebase, the configured webhook will trigger jira app demo.

    wm-done

  2. Go to Jira, you'll see the Jira issue status becomes Done.

    jira-done

The logic is in src/app/api/receive-bb-issue-webhook/route.ts. If it's a issue update, it will parse the Jira issue key from the Bytebase issue name, and then call the Jira API to update the issue status to Done.

const payload: BytebaseWebhookPayload = await request.json();

   if (payload.activity_type === "bb.issue.status.update") {
      ...
      const jiraIssueKeyMatch = payload.issue.name.match(/\[JIRA>([^\]]+)\]/);

      ...
      if (payload.issue.status === "DONE") {
         jiraStatus = "Done";
      } ...
      }
   }

Summary

In this tutorial, you have successfully set up a automatic database change workflow with Jira and Bytebase. We eliminate most of the manual process in the last tutorial.

  1. Bytebase issue is automatically created. And the created issue link is set automatically in the Jira issue.
  2. Once Bytebase rolls out the SQL, the Jira issue status is updated automatically.

If you want to automate further, you can also call Bytebase API to approve and roll out the SQL.

Edit this page on GitHub

Subscribe to Newsletter

By subscribing, you agree with Bytebase's Terms of Service and Privacy Policy.