Query Builder
A smart query builder for mysql
Note: It does not support having
and join
in the current version.
Installation
$ npm install mysql
$ npm install qbuilder
Example
Query data
var qb = qbuser ;
Friendly with mysql
var mysql = conn = mysql; conn;conn;
;
The basic query builder
The qbuilder and the qb instance have the same methods where
, and
, or
, insert
, select
, update
, delete
.
So qb().where()
and qb.where()
have the same effect.
var qb = ;
qb.where
, qb.and
, qb.or
Conditions qb.where
equals qb.and
qbsql //=>'WHERE `id` = 1 AND `name` = \'Jack\' AND `type` IN (1, 2) AND `status` IS NULL' qbsql //=> 'WHERE `name` = \'Jack\' OR `type` IN (1, 2)'
Short for id
qb //=> 'WHERE `id` = 123'qb //=> 'WHERE `id` = 123'
Operators
.eq(val)
,.eql(val)
,.equals(val)
.isNull()
is equal to.eq(null)
.in(val)
is equal to.eq(val)
when val is an array.not(val)
,.nq(val)
.notNull()
is equal to.not(null)
.notIn(val)
is equal to.not(val)
when val is an array.gt(val)
>
.gte(val)
>=
.lt(val)
<
.lte(val)
<=
.like(val)
.between(val, andVal)
qbsql //=> 'WHERE `name` = \'Jack\''
Combination
qb sql //=> 'WHERE `name` = \'Jack\' OR ( `type` = 1 AND `status` = 1 )'
Others
.groupby(field)
.sort(num)
.limit(num)
.offset(num)
.page(num, per_size)
qb sql //=> 'GROUP BY `name` LIMIT 10 OFFSET 10'
CRUD
qb.select(table)
qb.insert(table, values)
qb.update(table, values)
qb.delete(table)
Fields .fields(columns, forceEscape)
qb sql //=> 'SELECT name, count(*) FROM `user`'qb sql //=> 'SELECT `name` FROM `user`'
undefined
property
Ignore qbsql //=>'WHERE `id` = 1'
Connection
Query data through the qb.connection(mysqlOptions, tableList)
.
The connection object is extend from builder and has more usefull methods for query.
.query
The sql must be complete before query.
var qbuilder = qb = qb instanceof qbuilder //=> trueqb;
select
, insert
, update
, delete
Query when provide callback by qb;
Helper
find()
equalsselect()
fetchOne()
,findOne()
fetchValue()
create()
equalsinsert()
count()
pager(page, per_size, fn)
License
MIT