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.

Currently, MySQL is supported but further database types will be added in due course.

Conventions

Conventions for working with Patchworks data in queries are detailed in the sections below:

• Query variables

• Payload data

• Meta variables

• Flow variables

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:

SELECT * FROM products WHERE id = 4

But if we want the flexibility to vary the target id number, we can use a query variable:

SELECT * FROM products WHERE id = {{id_to_use}}

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:

SELECT * FROM products WHERE id = {{id_to_use}}

...and assume the value received at runtime is 8, the query would resolved as below:

SELECT * FROM products WHERE id = 8

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:

INSERT INTO orders (id, firstname, lastname, amount) 
VALUES (:id, :firstname, :surname, :amount);

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:

INSERT INTO orders

Specifies the table (orders) into which the new row will be inserted.

(id, firstname, lastname, amount) 

Defines column names in the orders table to receive values.

VALUES (:id, :firstname, :lastname, :amount);

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:

INSERT INTO products (sku, id) VALUES (:items.0.sku, :items.0.colour)

Meta-variables

Patchworks meta-variables can be referenced in queries using the standard meta-variable syntax:

[[meta.variableName]]

For example:

SELECT * FROM products WHERE category = '[[meta.our_category]]'

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:

SELECT * FROM products WHERE category = '[[meta.our_category]]'

...and assume the value received at runtime is bags, the query would resolved as below:

SELECT * FROM products WHERE category = 'bags'

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:

SELECT * FROM products WHERE quantity = {{flow.variables.our_quantity}} 

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:

SELECT * FROM products WHERE quantity = {{flow.variables.our_quantity}}

...and assume the value received at runtime is 0, the query would resolved as below:

SELECT * FROM products WHERE quantity = 0

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:

Example 1

SELECT [[meta.columns]] FROM products WHERE category = '[[meta.our_category]]'

Example 2

SELECT {{flow.variables.columns}} FROM products WHERE category = '[[meta.our_category]]'

Example 3

SELECT [[meta.columns]] FROM products WHERE category = '{{category}}'

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.

Escape character syntax may vary between database protocols but the principle is always the same.

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.