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, follow these steps:

  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.

    local database connection simple
    SQL can be used to run queries harder to achieve using the API. For example, aggregate queries:
    local database connection 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).

Use Database Resources in Scripts

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 initialized 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.

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]
}