CODEWITHSUNDEEP
node.jsaws-lambda
svj
svj

Reputation: 101

Mysql inserts with AWS Lambda + Node.js

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!

Upvotes: 4

Views: 5552

Answers (1)

matsev
matsev

Reputation: 33797

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.

Upvotes: 4

Related Questions