qbuilder
v0.1.3
Published
Query builder for mysql
Downloads
3
Maintainers
Readme
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 = require("qbuilder").connection("mysql://127.0.0.1/mysql", ["user"])
qb.user
.where("User", "root")
.count(function(err, num){
//num
});
Friendly with mysql
var mysql = require("mysql")
, conn = mysql.createConnection();
conn.query(qb.select("user").where("user", "root").limit(1), fn);
conn.query("SELECT * FROM user " + qb.where("User", "root").limit(1), fn);
co(function *(){
var res = yield qb.select("user").where("User", "root").limit(1)
})();
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 = require("qbuilder");
###Conditions qb.where
, qb.and
, qb.or
qb.where
equals qb.and
qb.where({
id: 1,
name: "Jack",
type: [1, 2],
status: null
}).sql //=>'WHERE `id` = 1 AND `name` = \'Jack\' AND `type` IN (1, 2) AND `status` IS NULL'
qb.and("name", "Jack").or("type", [1,2]).sql //=> 'WHERE `name` = \'Jack\' OR `type` IN (1, 2)'
Short for id
qb.where(123) //=> 'WHERE `id` = 123'
qb.where("123") //=> '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)
qb.and("name").eq("Jack").sql //=> 'WHERE `name` = \'Jack\''
Combination
qb.and("name").eq("Jack")
.or(qb.and({type: 1, status:1}))
.sql //=> 'WHERE `name` = \'Jack\' OR ( `type` = 1 AND `status` = 1 )'
Others
.groupby(field)
.sort(num)
.limit(num)
.offset(num)
.page(num, per_size)
qb.where()
.groupby("name")
.page(2, 10)
.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.select("user")
.fields(["name", "count(*)"])
.sql //=> 'SELECT name, count(*) FROM `user`'
qb.select("user")
.fields("name", true)
.sql //=> 'SELECT `name` FROM `user`'
Ignore undefined
property
qb.where({
id: 1,
status: undefined
}).sql //=>'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 = require("qbuilder")
, qb = require("qbuilder").connection("mysql://127.0.0.1/mysql", ["user"])
qb instanceof qbuilder //=> true
qb.select("user").query(fn);
Query when provide callback by select
, insert
, update
, delete
qb.where("User", "root").select("user", fn);
Helper
find()
equalsselect()
fetchOne()
,findOne()
fetchValue()
create()
equalsinsert()
count()
pager(page, per_size, fn)
License
MIT