Local Database Connection

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 General Configuration > ScriptRunner > Resources.

2. Select Create Resource > Local Database Connection.

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

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

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

5. 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 spaces')

}

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 spaces using:

import com.onresolve.scriptrunner.db.DatabaseUtil

DatabaseUtil.withSql('local') { sql ->

sql.firstRow('select count(*) from spaces')[0]

}