> For the complete documentation index, see [llms.txt](https://doc.wearepatchworks.com/product-documentation/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://doc.wearepatchworks.com/product-documentation/developer-hub/connector-builder/building-your-own-connector/building-a-database-connector.md).

# 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)
* [Oracle](https://docs.oracle.com/en/)

## 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:

<div align="left"><figure><img src="/files/U033Jf6a2L4SulxK1b44" alt="" width="375"><figcaption></figcaption></figure></div>

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:

<div align="left"><figure><img src="/files/LkjpUI0EGBhm7pDkVKhs" alt="" width="375"><figcaption></figcaption></figure></div>

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:

<table data-card-size="large" data-view="cards"><thead><tr><th></th><th></th><th></th><th data-type="content-ref"></th><th data-hidden data-card-cover data-type="files"></th></tr></thead><tbody><tr><td><p>As noted above, to build a database connector, you must set the <code>system type</code> field to <code>database</code>. This prompts a <code>database type</code> field below - for example:<br></p><p><img src="/files/U033Jf6a2L4SulxK1b44" alt="" data-size="original"></p></td><td></td><td></td><td></td><td><a href="/files/0wGvvu4qaT280GLQbJ2o">/files/0wGvvu4qaT280GLQbJ2o</a></td></tr><tr><td>Select your required database type from the dropdown field:</td><td><img src="/files/f8giQGKfwCMoAlZQzjV3" alt=""></td><td>Complete other settings <a href="/pages/oUsnYK3hlu3dokVNBSTb">as normal</a> and save your changes. As soon as you save this page, the database connector setup wizard is launched.</td><td></td><td><a href="/files/FR2WbxiZ3ShnzIafpUJm">/files/FR2WbxiZ3ShnzIafpUJm</a></td></tr></tbody></table>

{% 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:

<figure><img src="/files/SLEaB41SvVrp9KqAm8QV" alt=""><figcaption></figcaption></figure>

{% 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:

<figure><img src="/files/zpWdOhf1teGH2y3ikbYG" alt=""><figcaption></figcaption></figure>

Having confirmed this selection, the default setup is displayed:

<figure><img src="/files/dRtwiCwYtUs3Exy4Qzsz" alt=""><figcaption></figcaption></figure>

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)):

<figure><img src="/files/Yp1myKU4m7USZsq0IX7w" alt=""><figcaption></figcaption></figure>

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*.

<table data-card-size="large" data-view="cards"><thead><tr><th></th><th></th><th></th><th data-type="content-ref"></th><th data-hidden data-card-cover data-type="files"></th></tr></thead><tbody><tr><td>Add a <code>name</code> for this query and choose the type of <code>entity</code> that you're querying:</td><td><img src="/files/bQVgx39gJklfkNq83Ru1" alt=""></td><td><p>The <code>name</code> entered here is displayed to users when they configure a <a href="/pages/ksVeuo8Bz6ah8fn0PpJx">connector shape</a> to use this connector and are looking for a query to apply. Click the <code>create</code> button to confirm and access query configuration options.</p><p>For general information about these settings, please see our main <a href="/pages/pmthjiklPwhjMe9Godjq">Connector Builder section</a> - the same principles apply to both <em>endpoints</em> and <em>queries</em>.</p></td><td></td><td><a href="/files/0wGvvu4qaT280GLQbJ2o">/files/0wGvvu4qaT280GLQbJ2o</a></td></tr><tr><td>General details are added in the top panel, with query configuration in the tabs below:</td><td><img src="/files/8LChPkqxHhC8EWUXHFl4" alt=""></td><td></td><td></td><td><a href="/files/FR2WbxiZ3ShnzIafpUJm">/files/FR2WbxiZ3ShnzIafpUJm</a></td></tr><tr><td>In the top panel, set the direction of this query as appropriate - i.e. does it modify data (<code>send</code>) or retrieve data (<code>receive</code>)?</td><td><img src="/files/TeliIYTGupDzNlT0RsMd" alt=""></td><td>For general information about these settings, please see our main <a href="/pages/pmthjiklPwhjMe9Godjq">Connector Builder section</a> - the same principles apply to both <em>endpoints</em> and <em>queries</em>.</td><td></td><td><a href="/files/TOTpuxMoGkxiIp9XlMu9">/files/TOTpuxMoGkxiIp9XlMu9</a></td></tr><tr><td>Move down to the <code>authentication</code> tab and toggle the <code>DB user pass</code> authentication method <code>on</code> for this query:</td><td><img src="/files/0Lt43XK4BZ5ko2FqDKp2" alt=""></td><td>For general information about these settings, please see our main <a href="/pages/qln2AeXvmv2ccWPJgK2K">Connector Builder section</a> - the same principles apply to both <em>endpoints</em> and <em>queries</em>.</td><td></td><td><a href="/files/J3s1Om20NYQzzMcQm9mW">/files/J3s1Om20NYQzzMcQm9mW</a></td></tr><tr><td>If you want to add variables for inclusion in this query (so values can be provided at runtime and injected into queries), select the <code>query variables</code> tab and add the required variables:</td><td><img src="/files/FVgmH7cmWRfi4bm7q96O" alt=""></td><td>For examples of how variables might be used, see <a href="/pages/A6gV58e3s96J4ToTbaI4#using-variables">Working with database queries</a>. For general information about these settings, please see our main <a href="/pages/NkagMrYpKNNL9YbPDcsq">Connector Builder section</a> - the same principles apply to both <em>endpoints</em> and <em>queries</em>.</td><td></td><td><a href="/files/9Uhedo0QV7jsN3DX2ZpK">/files/9Uhedo0QV7jsN3DX2ZpK</a></td></tr><tr><td>Select the <code>query</code> tab to add your database query - for example:</td><td><img src="/files/ycsdoPiexK05VSDEsP8W" alt=""></td><td>This is where you define your query using the required syntax for your database (in this case, it's <em>MySQL</em>). Syntax highlighting is included (verification is not currently available - any issues with queries will be <a href="#error-handling">reported at runtime</a>).<br><br>Standard syntax is used for the database type however, there are some Patchworks conventions to consider - please see <a href="/pages/A6gV58e3s96J4ToTbaI4">Working with database queries</a> for more information.</td><td></td><td><a href="/files/nF2iCJJGfBgxOVPqfmI1">/files/nF2iCJJGfBgxOVPqfmI1</a></td></tr><tr><td>If your query includes parameters for pagination (<code>LIMIT</code> and <code>OFFSET</code>), select the <code>pagination</code> tab to define the required values to be passed in - for example:</td><td><img src="/files/PFyKQrVaRMPczyda0jNi" alt=""></td><td><code>Limit offset</code> is the most commonly used pagination method for databases. For general information on this topic please see our main <a href="/pages/t9u1O2xBdLFQHBya7sMB">Connector Builder section</a> - the same principles apply to both <em>endpoints</em> and <em>queries</em>.</td><td></td><td><a href="/files/zhacTL4ljI1umGB3ocXF">/files/zhacTL4ljI1umGB3ocXF</a></td></tr><tr><td>Having created all your required queries, you can go on to add instances of this connector (<a href="/pages/d6VPQqcgw9N6y2X6pP0y">for use in process flows</a>) in the <a href="/pages/8b0V4AbjjCtqyMhraqE5">normal way</a>:</td><td><img src="/files/mJZ2gceUe0nYMQd0NYno" alt=""></td><td><p>When prompted, choose the <code>DB user pass</code> authentication method, then enter your credentials and database details:</p><p><img src="/files/XYMVKIw48c4wcDuaNSpA" alt="" data-size="original"></p></td><td></td><td><a href="/files/9oihhwazivV18jQg69w0">/files/9oihhwazivV18jQg69w0</a></td></tr></tbody></table>

## 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)