AWS Lambda Node.js environment failed to loop insert to MySQL on first API call

Question

I'm trying to create a function in AWS Lambda (Node.js), which calls some REST API, and then inserts the API result into a MySQL database.

While the requirement is very simple, I encountered some problem when deploying to AWS Lambda (not happening on my local machine), where my first API call results in only 1 data element inserted, while from the second API call forward, all 4 data elements are inserted as intended. I tried various solutions available on Stack Overflow, and all give the same result.

Another problem is that the result is always {"message": "Internal server error"}, even though the data is inserted correctly from the second API call forwards.

Basically, I don't have much experience with Node.js, so I would appreciate if anyone could help me.

 'use strict';

const connection = require('serverless-mysql')({
    config: {
      host: 'xxxxxx.xxxxx.ap-southeast-1.rds.amazonaws.com',
      user: 'xxx',
      password: 'xxx',
      database: 'xxx_db'
    }
})
const axios = require('axios');

exports.handler = (event, context) => {

  //Get Data From API
  axios.get('https://xxx.example/wp-json/wp/v2/posts')
  .then(res => {
    const headerDate = res.headers && res.headers.date ? res.headers.date : 'no response date';
    console.log('Status Code:', res.status);
    console.log('Date in Response header:', headerDate);

    //this should result 4 data
    const posts = res.data;
      
    posts.forEach(post => {
        var sql = `INSERT INTO tbl_post(news_id, title, excerpt, content, category, image_link, modified_date, show_in_banner_F, show_in_list_F) VALUES ('${post.id}', '${post.title.rendered}', '${post.excerpt.rendered}', '${post.content.rendered}', '', '${post.yoast_head_json.og_image[0].url}', now(), 0, 0)`;  
        console.log(sql);
        let insert_query = connection.query(sql);
    });

    console.log("finished");
    connection.end();
    
    let responseBody = { message: "OK" };
    let response = {
        statusCode: 200,
        headers: {
            "Access-Control-Allow-Headers" : "Content-Type",
            "Access-Control-Allow-Origin": "*",
            "Access-Control-Allow-Methods": "OPTIONS,POST,GET"
        },
        body: JSON.stringify(responseBody)
    };
    return response;

  })
  .catch(err => {
    console.log('Error: ', err.message);
    let responseBody = { message: "Fail" };
    let response = {
        statusCode: 200,
        headers: {
            "Access-Control-Allow-Headers" : "Content-Type",
            "Access-Control-Allow-Origin": "*",
            "Access-Control-Allow-Methods": "OPTIONS,POST,GET"
        },
        body: JSON.stringify(responseBody)
    };
    return response;
  });
}

Answer 1

First of all a forEach loop will call connection.query(sql) function multiple times then exit the loop without actually waiting for each query to finish executing so you'll end up executing random number of queries each time you run this loop instead what you want to do is use async/await await connection.query(sql) in order to wait for each query inside the loop to finish executing before exiting the loop.

Also forEach loop is not designed for asynchronous code so you'll have to change that as well and use for...of instead. And you also have to use prepared statements using ? instead of inserting values with ${variable} to prevent sql injections.

for (const post of posts) {
   const sql = "INSERT INTO tbl_post(news_id, title) VALUES (?, ?)";
   const values = [post.id, post.title.rendered];
   console.log(mysql.format(sql,values)); // This would log query after values substitution 
   await connection.execute(sql, values);
 }

So the final code will look something like this:

exports.handler = async (event, context) => {
  try {
    //Get Data From API
    const res = await axios.get("https://xxx.xyz/wp-json/wp/v2/posts");
    const headerDate = res.headers && res.headers.date ? res.headers.date : "no response date";
    console.log("Status Code:", res.status);
    console.log("Date in Response header:", headerDate);

    //this should result 4 data
    const posts = res.data;
    for (const post of posts) {
      const sql = "INSERT INTO tbl_post(news_id, title) VALUES (?, ?)";
      const values = [post.id, post.title.rendered];
      console.log(mysql.format(sql, values)); // This would log query after values substitution
      await connection.execute(sql, values); // Execute prepares statement first then executes it.
    }

    console.log("finished");
    await connection.end();

    let responseBody = { message: "OK" };
    let response = {
      statusCode: 200,
      headers: {
        "Access-Control-Allow-Headers": "Content-Type",
        "Access-Control-Allow-Origin": "*",
        "Access-Control-Allow-Methods": "OPTIONS,POST,GET",
      },
      body: JSON.stringify(responseBody),
    };
    return response;
  } catch (err) {
    console.log("Error: ", err.message);
    let responseBody = { message: "Fail" };
    let response = {
      statusCode: 200,
      headers: {
        "Access-Control-Allow-Headers": "Content-Type",
        "Access-Control-Allow-Origin": "*",
        "Access-Control-Allow-Methods": "OPTIONS,POST,GET",
      },
      body: JSON.stringify(responseBody),
    };
    return response;
  }
};

As a side note consider using transactions if u want to guarantee that all queries inside the loop either succeed or fail.

Pro tip: use Promise.all() if u want to execute multiple async functions at the same time not one after the other.