0

I'm trying to figure out what the best time to actually initialize connections for mysql in node is.

Am I supposed to create a pool of connections and then set them to some global so that all my models have access to the pool? Or am I supposed to initialize connections whenever I'm doing queries?(Seems bad).

I'm sure there's some "proper" way to do it, but I'm not really certain what the best way is.

Glem
  • 449
  • 6
  • 16

3 Answers3

1

If you are going to pool connections, then don't initialize connections right when they're needed. When not using a pool, you can just store connection information when your application is starting up, and use it when you need it:

var mysql = require('mysql');
var connection = mysql.createConnection({
  host: 'localhost',
  user: 'me',
  password: 'secret'
});

Then for single use cases:

connection.connect();
connection.query('SELECT 1 + 1 AS solution', function(err, rows, fields) {
  // we are done with the connection
  connection.end();

  if (err) throw err;
  console.log('The solution is: ', rows[0].solution);
});

If you're pooling, you should just create a connection pool when your application is starting and fetch connections when you need them. You should not make more than one pool.

var mysql = require('mysql');
var pool  = mysql.createPool({
  host: 'example.org',
  user: 'bob',
  password: 'secret'
});

Then when you need a connection, you'd do something like this:

pool.getConnection(function(err, connection) {
  connection.query( 'SELECT something FROM sometable', function(err, rows) {
    // we are done using the connection, return it to the pool
    connection.release();

    // the connection is in the pool, don't use it here
  });
});
hexacyanide
  • 88,222
  • 31
  • 159
  • 162
0

After more research, think I've figured out the right way.

1) Create a connection pool on app start

2) Include that file in your models.

3) Get a connection from the pool.

Glem
  • 449
  • 6
  • 16
0

In the interest of keeping your code cleaner, I think you can also just invoke the pool object directly, according to the manual at https://github.com/felixge/node-mysql. This should abstract the logic for getting and releasing connections from the pool.

EG:

var result = yield pool.query("SELECT * FROM users");

(I'm using co-mysql with support for generators, but syntactically it should be the same w/out the callbacks)

wrentech
  • 376
  • 2
  • 3