Managing database connections in Node.js, best practices?

10
votes

I'm building an Node application which will query simple and more complex (multiple joins) queries. I'm looking for suggestions on how I should manage the mySQL connections.

I have the following elements:

  • server.js : Express
  • router1.js (fictive name) : Express Router middleware
  • router2.js (fictive name) : Express Router middleware

    //this is router1

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

    connection.connect(function(Err){...});

      connection.query('SELECT* FROM table WHERE id = "blah"', function(err,results,fields){
        console.log(results);
      });
      ...
    connection.end();
    })

Should I connect to mysql everytime '/router1/' is requested, like in this example, or it's better to leave one connection open one at start up? As: 

connection.connect();
outside of: 
router.get('/',function(req,res){
...
});
?
3
mysqlnode.jsdatabase
I think the best way instead of leaving open connections, use connection pools so that connections can be re-used. Here's a link how to pool connections. Here's a good article with more information. - kimobrian254
When a connection is first opened, a connection pool is created based on the connection string. After we close a connection in, the connect returns into the pool. When we open another connection with the same connection string, the pooler looks to see if there is an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. It’s recommended to close the connections or open the connections inside of a using block. In this way, the connections will be returned to the pool for future reuse - kimobrian254

3 Answers

5
votes

I am using mysql2 for this, it is basicly mysql but with promises. If you use mysql you can also do this.

Create a seperate file called connection.js or something.

const mysql = require('mysql2');

const connection = mysql.createPool({
    host: "localhost",
    user: "",
    password: "",
    database: ""
    // here you can set connection limits and so on
});

module.exports = connection;

Then it is probaly better you create some models and call these from within your controllers, within your router.get('/', (req, res) => {here});

A model would look like this:

const connection = require('../util/connection');

async function getAll() {
    const sql = "SELECT * FROM tableName";
    const [rows] = await connection.promise().query(sql);
    return rows;
} 
exports.getAll = getAll;

You can do this with or without promises, it doesn't matter. Your connection to the pool is automatically released when the query is finished. Then you should call getAll from your router or app.

I hope this helped, sorry if not.

2
votes

Connection pooling is how it should be done. Opening a new connection for every request slows down the application and it can sooner or later become a bottleneck, as node does not automatically closes the connections unlike PHP. Thus connection pool ensures that a fixed number of connections are always available and it handles the closing of unnecessary connections as and when required.

This is how I start my express app using Sequelize. For Mongoose, it is more or less simlar except the library API.

const sequelize = new Sequelize('database', 'username', 'password', {
    host: 'localhost',
    dialect: 'mysql',
    pool: {
        max: 5,
        min: 0,
        acquire: 30000,
        idle: 10000
    }
});

sequelize.authenticate()
    .then(
        // On successfull connection, open a port
        // and listen to requests. This is where the application 
        // starts listening to requests.
        () => {
            const server = http.createServer(app);
            server.listen(port);
        },
    )
    .catch(err => {
        console.error('Unable to connect to the database:', err);
        console.error('Cancelling app server launch');
    });

The app is started only after a database connection has been established. This ensures that the server won't be active without any database connection. Connection pool will keep the connections open by default, and use a connection out of the pool for all queries.

-6
votes
> npm install mysql

mysql is a great module which makes working with MySQL very easy and it provides all the capabilities you might need.

Once you have mysql installed, all you have to do to connect to your database is

var mysql = require('mysql')

var conn = mysql.createConnection({
  host: 'localhost',
  user: 'username',
  password: 'password',
  database: 'database'
})

conn.connect(function(err) {
  if (err) throw err
  console.log('connected')
})

Now you are ready to begin writing and reading from your database.