nodejs mysql close and open connection

Question

I have a nodejs app that connects to mysql When I leave connection open the server close it and when I use con.destroy or con.end I cant reconnect to mysql.

What should I do and is it better to keep closing and opining new connection for each request knowing there will be a lot.

module.exports = class DataAccessLayer {
constructor() {
    this.con = mysql.createConnection({
        host: ("host.com"),
        user: ("asdfg"),
        password: ("zxcvb"),
        database: ("DB_example")
    });

    this.con.connect(function (err) {
        if (err) throw err;
        console.log("Connected to DB");
    });
}

getUserBySenderId(senderId) {
    this.con.query("SELECT sender_id,first_name,last_name,creation_date " +
        "FROM USER " +
        "WHERE sender_id = '" + senderId + "'",
        function (err, result, fields) {
            if (err) throw err;
            console.log(result);
        });
}

Lalit Dashora · Accepted Answer · 2018-05-20 07:06:24Z

4

For your problem, you can visit this document https://www.npmjs.com/package/mysql#pooling-connections.

Instead of every time making a query and again and again doing database hit to MYSQL. Also need to remember when to close connection and when to open is a quite cumbersome task. You can use Sequelize http://docs.sequelizejs.com/ (ORM). This will help you from all these pains and let you focus on your business logic instead of having technical errors.

HTH Thanks!

answered May 20, 2018 at 7:06

Lalit Dashora

4855 silver badges16 bronze badges

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

Comments

Ananya Krishnaswamy · Accepted Answer · 2018-05-20 03:42:45Z

2

Creating and tearing down a database connection on every request will degrade the response time of each request thereby impacting overall performance. You should consider using connection pool as documented at https://www.npmjs.com/package/mysql#pooling-connections. The connection pool will insulate the application from scenarios such as the server terminating an idle connection due to lack of activity.

answered May 20, 2018 at 3:42

Ananya Krishnaswamy

681 silver badge7 bronze badges

Comments

Hemant · Accepted Answer · 2020-05-17 06:03:05Z

    "use strict"
    const mysql = require("mysql");

    class DB {
      constructor() {
        this.conn = mysql.createConnection({
          host: 'localhost',enter code here
          user: 'root',
          password: 'pass',
          database: 'db_name'
        });
      }

      connect() {
        this.conn.connect(function (err) {
          if (err) {
            console.error("error connecting: " + err.stack);
            return;
          }
          console.log("connected to DBB");
        });
      }

      checkQuery(id) { 

        try {
          this.conn.query(`SELECT * FROM tablename WHERE id = ${id}`, function (err, result) {
            if (err) {
              console.log('err');
              throw err;
            } else {
              console.log(result);

            }
          });
        } catch (e) {
          console.log('Exception caught in checkQuery()', e)
        }
      }

module.exports = DB
var obj = new DB();
obj.checkQuery(2);

João Pimentel Ferreira · Accepted Answer · 2021-07-11 18:26:44Z

You should use mysql pooling, nonetheless if you want to open and close a connection for every request, you can use async

const async = require('async')
const mysql = require('mysql')

const db = mysql.createConnection(DBInfo)
const query = 'SOME MYSQL QUERY'

async.series([
  function (next) {
    db.connect(function (err) {
      if (err) {
        console.error('error connecting: ' + err.stack)
        next(Error(err))
      } else {
        console.log('Connection successfuly')
        next()
      }
    })
  },
  function (next) {
    db.query(query, function (err, results, fields) {
      if (err) {
        next(Error(err))
      } else {
        console.log(query + ' submitted with success')
        next()
      }
    })
  },
  function (next) {
    db.end(function (err) {
      if (err) {
        next(Error(err))
      } else {
        next()
      }
    })
  }
],
function (err, results) {
  if (err) {
    console.error('There was an error: ', err)
    db2.end()
  }
})

Collectives™ on Stack Overflow

nodejs mysql close and open connection

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related