Managing database connections in Node.js, best practices?

Question

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){
...
});

?

Answer 1

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.