0.1.3 • Published 10 years ago
qbuilder v0.1.3
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