The Resources feature allows you to add connections to databases for use in scripts, and other places, for example:

ScriptRunner manages a connection pool, allowing database connections to be reused when future requests are required. A connection pool eliminates the need to close a connection after each use or specify connection details, such as passwords, in scripts. Instead of entering specific connection information, you can refer to the pool name entered when configuring the connection.

There are two types of database connection:

The Resources page lists all previously configured database connections.

External Database Connection

To set up a connection to an external database, you need to know the following information for the database:

  • The JDBC URL.

  • Any required credential information (username and password).

  • The driver class.

Your database administrator should be able to provide this information if you do not know it.

To set up an external database connection and make the database available to scripts:

  1. Navigate to ScriptRunner→Resources→Add New Item→Database Connection.

  2. Provide a name for the connection in Pool Name.

  3. Enter the JDBC URL of the database to which you wish to connect.

    For more information on sample JDBC URLs, see JDBC Driver Connection URL Strings.

  4. Provide the Driver Class Name. Click Show examples to see a list of common driver classes.

    external postgres
  5. Enter the username required to authenticate the database in User, anc the corresponding Password.

    The User and Password fields are not required if the information is provided in the URL.
  6. Optionally, enter a query into the SQL field to test receiving information from the database. Use Preview to test out different queries.

    This SQL query is not saved and is only used to test the connection.
  7. If the preview is successful, click Add.

Other Drivers

If you need to use another database driver, for example, an Excel or CSV driver, copy it into the tomcat lib directory of your installation and restart. For example this might be /opt/<application>/lib/.

You might want to use these if you wish to create a custom field that allows users to pick from a row in a spreadsheet.

In the example shown below we are using a CSV driver. This makes available all CSV in the directory provided in the JDBC URL. So in /tmp, we have a CSV file called devs.csv.

csv preview

Local Database Connection

Where possible, you should always use an application’s API to retrieve data rather than its database.

To set up a local database connection and make the local database (the one that your Atlassian application is using) available to scripts:

  1. Navigate to ScriptRunner→Resources→Add New Item→Local Database Connection.

  2. Provide a name for the connection in Pool Name. For local connections, we recommend the name local.

  3. Optionally, enter a query into the SQL field to test receiving information from the database. Use Preview to test out different queries.

    This SQL query is not saved and is only used to test the connection.
    db preview

    SQL can be used to run queries harder to achieve using the API. For example, aggregate queries:

    preview aggregate
  4. If the preview is successful, click Add.

The local connection is always read-only (except in the case of H2, where this driver does not support it).

Having set up a local connection, you can use it in a script as follows:

import com.onresolve.scriptrunner.db.DatabaseUtil

DatabaseUtil.withSql('local') { sql ->
    sql.rows('select * from project')
}

DatabaseUtil.withSql takes two arguments:

  1. The name of the connection as defined by you in the Pool Name parameter when adding the connection (in this example local),

  2. A closure. The closure receives an initialised groovy.lang.Sql object as an argument. See executing SQL for more information on executing queries. The benefit of using a closure is that it is returned the connection to the pool after execution.

    Compare with the alternative method of executing a query.

The withSql method returns whatever the closure returns, so as another example, you could get the number of projects using:

import com.onresolve.scriptrunner.db.DatabaseUtil

def nProjects = DatabaseUtil.withSql('local') { sql ->
    sql.firstRow('select count(*) from project')[0]
}

Have questions? Visit the Atlassian Community to connect, share, and learn with other Atlassian users and experts, including Adaptavist staff.

Ask a question about ScriptRunner for JIRA, Bitbucket Server, or Confluence.

Want to learn more? Check out courses on Adaptavist Learn, an online platform to onboard and train new users for Atlassian solutions.