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