sql_utilities 0.0.2

A library of helpers for manipulating/generating sql strings

sql_utilities is a library of helpers for manipulating/generating sql strings.

Usage

var someUserSuppliedValue = 'littlebobbytables';
var boundParams = {};
var sql = 'SELECT * FROM foo WHERE ' + PostgresUtils.bindEquals('field1', someUserSuppliedValue, boundParams) + ' ORDER BY field2 ASC'; 

The above will result in:

• sql: SELECT * FROM foo WHERE field1 = @0 ORDER BY field2 ASC
• boundParams: {0: 'whatever'}

You can also explicitly name the bound parameter if you like, and reuse it later in the query:

var someUserSuppliedValue = 'littlebobbytables';
var boundParams = {};
var sql = 'SELECT * FROM foo WHERE ' + PostgresUtils.bindEquals('field1', someUserSuppliedValue, boundParams, name: 'customName') + ' OR field2 = @customName'; 

Which will result in:

• sql: SELECT * FROM foo WHERE field1 = @customName OR field2 = @customName
• boundParams: {'customName': 'whatever'}

Methods like bindContains(), bindStartsWithAny(), and bindRegex() (and others) can be used to generate complex matching statements with very little code.

var startingValues = [ 'lorem', 'ipsum', 'dolor' ];
var endingValues   = [ 'sit', 'amet', 'consectetur' ];
var boundParams = {};
var sql = 'SELECT * FROM foo WHERE ' + PostgresUtils.andGroup([
  PostgresUtils.bindStartsWithAny('field1', startingValues, boundParams),
  PostgresUtils.bindEndsWithAny('field2', endingValues, boundParams),
  PostgresUtils.bindRegex('field3', RegExp(r'yourpatternhere', caseSensitive: true, multiLine: true), boundParams, name: 'matchPattern'),
]);

Will result in:

• sql:

  SELECT * FROM foo WHERE (
    (field1 LIKE @0 OR field1 LIKE @1 OR field1 LIKE @2)
    AND (field2 LIKE @3 OR field2 LIKE @4 OR field2 LIKE @5)
    AND field3 REGEXP(@matchPattern, im)
  )
  

• boundParams:

  {
    '0'           : 'lorem%',
    '1'           : 'ipsum%',
    '2'           : 'dolor%',
    '3'           : '%sit',
    '4'           : '%amet',
    '5'           : '%consectetur',
    'matchPattern': 'yourpatternhere'
  }
  

Supported Dialects:

• MySql
• PostgreSQL

Methods available:

• inClause()
• bindInClause()
• bindParam()
• bindParams()
• bindInsertValuesMap()
• bindUpdateValuesMap()
• bindGreaterThan()
• bindGreaterThanOrEqual()
• bindLessThan()
• bindLessThanOrEqual()
• bindEquals()
• bindContains()
• bindStartsWith()
• bindEndsWith()
• bindRegex()
• bindEqualsAny()
• bindContainsAny()
• bindStartsWithAny()
• bindEndsWithAny()
• bindRegexAny()
• orGroup()
• andGroup()

Features and bugs

For now, please file feature requests and bugs by emailing the author