Building a database connector

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:

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

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:

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

Select your required database type from the dropdown field:

Complete other settings as normal and save your changes. As soon as you save this page, the database connector setup wizard is launched.

For more information on the basic details page please see our general Connector Builder section.

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:

For more information on the variables page please see our general Connector Builder section.

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 name

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

Details for this authentication method are available on our DB user pass page. For more information on the authentication page please see our general Connector Builder section.

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.

Add a name for this query and choose the type of entity that you're querying:

The name entered here is displayed to users when they configure a connector shape to use this connector and are looking for a query to apply. Click the create button to confirm and access query configuration options.

For general information about these settings, please see our main Connector Builder section - the same principles apply to both endpoints and queries.

General details are added in the top panel, with query configuration in the tabs below:

In the top panel, set the direction of this query as appropriate - i.e. does it modify data (send) or retrieve data (receive)?

For general information about these settings, please see our main Connector Builder section - the same principles apply to both endpoints and queries.

Move down to the authentication tab and toggle the DB user pass authentication method on for this query:

For general information about these settings, please see our main Connector Builder section - the same principles apply to both endpoints and queries.

If you want to add variables for inclusion in this query (so values can be provided at runtime and injected into queries), select the query variables tab and add the required variables:

For examples of how variables might be used, see Working with database queries. For general information about these settings, please see our main Connector Builder section - the same principles apply to both endpoints and queries.

Select the query tab to add your database query - for example:

This is where you define your query using the required syntax for your database (in this case, it's MySQL). Syntax highlighting is included (verification is not currently available - any issues with queries will be reported at runtime). Standard syntax is used for the database type however, there are some Patchworks conventions to consider - please see Working with database queries for more information.

If your query includes parameters for pagination (LIMIT and OFFSET), select the pagination tab to define the required values to be passed in - for example:

Limit offset is the most commonly used pagination method for databases. For general information on this topic please see our main Connector Builder section - the same principles apply to both endpoints and queries.