how does createConnection work with nodeJS in mysql?

Question

What does createConnection do?

var connection = mysql.createConnection({
  host     : 'example.org',
  user     : 'bob',
  password : 'secret'
});

I'm writing an application in nodeJS using mysql module. I have some own modules, for example authentication, which definetely needs DB connection. Question is: if I have multiple modules where I use this method to create the connection, will it create a new connection for me everytime or use the first one? If creates, it creates the first time it loads my own module or everytime? Oh, and if it creates when is it going to be destroyed?

Here's how I have it in my authentication module:

var config = require('./config.js');
var mysql = require('mysql');
var connection = mysql.createConnection(config.connectionString);

exports.login = function() ...

I have some basic understanding missings about how modules and own modules work.

Thanks for the answers.

Dave · Accepted Answer · 2018-03-14T12:56:41.257

You can create a connection pool in one module and then share that pool across all your modules, calling pool.getConnection() whenever you need to. That might be better than keeping a single, shared connection open all the time.

One project I'm working on does this:

utils/database.js

var mysql = require('mysql');

var pool = mysql.createPool({
    connectionLimit: 100,
    host: 'localhost',
    user: 'xxxxx',
    password: 'yyyyy',
    database: 'zzzzz',
    debug: false
});

module.exports = pool

accounts.js

var express = require('express');
var router = express.Router();

var pool = require('./utils/database');

router.get('/', function(req, res) {

    pool.getConnection(function(err, connection) {

        // do whatever you want with your connection here

        connection.release();

    });
});

module.exports = router;

Another way I'm playing around with is like this:

utils/database.js

var mysql = require('mysql');

var pool = mysql.createPool({
    connectionLimit: 100,
    host: 'localhost',
    user: 'xxxxx',
    password: 'yyyyy',
    database: 'zzzzz',
    debug: false
});

var getConnection = function(callback) {
    pool.getConnection(function(err, connection) {
        callback(err, connection);
    });
});

module.exports = getConnection;

accounts.js

var express = require('express');
var router = express.Router();

var createConnection = require('./utils/database');

router.get('/', function(req, res) {

    createConnection(function(err, connection) {

        // do whatever you want with your connection here

        connection.release();

    });
});

module.exports = router;

The connection limit 100 means 100 concurrent connections ? Thanks — Marin, May 23 '17 at 16:58
In the first example, I don't think `pool.createConnection()` is a valid method. — A-Diddy, Mar 13 '18 at 17:22

score 6 · Answer 2 · answered Oct 20 '16 at 14:11

It will create a new connection every time you call connection.connect(). The connection is destroyed when either the program exits, or connection.end() is called. If you want to reuse the connection, you can put the connection logic in a separate module and then just export the connection itself, like this.

In a file called connection.js

var mysql = require("mysql");
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'user',
  password : 'password'
});
connection.connect();
module.exports = connection;

And then in any client file:

var connection = require("./connection.js");
connection.query('some query', callback);

Each file the requires connection.js will reuse the existing connection.

so if I put the connection itself in a separate module, whenever I use connection (even in different modules by requiring it) it wont make another instance of it, so there will be only one? — Kiss Koppány, Oct 20 '16 at 16:10
yes, correct. You can verify this yourself by logging into the mysql shell and running `SHOW STATUS WHERE variable_name = "Threads_connected";` — Larry Turtis, Oct 20 '16 at 16:30

how does createConnection work with nodeJS in mysql?

2 Answers2

Linked