Local Database Connection

Local database connections connect to the current database your Atlassian application is using.

Tip: 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:

Navigate to General → Configuration → ScriptRunner → Resources.
Select Create Resource → Local Database Connection.
Provide a name for the connection in Pool Name.
For local connections, we recommend the name local.
Optionally, enter a query into the SQL field to test receiving information from the database. Use Preview to test out different queries.
Note: This SQL query is not saved and is used only for testing the connection.
SQL can be used to run queries that are harder to achieve using the API. For example, aggregate queries.
If the preview is successful, click Add.
Note: 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 spaces') 
}

DatabaseUtil.withSql takes two arguments:

The name of the connection as defined by you in the Pool Name parameter when adding the connection (in this example, local),
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 returns 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 spaces using:

import com.onresolve.scriptrunner.db.DatabaseUtil 
DatabaseUtil.withSql('local') { sql -> 
 sql.firstRow('select count(*) from spaces')[0] 
}