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:
jira
folder for this tutorialjira
demo appDatabase Change
issue filling the summary, SQL, database, and description fields, the status is Todo
.In Progress
.Done
, Jira issue status will be set to Done
.jira
demo app.Go to the jira
folder of the api-example
repository, and copy env-template.local
file as .env.local
. Replace the placeholders with yours.
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.
Go to Jira, click Settings and then System settings in the dropdown menu.
Click WebHooks on the left sidebar, and then click + Create a WebHook.
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.
Go to Bytebase, click IAM & Admin > Users & Groups on the left sidebar, and then click +Add User.
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.
In Progress
/ Done
and set its Bytebase issue linkGo to Atlassian Account >Security > API tokens to generate an API token. Copy the API Token to the .env.local
file.
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.
View the jira
app demo, you will see there’s a jira webhook received with Todo
status.
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
.
When it receive the trigger, it checks if the issue type is Database Change
. and then if the webhook event is issue_created
.
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.
Once matched, it will create a Bytebase issue.
which internally involves four steps:
In Progress
If you go back to Jira, you’ll see the Jira issue status becomes In Progress
with Bytebase url link filled.
View the jira
app demo, it’s updated, too.
The logic is still in src/api/receive-jira-issue-webhook/route.ts
.
Once the Bytebase issue is created via API, the demo app will parse the Bytebase issue link.
Then call the Jira API to update Bytebase issue link field and change the status from Todo
to In Progress
.
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 statusYou now act as DBA, go to Bytebase to roll out the database change.
Once change is rolled out, Bytebase will record the change in the database Change History.
You can also click View change to view the change diff.
Done
, Jira API will set Jira issue status as Done
.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.
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
.
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.
If you want to automate further, you can also call Bytebase API to approve and roll out the SQL.
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:
jira
folder for this tutorialjira
demo appDatabase Change
issue filling the summary, SQL, database, and description fields, the status is Todo
.In Progress
.Done
, Jira issue status will be set to Done
.jira
demo app.Go to the jira
folder of the api-example
repository, and copy env-template.local
file as .env.local
. Replace the placeholders with yours.
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.
Go to Jira, click Settings and then System settings in the dropdown menu.
Click WebHooks on the left sidebar, and then click + Create a WebHook.
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.
Go to Bytebase, click IAM & Admin > Users & Groups on the left sidebar, and then click +Add User.
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.
In Progress
/ Done
and set its Bytebase issue linkGo to Atlassian Account >Security > API tokens to generate an API token. Copy the API Token to the .env.local
file.
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.
View the jira
app demo, you will see there’s a jira webhook received with Todo
status.
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
.
When it receive the trigger, it checks if the issue type is Database Change
. and then if the webhook event is issue_created
.
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.
Once matched, it will create a Bytebase issue.
which internally involves four steps:
In Progress
If you go back to Jira, you’ll see the Jira issue status becomes In Progress
with Bytebase url link filled.
View the jira
app demo, it’s updated, too.
The logic is still in src/api/receive-jira-issue-webhook/route.ts
.
Once the Bytebase issue is created via API, the demo app will parse the Bytebase issue link.
Then call the Jira API to update Bytebase issue link field and change the status from Todo
to In Progress
.
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 statusYou now act as DBA, go to Bytebase to roll out the database change.
Once change is rolled out, Bytebase will record the change in the database Change History.
You can also click View change to view the change diff.
Done
, Jira API will set Jira issue status as Done
.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.
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
.
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.
If you want to automate further, you can also call Bytebase API to approve and roll out the SQL.