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.
- Oracle
- MySql ( partly support for now )
There is sql files to create mock tables for testing purposes in Tests/Databases/Oracle
. In this section, this tables will be used.
hqb
is available on npm. To install it, type:
$ npm install hqb
You can initialize connection with either a config or connection promise.
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 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);
Set auto commit, default is true :
hqb.setAutoCommit(boolean);
Set fetch mode, 'OBJECT' or 'ARRAY' - Default OBJECT
hqb.setFetchMode(string);
Set fetch size, integer - Default 100000
hqb.setFetchSize(integer);
Begin transaction :
hqb.beginTransaction();
Commit transaction :
Note: 'commit' set auto commit property to true after commit the changes
hqb.commit();
Rollback transaction :
Note: 'rollback' set auto commit property to true after rollback the changes
hqb.rollback();
You can get connection from hqb via this function.
hqb.getConnection(callback);
Close connection.
Note: Dont forget the close connection.
hqb.closeConnection();
The function gives an QueryBuilder object.
This is the class that handles all operations about sql.
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]);
This function set the main table of select query.
qb.from(tableName);
qb.from(tableName, tableAlias);
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]);
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);
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);
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);
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]);
You can handle limiting operations with this functions.
qb.offset(recordOffsetToReturn);
qb.limit(recordCountToReturn);
You can specify the table name for insert via this function.
qb.insert(tableName);
You can specify the table name for update via this function.
qb.update(tableName);
You can set properties and aliases via this function.
qb.set([
{
property: propertyName,
alias: alias
},
{
property: propertyName,
alias: alias
}
]);
qb.set(propertyName, alias);
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 }]);
This function allows you to execute all-style sql queries with hqb.
Note: 'raw' overrides all previous calls
qb.raw(query, parameters);
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);
This function returns created sql.
qb.getSql();
This function returns bound parameters.
qb.getParameters();
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
});