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.