JSPM

  • ESM via JSPM
  • ES Module Entrypoint
  • Export Map
  • Keywords
  • License
  • Repository URL
  • TypeScript Types
  • README
  • Created
  • Published
  • Downloads 1
  • Score
    100M100P100Q40530F
  • License MIT

Rewrite a select statement embedding a filter, sort, group or pagination using an otions object

Package Exports

  • sql-view

This package does not declare an exports field, so the exports above have been automatically detected and optimized by JSPM instead. If any package subpath is missing, it is recommended to post an issue to the original package (sql-view) to support the "exports" field. If that is not possible, create a JSPM override to customize the exports field for this package.

Readme

sql-view NPM version Dependency Status CircleCI Coverage Status

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-view

Usage

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:

  • limit
  • skip
  • order
  • select

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