Local Database Connection

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 ScriptRunner → Resources → Add New Item → Local database connection.
Provide a name for the connection in Pool Name.
For local connections, we recommend the name local.
Optional, enter a query into the SQL field to test receiving information from the database.
Note: SQL can be used to run queries that are harder to achieve using the API, such as aggregate queries.
Select Preview to test out different queries.
Tip: The SQL query is not saved when you use Preview; this function is only used to test the connection.
If the preview is successful, select Add.
Note: The local connection is always read-only (except in the case of H2, where this driver does not support it).

Use your database resources inscripts

Warning: When using SQL queries in your scripts, be cautious about SQL injection vulnerabilities. Avoid using string interpolation or concatenation to insert values directly into SQL strings. Instead, use parameterized queries or prepared statements to safely include user input or variable data in your SQL queries. This practice helps prevent potential security risks associated with SQL injection attacks.

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

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

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 is returned the connection to the pool after execution.

The withSql method returns whatever the closure returns, 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]
}