Query Store

What Query Store does

Query Store is a platform action that runs one SQL statement against the Data Stores of your workspace, as a single step in a process.

• No setup. There is no connection string, no credential and no external database. The action works on the Data Stores that already live in your workspace.
• Reads and writes. Filter, join, group and total your data, or insert, update and delete rows in bulk.
• Point and click. Pick Data Stores and columns from a list instead of typing their names.
• Safe values. Values from your process reach the query as parameters, so they can't change what the query does.
• Guardrails. Statements that could damage your data or reach outside your workspace are refused with a clear message.

A process with a Query Store step. Click the step to open its properties; everything is set up under Configure Query.

Query Store, Data Store action or Execute Query?

Before you start

• An organisation workspace. Query Store is not available in a personal workspace. If you try to add it there, the process is refused with "not available on a personal workspace".
• At least one Data Store with some data. See Creating a Data Store.
• A process open in the Process Designer.

Step 1 — Add the action

In the Process Designer, type Query in the toolbox search. Query Store is at the top of the list. Drag it onto the canvas, or double-click it, then connect it between your steps.

Search the toolbox for "Query" and add Query Store to the canvas.

Query Store sits above the database actions. Execute Query, underneath it, is a different action: it talks to your own SQL Server.

Click the new step to open its properties panel. Give it a name that says what it does, for example Find customers in city, then click Configure Query.

Step 2 — Write the query

Configure Query opens an SQL editor with syntax highlighting.

A finished query. The highlighted chips are a Data Store and its columns; @city is a parameter.

Insert Data Stores and columns with Ctrl + I

You never type a Data Store's name. Instead:

1. Click in the editor where the table or column should go.
2. Press Ctrl + I (Cmd + I on Mac) to open the picker.
3. Type in the search box to filter, then click a Data Store to insert it as a table, or click one of its columns to insert that column.

Type SQL as usual. Press Ctrl + I, pick the Data Store, keep typing, press Ctrl + I again and pick a column.

The picker lists process variables at the top and your Data Stores, with their columns, underneath.

Each chip is linked to the Data Store's ID, not its name:

• Renaming is safe. Rename a Data Store or a column and the query keeps working; the chip shows the new name.
• Deleting shows up in red. If a Data Store or column used in the query is deleted, its chip turns red and reads Unknown. The step fails until you replace it.
• Any name works. Names with spaces or special characters work, because the name never goes into the SQL.

Two Data Stores in one query

When you join two Data Stores that have a column with the same name, give each one a short alias and put the alias in front of every column:

SELECT c.Name AS Customer, SUM(o.Amount) AS Revenue
FROM   DEMO_QS_Customers AS c
JOIN   DEMO_QS_Orders    AS o ON o.CustomerCode = c.CustomerCode
GROUP BY c.Name

(In the editor, each Data Store and column name above is a chip.)

Without the aliases the query is refused, because the database can't tell which CustomerCode you mean.

A sales report joining customers to orders, with AS c / AS o aliases.

Step 3 — Pass values in with parameters

To use a value from your process, such as a form field, an input or the output of an earlier step, write a parameter in the query and map it below the editor.

1. In the query, write @ followed by a name: WHERE City = @city.
2. Under Map the query parameter names to the values they are bound to, click + Add parameter (the first row is already there).
3. In the left field, type the parameter name and press Enter. The list suggests the names it finds in your query.
4. In the right field, either type a fixed value, or click + and pick a process variable.

The name goes on the left (confirm it with Enter). On the right, click + and pick the variable that supplies the value.

@city gets its value from the process variable City.

Why parameters matter: a parameter's value is always treated as data, never as part of the SQL. If someone types Cluj' OR '1'='1 into your form, the query looks for a city with exactly that name and finds nothing, instead of returning every row.

Parameter names: start with a letter or underscore, then letters, digits and underscores only, 64 characters at most, and each name once per query. The leading @ in the mapping is optional.

Passing a list

A list variable, for example customer codes selected in a form, arrives in the query as a JSON array. Use one of these patterns:

Step 4 — Time out and outputs

Result Rows and Total Count each write into a process variable.

There is no row limit. A SELECT with no WHERE or LIMIT returns the whole table, so add a LIMIT when you only need a few rows.

Step 5 — Test the step on its own

You can run a Query Store step without running the whole process. Open the step, click the vertical Test Action tab on the left edge of the properties panel, then click Test action.

Test Action runs only this step. Test output shows the status and the value of each output.

Step 6 — Run the process and see the results

Click Save, Validate and Run. If the process has inputs, a dialog asks for them. Enter them and click Run.

Run, fill in the input, then open the instance. The results are on the Variables tab.

The process's inputs appear in the Run dialog.

When the run finishes, click Check instance (it opens in a new tab), or open the process's instances and choose History → Show details. On the Variables tab, the query's outputs are listed under Output.

Customers holds the rows as JSON; CustomerCount holds how many there were.

What you can write

Each run takes exactly one statement. These are allowed:

UNION, subqueries and the usual MySQL functions work inside these statements.

Examples

In the editor, each Data Store and column name below is a chip inserted with Ctrl + I.

Filter with a parameter

SELECT CustomerCode, Name, City, Tier
FROM   DEMO_QS_Customers
WHERE  City = @city
ORDER BY Name

A report across two Data Stores

SELECT c.Name AS Customer, COUNT(*) AS Orders, SUM(o.Amount) AS Revenue
FROM   DEMO_QS_Customers AS c
JOIN   DEMO_QS_Orders    AS o ON o.CustomerCode = c.CustomerCode
WHERE  o.Status <> 'Cancelled'
GROUP BY c.Name
ORDER BY Revenue DESC

Update in bulk

UPDATE DEMO_QS_Orders
SET    Status = 'Shipped'
WHERE  CustomerCode = @customer AND Status = 'New'

Total Count tells you how many orders were shipped.

Add rows

INSERT INTO DEMO_QS_Customers (CustomerCode, Name, City, CreatedById, UpdatedById)
VALUES (@code, @name, @city, @userId, @userId)

Guardrails: what is refused

Query Store only ever touches the Data Stores of the workspace the process runs in. On top of that, these are refused before anything runs, each with its own message:

You always get one of these clear messages, never a raw database error.

Handling errors in the process

When a Query Store step fails, because a query was refused, a SQL error or a time-out, the process stops and the instance is marked with errors. To handle the failure yourself, add an error branch: drag a connection from the step's error port to the step that should run instead. You can store the error in a variable of type Error to use its message.

When the DROP TABLE is refused, the process follows the red Error line. The error branch records the message and the process finishes successfully.

Action errors shows the message for the failed step. The error variable holds the same message for later steps.

Combining Query Store with other features

Troubleshooting