NodeJs IF Statement in AWS Lambda using MySQL database

Question

I am trying to pass an IF statement (IF data exists in table, [true] update data, [false] insert data) in NodeJS for either of the following queries:

var mysql = require('mysql');
var config = require('./config.json');
var pool  = mysql.createPool({
    host     : config.dbhost,
    user     : config.dbuser,
    password : config.dbpassword,
    database : config.dbname
  });

exports.handler = (event, context, callback) => {
  context.callbackWaitsForEmptyEventLoop = false;
  pool.getConnection(function(err, connection) {

      // IF STATEMENT
      var id = ... //select statement to the same table...??
      If (id = ?) {

          // If TRUE
          connection.query('UPDATE table SET email = "'[email protected]'" WHERE id = 1;', function (error, results, fields) {
          // And done with the connection.
          connection.release();
          // Handle error after the release.
          if (error) callback(error) ;
          else callback(null, results);,
 
          // If FALSE
          connection.query('INSERT INTO table (id, name, email) VALUES (1, "'John Doe'", "'[email protected]'");', event['i'], function (error, results, fields) {
          // And done with the connection.
          connection.release();
          // Handle error after the release.
          if (error) callback(error) ;
          else callback(null, results);

      }; 
    });
  });
};

I am new to NodeJS and I am still trying to figure out the IF STATEMENT with the codes I have above. I will keep you posted.

@CHRISWILLIAMS RESULTS DETAILS FOR HIS ANSWER

Basically, I have 2 APIs.

The first API runs initially, drop canvass_prices if exists. This one I don't have any issue. Here's the result of the first API:

Response:
[
  {
    "ID": 1,
    "Item": "Earth Science Deep Conditioning Masque For Hair - 2 Fl Oz",
    "Qty": 30,
    "Container": "Bottle",
    "Size": "750ml",
    "Reiciendis eos nostrum ut sequi.": 1680,
    "Sed quidem aspernatur quisquam ut.": 19920,
    "Aut dolorem repellendus iste nisi...": 79170
  }
]

Request ID:
"e2bbe38b-8121-4cb5-aa88-ac570b066759"

Function logs:
START RequestId: e2bbe38b-8121-4cb5-aa88-ac570b066759 Version: $LATEST
END RequestId: e2bbe38b-8121-4cb5-aa88-ac570b066759
REPORT RequestId: e2bbe38b-8121-4cb5-aa88-ac570b066759  Duration: 287.67 ms Billed Duration: 300 ms Memory Size: 128 MB Max Memory Used: 74 MB

And this is the 2nd API (see my 1st update here) which I am having trouble with. This API will update the canvass_prices created by 1st API.

However, the first time I clicked the result is this:

Response:
[
  {
    "ID": 1,
    "Item": "Earth Science Deep Conditioning Masque For Hair - 2 Fl Oz",
    "Qty": 30,
    "Container": "Bottle",
    "Size": "750ml",
    "Reiciendis eos nostrum ut sequi.": 1680,
    "Sed quidem aspernatur quisquam ut.": 19920,
    "Aut dolorem repellendus iste nisi...": 79170,
    "Voluptates laudantium voluptas nam.": null,
    "Ipsum voluptatem dolorum commodi.": null
  }
]

Request ID:
"4aed4c97-8ef6-485c-93b4-ca2eaf95703d"

Function logs:
START RequestId: 4aed4c97-8ef6-485c-93b4-ca2eaf95703d Version: $LATEST
END RequestId: 4aed4c97-8ef6-485c-93b4-ca2eaf95703d
REPORT RequestId: 4aed4c97-8ef6-485c-93b4-ca2eaf95703d  Duration: 393.79 ms Billed Duration: 400 ms Memory Size: 128 MB Max Memory Used: 75 MB

The the second and subsequent clicks solves my issue, it returns the correct results and will not further return duplicate "ID".

Response:
[
  {
    "ID": 1,
    "Item": "Earth Science Deep Conditioning Masque For Hair - 2 Fl Oz",
    "Qty": 30,
    "Container": "Bottle",
    "Size": "750ml",
    "Reiciendis eos nostrum ut sequi.": 1680,
    "Sed quidem aspernatur quisquam ut.": 19920,
    "Aut dolorem repellendus iste nisi...": 79170,
    "Voluptates laudantium voluptas nam.": null,
    "Ipsum voluptatem dolorum commodi.": null
  },
  {
    "ID": 9,
    "Item": "Laci Le Beau Maximum Strength Super Dieter's Tea Cinnamon Spice - 12 Tea Bags",
    "Qty": 10,
    "Container": "Bottle",
    "Size": "750ml",
    "Reiciendis eos nostrum ut sequi.": null,
    "Sed quidem aspernatur quisquam ut.": 30,
    "Aut dolorem repellendus iste nisi...": null,
    "Voluptates laudantium voluptas nam.": 1510,
    "Ipsum voluptatem dolorum commodi.": 17910
  }
]

Request ID:
"de40d4fa-06bb-433f-81d0-d667b2e2bca6"

Function logs:
START RequestId: de40d4fa-06bb-433f-81d0-d667b2e2bca6 Version: $LATEST
END RequestId: de40d4fa-06bb-433f-81d0-d667b2e2bca6
REPORT RequestId: de40d4fa-06bb-433f-81d0-d667b2e2bca6  Duration: 70.41 ms  Billed Duration: 100 ms Memory Size: 128 MB Max Memory Used: 75 MB

I wondering why it comes on the second click.

See your quoting, you've got a signle quote query string, with a quoted email. The quoted email ends the first string. Pay attention to the syntax highlighting. Use different quote styles, like double quotes " on the outside and single quotes on the inside. Flip if requirements changes. — danblack
– danblack, Commented Jul 25, 2020 at 2:04
Also use insert ... on duplicate key update .. and you won't be subject to race conditions. — danblack
– danblack, Commented Jul 25, 2020 at 2:06
Appreciate your advise. Other than the queries, I am having trouble with my nodejs code on IF STATEMENT with variable referred from the table. — RickyBelmont
– RickyBelmont, Commented Jul 25, 2020 at 4:17
If you use insert ... on duplicate key udpate ... you don't an IF STATEMENT. It will achieve the results if the results regardless. Are your email addresses intentionally different in this question? What do you want id equal to? Is this the same ID n the query? — danblack
– danblack, Commented Jul 25, 2020 at 4:53
@danblack Yes, the email addresses are exactly different. If the record exist then it needs update for email correction. If not, then it is inserted. However, that 2 queries are working already. My main issue is this part. I don't know how to do this right. var id = ... //select statement to the same table...?? If (id == ?) { — RickyBelmont
– RickyBelmont, Commented Jul 25, 2020 at 9:19

danblack · Accepted Answer · 2020-07-27 01:43:31Z

1

Or without an if statement and a race conditions:

exports.handler = (event, context, callback) => {
  context.callbackWaitsForEmptyEventLoop = false;
  pool.getConnection(function(err, connection) {
      connection.query('INSERT INTO table (id, name, email)
        VALUES (1, "John Doe", "[email protected]")
        ON DUPLICATE KEY UPDATE table email = "[email protected]";', function (error, results, fields) {
          // And done with the connection.
          connection.release();
          // Handle error after the release.
          if (error) callback(error) ;
          else callback(null, results);
      }; 
    });
  });

This assumes id is a primary or unique key in the table.

edited Jul 27, 2020 at 1:43

answered Jul 26, 2020 at 1:32

danblack

14.9k2 gold badges27 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

RickyBelmont Over a year ago

I like your solution cleaner and direct but can you please check my solution in the update. I am having hard time figuring out where the error is coming from. Please bear I have share my exact codes in the update above.

danblack Over a year ago

This is a rather different question/problem all of a sudden. INSERT isn't a function that can be placed as a result to CONCAT, its a statement on iits own. Try asking again as a pure SQL question on dba.stackexchange.com with what you want to be inserted and some table structures. There are far too many DISTINCTs in your query and the user variable aspects look odd. What mysql version you are targetting would help too.

RickyBelmont Over a year ago

I understand. Sorry about that. But the case here can be confined on what you suggested. There are prepared statement, execute, deallocate after which but I prefer not to show to isolate the error.

RickyBelmont Over a year ago

Clarification: Referring to your event['id'] you indicated in your solution, is it required as primary reference of the ON DUPLICATE KEY UPDATE?

danblack Over a year ago

remove event[id], it was a mistake. The primary reference comes from the id in the insert part of the query.

|

Chris Williams · Accepted Answer · 2020-07-25 09:57:28Z

1

Assuming that this is specifically a NodeJS only answer then the problem is you are = rather than ==. The single equals assigns Id to the value you're trying to compare to, whereas the double equals is the notation for is equal to. There's also === which presents as is identical to (== does not care for type so 5=='5' would be true, whereas fail for identical to).

Below is the fixed code.

var mysql = require('mysql');
var config = require('./config.json');
var pool  = mysql.createPool({
    host     : config.dbhost,
    user     : config.dbuser,
    password : config.dbpassword,
    database : config.dbname
  });

exports.handler = (event, context, callback) => {
  context.callbackWaitsForEmptyEventLoop = false;
  pool.getConnection(function(err, connection) {

      // IF STATEMENT
      connection.query('SELECT id from table where id = ?', [event['id']], function (error, results, fields) {
          if (results.length > 0) {

              // If TRUE
              connection.query('UPDATE table SET email = "'[email protected]'" WHERE id = 1;', function (error, results, fields) {
              // And done with the connection.
              connection.release();
              // Handle error after the release.
              if (error) callback(error) ;
              else callback(null, results);,
     
              // If FALSE
              connection.query('INSERT INTO table (id, name, email) VALUES (1, "'John Doe'", "'[email protected]'");', event['i'], function (error, results, fields) {
              // And done with the connection.
              connection.release();
              // Handle error after the release.
              if (error) callback(error) ;
              else callback(null, results);

          }; 
        });
    });
  });

edited Jul 25, 2020 at 9:57

answered Jul 25, 2020 at 6:50

Chris Williams

35.7k4 gold badges46 silver badges79 bronze badges

11 Comments

RickyBelmont Over a year ago

In this code if (id == ?) { how can I assign an event parameter just like what we did in the query. This ? is a variable that I assign as parameter.

Chris Williams Over a year ago

You would just put the variable name, so for example if this came from event['id'] you would have id == event['id']

RickyBelmont Over a year ago

What! that's how easy it is? Let me get back to you. I will try now.

Chris Williams Over a year ago

This assumes that id is a specific value you've post processed from your query

Chris Williams Over a year ago

Have updated the code, I think this does exactly what you're looking for

|

Collectives™ on Stack Overflow

NodeJs IF Statement in AWS Lambda using MySQL database

2 Answers 2

7 Comments

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related