Building a database connector
Last updated
Last updated
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.
Currently, the following database types are supported:
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.
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:
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:
For more information on the basic details page please see our general Connector Builder section.
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.
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.
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.
Any issues found with queries at runtime (e.g. syntax errors) are reported from the database (verbatim) in run logs.
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:
Add a name for this query and choose the type of entity that you're querying:
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)?
Move down to the authentication tab and toggle the DB user pass authentication method on for this query:
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:
Select the query tab to add your database query - for example:
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:
When prompted, choose the DB user pass authentication method, then enter your credentials and database details:
Complete other settings and save your changes. As soon as you save this page, the database connector setup wizard is launched.
The name entered here is displayed to users when they configure a 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 - the same principles apply to both endpoints and queries.
For general information about these settings, please see our main - the same principles apply to both endpoints and queries.
For general information about these settings, please see our main - the same principles apply to both endpoints and queries.
For examples of how variables might be used, see . For general information about these settings, please see our main - the same principles apply to both endpoints and queries.
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 ). Standard syntax is used for the database type however, there are some Patchworks conventions to consider - please see for more information.
Limit offset is the most commonly used pagination method for databases. For general information on this topic please see our main - the same principles apply to both endpoints and queries.
Having created all your required queries, you can go on to add instances of this connector () in the :
