Automate Database Change with Jira

Automate database changes using Jira and Bytebase.

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:

Prerequisites

• A Jira workspace
• A Bytebase workspace
• Manual Database Change with Jira completed
• Download the api-example repository, you’ll only need jira folder for this tutorial

Workflow Overview

Setup

1. Configure the environment variables and run the jira demo app
2. Setup Jira 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 deploy the database change.
5. (Bytebase Poll Comparison -> Jira API) Once the Bytebase issue rolls out and becomes Done, Jira issue status will be set to Done.

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=[e-mail]
      NEXT_PUBLIC_JIRA_API_TOKEN=xxxxxxx
      NEXT_PUBLIC_JIRA_PROJECT_KEY=KEY
      NEXT_PUBLIC_BB_PROJECT_NAME=projects/jira-api
      NEXT_PUBLIC_BB_HOST=https://xxxxxxx
      NEXT_PUBLIC_BB_SERVICE_ACCOUNT=[e-mail]
      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 achieve this.

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.

   

2. Click WebHooks on the left sidebar, and then click + Create a 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.

   

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.

   

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.

   

Jira API: To update Jira issue status to In Progress/ Done and set its Bytebase issue link

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

   

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.

   

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

   

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.

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 and Bytebase’s project name with the ones you configured in the .env.local. then it will try to match the Jira’s database with the database belonging to that Bytebase project.

     ...
      // Fetch databases for the matching project
      const databasesData = await fetchData(`${process.env.NEXT_PUBLIC_BB_HOST}/v1/${process.env.NEXT_PUBLIC_BB_PROJECT_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. Once 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, 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);
   

Step 3 (Bytebase API -> Jira API) Once the Bytebase issue is created, the Jira API will set Bytebase issue link in Jira issue, and set status as In Progress

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

   

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

   

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 deploy the database change.

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

   

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

   

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

   

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

1. Once the issue has rolled out in Bytebase, the Bytebase poll comparison will run, and if there’s any change, it will trigger jira app demo.

   

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

   

The logic is in src/app/api/poll-bytebase-issue/route.ts. Bytebase will poll the issue status every 3 seconds, 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.

      for (const issue of issues) {
            const jiraIssueKeyMatch = issue.title.match(/\[JIRA>([^\]]+)\]/);
            const jiraIssueKey = jiraIssueKeyMatch ? jiraIssueKeyMatch[1] : null;

            // Skip if no Jira issue is linked
            if (!jiraIssueKey) continue;

            // Skip if we've already processed this issue with the same status
            const lastStatus = processedIssues.get(issue.name);
            if (lastStatus === issue.status) continue;

            // Update the processed issues map
            processedIssues.set(issue.name, issue.status);

            let jiraStatus;
            if (issue.status === "DONE") {
                jiraStatus = "Done";
            } else if (issue.status === "OPEN") {
                jiraStatus = "In Progress";
            }

            if (jiraStatus) {
               try {
                    await updateJiraIssueStatus(jiraIssueKey, jiraStatus);
                    ...

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 deploy the SQL.