Configuring a database connection

Introduction

To configure a database connection, add a connector shape to your process flow in the normal way and select the required source instance and source query for an existing 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 queryrather 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 or sends 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

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:

The number of payloads received is determined by pagination options defined in the query setup, and whether these options are referenced in the associated query.

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

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

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

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

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

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)

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:

If you want to see the data that was passed in, toggle the save response in payload option to on.

Related pages

• Building a database connector
• Working with queries