How to send several MySQL records to an API endpoint?

Hello,

I’ve got on one server a database with records. On the other server, I’ve got an API endpoint to receive the data.
To make things more efficient, I’d like to send multiple records at the same time - so it should be an array of JSON objects.

Somehow, I can’t figure out, how to approach this in n8n.

  1. Iterate through MySQL results
  2. Add results to an array
  3. If done with all results, send array to API
{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        50,
        300
      ]
    },
    {
      "parameters": {
        "requestMethod": "POST",
        "url": "http://localhost/api.php",
        "responseFormat": "string",
        "jsonParameters": true,
        "options": {},
        "bodyParametersJson": "{\"user_id\": 9, \"url\": \"https://test1.de\", \"column\": \"test\"}"
      },
      "name": "HTTP Request",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 1,
      "position": [
        1050,
        200
      ]
    },
    {
      "parameters": {
        "functionCode": "all_data.push(\"Test\");\n\nreturn all_data;"
      },
      "name": "Function",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        1030,
        460
      ]
    },
    {
      "parameters": {
        "batchSize": 1,
        "options": {
          "reset": "={{$node[\"SplitInBatches\"].context[\"noItemsLeft\"]}}"
        }
      },
      "name": "SplitInBatches",
      "type": "n8n-nodes-base.splitInBatches",
      "typeVersion": 1,
      "position": [
        650,
        300
      ]
    },
    {
      "parameters": {
        "conditions": {
          "boolean": [
            {
              "value1": "={{$node[\"SplitInBatches\"].context[\"noItemsLeft\"]}}",
              "value2": true
            }
          ]
        }
      },
      "name": "IF",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        850,
        300
      ]
    },
    {
      "parameters": {
        "values": {
          "string": [
            {
              "name": "all_data",
              "value": "={{$node[\"SplitInBatches\"].json}}"
            }
          ]
        },
        "options": {}
      },
      "name": "Set",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        1220,
        460
      ]
    },
    {
      "parameters": {
        "values": {
          "string": [
            {
              "name": "all_data"
            }
          ]
        },
        "options": {}
      },
      "name": "Set1",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        250,
        300
      ]
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "SELECT * FROM table"
      },
      "name": "MySQL",
      "type": "n8n-nodes-base.mySql",
      "typeVersion": 1,
      "position": [
        450,
        300
      ]
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Set1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Function": {
      "main": [
        [
          {
            "node": "Set",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "SplitInBatches": {
      "main": [
        [
          {
            "node": "IF",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "IF": {
      "main": [
        [
          {
            "node": "HTTP Request",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Function",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set": {
      "main": [
        [
          {
            "node": "IF",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set1": {
      "main": [
        [
          {
            "node": "MySQL",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "MySQL": {
      "main": [
        [
          {
            "node": "SplitInBatches",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Something along those lines - or do I have a conceptual error?

Hey @Chris7935,

You don’t need to create a loop. After your MySQL node you can use a Function node to create the array and then send it to the API. The Function node executes only one. So if you have 10 items returned by the Split in Batches node, the Function node will execute once, and pass the data to the next node.

Thank you @harshil1712 ,

It almost looks good.
In my function I do that:

const newItems = [];

for (const item of items) {
	newItems.push(item);
}

return newItems;

What’s the right expression now for using the result in the HTTP Request?

When I use this:

{{$node["Function"].json}}

it works - but only with the first record.

Hey @Chris7935!

Can you share the output returned by the Function node? Your function seems to create an array and return the items. You may want to take a look at the code snippet mentioned here: JavaScript Code Snippets | Docs

@harshil1712 thanks for your support.
It seems as if the return from the function node is correct.
Maybe I’m just using the HTTP Request in a wrong way?


Hey @Chris7935,

Is there a particular reason you’re using String as the response format? Also, do you have any documentation for the API, that I can refer to? With the current information, it is difficult to debug the issue.

@harshil1712 The response is just / only the data that is coming back from the API?
There is no documentation - I wrote it in PHP.

<?php

$json_string = json_encode($HTTP_RAW_POST_DATA);

$file_handle = fopen('my_filename.json', 'w');
fwrite($file_handle, $json_string);
fclose($file_handle);

With the current n8n flow, I see the first record is being written into the json file.
I’d like to have all the records in the json file.

From the screenshot of the HTTP Request node you shared, it looks like the node is processing all the incoming items and making 36 requests.
Your expression also looks good. Can you try using {{$json["shop_id"]}}, instead of {{$node["Function"].json["shop_id"]}}. Honestly, I don’t think this will make any difference, but it is worth a try.

Can you also try making a request to a dummy endpoint eg. webhook.site? This will help us know how many requests are made, and if something is wrong with your API instead.

I’ve just sent the data (with the shop_id only) to:
https://webhook.site/#!/f958387c-f164-44e0-a57e-296df8110d45/91cd0175-87b4-4538-8420-dee715230e69/1
It looks like, as if the HTTP Request is being sent in a loop!
I thought, that there is only one request with the whole array inside.

I’ve changed my API to open the file ‘a’ instead of ‘w’ and now all data is inside the file.
Do I trigger one or dozens of HTTP requests there?

Hey @Chris7935,

Your Function node returns 36 items. If you want to make a single request and send all the data in a single request, you will have to combine it into a single array. This can be done using the following code snippet:

 return [
  {
    json: {
      data_object: items.map(item => item.json)
    }
  }
]

In your HTTP Request node, you can than refer to data_object which will contain all the 36 items.

Thank you very much - that works now and it all seems logical now :slight_smile:

1 Like

Wonderful! Have fun!

Please mark the solution that worked for you, or if you used a different approach, do share! :slight_smile:

1 Like