j-sql

generate sql from js

api

• select
• update
• insert
• del
• set
• where
• or
• order
• group
• limit
• done

examples

install

var SQL = require('j-sql')

sql select

var sql = SQL.select('user_table', '*').done()

/*
select * from user_table
*/

var sql = SQL.select('user_table', ['name', 'age', 'point', 'create_time']).done()

/*
select name, age, point, create_time from user_table
*/

var sql = SQL.select('user_table', {
	name: 'name',
	age: 'age',
	point: 'point',
	create_time: 'createTime'
}).done()

/*
select name as name, age as age, point as point, create_time as createTime from user_table
*/

var sql = SQL.select('user_table', ['name', 'age', 'point', 'create_time']).limit('0,30').done()

/*
select name, age, point, create_time from user_table limit 0,30
*/

var sql = SQL.select('user_table', ['name', 'age', 'point', 'create_time']).group('name').done()

/*
select name, age, point, create_time from user_table group by name
*/

supported where options

• =
• <> means between
• <
• <=
• >
• >=
• like

var sql = SQL.select('user_table', ['name', 'age', 'point', 'create_time']).where({
	'name': ['=', 'hao'],
	'age': ['<>', 12, 23],
	'point': ['>', 1200]
}).done()

/*
select name, age, point, create_time from user_table
where name ="hao" and age between 12 and 23 and point > 1200
*/

var sql = SQL.select('user_table', ['name', 'age', 'point', 'create_time']).where({
	'name': ['like', '%hao%']
}).done()

/*
select name, age, point, create_time from user_table where name like "%hao%"
*/
	})

var sql = SQL.select('user_table', ['name', 'age', 'point', 'create_time']).order({
	age: 'asc',
	point: 'desc'
}).done()

/*
select name, age, point, create_time from user_table order by age asc, point desc
*/

• support multi table query, but when you use == in where options or or options, the string will not be translate to "string"

var sql = SQL.select('user_table, member_table', {
	'user_table.name': 'name',
	'user_table.age': 'age',
	'user_table.point': 'point',
	'user_table.create_time': 'createTime',
	'member_table.id': 'memberId',
	'member_table.nickname': 'nickname'
}).where({
	'user_table.name': ['=', 'hao'],
	'user_table.age': ['<>', 12, 23],
	'user_table.point': ['>', 1200],
	'member_table.user_id': ['==', 'user_table._id'],
	'member_table.nickname': ['=', 'hello']
}).order({
	'user_table.age': 'asc',
	'user_table.point': 'desc'
}).limit('0,30').done()

/*
select user_table.name as name, user_table.age as age, user_table.point as point,
user_table.create_time as createTime, member_table.id as memberId, member_table.nickname as nickname
from user_table, member_table where user_table.name = "hao" and user_table.age between 12 and 23
and user_table.point > 1200 and member_table.user_id = user_table._id
and member_table.nickname = "hello" order by user_table.age asc, user_table.point desc limit 0,30
*/

var sql = SQL.select('user_table u, member_table m', {
	'u.name': 'name',
	'u.age': 'age',
	'u.point': 'point',
	'u.create_time': 'createTime',
	'm.id': 'memberId',
	'm.nickname': 'nickname'
}).where({
	'u.name': ['=', 'hao'],
	'u.age': ['<>', 12, 23],
	'u.point': ['>', 1200],
	'm.user_id': ['==', 'u._id'],
	'm.nickname': ['=', 'hello']
}).order({
	'u.age': 'asc',
	'u.point': 'desc'
}).limit('0,30').done()

/*
select u.name as name, u.age as age, u.point as point, u.create_time as createTime,
m.id as memberId, m.nickname as nickname
from user_table u, member_table m where u.name = "hao" and u.age between 12 and 23
and u.point > 1200 and m.user_id = u._id and m.nickname = "hello"
order by u.age asc, u.point desc limit 0,30
*/

sql update

var sql = SQL.update('user_table').set({
	name: 'hao',
	age: 22,
	point: 1200,
	create_time: '2013-8-26'
}).done()

/*
update user_table set name = "hao", age = 22, point = 1200, create_time = "2013-8-26"
*/

var sql = SQL.update('user_table').set({
	name: 'xin',
	age: 23,
	point: 1300,
	create_time: '2013-8-27'
}).where({
	'name': ['=', 'hao'],
	'age': ['<>', 12, 23],
	'point': ['>', 1200]
}).done()

/*
update user_table set name = "xin", age = 23, point = 1300, create_time = "2013-8-27"
where name ="hao" and age between 12 and 23 and point > 1200
*/

sql insert

var sql = SQL.insert('user_table').set({
	name: 'hao',
	age: 12,
	point: 3333,
	create_time: '2013-09-14'
}).done()

/*
insert into user_table set name = "hao", age = 12, point = 3333, create_time = "2013-09-14"
*/

var sql = SQL.insert('user_table').values({
	name: 'hao',
	age: 12,
	point: 3333,
	create_time: '2013-09-14'
}).done()

/*
insert into user_table ( name, age, point, create_time ) values ( "hao", 12, 3333, "2013-09-14" )
*/

sql delete

var sql = SQL.del('user_table').where({
	'name': ['=', 'hao'],
	'age': ['<>', 12, 23],
	'point': ['>', 1200]
}).done()

/*
delete from user_table where name = "hao" and age between 12 and 23 and point > 1200
*/

var sql = SQL.del('user_table').where({
	'name': ['=', 'hao'],
	'age': ['<>', 12, 23],
	'point': ['>', 1200]
}).or({
	'nickname': ['=', 'hx'],
	'money': ['<>', 2345, 3456],
	'phone': ['>', 2345678]
}).done()

/*
delete from user_table where name = "hao"
and age between 12 and 23 and point > 1200 or nickname = "hx"
or money between 2345 and 3456 or phone > 2345678
*/

test

test coverage: 99%

License

MIT