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