sql_utilities

Description:

sql utilities

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';

copied to clipboard
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';
copied to clipboard
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'),
]);
copied to clipboard
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)
)
copied to clipboard

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


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