How to organize interaction with DB in nodejs?

Question

I have a such structure in my express app:

db helper, that i use to interacts to my maria db

code:

var MariaSQL = require('mariasql');
var db = new MariaSQL();
var queries = {
    getUserByID : 'SELECT * FROM user WHERE id=:id',
    getUserByUsername : 'SELECT * FROM user WHERE username=:username'
};

module.exports = {
    connect : function(config){
        db.connect({
            host : config.maria.host,
            user : config.maria.user,
            password : config.maria.password,
            db : config.maria.db
        });
        db.on('connect', function() {
            console.log('Successfully connected to DB');
        })
        .on('error', function(err) {
            console.log('Connection error: ' + err);
        })
        .on('close', function(hadError) {
            console.log('Client closed');
        });
    },
    executeQuery : function(queryName, queryData, callback){
        var data = queryData || {};
        var result = [];
        var error;
        if(queryName in queries){
            var pq = db.prepare(queries[queryName]);
            db.query(pq(data))
            .on('result', function(res) {
                res.on('row', function(row) {
                    result.push(row);
                })
                .on('error', function(err){
                    error = err;
                })
                .on('end', function(info) {
                    //console.log('Result finished successfully, numRows = ' + info.numRows + ', insertid=' + info.insertId);
                });
            })
            .on('end', function() {
                if(error)
                    callback(error);
                else
                    callback(null, result);
            });
        } else {
            callback(new Error('Wrong query with name = ' + queryName));
        }
    }
};

In my app.js i call db_helper.connect(config); to initialize connection

Then i just call executeQuery method of db_helper to execute queries and get results, f.e.:

db_helper.executeQuery('getUserByUsername', {username : username}, function(err, user){
      ... 
});

So, i have some very important questions for me:

Is it normal way to organize interaction with DB?
Why i have multiple messages Successfully connected to DB? After all, I call this method only once, when i initilize connection, is not it?
As I understand author closes connection each time all date recieved using method c.end(). But can i not to close connection and use opened one?

p.s. Sorry for my english... And thanks for help, it's my first question :)

Consider splitting this question into three questions, one about structuring the db access, one about the "Successfully connected to DB" issue (You should post some code to show where you call db.connect) and one concerning the connection open/close handling — saintedlama
– saintedlama, Commented Dec 10, 2013 at 7:53

saintedlama · Accepted Answer · 2013-12-10 08:00:17Z

1

I guess there is no normal way to organize a project or the data access to a project. I would recommend splitting the connection and query logic and I would not try to create a big hash queries to store all queries used in a central place.

Your classes/modules/functions should be responsible for only one thing (See Single responsibility principle). That makes change (for example adding a query) local and does not force code that only want's to query users, persons, ... to see all queries stored in a single object (See Interface segregation principle). Following these two principles makes your code more maintainable (change, extension) and follows the node.js module principle to create modules that do one thing and only one thing.

This answer only discusses the organisation part of your question.

answered Dec 10, 2013 at 8:00

saintedlama

6,8781 gold badge30 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to organize interaction with DB in nodejs?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related