sql-view

Rewrite a select statement embedding a filter, order, group or pagination using an otions object. For MS Sql Server and postgres

Install

1$ npm install --save sql-view

Usage

1var sqlView = require('sql-view')('postgres');
2
3// build(view, criteria)
4var view = sqlView.build('SELECT * FROM products'), {
5  where: {
6    price: {
7      lt: '1000'
8    }
9  });
10console.log(view);
11// => { statement: 'SELECT * FROM (SELECT * FROM "products") t WHERE "price"<$1',
12//      params: [ '1000' ]
13//    }
14
15view = sqlView.build('products'), {
16  where: {
17    price: {
18      lt: '1000'
19    }
20  });
21console.log(view);
22// => { statement: 'SELECT * FROM "products" WHERE "price"<$1',
23//      params: [ '1000' ]
24//    }
25

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.

1sqlView.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

1sqlView.build('select * from table', { where: { name: 'briggs' }})

They can be used together to filter for multiple columns

1sqlView.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.

1sqlView.build('select * from table', { where: {
2  name : {
3    contains : 'alt'
4  }
5}})

With an array each element is treated as or as in queries

1sqlView.build('select * from table', { where: {
2  name : ['briggs', 'mike']
3}});

Not in queries work similar to in queries

1sqlView.build('select * from table', { where: {
2  name: { not : ['briggs', 'mike'] }
3}});

Performing or queries is done by using an array of objects

1sqlView.build('select * from table', { where: {
2  or : [
3    { name: 'briggs' },
4    { occupation: 'unknown' }
5  ]
6}})

The following modifiers are available to use when building queries

• 'lt'
• 'lte'
• 'gt'
• 'gte'
• 'not'
• 'like'
• 'contains'
• 'startsWith'
• 'endsWith'

1sqlView.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:

• limit
• skip
• order
• select

Limits the number of results returned from a query

1sqlView.build('select * from table', { where: { name: 'foo' }, limit: 20 })

Returns all the results excluding the number of items to skip

1sqlView.build('select * from table', { where: { name: 'foo' }, skip: 10 });

skip and limit can be used together to build up a pagination system.

1sqlView.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.

1// Sort by name in ascending order (default)
2sqlView.build('select * from table', { where: { name: 'foo' }, order: 'name' });
3// or
4sqlView.build('select * from table', { where: { name: 'foo' }, order: 'name asc' });
5
6// Sort by name in descending order and also in email
7sqlView.build('select * from table', { where: { name: 'foo' }, order: ['name desc', 'email'] });
8

Apply a projection

1// Returns only the field name
2sqlView.build('select * from table', { where: { age: { lt: 30 } }, select: ['name'] })

Grouping

1// Returns only the field name
2sqlView.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