Sato Sato - 1 year ago 54
Node.js Question

Should I do work in database query or in application logic?

For example, I have a table(mongodb)

var schema = new mongoose.Schema({
username: { type: String, unique: true, require: true },
password: String,

role: { type: String, enum: ['admin', 'user'], default: 'USER' },
ttt: {type: String, enum: ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J'], }
deleted: { type: Boolean, default: false },
disabled: { type: Boolean, default: false },

}


Then I need to get stat of users(nodejs):

var deletedUserCount = Users.count({deleted: true})
var disabledUserCount = Users.count({disabled: true})
var totalUserCount = Users.count({})
var adminUserCount = Users.count({role: 'admin'})
var userUserCount = Users.count({role: 'user'})


This will fire 5 db queries.

Should I do something like this:

var users = Users.find({}, {role: 1, deleted: 1, disabled: 1})
var deletedUsers = _.filter(users, function(u) {return u.deleted;})
var deletedUserCount = deletedUsers.length;


One db query. Do work in application level.

Which way has better performance?

This is a simplified example, what happens if the example is more complicated?

What rules should I follow?

And suppose I want to get countByttt

tttACount = Users.count({ttt: 'A'})

h22 h22
Answer Source

As the database is strong in search and paging, I think these tasks must be done at the database query level. The attempt to move such functionality to application may cause to fetch way to many rows from the database into application (all pages rather than one and all search candidates rather than matches).

Other logic, especially more complex, does not benefit that much from being in the database. Having it at the application level usually allows to write and debug Unit tests much simpler, while there are testing frameworks for the database stored procedures also.

When implementing at application level, it may be better to implement all logic in some layer that is accessed from the rest of the application and is the only code that calls the database.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download