# Configuring a database connection
## Introduction
To configure a database connection, add a connector shape to your process flow [in the normal way](https://doc.wearepatchworks.com/product-documentation/process-flows/building-process-flows/process-flow-shapes/standard-shapes/connector-shape) and select the required `source instance` and `source query` for an existing [database connector](https://doc.wearepatchworks.com/product-documentation/developer-hub/connector-builder/building-your-own-connector/building-a-database-connector):
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](https://doc.wearepatchworks.com/product-documentation/developer-hub/connector-builder/building-your-own-connector/building-a-database-connector#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:
target columns exist in the database
target columns are configured (in the database) to accept null values.
|
| | `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.
|
| 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](https://doc.wearepatchworks.com/product-documentation/developer-hub/connector-builder/building-your-own-connector/building-a-database-connector)
* [Working with queries](https://doc.wearepatchworks.com/product-documentation/developer-hub/connector-builder/building-your-own-connector/building-a-database-connector/working-with-queries)
Example (simple array)