Introduction

Building a database connector with the Connector Builder differs slightly from building standard connectors - instead of defining API endpoints, we define database queries. As such, you'll notice some UI variations from our standard Connector Builder guidelines.

Supported database types

Currently, the following database types are supported:

• MySQL

Security

• Database connectors are authenticated with username and password over SSL (if available, otherwise with no SSL).

• We recommend ensuring that the accounts used to authenticate instances of this connector are associated with 'least privilege' permissions (so if you only need to read data, ensure that read-only permissions are applied).

• Please ensure that any Patchworks variables used in queries are escaped, where appropriate - variable values are substituted at run time as-is; they are not interpreted, sanitised, or escaped automatically. For more information please see Working with queries.

Database connector setup

Once you've chosen to add a new connector and launched the Connector Builder, a basic details form is displayed. For a database connector, you must set the system type field to database. This prompts a database type field below - for example:

When you go on to choose a database type and save this page, you'll step through a setup wizard to complete your connector setup:

This is very similar to the endpoint setup wizard, but there are some notable differences. These are detailed below:

• Settings

• Variables

• Authentication

• Queries

Settings

As noted above, to build a database connector you must choose to set the system type field to database. This prompts a database type field:

Variables

Variables are added and used as normal - i.e. any variables defined here are available when you move on to set up authentication for your connector:

Authentication

An authentication method must be defined as normal however, for a database connector the DB user pass authentication method should be used:

Having confirmed this selection, the default setup is displayed:

Values for all required authentication variables shown here must be provided when a user adds an instance of this connector. With the default (recommended) setup this means that the following values are expected:

• username

• password

• port

• host

• database

Unless you have a specific reason for changing this setup, you can simply save this tab and continue.

Queries

The queries tab is only displayed when you add a database type connector. This is where you define queries that can be used in process flows (via the connector shape):

The process of adding a query is very similar to that of adding endpoints - having chosen to add a new system query, you step through a series of pages to provide a name and a direction; define any required variables, add the query itself, and set pagination options.

If you're familiar with adding endpoints, you will recognise some of these options however, the steps below show specific setup requirements for queries.

Error handling

Any issues found with queries at runtime (e.g. syntax errors) are reported from the database (verbatim) in run logs.

Related pages

• Working with database queries

• Configuring a database connection

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.