Mysql inserts with AWS Lambda + Node.js

Question

I'm running nodejs function in Amazon Lambda. It is supposed to do an insert to mysql DB after a HTTP get. Everything seems to be fine -- looking at the cloudwatch logs the query is parsed correctly and if I copy paste the query to mysql console it does exactly what it is supposed to.

Essentially:

var mysql = require('mysql')
var connection = createConnection({ connection details }); 
connection.connect();

var query = connection.query('Insert into AAA select * \
  from BBB where BBB.a = ?;', [parameter],
  function(err, result) {}
 );

connection.end();

The problems is that the Lambda version simply does nothing. Query is visible and correct and the function returns cleanly but it never actually inserts anything. I have the same problem with update query as well but all the mysql selects work and return stuff so the problem is not that. The insert also works when I run it on my machine -- when I push it to lambda the problem appears.

I tried to add a separate commit statement but couldn't get it working either. I'm clearly missing something but can't figure out what. Do I need to have a transaction block for updates?

EDIT: Per Mark B's request. I think I tried to be smarter than I am by showing only part of the code. The whole logic was:

exports.handler = function(event, context, callback){     
    if ( event.A == -1 ){
      exports.updateDB(event, function(res) {
        context.succeed(res) 
      }
    }
};

exports.updateDB = function(event, callback) {

  var mysql = require('mysql')
  var connection = createConnection({ connection details }); 
  connection.connect();

  var query = connection.query( 'update products set A=? where product_id = ?;',
  [parameters],
  function(err,result){ });

  var query = connection.query( 'insert into other_table select * from products where product_id = ?;',
  [parameters],
  function(err,result){ });         

  connection.commit(function(err) {
    if(err) { 
      connection.rollback(function() {
        throw(err);
      });
    }  
  connection.end();
  });
callback({"ok":"ok"})
};

Per advice given here I made the following changes. I took the last callback away, and did put callbacks inside both connection.queries:

   var query = connection.query( 'insert into other_table select * from products where product_id = ?;',
  [parameters],
  function(err,result){ 
     callback({"ok":"ok"}) 
  });

And it seems to work. I'm guessing now that the commit -part does nothing but it doesn't seem to break it either. It probably is obvious at this point that I'm not much of a developer and even less so familiar with node.js so I truly appreciate the help I got!

Can you show your entire code?

Mark B
– Mark B

2016-09-03 13:28:12 +00:00
Commented Sep 3, 2016 at 13:28 — Mark B
– Mark B, Commented Sep 3, 2016 at 13:28

matsev · Accepted Answer · 2016-09-02 19:07:02Z

4

Please note that the query function is an asynchronous function, meaning that it will be no result available until the callback function is triggered. In your sample code, the connection is closed immediately after it was triggered, long before the callback is executed. Try changing the code so that connection is closed by in the callback function, e.g.

var query = connection.query('Insert into AAA select * \
  from BBB where BBB.a = ?;', [parameter],

  function(err, result) {

     // now it is ok to close the connection
     connection.end();

     if (err) {
        // error handling
     } 
     else {
        // do something with the result
     }
  }
 );

By the way, since you are working with Lambda, the same thing applies to the callback(), context.succeed() and context.fail() function handlers. In other words, it is likely that you would like to call them where I wrote the comments about error and result handling above.

answered Sep 2, 2016 at 19:07

matsev

34k18 gold badges127 silver badges169 bronze badges

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

3 Comments

svj Over a year ago

Thanks. I'm aware of asynchronity and I'm using callback in the structure - but I'm not sure I'm doing it correctly. But if I send a request to DB shouldn't it run even if the calling entity quits?

matsev Over a year ago

Well, you initiate the request by calling the query() function, but if the callback that is supposed to handle the result has been terminated (because the function scope in which the callback was created in has returned) then you are out of luck.

svj Over a year ago

Looking that more closely, I think it solved my problem. I had two similar queries in a row and had the callback() outside of the queries at the end of the "transaction block". Now that have the callback inside connection.query() it works. Thank you -- and you are right about the error handling which I want to add but didn't for this example.

Collectives™ on Stack Overflow

Mysql inserts with AWS Lambda + Node.js

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related