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

• 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.

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:

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.

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'

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

Variable placement & escaping

Any combination of variables can be used in both SELECT and WHERE sides of a query. For example:

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

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

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.

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.