sql-view 
Rewrite a select statement embedding a filter, order, group or pagination using an otions object. For MS Sql Server and postgres
Install
$ npm install --save sql-viewUsage
var sqlView = require('sql-view')('postgres');
// build(view, criteria)
var view = sqlView.build('SELECT * FROM products'), {
where: {
price: {
lt: '1000'
}
});
console.log(view);
// => { statement: 'SELECT * FROM (SELECT * FROM "products") t WHERE "price"<$1',
// params: [ '1000' ]
// }
view = sqlView.build('products'), {
where: {
price: {
lt: '1000'
}
});
console.log(view);
// => { statement: 'SELECT * FROM "products" WHERE "price"<$1',
// params: [ '1000' ]
// }Criteria
The criteria objects are formed using one of four types of object keys. These are the top level keys used in a query object. It is loosely based on the criteria used in Waterline.
sqlView.build('select * from table', { where: { name: 'foo' }, skip: 20, limit: 10, order: 'name DESC' });Use the key as the column name and the value for a exact match
sqlView.build('select * from table', { where: { name: 'briggs' }})They can be used together to filter for multiple columns
sqlView.build('select * from table', { where: { name: 'briggs', state: 'california' }})Keys can also hold any of the supported criteria modifiers to perform queries where a strict equality check wouldn't work.
sqlView.build('select * from table', { where: {
name : {
contains : 'alt'
}
}})With an array each element is treated as or as in queries
sqlView.build('select * from table', { where: {
name : ['briggs', 'mike']
}});Not in queries work similar to in queries
sqlView.build('select * from table', { where: {
name: { not : ['briggs', 'mike'] }
}});Performing or queries is done by using an array of objects
sqlView.build('select * from table', { where: {
or : [
{ name: 'briggs' },
{ occupation: 'unknown' }
]
}})The following modifiers are available to use when building queries
'lt''lte''gt''gte''not''like''contains''startsWith''endsWith'
sqlView.build('select * from table', { where: { age: { lte: 30 }}})Pagination
Allow you refine the results that are returned from a query. The current options available are:
limitskiporderselect
Limits the number of results returned from a query
sqlView.build('select * from table', { where: { name: 'foo' }, limit: 20 })Returns all the results excluding the number of items to skip
sqlView.build('select * from table', { where: { name: 'foo' }, skip: 10 });skip and limit can be used together to build up a pagination system.
sqlView.build('select * from table', { where: { name: 'foo' }, limit: 10, skip: 10 });Results can be sorted by attribute name. Simply specify an attribute name for natural (ascending) order, or specify an asc or desc flag for ascending or descending order respectively.
// Sort by name in ascending order (default)
sqlView.build('select * from table', { where: { name: 'foo' }, order: 'name' });
// or
sqlView.build('select * from table', { where: { name: 'foo' }, order: 'name asc' });
// Sort by name in descending order and also in email
sqlView.build('select * from table', { where: { name: 'foo' }, order: ['name desc', 'email'] });Apply a projection
// Returns only the field name
sqlView.build('select * from table', { where: { age: { lt: 30 } }, select: ['name'] })Grouping
// Returns only the field name
sqlView.build('select * from table', { groupBy: 'state', sum: 'population' })The group functions available are: sum, avg, max and min
Credits
Inspired by the query language of Waterline implemented by cnect
License
MIT © Andre Gloria