qbuilder

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);

Use in co and koa

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() equals select()
• fetchOne(), findOne()
• fetchValue()
• create() equals insert()
• count()
• pager(page, per_size, fn)

License

MIT