hqb

Query Builder For Nodejs!

The Hqb aims to handle all database layer operations. For now, It basically supports to create complex select queries and execute them properly. Also, You can use raw function for all other stuffs like insert, update, delete.

Dialects

• Oracle
• MySql ( partly support for now )

Depencies

• oracledb,
• mysql,
• mocha,
• chai,

Getting Started

There is sql files to create mock tables for testing purposes in Tests/Databases/Oracle. In this section, this tables will be used.

Installation

hqb is available on npm. To install it, type:

$ npm install hqb

Initialize, Connection and Query

You can initialize connection with either a config or connection promise.

initConnection

The initConnection function creates a connection promise with given config parameters for future uses. Config can contains all oracledb's connection configurations.

• type : could ORACLE or MYSQL
• poolAlias : If set hqb tries to use pool connection
• serverVersion : It is required for oracle connections. With 12c, Oracle has better limiting clause.

var Hqb = require('hqb');
 
var hqb = new Hqb();
hqb.initConnection({
    type : 'ORACLE',
    poolAlias: 'poolAlias',
    connectString: 'connectString',  
    user: 'user',  
    password: 'password',
    serverVersion : 1201000000
});

setConnection

setConnection function takes a database type and connection promise to use when executes sql.

var Hqb = require('hqb');
var oracledb = require('oracledb');
 
var hqb = new Hqb();
var config = {
    connectString: 'connectString',  
    user: 'user',  
    password: 'password',
};
 
var prm = new Promise(function(resolve, reject){
    oracledb.getConnection(config, function(err, conn){
        if(err)
            reject(err);
        else
            resolve(conn);
    });
});
 
hqb.setConnection("ORACLE", prm);

setAutoCommit

Set auto commit, default is true :

hqb.setAutoCommit(boolean); 

setFetchMode

Set fetch mode, 'OBJECT' or 'ARRAY' - Default OBJECT

hqb.setFetchMode(string); 

setFetchSize

Set fetch size, integer - Default 100000

hqb.setFetchSize(integer); 

beginTransaction

Begin transaction :

hqb.beginTransaction(); 

commit

Commit transaction :

Note: 'commit' set auto commit property to true after commit the changes

hqb.commit(); 

rollback

Rollback transaction :

Note: 'rollback' set auto commit property to true after rollback the changes

hqb.rollback(); 

getConnection

You can get connection from hqb via this function.

hqb.getConnection(callback); 

closeConnection

Close connection.

Note: Dont forget the close connection.

hqb.closeConnection(); 

createQueryBuilder

The function gives an QueryBuilder object.

QueryBuilder Class

This is the class that handles all operations about sql.

select and addSelect

This functions fill the columns on select query. Both support array and string as parameter.

Note: 'select' overrides previous calls to select and addSelect

qb.select(column);
 
qb.select([column1, column2]);
 
qb.addSelect(column);
 
qb.addSelect([column1, column2]);

from

This function set the main table of select query.

qb.from(tableName);
 
qb.from(tableName, tableAlias);

where, andWhere, orWhere

This functions add where conditions to query. All support array and string as parameter.

Note: 'where' overrides previous calls to where, andWhere and orWhere

qb.where(cond1);
 
qb.where([cond1, cond2]);
 
qb.andWhere(cond1);
 
qb.andWhere([cond1, cond2]);
 
qb.orWhere(cond1);
 
qb.orWhere([cond1, cond2]);

join, leftJoin, rightJoin

This functions add joins.

Note : 'joinConditions' is an optional parameter.

qb.join(tableName, alias, joinConditions);
 
qb.leftJoin(tableName, alias, joinConditions);
 
qb.rightJoin(tableName, alias, joinConditions);

orderBy and addOrderBy

This functions add order clause.

Note: 'orderBy' overrides previous calls to orderBy and addOrderBy

qb.orderBy(column);
 
qb.orderBy(column, direction);
 
qb.addOrderBy(column);
 
qb.addOrderBy(column, direction);

groupBy and addGroupBy

This functions add group by clause.

Note: 'groupBy' overrides previous calls to groupBy and addGroupBy

qb.groupBy(column);
 
qb.groupBy(column);
 
qb.addGroupBy(column);
 
qb.addGroupBy(column);

having, andHaving, orHaving

This functions add having conditions to query. All support array and string as parameter.

Note: 'having' overrides previous calls to having, andHaving and orHaving

qb.having(cond1);
 
qb.having([cond1, cond2]);
 
qb.andHaving(cond1);
 
qb.andHaving([cond1, cond2]);
 
qb.orHaving(cond1);
 
qb.orHaving([cond1, cond2]);

offset and limit

You can handle limiting operations with this functions.

qb.offset(recordOffsetToReturn);
 
qb.limit(recordCountToReturn);

Insert

You can specify the table name for insert via this function.

qb.insert(tableName);

Update

You can specify the table name for update via this function.

qb.update(tableName);

set

You can set properties and aliases via this function.

qb.set([
    {
        property: propertyName,
        alias: alias
    },
    {
        property: propertyName,
        alias: alias
    }
]);
 
qb.set(propertyName, alias);

setParameter and setParameters

This functions binding parameters to sql.

Note: 'setParameters' overrides previous calls to setParameter and setParameters

qb.setParameter({ bindName : bindValue });
 
qb.setParameters([{ bindName1 : bindValue1 }, { bindName2 : bindValue2 }]);

raw

This function allows you to execute all-style sql queries with hqb.

Note: 'raw' overrides all previous calls

qb.raw(query, parameters);

execute

This function executes the query with provided/created via above functions. Also this function can give data count executed sql without limits. This count query is handled asynchronously in executor, so time loss is minimized.

// countParam is a boolean and default is false
qb.raw(countParam, callback); 
 
qb.raw(callback); 

getSql

This function returns created sql.

qb.getSql(); 

getParameters

This function returns bound parameters.

qb.getParameters(); 

Example

var Hqb = require('hqb');
 
var hqb = new Hqb();
hqb.initConnection({
    type : 'ORACLE',
    poolAlias: 'poolAlias',
    connectString: 'connectString',  
    user: 'user',  
    password: 'password',
    serverVersion : 1201000000
});
 
var qb = hqb.createQueryBuilder();
qb.select(['a.plate as plate', 'b.name as company', 'c.name as brand'])
.from('HQB_TEST_BUS', 'a')
.join('HQB_TEST_COMPANY', 'b', 'a.company_id = b.company_id')
.leftJoin('HQB_TEST_BRAND', 'c', 'a.brand_id = c.brand_id')
.where('a.company_id = :companyId')
.setParameter({companyId : 1})
.offset(0)
.limit(2)
.execute(true, function(err, data){
    // do something
});