Nodejs 12 Callback not working for mysql connection?

Question

I'm writing this code to run in AWS Lambda.

Looking at mysql connection documentation

I expect, if things are working with out errors, to get the message "Database connected successfully!" and then to get a message "connected as id " but that is not the order that it is happening. Here is my code.

'use strict';

let mysql = require('mysql');

const connection = mysql.createConnection({
  dateStrings: true,
  host     : process.env.rds_host,
  user     : process.env.rds_user,
  password : process.env.rds_password,
  database : process.env.rds_database,
  port     : process.env.rds_port
});

exports.handler = (event, context, callback) => {
  //prevent timeout from waiting event loop
  context.callbackWaitsForEmptyEventLoop = false;

  let sql = 'SELECT * FROM company ORDER BY points DESC, name ASC';
  let data = null;
  console.log('\nGetCompanies SQL: ', sql);
  let responseBody = "";
  let statusCode   = 0;

  connection.connect(function(err) {
  if (err) {
      statusCode = 500;
      responseBody = err;
    }
    else{
      console.log("Database connected successfully!");
      statusCode = 200;
      responseBody = "Database connected successfully!";
    }
  });
  console.log('connected as id ' + connection.threadId);

  connection.query(sql, data, function(queryError, results) {
    if(queryError) {
      console.error(queryError.message);
      callback(queryError);
    }
    else
    {
      console.log('\nGetCompanies Results: ', results[0]);
      callback(null, results);
    }
  });
};

Here is the logged output:

INFO
GetCompanies SQL: SELECT * FROM company ORDER BY points DESC, name ASC 2020-01-01T11:52:57.813Z

INFO connected as id null 2020-01-01T11:52:57.952Z

INFO Database connected successfully! 2020-01-01T11:52:57.974Z

My thought was that the function that I supply to:

connection.connect(function(err) {

would execute before any code after connection.connect. Am I wrong to think that?

One more question: Why is the

connected as id null? I got that code (connection.threadId) straight from the mysql docs.

I know this is no big deal if all it effects is the order of log messages but I have other functions where I can't make queries because the connection isn't there yet.

It's quite confusing when it blows right past the

if (err) {
  statusCode = 500;
  responseBody = err;
}
else{
  <do some connection.query code here>
}

I put these log messages here because this method works and my other method doesn't.

T. Short · Accepted Answer · 2020-01-01 14:05:30Z

You are getting that behaviour because console.log('connected as id ' + connection.threadId); is not waiting for connection.connect to finish connecting.

You need to use promises here.

You could try to use async/await.

exports.handler = async (event, context, callback) => {
    //prevent timeout from waiting event loop
    context.callbackWaitsForEmptyEventLoop = false;

    let sql = 'SELECT * FROM company ORDER BY points DESC, name ASC';
    let data = null;
    console.log('\nGetCompanies SQL: ', sql);
    let responseBody = "";
    let statusCode   = 0;

    try {
        await connection.connect();

        console.log("Database connected successfully!");
        console.log('connected as id ' + connection.threadId);

        const queryResult = await connection.query(sql, data);

        // do something with queryResult
    } catch (e) {
        // handle error
    }
};

EDIT

The resource you linked to suggests to use promises over callbacks:

await any promise instead of using callbacks

Arun Kamalanathan · Accepted Answer · 2020-01-02 12:30:57Z

would execute before any code after connection.connect. Am I wrong to think that?

The function you are passing to the connection.connect is called a callback function. what that mean is, it will be called only after a successful connection(or after the connection attempt is errored) is made. So the answer is no. The connection.connect is called and then immediately it will call the next statement. It will not wait for the current statement, because javascript is event driven and non blocking.

connected as id null? I got that code (connection.threadId) straight from the mysql docs.

This is because of the previous statement, you are logging the connectionId before a connection is obtained.

using Callbacks

Your code should be like the below. I haven't modified much, the connection to the database is made only after the callback of the connect event is called.

exports.handler = (event, context, callback) => {
  //prevent timeout from waiting event loop
  context.callbackWaitsForEmptyEventLoop = false;

  let sql = 'SELECT * FROM company ORDER BY points DESC, name ASC';
  let data = null;
  console.log('\nGetCompanies SQL: ', sql);
  let responseBody = "";
  let statusCode = 0;

  connection.connect(function (err) {

    // we are inside the callback function a successful connection has been obtained , or error connecting to database
    if (err) {
      statusCode = 500;
      responseBody = err;
    }
    else {
      console.log("Database connected successfully!");
      statusCode = 200;
      responseBody = "Database connected successfully!";
    }

    // connection exists
    console.log('connected as id ' + connection.threadId);

    connection.query(sql, data, function (queryError, results) {
      if (queryError) {
        console.error(queryError.message);
        callback(queryError);
      }
      else {
        console.log('\nGetCompanies Results: ', results[0]);
        callback(null, results);
      }
    });
  });
};

using promises

If you could use promises to make your code readable and understandable.

import { promisify } from 'util';
exports.handler = async (event, context) => {
  //prevent timeout from waiting event loop
  context.callbackWaitsForEmptyEventLoop = false;

  let sql = 'SELECT * FROM company ORDER BY points DESC, name ASC';
  let data = null;
  console.log('\nGetCompanies SQL: ', sql);

  const connect = promisify(connection.connect);
  const query = promisify(connection.query);

  try {
    const connection = await connect();

    // connection exists
    console.log('connected as id ' + connection.threadId);
    const results= await query(sql);

    return {
      statusCode: 200,
      responseBody: JSON.stringify(results)
    }

  } catch (err) {
    return {
      statusCode: 500,
      responseBody: err.message
    }
  }
};

Ok got it. :-) Thanks for the quick reply. So everything I want to do after the connection needs to be in the connections callback. or handled with a promise.
@arun-k It's customary if you use part of someone else's answer to quote them at least :)
@KingAndrew correct. Nothing wrong in using callbacks. It's just that async/await is clean. Note the usage of util.promisify . It's handy because its used to convert a callback to a promise
@T.Short sorry you feel it that way. But that's not true. I think you are talking about the promises part. If you look at carefully, I am using typescript for imports and also using promisify. If you mean that I used your answer, that's not true because, you are awaiting on connection.connect. You cannot await on a statement with callback.
@ArunK do I need an await in front of query(sql)? If not why?

choban · Accepted Answer · 2020-01-01 14:09:15Z

You should move your connection.query statement into the connection.connect scope as even if you can not connect to database you will try to make a query which is not working properly.

exports.handler = (event, context, callback) => {
  //prevent timeout from waiting event loop
  context.callbackWaitsForEmptyEventLoop = false;

  let sql = 'SELECT * FROM company ORDER BY points DESC, name ASC';
  let data = null;
  console.log('\nGetCompanies SQL: ', sql);
  let responseBody = "";
  let statusCode   = 0;

  connection.connect(function(err) {
  if (err) {
      statusCode = 500;
      responseBody = err;
    }
    else{
      console.log("Database connected successfully!");
      statusCode = 200;
      responseBody = "Database connected successfully!";
      connection.query(sql, data, function(queryError, results) {
        if(queryError) {
          console.error(queryError.message);
          callback(queryError);
        }
        else
        {
          console.log('\nGetCompanies Results: ', results[0]);
          callback(null, results);
        }
      });
    }
  });
  console.log('connected as id ' + connection.threadId);

};

Collectives™ on Stack Overflow

Nodejs 12 Callback not working for mysql connection?

3 Answers 3

Comments

using Callbacks

using promises

6 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

using Callbacks

using promises

6 Comments

Comments

Linked

Related