# 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](/product-documentation/developer-hub/connector-builder/building-your-own-connector.md). ## Supported database types Currently, the following database types are supported: * [MySQL](https://dev.mysql.com/) * [PostgreSQL](https://www.postgresql.org/) * [Microsoft SQL](https://learn.microsoft.com/en-us/sql/t-sql/queries/queries?view=sql-server-ver16) ## 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](/product-documentation/developer-hub/connector-builder/building-your-own-connector/building-a-database-connector/working-with-queries.md#variable-placement-and-escaping). ## Database connector setup Once you've chosen to [add a new connector and launched the Connector Builder](/product-documentation/developer-hub/connector-builder/accessing-the-connector-builder.md), 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](/product-documentation/developer-hub/connector-builder/building-your-own-connector/4-endpoints/adding-a-new-endpoint.md), but there are some notable differences. These are detailed below: * [Settings](#basic-settings) * [Variables](#variables) * [Authentication](#authentication) * [Queries](#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:

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:

/files/t8h6BiTrnOmuToO9J8Fb
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./files/FR2WbxiZ3ShnzIafpUJm
{% hint style="info" %} For more information on the *basic details* page please see our [general Connector Builder section](/product-documentation/developer-hub/connector-builder/building-your-own-connector/1-basic-details.md). {% endhint %} ### Variables Variables are added and used [as normal](/product-documentation/developer-hub/connector-builder/building-your-own-connector/2-connector-variables.md) - i.e. any variables defined here are available when you move on to set up [authentication](#authentication) for your connector:
{% hint style="info" %} For more information on the *variables* page please see our [general Connector Builder section](/product-documentation/developer-hub/connector-builder/building-your-own-connector/2-connector-variables.md). {% endhint %} ### Authentication An authentication method must be defined [as normal](/product-documentation/developer-hub/connector-builder/building-your-own-connector/3-authentication-methods.md) however, for a database connector the [DB user pass](/product-documentation/developer-hub/connector-builder/building-your-own-connector/3-authentication-methods/supported-authentication-types/db-user-pass-authentication.md) 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. {% hint style="info" %} Details for this authentication method are available on our [DB user pass](/product-documentation/developer-hub/connector-builder/building-your-own-connector/3-authentication-methods/supported-authentication-types/db-user-pass-authentication.md) page. For more information on the *authentication* page please see our [general Connector Builder section](/product-documentation/release-information/core-release-notes/2023-12-21-release-notes-core.md). {% endhint %} ### 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](/product-documentation/process-flows/building-process-flows/process-flow-shapes/standard-shapes/connector-shape.md)):
The process of adding a *query* is very similar to that of adding [endpoints](/product-documentation/developer-hub/connector-builder/building-your-own-connector/4-endpoints.md) - 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.

/files/0wGvvu4qaT280GLQbJ2o
General details are added in the top panel, with query configuration in the tabs below:/files/FR2WbxiZ3ShnzIafpUJm
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. /files/TOTpuxMoGkxiIp9XlMu9
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. /files/J3s1Om20NYQzzMcQm9mW
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. /files/9Uhedo0QV7jsN3DX2ZpK
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.		/files/nF2iCJJGfBgxOVPqfmI1
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. /files/zhacTL4ljI1umGB3ocXF
Having created all your required queries, you can go on to add instances of this connector (for use in process flows) in the normal way:

When prompted, choose the DB user pass authentication method, then enter your credentials and database details:

/files/9oihhwazivV18jQg69w0
## Error handling Any issues found with queries at runtime (e.g. syntax errors) are reported from the database (verbatim) in [run logs](/product-documentation/process-flows/error-reporting-and-exception-handling/run-logs-and-queue.md). ## Related pages * [Working with database queries](/product-documentation/developer-hub/connector-builder/building-your-own-connector/building-a-database-connector/working-with-queries.md) * [Configuring a database connection](/product-documentation/process-flows/building-process-flows/process-flow-shapes/standard-shapes/connector-shape/configuring-a-database-connection.md) --- # 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.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.