# Configuring a database connection ## Introduction To configure a database connection, add a connector shape to your process flow [in the normal way](/product-documentation/process-flows/building-process-flows/process-flow-shapes/standard-shapes/connector-shape.md) and select the required `source instance` and `source query` for an existing [database connector](/product-documentation/developer-hub/connector-builder/building-your-own-connector/building-a-database-connector.md): ![](/files/y5CxSfCl4Njl2T2HnBIu) Having selected a `source instance`, you'll know if you're working with a database connector because the subsequent field requires you to choose a `source query`rather than a `source endpoint`. Generally, database connector settings work on the same principles as 'normal' connectors but there are differences, depending on whether you're using a query that [receives](#receive-queries) or [sends](#send-queries) data. ## Receive queries When a connector shape is configured with a `receive` type query (i.e. you're receiving data from a database), you'll see settings sections for `variables`, `error handling`, and `response handling`:
These options are summarised [below](#receive-options). ### Receive options | Section | Setting | Summary | | ----------------- | -------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Variables | Custom | Any query variables defined for the selected query are displayed here. Variables may be mandatory (denoted with an asterisk) so a value must be present at runtime, or optional. | | Error handling | `Retries` | Sets the number of retries that will be attempted if a connection can't be made. You can define a value between `0` and `2`. The default setting is `1`. | | Response handling | `Save response in payload` |

Set this option to on to save the response from the completed operation IN the payload for subsequent processing.

This option provides the ability to access the response body via payload parameters.

By default, the response is saved in a field named response however, when the save response in payload option is toggled on, you can specify your preferred field name.

| ### Response data When a process flow runs using a `receive` type query, received data is returned in a payload. You can view this data in logs - for example:
{% hint style="info" %} The number of payloads received is determined by [pagination options](/product-documentation/developer-hub/connector-builder/building-your-own-connector/building-a-database-connector.md#queries) defined in the query setup, and whether these options are referenced in the associated query. {% endhint %} ## Send queries When a connector shape is configured with a `send` type query (i.e. you're sending data to a database), you'll see settings sections for `variables`, `database`, `error handling`, and `response handling`:
These options are summarised [below](#send-options). ### Send options | Section | Setting | Summary | | ----------------- | -------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Variables | Custom | Any query variables defined for the selected query are displayed here. Variables may be mandatory (denoted with an asterisk) so a value must be present at runtime, or optional. | | `Database` | `Override query` |

You can enter your own database query here which will be run instead of the source query already selected.

Before using an override query you should ensure that:

| | | `Items path` |

If your incoming payload contains a single array of items, enter the field name here. In doing so, the process flow loops through each item in the array and performs multiple inserts as one operation.

For more information please see About item paths.

| | Error handling | `Retries` | Sets the number of retries that will be attempted if a connection can't be made. You can define a value between `0` and `2`. The default setting is `1`. | | Response handling | `Save response as payload` | Set this option to `on` to save the response from the completed operation as a payload for subsequent processing. | | | `Save response in payload` |

Set this option to on to save the response from the completed operation IN the payload for subsequent processing.

This option saves the response from your database, together with the payload that was sent in.

By default, the response is saved in a field named response however, when the save response in payload option is toggled on, you can specify your preferred field name.

| ### About item paths The `items path` field is used to run the associated query for multiple items (i.e. database rows) in a single operation, by looping through all items in a given array:
Here, specify the field name in your payload associated with the array to be targeted.
Example (simple array) In the example below, our payload includes one `products` field with items in an array: ```json { "products": [ { "id": 4001, "sku": "BAG-001", "colour": "Blue", "category": "Bags", "quantity": 11 }, { "id": 4002, "sku": "BAG-002", "colour": "Green", "category": "Bags", "quantity": 11 }, { "id": 4003, "sku": "BAG-003", "colour": "Pink", "category": "Bags", "quantity": 12 }, { "id": 4004, "sku": "BAG-004", "colour": "Red", "category": "Bags", "quantity": 10 } ] } ``` To send all these items into our database, the `items path` should be set to: `products`
Example (nested arrays) In the example below, our payload includes a top-level `products` array with nested `accessories` and `footwear` arrays: ```json { "products": [ { "accessories": [ { "id": 4, "sku": "BAG-003", "colour": "Green", "category": "Bags", "quantity": 11 }, { "id": 5, "sku": "BAG-004", "colour": "Pink", "category": "Bags", "quantity": 12 } ] }, { "footwear": [ { "id": 6, "sku": "SHOE-001", "colour": "Black", "category": "Shoes", "quantity": 20 }, { "id": 7, "sku": "SHOE-002", "colour": "White", "category": "Shoes", "quantity": 15 }, { "id": 8, "sku": "BOOT-001", "colour": "Brown", "category": "Boots", "quantity": 10 } ] } ] } ``` To send all `accessories` items into our database, the `items path` should be set to: `products.0.accessories` To send all `footwear` items into our database, the `items path` should be set to: `products.1.footwear`
The `items path` field can't be used to target individual payload items. For this, you'd need an alternative approach - for example, you might choose to `override query` and provide a more specific query to target the required item.
Example (target specific item) In the example below, our payload includes a top-level `products` array with nested `accessories` and `footwear` arrays: ```json { "products": [ { "accessories": [ { "id": 4, "sku": "BAG-003", "colour": "Green", "category": "Bags", "quantity": 11 }, { "id": 5, "sku": "BAG-004", "colour": "Pink", "category": "Bags", "quantity": 12 } ] }, { "footwear": [ { "id": 6, "sku": "SHOE-001", "colour": "Black", "category": "Shoes", "quantity": 20 }, { "id": 7, "sku": "SHOE-002", "colour": "White", "category": "Shoes", "quantity": 15 }, { "id": 8, "sku": "BOOT-001", "colour": "Brown", "category": "Boots", "quantity": 10 } ] } ] } ``` To insert the FIRST item in the `footwear` array, we'd leave the `items path` field blank and enter the following `override query`: {% code overflow="wrap" %} ```sql INSERT INTO products (id, sku, colour, category, quantity) VALUES (:products.1.footwear.0.id, :products.1.footwear.0.sku, :products.1.footwear.0.colour, :products.1.footwear.0.category, :products.1.footwear.0.quantity) ``` {% endcode %}
### Response data When a process flow runs using a `send` type query, the default behaviour is for the response from your database to be returned as the payload. You can view this in logs - for example:
{% hint style="info" %} If you want to see the data that was passed in, toggle the [save response in payload](#send-options) option to `on`. {% endhint %} ## Related pages * [Building a database connector](/product-documentation/developer-hub/connector-builder/building-your-own-connector/building-a-database-connector.md) * [Working with queries](/product-documentation/developer-hub/connector-builder/building-your-own-connector/building-a-database-connector/working-with-queries.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/process-flows/building-process-flows/process-flow-shapes/standard-shapes/connector-shape/configuring-a-database-connection.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.