Nearly Perfect Mediocracy

    sql-view

    1.0.14 • Public • Published

    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

    Install

    npm i sql-view

    DownloadsWeekly Downloads

    203

    Version

    1.0.14

    License

    MIT

    Unpacked Size

    14.8 kB

    Total Files

    4

    Last publish

    Collaborators

    • andrglo