Jeffrey Devloo Jeffrey Devloo - 6 months ago 87
Node.js Question

Knex Query build - build chain dynamicly

I've traded node-DBI for knex because it has more function that I require.
So far I'd make the same choice again but only one thing is holding me back: writing abstract methods that take a options variable where params like where, innerjoin and such are contained in.
Using node-dbi I could easily forge a string using these variables but I can't seem to create the knex chain dymanicly because after using a switch, you'd get knex.method is not a function.

Any idea how to resolve this?

I'm looking for something as in

`getData(table,options){
var knex=knex
if(options.select)
/** append the select data using knex.select()
if(options.where)
/** append the where data using knex.where(data)*/
if(options.innerJoin)
/** append innerjoin data*/
}`


This way I can avoid having to write alot of DB functions and let my Business Logical Layers handel the requests

Answer
/*This function serves as the core of our DB layer
This will generate a SQL query and execute it whilest returning the response prematurely
@param obj:{Object} this is the options object that contain all of the query options
@return Promise{Object}: returns a promise that will be reject or resolved based on the outcome of the query
The reasoning behind this kind of logic is that we want to abstract our layer as much as possible, if evne the slightest 
sytnax change occurs in the near future, we can easily update all our code by updating this one
We are using knex as a query builder and are thus relying on Knex to communicate with our DB*/
/*Can also be used to build custom query functions from a data.service. This way our database service will remain
unpolluted from many different functions and logic will be contained in a BLL*/
/* All available options
var options = {
    table:'table',
    where:{operand:'=',value:'value',valueToEqual:'val2'},
    andWhere:[{operand:'=',value:'value',valueToEqual:'val2'}],
    orWhere:[{operand:'=',value:'value',valueToEqual:'val2'}],
    select:{value:['*']},
    insert:{data:{}},
    innerJoin:[{table:'tableName',value:'value',valueToEqual:'val2'}],
    update:{data:{}}
}*/
/*Test object*/
/*var testobj = {
    table:'advantage',
    where:{operand:'>',value:'id',valueToEqual:'3'},
    select:{value:['*']},
    innerJoin:{table:'User_Advantage',value:'User_Advantage.Advantageid',valueToEqual:'id'}
}
var testobj = {
    table:'advantage',
    where:{operand:'>',value:'id',valueToEqual:'3'},
    select:{value:['*']},
    innerJoin:{table:'User_Advantage',value:'User_Advantage.Advantageid',valueToEqual:'id'}
}
queryBuilder(testobj)*/
function queryBuilder(options){
 var promise = new Promise(function (resolve, reject) {
    var query;
    for (var prop in options) {
        /*logger.info(prop)*/
        if (options.hasOwnProperty(prop)) {
            switch (prop) {
                case 'table':
                query = knex(options[prop]);
                break;
                case 'where':
                query[prop](options[prop].value, options[prop].operand, options[prop].valueToEqual);
                break;
                /*andWhere and orWhere share the same syntax*/
                case 'andWhere':
                case 'orWhere': 
                for(let i=0, len=options[prop].length;i<len;i++){
                    query[prop](options[prop][i].value, options[prop][i].operand, options[prop][i].valueToEqual);
                }
                break;
                case 'select':
                query[prop](options[prop].value);
                break;
                /*Same syntax for update and insert -- switch fallthrough*/
                case 'insert':
                case 'update':
                query[prop](options[prop].data);
                break;
                case 'innerJoin':
                for(let i=0, len=options[prop].length;i<len;i++){
                    query[prop](options[prop][i].table, options[prop][i].value, options[prop][i].valueToEqual);
                }
                break;
            }
        }
    }
    return query
    .then(function (res) {
        return resolve(res);
    }, function (error) {
        logger.error(error)
        return reject(error);
    })
    return reject('Options wrongly formatted');
});
 return promise
}

Thanks to Molda I was able to produce the code above. This one takes a Object called options in as a parameter and will build the knex chain based on this value. See the comments for the syntax of Object Not every knex query option has been included but this will serve as a good base for anyone trying to achieve a similar effect.

Some examples to use this:

/*Will return all values from a certain table
@param: table{String}: string of the table to query
@param: select{Array[String]}: Array of strings of columns to be select -- defaults to ['*'] */
function getAll(table,select) {
    /*Select * from table as default*/
    var selectVal=select||['*']
    var options={
        table:table,
        select:{value:selectVal}
    }
    return queryBuilder(options)
}

or a more specific use case:

function getUserAdvantages(userid){
    var options = {
        table:'advantage',
        innerJoin:[{table:TABLE,value:'advantage.id',valueToEqual:'user_advantage.Advantageid'}],
        where:{operand:'=',value:'user_advantage.Userid',valueToEqual:userid}
    }
    return sqlService.queryBuilder(options)
}

Note: the sqlService is a module of node that I export containing the queryBUilder method.

Edit: I wanted to add that the only roadblock I had was using the .from / .insert from Knex. I no longer use these methods as they resulted in errors when using them. Ive used knex(table) as commented.

Comments