SR database picker BLUE TRANS2
August 20, 2019

Connect Jira to external and local databases using ScriptRunner

AD
Andra Dinu 7 minute read

Resources, ScriptRunner's newest feature, lets you query data from any database and integrate the results in Jira. You can use these queries in automations and customisations created with ScriptRunner - such as displaying the results in custom fields.

Ever found yourself having to manually update various fields on Jira issues with information from another database, and then filling in the same info over and over on every ticket? If holding large amounts of data in different repositories while trying to keep everything in sync in Jira is giving you nightmares, Resources can help you sleep easy.

With Resources in ScriptRunner Server and Data Center (versions 5.5.9 onwards) you can:

  • Query and display information in Jira directly from external databases 
  • Call and display info from the current database your Atlassian application is using 

Not only can you query data from these repositories, you can also update your databases, modifying your records from your Jira tickets. 

Select an option: create a connection to an external database or one to the local database used by your Atlassian apps.

Previously, connecting to a database using ScriptRunner involved running some pretty complicated code and opening new connections for every new query. This process was laborious and time consuming.

With Resources, it’s all much faster and straightforward. ScriptRunner manages the connection pool, allowing the database connections you created in Resources to be reused in any Jira automation or customisations you create in ScriptRunner. 

The connection pool eliminates the need to close the connection after each use or to specify connection details such as passwords inside the scripts. Instead of adding connection information inside your scripts, all you need to do is refer to the pool name you entered when you configured the connection in Resources. 

Query data from an external database

You can connect your Jira instance to any external database, including CSV or Excel files. Let’s take a look at a couple of examples:

Connect an SQL product database to Jira to automate support tickets

Imagine your company sells electronic products. Your external database holds the buyer’s name, email, the product serial number, and the start and end date for the product maintenance cover.  

A customer purchases a device from you and now has a problem with it, so they raise a support ticket. You can use Resources to connect your customer database with Jira Service Desk, so the field recognises valid serial numbers and returns an error message if they fill in an invalid serial number:

report a system problem

Jira Service Desk is connected to the customer database and returns a message when invalid customer info is entered.

The support agent receives the ticket with the serial number provided by the customer, as well as other related custom fields are automatically filled in, such as the name of the product and the maintenance end date.

Custom fields in the support ticket automatically fill with product info matching the customer data in database.

Watch the video below to see a step by step guide of how to set up this automation using ScriptRunner or read our documentation to learn how to set up your own database connections and see more examples, including how to set up a connection with the local database used by your Atlassian application. 

Set up custom fields to display info from your database on your tickets 

Imagine you have a long list of leads’ names and information stored in a CSV, and you want to display the information in a Jira issue. Typing the name in a custom field can take a long time and spelling errors would easily compromise all of your hard work. 

Here’s how you can easily create a custom field that allows your users to pick from a row in a CSV file:

Custom field created with Database Picker.

To achieve this, you can use the Database Picker scripted field. It displays database records returned by a pre-configured SQL query from a connected database we configured with Resources - whether that query connects to a table or to multiple tables.

Watch the video below to follow the step-by-step guide on setting up a custom field that displays data from a database, after using Resources to create a connection to a CSV database. Or, read our documentation to find more use cases and set up your own custom field using the Database Picker.

Endless possibilities for your database integrations with Jira 

ScriptRunner the app with the largest number of installs on the Atlassian marketplace for customising, automating and integrating Jira and there’s a good reason for that: its toolset offers virtually endless options for bending your Jira instance to your will.

Now every place in ScriptRunner which has a script editor can be connected to a database. Furthermore, the Database picker scripted field makes it easy to use Resources in combination with ScriptRunner’s other features. Wherever you are, in a listener or in a workflow function, you can easily retrieve data from an external source.      

For example, by combining Behaviours with the Database picker you can easily define how fields behave for issues in a given project depending on the values returned by your external database. 

Start your free trial:

If you haven’t experienced the full power of ScriptRunner for Jira yet, get a 30-day free trial here:

How does ScriptRunner compare?

While ScriptRunner offers a lot more functionality beyond database connections, we are not the only app on the marketplace that allows you to connect to databases and display the information on your issues.

The database integration feature in ScriptRunner is meant to fit within its much broader set of features and to be used in custom scripts and other automations, so Groovy knowledge is something needed to take advantage of all possibilities. This makes it a bit more complicated to use than than dedicated, one-purpose apps. However, for the most common cases (such as displaying query results in custom fields in Jira), using the Database Picker scripted field is a straightforward affair.

 Here’s how we compare to other apps in the Marketplace:

 

ScriptRunner for Jira

Elements Connect (formerly nFeed)

Power Database Fields PRO™

Server price (500 users)

$1,250

$3,500

$1,500

Fill in custom fields in Jira with data from any SQL database

Yes

Yes

Yes

Fill custom fields in Jira with data from your local Atlassian database

Yes

Yes

No

Configure how your data is displayed with HTML

Yes

Yes 

No 

Display real-time data 

Yes

Yes

No

Preview your fields’ set-up

Yes

Yes

No

JSD support

Yes

Yes

Yes

Restrict data seen/accessed  by certain users

Yes

No

Yes

Run reports on database fields even if information is extracted from the database

Yes


No

Yes

Fill in custom fields in Jira with data from LDAPs

In the roadmap 

Yes

No

Fill in custom fields in Jira with data from REST APIs

In the roadmap 

Yes

No

Set up automations to  modify issues after a certain time has elapsed

Yes

No

No

Change the resolution of all issues returned by a query in bulk

Yes

No 

No 

Built-in features for common admin needs such as copying a project or copy custom field values

Yes

No

No

Temporarily assume the identity of a user to diagnose and solve their issues

Yes

No

No 

Execute a custom action as a result of an event in Jira.

Yes

No

No

Adds a new set of JQL functions to use in issue search and filters, and reuse them in dashboards and software boards.

Yes

No

No

Add extra web items to Jira, such as menu items and buttons.

Yes

No

No

Modify Forms, Fields and Issues to behave the way you want them

Yes

No

No

Integrate Jira with other external systems such as CRMs or payment portals.

Yes

No

No

And much more!

Yes

No

No

 

Start your free trial:

If you haven’t experienced the full power of ScriptRunner for Jira yet, get a 30-day free trial here: