Node.js/Express.js : Factory design pattern for DB queries

Question

I'm new to Node.js/Express.js, trying to build a basic website with those.

I handle my database queries with node-mysql but right now I'm doing aweful code such as :

router.get('/', function(req, res, next) {
    var query = 'INSERT INTO users(token) VALUES (?)';
    connection.query(query, [req.query.token], function(err, rows, fields) {
        if (err) throw err;
        res.cookie('token', req.query.token);    
    });

I have like 10 differents routes, in each of them the queries are hard-coded.

I'd like to be able to build database factories so my code looks like :

router.get('/', function(req, res, next) {
        var success = userFactory.InsertToken(req.query.token);
        if (success) //maybe a callback instead
            res.cookie('token', req.query.token);    
    });

What is the best practice to do so ?

Answer 1

On prototypal level i have tried to write small piece of stubs for you. This code may have some error, as idea of code is to demonstrate the separation of code and placing it in appropiate position.

route.js

'use strict';
var Controller = require('./controller');

router.get('/', function(req, res, next) {
        Cotroller.InsertToken(req, res, function(err, data){
            if(err){
                // handle error.
            }else{
                // handle data
                res.send(data);
            }
        });
    });

controller.js

'use strict';

var UserModel = require('./user');

/**
*   State of model should be changed from here, this allows you to change certain Api without affecting other (as far as model remain same).
*/
module.export.InsertToken = function(req, res, cb){
    // create user agaist which this token needs to be inserted.
    var user  = new UserModel();
    user.insert(req.query.token, function(err, data){
        if(err){
            return cb(err);
        }else{
            return cb(data);
        }
    });
}

user.js

'use strict';
var userDao = require('./UserDao');
/*
* All business actions should be taken here.
*/
module.export.User = function(){
    // add user properties here...
};

User.prototype.InsertToken = function(arguments, cb) {
    // Take business action before adding the token.
    userDao.insertToken(arguments, cb);
};

userDao.js

'use strict';
var queryFactory = require('./queryFactory').QueryFactory;

// All DB connection handling is centralized under one hood.
module.exports.InsertToken = function(arguments, cb){
    var queryID = 'InsertUserQuery';
    var query = queryFactory.getQuery(queryID);
    // obtain db connection and make query as usual.    
}

queryFactory.js

'use strict';

module.exports.QueryFactory = function(){
    var QueryMap = {
        'InsertUserQuery': 'Insert into user(token) values(?)';
    }


    return function getQuery(QueryID){
        return QueryMap[QueryID];
    }
}

Note:

This sort of solution is best for medium scale application in my opinion. Since separation of code can be done in n number of ways, so their are n solutions to this problem which makes it opinionated question.

If your application is bit complex and contain decent amount of business logic, then as per this solution, your user.js will be large and you may want to move real business logic from Model to Service layer.