This page provides all the relevant information required to use and customize the macro in an efficient and optimal manner. The SQL Query macro supports read-only SQL statements and displays values from the given SQL statements or scripts attached to Confluence pages.

The macro uses a profile to connect to the required database and retrieve the relevant information. Administrators configure profiles that contain sensitive information such as database and user connection details. Contact your administrator to learn about the profiles available in your instance. Read more in this article about profile configuration.

How a profile affects macro behavior:

The SQL Query macro behaves as follows depending on the chosen database and the database privileges the user (configured in the profile) holds:

Database

Points to note

PostgreSQL

MySQL

Macro creates a read-only session for the database connection established through the specified profile. Given SQL statement(s) or the script file is executed, and results are displayed in a table in the Preview panel of the macro editor.

Allows SQL statements or scripts in read-only mode only.
Error messages are displayed if Create, Insert, Update, or Delete SQL statements are given in editor or in a script attached to a page. This is because the database itself does not allow. execution of such statements as the logged in user has read-only privileges.

Microsoft SQL Server

Given SQL statement(s) or the script file is executed, and results are displayed in a table in the Preview panel of the macro editor. Once the statements are run:

the database reflects the operations performed, and,
the macro executes a rollback that reverts the database to its previous state.

Thus, Confluence administrators must make sure to provide credentials of a user with read-only privileges configured in the database.

Macro editor components

Use the macro editor to provide SQL statements or use SQL scripts attached to Confluence pages to retrieve data using the configured profile. The main components of the editor are:

Data source panel: Specify the data source and view any messages returned by the database. There are two tabs in this panel as follows:
- Data source tab: The parameters on this tab are mostly mandatory to connect to your database and run the given query/script. Select a Data source profile, and then, either enter the relevant SQL statement(s) in the SQL statement section, or enable the Select SQL as an attachment option to specify the SQL script attached to a Confluence page. Contact your administrator to know about the profiles configured in your instance.
- Message tab: This tab displays the information about successful query/script execution or any error messages. The text on this tab changes to red if any errors were encountered, or turns green if the query execution was successful.
After providing the data source, click Run to generate a table with the required data retrieved through the specified SQL statement or script. Click the cog icon () to view the Settings panel for other parameters to further define SQL query/script execution and format the generated table.
Settings panel: Specify other parameters here to further define how the macro must handle the incoming data. The parameters available here are defined in the Parameters section below.
Preview panel: See the data retrieved from the database and apply styles to the generated table here.

Parameters

The following parameters are available in the macro editor:

Macro editor tab	Macro editor label	Default value	Description	Macro parameter
Main panel	Data source profile		Select a profile from the list of pre-configured profiles. This field is mandatory. A profile contains the required information to connect to the preferred database with given user credentials. This ensures that sensitive information is secure and is not visible to all. Contact your administrator to know about the profiles available for your use. Refer to the Configuration documentation to learn more about profile configuration.	dataSource
	Select SQL script attachment	Off	Enable this option to provide an SQL script attached to the same or different space/page. If enabled, the following drop-down lists are displayed: Space: Specify the space from which the SQL script is to be accessed. This field is mandatory. Page: Specify the page in the specified space that contains the required SQL script file as an attachment. This field is mandatory. Attachment: Select the SQL script file to be executed. This field is mandatory. File encoding: Select the encoding of the SQL script file if different from the system default handling. By default, UTF-8 is selected. This parameter is optional.	useAttachment
	SQL statement		Enter the required SQL statement to be executed to retrieve the relevant information. This field is mandatory. If Select SQL as an attachment is enabled and the SQL script file details are provided, this parameter displays the file contents, but doesn't permit editing the attached file contents.
Settings > Runtime	Transaction isolation level	None Or, Default (<value specified either in Global configuration or profile configuration>)	Specify the default level to be used for transactions. The following options are available for selection: Read Committed Read Uncommitted Repeatable read Serializable None Most Database Management Systems (DBMS) offer a number of transaction isolation levels to control the degree of locking that occurs when selecting data. To know more about what you can do with each type, refer to Wikipedia: Isolation or see your database documentation. The administrator sets the default transaction isolation level in Global configuration. If a value is provided in Global configuration, the parameter displays Default and the value in round brackets as the default value for the macro. Select a value from the list to override the default. The selected database must support the selected transaction isolation level.	transactionIsolation
	Maximum number of rows to retrieve and display	250 Or, Default (<value specified either in Global configuration or profile configuration>)	Select the maximum number of rows to be processed and displayed. This setting prevents queries from using excessive resources that can result in a large number of rows. The following options are available from a selection list: 250 500 1000 2500 5000 10000 25000 The target database driver must support the remote query timeout server configuration option (Maximum number of seconds for query to run). The administrator sets the maximum number of rows to be processed in Global configuration. If a value is provided in Global configuration, the parameter displays Default and the value in round brackets as the default value for the macro. Select a value from the list to override the default.	limit
	Maximum number of seconds for query to run	120 Or, Default (<value specified either in Global configuration or profile configuration>)	Select the time in seconds that a query can take before a forced timeout. This prevents queries that take too long, from impacting other users. The following options are available from a selection list: 1 5 10 30 60 120 The administrator sets the maximum query time in Global configuration. If a value is provided in Global configuration, the parameter displays Default and the value in round brackets as the default value for the macro. Select a value from the list to override the default.	queryTimeout
	Stop encoding of HTML characters	off	If the SQL is producing HTML, this parameter needs to be enabled (On). The administrator can control access to the parameter due to security considerations. The restriction is set via global app configuration or by more fine-grained control using Macro security in SQL Configuration. The restrictions can be configured for the parameter disableAntiXss to trusted users and groups in trusted spaces. It may be necessary for queries that produce HTML constructs.	disableAntiXss
Settings > Rendering	Table ID		Enter an ID for the table to be used in chart macros, JavaScript, and custom styles.	id
	Display data filter	Off	Enable this option to view drop-down lists with column values below each column header. Either, select or enter the value to filter the specific column.	displayDataFilter
	Insert auto number column	Off	Enable this option to display numbers beside each row in a separate column that is shown as the first column of the table.	autoNumber
	Enable pagination	On	Disable this option to display the retrieved data in a continuous, single table. If enabled, this option displays a pagination panel beneath the table based on the values provided in Global configuration or in the macro's Number of rows to be displayed on one page parameter.	pagination
	Number of rows to be displayed on one page		Select the maximum number of rows to be displayed on a page. The administrator sets the maximum number of rows to be processed in Global configuration. Select a value from the list to override the default.	rowsPerPage
	Show error if there are no rows	Off	Enable this option to display an error message if the query or script does not retrieve any from the database.	noDataError
	Text to display when there are no rows		Enter the text to be displayed if the query or script does not retrieve any from the database.	noDataMessage
	Show SQL	Off	Enable this option to display the SQL statement or script used in a box below the table.	showSQL

Helpful resources

Administrator guide to know more about app configuration
Release notes to understand app features per version

SQL for Confluence

SQL Query macro - Cloud

Macro editor components

Parameters

Helpful resources