XSQL

var Xsql = require('xsql');
var x = new Xsql({dialect:'mysql'});

var query = [
  x.select(x.names(['col1','col2'],'tbl')),
  x.from(x.name('tbl')),
  x.where(x.eq(x.name('id','tbl'),2)),
  ';'
].join(' ');

select `tbl`.`col1`,`tbl`.`col2` from `tbl` where `tbl`.`id`=2 ;

Primitives

• quotes : [String]

• wrap : [String, Undefined|String]

• escape : [String]

• string : [String, Undefined|String]

• name : [String, Undefined|String, Undefined|String]

• names : [Array, Undefined|String, Undefined|String]

• schema : [Undefined|String]

• as : [String, String]

• alias : [String, String]

• func : [String, String|Array, Undefined|String]

• select : [String|Array]

• from : [String]

• join : [String, String|Array, Undefined|String]

• eq : [String, String|Number|Null]

• eqv : [String, Undefined|Null|String|Number|Boolean]

• groupby : [String|Array]

• orderby : [String|Object|Array, Undefined|String]

• limit : [Number, Number]

• in : [String|Number|Array]

• and : [String|Array]

• or : [String|Array]

• between : [String|Number, String|Number]

• like : [String]

• where : [String|Array, Undefined|String]

• insert : [String, String|Array, String|Array]

• update : [String, String|Array, String|Array]

• delete : [String]

Examples

Functions

var x = new xsql({dialect:'mysql'});

var concat = x.func('concat_ws',[
  x.wrap(' '),
  x.name('col1','tbl'),
  x.name('col2','tbl')
]);

var group = x.func('group_concat',['distinct', concat],' ');

var partial = x.as(group,x.name('alias'));

group_concat(distinct concat_ws(' ',`tbl`.`col1`,`tbl`.`col2`)) as `alias`

Join Tables

var query = [
  x.select([x.name('col','tbl1'),x.name('col','tbl2')]),
  x.from(x.name('tbl1')),
  x.join(x.name('tbl2'),x.eq(x.name('id','tbl1'),x.name('id','tbl2')),'left'),
  ';'
].join(' ');

select `tbl1`.`col`,`tbl2`.`col` from `tbl1`
  left join `tbl2` on `tbl1`.`id`=`tbl2`.`id` ;

PostgreSQL Schema

public by default

var x = new xsql({dialect:'pg'});
var query = [
  x.select(x.name('col','tbl',x.schema())),
  x.from(x.name('tbl',x.schema())),
  ';'
].join(' ');

select "public"."tbl"."col" from "public"."tbl" ;

globally defined

var x = new xsql({dialect:'pg', schema:'y'});
var query = [
  x.select(x.name('col','tbl',x.schema())),
  x.from(x.name('tbl',x.schema())),
  ';'
].join(' ');

select "y"."tbl"."col" from "y"."tbl" ;

specify schema

var x = new xsql({dialect:'pg', schema:'y'});
var query = [
  x.select(x.name('col','tbl',x.schema('z'))),
  x.from(x.name('tbl',x.schema())),
  ';'
].join(' ');

select "z"."tbl"."col" from "y"."tbl" ;

skip on other dialects

var x = new xsql({dialect:'sqlite'});
var query = [
  x.select(x.name('col','tbl',x.schema('z'))),
  x.from(x.name('tbl',x.schema())),
  ';'
].join(' ');

select "tbl"."col" from "tbl" ;

Wrapping it up

concat

function concat(columns, separator) {
  return (/mysql|pg/.test(x.dialect))
    ? x.func('concat_ws',[x.wrap(separator),columns])
    // sqlite
    : columns.join("||'"+separator+"'||");
}

mysql

var x = new xsql({dialect:'mysql'});
var partial = concat([x.name('col1','tbl'), x.name('col2','tbl')],',');

concat_ws(',',`tbl`.`col1`,`tbl`.`col2`)

sqlite

var x = new xsql({dialect:'sqlite'});
var partial = concat([x.name('col1','tbl'), x.name('col2','tbl')],',');

"tbl"."col1"||','||"tbl"."col2"

group

function group (columns) {
  return (/mysql|sqlite/.test(x.dialect))
    ? x.func('group_concat',['distinct',columns],' ')
    // pg
    : x.func('string_agg',['distinct',[columns,x.wrap(',')].join()],' ')
}

mysql

var x = new xsql({dialect:'mysql'});
var partial = group([x.name('col1','tbl'), x.name('col2','tbl')],',');

group_concat(distinct `tbl`.`col1`,`tbl`.`col2`)

pg

var x = new xsql({dialect:'pg'});
var partial = group([x.name('col1','tbl'), x.name('col2','tbl')],',');

string_agg(distinct "tbl"."col1","tbl"."col2",',')

License

MIT