SQL Selector

Build SQL selectors using MongoDB syntax.

This project is essentially code inspired from minimongo and packaged as standalone to be used with MySQL, MySQL2, PostgreSQL, or any other SQL adapter.

Usage

import { mySqlParser } from 'sql-selector';
import mysql from 'mysql';


const filter = {
   createdAt: { $lt: new Date('2020-01-01') },
   deprecated: { $ne: true }
};

const params = [];
const sql = `
SELECT *
  FROM products
 WHERE ${mySqlParser.parse(filter, params)}
`;
// sql = 'SELECT * FROM products WHERE createdAt < ? AND deprecated <> ?'
// params = [ 2020-01-01T00:00:00.000Z, true ]


const connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'me',
  password : 'secret',
  database : 'my_db'
});

connection.connect();

connection.query(sql, params, (error, results, fields) => {
  if (error) throw error;
  console.log('Products found : ', results);
});
 
connection.end();

Limitations

The current implementation should work with any RDBMS, but make sure that your queries are properly parsed! This module is currently implemented with general-purpose usage and may not fully support database-specific operators, such as querying JSON objects.

Again, test your queries before using this module in production!

The goal of this project is to build WHERE clause where fields should match user-specified arguments, not to match two different fields; those conditions should be hardcoded directly in your queries.

Currently, not all standard operators have been implemented, so if anyone wnats to contribute, PR welcome! All proposed operators must either be generic (i.e. added to standardOperators in operators.js) or dialect-specific (i.e. extending the proper dialect class). Each PR must be accompanied with their respective unit tests by modifying the corresponding existing tests.

Adding functionalities

The implementation can easily adapt to new operators and functionalities. For example, implementing BETWEEN :

import { mySqlParser, processHelpers } from 'sql-selector';

// add operators to the mySqlParser, but does not affect
// the operators of other parsers
Object.assign(mySqlParser.operators, {
   // override equality operator
   $between: (path, value, params, ctx, parser, helpers) => {
      if (!Array.isArray(value) || value.length !== 2) {
        if (ctx.$to) {
          value = [value, ctx.$to];
        } else {
          throw new Error('Between requires an array with exactly 2 elements or a $to attribute');
        }
      }

      const column = parser.formatColumnName(path);
      const [ col, low ] = processHelpers(helpers, column, parser.formatValue(value[0], params), params, ctx); 
      const [ , high ] = processHelpers(helpers, null, parser.formatValue(value[1], params), params, ctx); 
      
      return `(${col} BETWEEN ${low} AND ${high})`;
   }
});

// add helpers to the mySqlParser, but does not affect
// the helpers of other parsers
Object.assign(mySqlParser.helpers, {
  // define a user-defined function (NOTE: value is already formatted!)
  $foo: (value, arg, parser, params) => `FOO(${value}, ${mySqlParser.formatValue(arg, params)})`
});

const params = [];

mySqlParser.parse({
   foo: { $between: [ 0, 10 ] },
   bar: { $between: 53.37, $to: 99.9, $foo: 'test' }
}, params );
// -> '(foo BETWEEN ? AND ?) AND (bar BETWEEN FOO(?, ?) AND FOO(?, ?))'
// params = [ 0, 10, 53.37, "test", 99.9, "test" ]

Note: operators and helpers must start with a $ character, otherwise they will be ignored by the parser.

Note: make sure all operators and helpers have distinct names, otherwise the parser's behavior will be undefined. For example, one may be tempted to have { $between: a, $and: b } however $and is already a logical operator.

Operators

Logical

• $eq | $ne : Test if equal (=) or not equal (<>)

  { foo: 123 }
  // -> 'foo = 123'
  { foo: { $eq: 123 } }
  // -> 'foo = 123'

• $gt | $gte : Test "greater than" (>) or "greater than or equal" (>=)

• $lt | $lte : Test "lesser than" (<) or "lesser than or equal" (<=)

  { foo: { $gt: 123 } }
  // -> 'foo > 123'

• $in | $nin : Test for matches (or mismatches) within an array of values

  { foo: { $in: [ 1, 2, 3 ] } }
  // -> 'foo IN (1, 2, 3)'

• $like : Test with a like pattern

  { foo: { $like: '%abc%' } }
  // -> 'foo LIKE "%abc"'
  { foo: { $like: '%abc%', $negate: true } }
  // -> 'foo NOT LIKE "%abc%"'

• $regex : Test with a regular expression

  { foo: { $regex: /abc/ } }
  // -> 'foo REGEXP "abc"'
  { foo: { $regex: 'ABC', $ignoreCase: true } }
  // -> 'LOWER(foo) REGEXP "abc"'
  { foo: { $regex: /ABC/i, $negate: true } }
  // -> 'LOWER(foo) NOT REGEXP "abc"'

  NOTE: it is possible to use RegExp instances as values, but they are not fully supported and will currently not work as expected in some cases at the moment, so use a String instead. Also, regular expression patterns are RDBMS-specific!

• $and : Group multiple subconditions with AND operands

• $or : Group multiple subconditions with OR operands

  { $and: [ { foo: 123 }, { bar: { $ne: 456 } } ] }
  // -> 'foo = 123 AND bar <> 456'

• $not : Negate condition and group any multiple subconditions with AND operands

• $nor : Negate condition and group any multiple subconditions with OR operands

  { $not: [ { foo: 123 }, { bar: 456 } ] }
  // -> 'NOT (foo = 123 OR bar = 456)'

Functional

Certain options may be provided to logical operators, these provide ways to control an operator's behavior. Those are arbitrary and may be declared and used without any configuration whatsoever.

• $options (used by $regex) : provide optiosn that is passed to the RegExp constructor

  { foo: { $regex: 'abc', $options: 'i' } }
  // -> 'LOWER(foo) REGEXP "abc"'

  Note: only i, for case insensitive, is currently supported.

• $negate (used by $regex, $like) : negate the operator

  { foo: { $like: 'abc', $negate: true } }
  // -> 'foo NOT LIKE "abc"'

Helpers

Unlike operators, helpers are only used to transform values by wrapping them or transforming them.

Beware of using self-cancelling helpers! For example: { $eq: 'foo', $lowerCase: true, $upperCase: true } may unpredictably transform the value into either lower case, or upper case.

• $cast: <datatype> : will cast the value into the given datatype

  { foo: { $gt: '2020-01-01', $cast: 'datetime' } }
  // -> 'foo > CAST("2020-01-01" AS datetime)

Multiple tables support

const filter = {
   u: { active: true },
   p: { email: '[email protected]' }
};
const params = [];

const query = `
SELECT u.*,
       p.*
  FROM tbl_users u
  JOIN tbl_profiles p ON u.id = p.user_id
 WHERE ${parser.parse(filter, params)}
`;
// -> SELECT u.*, p.* 
//      FROM tbl_users u
//      JOIN tbl_profiles p ON u.id = p.user_id
//     WHERE u.active = ? AND p.email = ?
// params = [ true, '[email protected]' ]