dabs

DaBs

Simple SQL Query Builder.

Uses MySQL2 as DB engine.

Quickstart

npm install dabs

###CONNECT

const dabs = require("dabs");
const db = await dabs.connect("mysql://user:pass@host/database");

###SELECT

const u = db.table("users");
const n = db.table("data");

let q = u.select( u.name, u.mail )
  .join(n, n.uid.eq(u.uid) )
  .fields( n.title.as("title") )
  .fields( db.fn.COUNT(n.id).as("alias_count") )
  .fields( db.fn.AVG(n.field).as("alias_avg") )
  .where( u.uid.equals(db._("placeholder")) )
  .where( n.id.gt(10) )
  .sortBy( n.id.asc(), u.uid.desc() )
  .groupBy( n.id )
  .having( db.$("alias_avg").gt(100) );

let result = await q.exec({ placeholder: 5 });
console.info(q.toString());

SELECT
`users`.`name`,`users`.`mail`,`data`.`title` AS title,COUNT(`data`.`id`) AS alias_count,AVG(`data`.`field`) AS alias_avg
FROM users
JOIN `data` ON (`data`.`uid` = `users`.`uid`)
WHERE (`users`.`uid` = ? AND `data`.`id` > ?)
GROUP BY `data`.`id`
HAVING (`alias_avg` > ?)
ORDER BY `data`.`id` ASC,`users`.`uid` DESC

###INSERT

u.insert()
  .fields(u.name, u.email)
  .exec({ name: "test", email: "[e-mail]"})
  .then((data) => console.log(data))
  .catch((e) => console.log(e));

###UPDATE

n.update()
  .fields( n.f1 )
  .where( n.f1.equals(db._("placeholder")) )
  .whereAny( n.f2.equals("x"), n.f3.equals("y") )
  .exec({ f1: "a specific value", placeholder: "test" })
  .then((data) => console.log(data))
  .catch((err) => console.log(err));

###DELETE

n.delete()
  .where( n.f1.equals(db._("placeholder")) )
  .exec({ placeholder: "what" })
  .then((data) => console.log(data))
  .catch((err) => console.log(err));