QueryWrap

Intro

QueryWrap takes a SQL query and wraps it with additional SQL to simplify filtering, sorting, and pagination. QueryWrap was designed to help with building REST APIs that need to support these types of features without reinventing the wheel.

Prerequisites

QueryWrap requires node-oracledb but it's not listed in the dependencies to prevent duplicate installations of the driver (QueryWrap should utilize the driver that's already installed for your project). See the INSTALL page of node-oracledb for details on installing that module.

Installation

Run the following command to install QueryWrap:

npm install query-wrap --save

QueryWrap Class

queryWrap.execute()

Signature

Promise:

promise = execute(String sql, [Object bindParams, [Object options]]);

The execute method is similar to node-oracledb's execute method, but there are some important differences. QueryWrap uses the 'default' connection pool to execute a query unless a pool or connection is passed in via the options object. When working with a pool (default or otherwise), connections are automatically obtained and closed.

The options object adds support for:

• filter - builds a dynamic where clause from an object (JS or JSON)
• sort - builds a dynamic order by clause from an array (JS or JSON)
• skip - starts the rows returned at a given offset (used for pagination)
• limit - restricts the number of rows returned (used for pagination)
• pool - the pool from which connections should be obtained (poolAlias or instance)
• connection - the connection to use to execute SQL statements (a pool should be used instead when possible)

The result returned from the execute method is different from that of node-oracledb. The result has the following properties:

• items - the rows of data returned from the query
• count - the number of rows returned (count === items.length)
• skip - the skip value used (will match the value was passed in or the default)
• limit - the limit value used (will match the value was passed in or the default)
• totalResults - the total number of rows returned from the query (after filtering but before limit applied)
• hasMore - a Boolean that indicates when pagination has prevented additional results from being returned

Not all of node-oracledb's features are supported. For example, ResultSets and streaming LOBs are not supported.

Examples

The following examples assume the 'default' connection pool was created in another module and use the following table and data:

create table t (
  c number
);
 
-- Insert 10 rows with values 1-10 
insert into t
select rownum
from dual
connect by rownum <= 10;
 
commit;

Using skip and limit

Fetch only the first 5 rows:

const oracledb = require('oracledb');
const queryWrap = require('query-wrap');
 
// The following values would usually come in as inputs from a RESTful API.
const skip = 0;
const limit = 5; 
 
async function getResult() {
  const result = await queryWrap.execute(
   `select c "c"
    from t
    order by c`,
    [], // no binds
    {
      skip: skip,
      limit: limit
    }
  );
 
  console.log(result);
  /*
  { items: [ { c: 1 }, { c: 2 }, { c: 3 }, { c: 4 }, { c: 5 } ],
    count: 5,
    limit: 5,
    skip: 0,
    totalResults: 10,
    hasMore: true }
  */
}
 
getResult();

Fetch the next 5 rows:

const oracledb = require('oracledb');
const queryWrap = require('query-wrap');
 
// The following values would usually come in as inputs from a RESTful API.
const skip = 5;
const limit = 5; 
 
async function getResult() {
  const result = await queryWrap.execute(
   `select c "c"
    from t
    order by c`,
    [], // no binds
    {
      skip: skip,
      limit: limit
    }
  );
 
  console.log(result);
  /*
  { items: [ { c: 6 }, { c: 7 }, { c: 8 }, { c: 9 }, { c: 10 } ],
    count: 5,
    limit: 5,
    skip: 5,
    totalResults: 10,
    hasMore: false }
  */
}
 
getResult();

Using sort

How data is sorted is often determined by end users of applications. The sort option provides a programmatic way to specify how this is done. sort accepts an array of objects (JS or JSON) to sort by, in their relative sort order. Each object must contain a column property which should contain the name of the column to sort by.

An optional order property can be specified to control the sort order. Use either 'asc', '1', or 1 for ascending order and 'desc', '-1', or -1 for descending order. The default sort order is ascending.

const oracledb = require('oracledb');
const queryWrap = require('query-wrap');
 
// The following values would usually come in as inputs from a RESTful API.
const sort = [{column: 'c', order: 'desc'}];
const skip = 0;
const limit = 3; 
 
async function getResult() {
  const result = await queryWrap.execute(
   `select c "c"
    from t`,
    [], // no binds
    {
      sort: sort,
      skip: skip,
      limit: limit
    }
  );
 
  console.log(result);
  /*
  { items: [ { c: 10 }, { c: 9 }, { c: 8 } ],
    count: 3,
    limit: 3,
    skip: 0,
    totalResults: 10,
    hasMore: true }
  */
}
 
getResult();

Using filter

The filter option provides a means filtering data programmatically. filter accepts an object (JS or JSON) and translates it to a where clause using bind variables.

There are two different syntaxes currently supported:

1. {column: value}
2. {column: {operator: value}}

column should be the name of the column to filter by (note that the keys are case sensitive). The first syntax will create a column = :value filter while the second syntax allows for operators other than =. The following operators are currently supported:

• $eq - whether a column value is equal to a given scalar
• $ne - whether a column value is different from a given scalar
• $lt - whether a column value is less than a given scalar
• $lte - whether a column value is less than or equal to a given scalar
• $gt - whether a column value is greater than a given scalar
• $gte - whether a column value is greater than or equal to a given scalar
• $between - whether a colum value is between (inclusive) to scalar values
• $nbetween - whether a colum value is not between (inclusive) to scalar values
• $in - weather a column value is a member of a given set of scalar values
• $nin - weather a column value is not a member of a given set of scalar values
• $like - whether a column value matches a given SQL LIKE pattern
• $instr - whether a column value has a given substring
• $ninstr - whether a column value does not have a given substring
• $null - whether a column is null (value should be null, e.g. {column: {$null: null}})
• $nnull - whether a column is not null (value should be null, e.g. {column: {$nnull: null}})

filter objects can contain multiple filters for different columns, but work would need to be done to support multiple filters on the same column (perhaps via complex $and or $or operators or by accepting an array of filters).

const oracledb = require('oracledb');
const queryWrap = require('query-wrap');
 
// The following value would usually come in as input from a RESTful API.
const filter = {c: {$lt: 4}};
 
async function getResult() {
  const result = await queryWrap.execute(
   `select c "c"
    from t`,
    [], // no binds
    {
      filter: filter
    }
  );
 
  console.log(result);
  /*
  { items: [ { c: 1 }, { c: 2 }, { c: 3 } ],
    count: 3,
    limit: 50,
    skip: 0,
    totalResults: 3,
    hasMore: false }
  */
}
 
getResult();