Working with queries
Introduction
When writing queries for a database connector, you use standard query language for the database type. However, there are some Patchworks-specific conventions to consider when passing in data from a Patchworks source, and some best practice recommendations.
Conventions
Conventions for working with Patchworks data in queries are detailed in the sections below:
Query variables
If you want to retrieve data based on given values that vary at runtime, you can define query variables in the connector setup, and include these in your queries:
Query variables work in the same way as endpoint variables - they can be used to pass static values into queries or pass dynamic values at runtime.
To reference these variables in queries, we use the standard variable syntax for Patchworks endpoint/query variables:
{{variableName}}
For example, if we always wanted to target a row where the id column is set to 4, we might use the query below:
But if we want the flexibility to vary the target id number, we can use a query variable:
In this case, the value of our {{id_to_use}} variable would be passed into the query at runtime - perhaps specified by a user via connector shape settings, or generated from a script.
When defining a query variable for your connector, setting it to be configurable by a user means that the variable is exposed in connector shape settings when the query is used - for example:
If we follow through with our example:
...and assume the value received at runtime is 8, the query would resolved as below:
We don't interpret or sanitise query variable values - these are passed into the query as-is. As such, you'll need to consider if a variable should be escaped with quotes - see the Variable placement & escaping section below.
Payload data
It's likely that you'll likely need to reference data from an incoming Patchworks payload in queries - for example:
Here we are saying: take id, firstname, surname , and amount values from the incoming payload and insert them into id, firstname, lastname, and amount columns in the orders table. The query breaks down as follows:
Specifies the table (orders) into which the new row will be inserted.
Defines column names in the orders table to receive values.
Defines placeholders for payload fields - these are replaced by actual values (from the payload) at runtime.
Payload parameter syntax
As can be seen in the example above, we use colon-syntax parameters to reference payload data fields:
:placeholder
At runtime, parameter placeholders are replaced with corresponding values from the incoming payload.
Data is interpreted and inserted into the query as necessary - replacements with this syntax are completely safe and escaped.
You can access nested payload fields using dot notation (like standard payload variables). For example:
Meta-variables
Patchworks meta-variables can be referenced in queries using the standard meta-variable syntax:
[[meta.variableName]]
For example:
In this example, we select all columns (*) in our products table but only for rows where the category column is set to a value that will be passed in via a meta-variable [[meta.our_category]] at runtime.
Meta-variable values can be set in a script, or via a set variables shape. If we follow through with our example:
...and assume the value received at runtime is bags, the query would resolved as below:
We don't interpret or sanitise query variable values - these are passed into the query as-is. As such, you'll need to consider if a variable should be escaped with quotes - see the Variable placement & escaping section.
Flow variables
Patchworks flow variables can be referenced in database queries using the standard flow variable syntax:
{{flow.variables.variableName}}
For example:
In this example, we select all columns (*) in our products table but only for rows where the quantity column is set to a value that will be passed in via a flow variable {{flow.variables.our_quantity}} at runtime.
Flow variable values can be set in process flow settings, via a script, or in a set variables shape. If we follow through with our example:
...and assume the value received at runtime is 0, the query would resolved as below:
We don't interpret or sanitise query variable values - these are passed into the query as-is. As such, you'll need to consider if a variable should be escaped with quotes - see the Variable placement & escaping section.
Variable placement & escaping
Any combination of variables can be used in both SELECT and WHERE sides of a query. For example:
On the SELECT side, we don't need to worry about using escape quotes for string/varchar columns because we're always referring to column names.
On the WHERE side, you should ensure that escape quotes are specified for variables where appropriate. Variables are not interpreted, sanitised or escaped - values are replaced as-is.
If you don’t properly escape variables being passed into queries, your database may interpret unescaped user input as part of the query itself, rather than treating it as plain data. As such, it's possible that a rogue actor could manipulate queries by injecting malicious code into your variables. With this in mind, always ensure you're working with known, safe data.
Best practice recommendations
Query character limit. Most database types will have a maximum character limit for queries - for example, MySQL has a limit of 1024K characters. Keep this in mind if you're inserting large amounts of data and consider batching your updates as appropriate.
Last updated