# 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](#conventions) to consider when passing in data from a Patchworks source, and some [best practice recommendations](#best-practice-recommendations). {% hint style="info" %} Currently, MySQL is supported but further database types will be added in due course. {% endhint %} ## Conventions Conventions for working with Patchworks data in queries are detailed in the sections below: * [Query variables](#query-variables) * [Payload data](#payload-data) * [Meta variables](#meta-variables) * [Flow 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](/product-documentation/developer-hub/connector-builder/building-your-own-connector/building-a-database-connector.md), and include these in your queries:
`Query variables` work in the same way as [endpoint variables](/product-documentation/developer-hub/connector-builder/building-your-own-connector/4-endpoints/endpoint-options/endpoint-variables.md) - 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: ```sql SELECT * FROM products WHERE id = 4 ``` But if we want the flexibility to vary the target `id` number, we can use a query variable: ```sql 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](/product-documentation/process-flows/building-process-flows/process-flow-shapes/standard-shapes/connector-shape/configuring-a-database-connection.md), or generated from a [script](/product-documentation/process-flows/building-process-flows/process-flow-shapes/advanced-shapes/script-shape.md). {% hint style="info" %} 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](/product-documentation/process-flows/building-process-flows/process-flow-shapes/standard-shapes/connector-shape/configuring-sftp-connections.md) when the query is used - for example: {% endhint %} If we follow through with our example: ```sql SELECT * FROM products WHERE id = {{id_to_use}} ``` ...and assume the value received at runtime is `8`, the query would resolved as below: ```sql 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 ](#variable-placement-and-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: {% code overflow="wrap" %} ```sql INSERT INTO orders (id, firstname, lastname, amount) VALUES (:id, :firstname, :surname, :amount); ``` {% endcode %} 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./files/t8h6BiTrnOmuToO9J8Fb
(id, firstname, lastname, amount)
Defines column names in the orders table to receive values./files/FR2WbxiZ3ShnzIafpUJm
VALUES (:id, :firstname, :lastname, :amount);
Defines placeholders for payload fields - these are replaced by actual values (from the payload) at runtime./files/TOTpuxMoGkxiIp9XlMu9
#### 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. {% hint style="warning" %} Data is interpreted and inserted into the query as necessary - replacements with this syntax are completely safe and escaped. {% endhint %} You can access nested payload fields using dot notation (like [standard payload variables](/product-documentation/process-flows/building-process-flows/dynamic-variables/payload-variables.md)). For example: ```sql 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: ```sql 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](/product-documentation/process-flows/building-process-flows/process-flow-shapes/advanced-shapes/script-shape.md), or via a [set variables](/product-documentation/process-flows/building-process-flows/process-flow-shapes/standard-shapes/set-variables-shape.md) shape. If we follow through with our example: ```sql SELECT * FROM products WHERE category = '[[meta.our_category]]' ``` ...and assume the value received at runtime is `bags`, the query would resolved as below: ```sql SELECT * FROM products WHERE category = 'bags' ``` {% hint style="warning" %} 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 ](#variable-placement-and-escaping)section. {% endhint %} ### Flow variables Patchworks [flow variables](/product-documentation/process-flows/building-process-flows/dynamic-variables/flow-variables.md) can be referenced in database queries using the standard flow variable syntax: `{{flow.variables.variableName}}` For example: ```sql 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](/product-documentation/process-flows/building-process-flows/dynamic-variables/flow-variables/adding-and-managing-flow-variables.md), via a [script](/product-documentation/process-flows/building-process-flows/process-flow-shapes/advanced-shapes/script-shape.md), or in a [set variables](/product-documentation/process-flows/building-process-flows/process-flow-shapes/standard-shapes/set-variables-shape.md) shape. If we follow through with our example: ```sql SELECT * FROM products WHERE quantity = {{flow.variables.our_quantity}} ``` ...and assume the value received at runtime is `0`, the query would resolved as below: ```sql SELECT * FROM products WHERE quantity = 0 ``` {% hint style="warning" %} 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 ](#variable-placement-and-escaping)section. {% endhint %} ## Variable placement & escaping Any combination of variables can be used in both `SELECT` and `WHERE` sides of a query. For example: {% code title="Example 1" %} ```sql SELECT [[meta.columns]] FROM products WHERE category = '[[meta.our_category]]' ``` {% endcode %} {% code title="Example 2" %} ```sql SELECT {{flow.variables.columns}} FROM products WHERE category = '[[meta.our_category]]' ``` {% endcode %} {% code title="Example 3" %} ```sql SELECT [[meta.columns]] FROM products WHERE category = '{{category}}' ``` {% endcode %} 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. {% hint style="danger" %} 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. {% endhint %} {% hint style="info" %} Escape character syntax may vary between database protocols but the principle is always the same. {% endhint %} ## 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. --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://doc.wearepatchworks.com/product-documentation/developer-hub/connector-builder/building-your-own-connector/building-a-database-connector/working-with-queries.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.