How to use ExpressJs + MySQL in correct fashion
<p><strong>PS: Question might seem long, but there are small focused question below.</strong></p>

<p>So I am among the unfortunate few who have to use MySQL with express just because it made sense. This is to be used as an API backend. I generated an express app using the generator present on their site (<a href="https://expressjs.com/en/starter/generator.html" rel="nofollow">https://expressjs.com/en/starter/generator.html</a>) and installed mysql from mysqljs (<a href="https://github.com/mysqljs/mysql" rel="nofollow">https://github.com/mysqljs/mysql</a>). </p>

<p>Now all the examples for express have MongoDb as example and they just use a wrapper. And mysqljs examples don't tell you much either. So here is my project structure with a few particular questions. </p>

<pre><code>bin - auto generated files to start the app
config
models (Maybe have a base model which can talk to db wrapper and other models can extend that)
routes
.... index.js
.... users.js etc etc (each included in the app.js to have some sort of structure)
utils
.... db.js (check code below)
app.js
package.json
</code></pre>

<p>Now this is what I have in my db.js</p>

<pre><code>var mysql = require('mysql');

var pool = mysql.createPool({
    connectionLimit : 100,
    host: 'localhost',
    user: 'root',
    password: 'notsoblank',
    database: 'somedevdb'
});

module.exports = pool;
</code></pre>

<p>And in my route files (like users.js, index.js), I am using this as:</p>

<pre><code>var express = require('express');
var router = express.Router();
var db = require('../utils/db');
/* GET groups listing. */
router.get('/', function(req, res, next) {
    db.getConnection(function(err, connection) {
        var sql = 'SELECT * from groups';
        connection.query(sql, function(err, rows, fields) {
            if (err) throw err;
            res.json(rows);
        });
        connection.release();
    });
});

module.exports = router;
</code></pre>

<p>And then in app.js, this above file will be included like this:</p>

<pre><code>var express = require('express');
var path = require('path');
var logger = require('morgan');

var app = express();

var routes = require('./routes/index');
var users = require('./routes/users');
var groups = require('./routes/groups');

app.use('/apis/groups', groups);

module.exports = app;
</code></pre>

<p>Now if I am running this app, I will have an API endpoint like <code>localhost:5000\apis\groups</code> and this can be consumed by other apps. </p>

<p>Here are my questions based on this:</p>

<ol>
<li><p>I am importing that db.js in every route file (index.js, users.js, groups.js). Does this mean that there is a db pool created every time we hit an api? </p></li>
<li><p>I am going to write a lot of plain sql for my work. Now I know that there are a lot of adapters or OEMs, but I just want to write a simple wrapper with methods like getOne or add or delete etc. What will be the best approach for that. </p></li>
<li><p>Connection.query returns a promise. If I want to do some further manipulations, what's the best way to do this. </p></li>
</ol>

<p>Apart from keeping the db credentials in db.js, anything else wrong with this approach that would potentially stop it from scaling?</p>