How to organize communication with database in Node.Js?

Question

I have a problem to design communication with MySQL database in my Nodejs's app. The biggest problem is that queries are async, so it becomes complicated to design my projects. For example, I have excercises.js

EXCERCISES.JS

var express = require('express');
var database = require('../database/database.js');

var router = express.Router();

console.log(database.db(saveDbData))
/* GET users listing. */
router.get('/', function(req, res, next) {

  res.render('exercises',{title: 'Exercises', ex: #DATABASE RESULT});
});

module.exports = router;

In need to write query's result in ex field.

Then I write a module to handle mysql connection

DATABASE.JS

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

var results;

var db = function(){
    pool.query('SELECT name FROM Exercises', function(error, results, fields){
    if (error) throw error;
    res = results;
})
    return res;
}

module.exports = {
    db: db,
}

Obviously, It doesn't work because pool.query is async. The only alternative that I've found on the web is something like this:

EXERCISES.JS


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


pool.query('SELECT name FROM Exercises', function(error, results, fields){
    if (error) throw error;
    router.get('/', function(req, res, next) {
        res.render('exercises',{title: 'Exercises', ex: result[0].name});
    }); 
})

But in this way, mysql parts and routing/render parts are mixed. Is it still a well-designed solution? Are there more elegant solutions?

EDIT:

I have modified the files and I have used Promise like this

EXERCISES.JS

var express = require('express');
var database = require('../database/database.js');

var router = express.Router();


var data = database.db()
.then(
    function(data){
        console.log("Resolved");
        /* GET users listing. */
        router.get('/', function(req, res, next) {
            res.render('exercises',{title: 'Exercises', ex: data[0].name});
        });
    })
.catch(
    error => console.error(error));



module.exports = router;

DATABASE.JS

ar mysql = require('mysql');


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

var res;
var db = function(){
    return new Promise(function(resolve, reject){
        pool.query('SELECT name FROM Exercises', function(error, results, fields){
            if (error) reject(error);
            resolve(results)

        })
    })
}


module.exports = {
    db: db,
}

And it works, but I don't think it is the best solution. For example, what if I want to get data for render from more queries? I'm new in these technologies so I'm not able to figure out the best way to integrate database and html pages's rendering.

I recomend you to use a sequelize or another ORM, it makes all the work with DB much more cleaner — l2ysho
– l2ysho, Commented Jul 8, 2019 at 19:54

slebetman · Accepted Answer · 2019-07-09 11:04:01Z

Have you ever wondered why all web frameworks in node requires you to return responses using the res object instead of just return? It's because all web frameworks expect that you need to do something asynchronous.

Consider a web framework design similar to Laravel (PHP) or Spring Framework (Java):

// Theoretical synchronous framework API:
app.get('/path', function (request) {
    return "<html><body> Hello World </body></html>";
});

Then if you need to do anything async you will face the issue that the data you're fetching hasn't returned by the time you need to return the HTTP request:

// Theoretical synchronous framework API:
app.get('/path', function (request) {
    return ??? // OH NO! I need to return now!!
});

It is for this reason that web frameworks in javascript don't act on return values. Instead it passes you a callback to call when you are done:

// Express.js
app.get('/path', function (request, response) {
    doSomethingAsync((err, result) => {
        response.send(result);
    });
});

So for your code you just need to do:

router.get('/', function(req, res) {
    pool.query('SELECT name FROM Exercises', function(error, results, fields){
        if (error) throw error;   
        res.render('exercises',{title: 'Exercises', ex: result[0].name});
    }); 
});

Exporting the database

Exporting the database is as simple as exporting pool:

db.js

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

module.exports = {
    pool: pool
}

exercises.js

let db = require('./db');

// you can now use db.pool in the rest of your code
// ..

Reusing your queries

Instead of coding SELECT statements in your controllers (routes) you can (and should) code them in your db module(s):

db.js

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

function getExerciseNames (callback) {
    pool.query('SELECT name FROM Exercises',callback);
}

module.exports = {
    pool: pool
}

Then in your controller logic you just need to do:

router.get('/', function(req, res) {
    db.getExerciseNames(function(error, results, fields){
        if (error) throw error;   
        res.render('exercises',{
            title: 'Exercises',
            ex: result[0].name
        });
    }); 
});

Caching

If your intention is to query the db only once to cache the value of Exercises then don't invert the Express routing flow. Instead implement the caching at your db layer:

db.js:

var exerciseNamesCache = [];
var exerciseNamesFields = [];

function getExerciseNames (callback) {
    if (exerciseNamesCache.length > 0 && exerciseNamesFields.length > 0) {
        callback(null, exerciseNamesCache, exerciseNamesFields);
    }
    pool.query('SELECT name FROM Exercises',function(error, results, fields){
        if (!error) {
            exerciseNamesCache = results;
            exerciseNamesFields = fields;
        }
        callback(error, results, fields);
    });
}

Promises

Promises is a design pattern for handling callbacks. It is comparable to Java's Futures (CompletionStage etc.) only a lot more lightweight. If an API you are using returns a promise instead of accepting a callback then you need to call res.render() inside the promise's .then() method:

router.get('/', function(req, res, next) {
    doSomethingAsync()
        .then(function(result){
            res.send(result);
        })
        .catch(next); // remember to pass on asynchronous errors to next()
});

If the API you're using accepts a callback then weather or not you wrap it in a promise is more a matter of taste. I personally wouldn't do it unless you are also using another API that returns a promise.

async/await

One advantage of promises is that you can use them with await. Express specifically works well with async/await. Just remember you can only use await inside a function marked with async:

router.get('/', async function(req, res, next) {
    let result = await doSomethingAsync();
    res.send(result);
});

Multiple asynchronous operations

Fetching multiple asynchronous data can be as simple as:

router.get('/', function(req, res, next) {
    doSomethingAsync(function(result1){
        doSomethingElse(function(result2) {
            res.json([result1, result2]);
        });
    });
});

With promises that would be:

router.get('/', function(req, res, next) {
    doSomethingAsync()
        .then(function(result1){
            return doSomethingElse()
                .then(function(result2) {
                    return [result1, result2];
                 });
         })
         .then(function(results){
             res.json(results);
         })
         .catch(next);
});

But both the above code perform the requests sequentially (get result1 then get result2). If you want to fetch both data in parallel you can do this with Promises:

router.get('/', function(req, res, next) {
    Promise.all([
        doSomethingAsync(),  // fetch in parallel
        doSomethingElse()
    ])
    .then(function(results){
        res.json(results);
    });
})

With callbacks it's a little bit more complicated. There is a design pattern you can use and someone has actually implemented it as a library called async.js but often the easiest solution is to wrap them in Promises and use Promise.all(). Still, do check out async.js since it has functionality useful for things like batching requests, perform async operations while a condition is true etc. (the promise based counterpart of that library is async-q)

Thank you very much. I really appreciate your help! I have only one question: In this way, I will able to use results of query in the callback function, then I'm forced to call res.render(...) inside the callback function. But what if res.render needs values from different queries? Looking for it on the web, I get I need to use Promise.all or something like that. Is it the right way? Maybe I have not got completly caching part of your answer: for example, is it really possible to store results in exerciseNameCache variable?
With promises if you want to fetch items in parallel use Promise.all() otherwise chain your promises in .then(). With callbacks just call another callback in the callback

Neel Rathod · Accepted Answer · 2019-07-09 06:48:36Z

You can use npm modules for achieving the async task with MySQL. I recommended to choose sequilize or jm-ez-mysql. If you go with jm-ez-mysql then your code structure like

server.js

require('./config/database.js');

./config/database.js

const sql = require('jm-ez-mysql');

// Init DB Connection
const connection = My.init({
  host: process.env.DBHOST,
  user: process.env.DBUSER,
  password: process.env.DBPASSWORD,
  database: process.env.DATABASE,
  dateStrings: true,
  charset: 'utf8mb4',
  timezone: 'utc',
  multipleStatements: true,
});

module.exports = {
  connection,
};

After that, you can use MySQL asynchronously.

./exercise.js

const sql = require('jm-ez-mysql');

const exerciseUtil = {};

exerciseUtil.searchUserById = async (id) => {
  try {
    // table name, db fields, condition, values
    const result = await sql.first('users', ['id, name, email'], 'id = ?', [id]);
    return result; // Which return your result in object {}
  } catch (err) {
    console.log(err);
    throw err;
  }
};

module.exports = exerciseUtil;

I hope it helps. Happy Coding :)

Collectives™ on Stack Overflow

How to organize communication with database in Node.Js?

2 Answers 2

Exporting the database

Reusing your queries

Caching

Promises

async/await

Multiple asynchronous operations

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Exporting the database

Reusing your queries

Caching

Promises

async/await

Multiple asynchronous operations

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related