mysql-bricks
sql-bricks extension for MySQL
mysql-bricks is an extension to the awesome sql-bricks library, that helps generate SQL statements for MySQL DB. It adds MySQL specific functions and formatting on top of the sql-bricks functionality.
Installation
Requires Node.js V6+ and ECMAScript 6 to run
$ npm install mysql-bricks
Usage
see sql-bricks documentation for common SELECT, INSERT, UPDATE and DELETE functionality. Below are examples of usage for the MySQL-specific functionality:
INSERT ... ON DUPLICATE KEY UPDATE ...
let values = [[123, 'Daniel', 41, 92], [456, 'David', 34, 87], [789, 'Rachel', 22, 98]];
let query = sql.insert('main.some_table', 'id', 'name', 'age', 'grade')
.values(values)
.onDuplicateKeyUpdate(['grade'])
.toString();
ON DUPLICATE KEY UPDATE clause can also contain a column-value pair, where the value is a free text sql.
Note that free-text sql will not be automatically quoted if it contains reserved words.
let values = [[123, 'Daniel', 41, 92, 0], [456, 'David', 34, 87, 0], [789, 'Rachel', 22, 98, 0]];
let query = sql.insert('main.some_table', 'id', 'name', 'age', 'grade', 'counter')
.values(values)
.onDuplicateKeyUpdate(['age', 'grade', { counter: 'counter + 1' }])
.toString();
INSERT IGNORE ...
let values = [[123, 'Daniel', 41, 92], [456, 'David', 34, 87], [789, 'Rachel', 22, 98]];
let query = sql.insert('main.some_table', 'id', 'name', 'age', 'grade')
.ignore()
.values(values)
.toString();
LIMIT (SELECT / UPDATE / DELETE)
let query = sql.select().from('main.some_table').limit(100).toString();
MySQL supports optional 'limit' clause in UPDATE and DELETE queries:
let query = sql.delete('main.some_table').limit(100).toString();
OFFSET
let query = sql.select().from('main.some_table').limit(100).offset(50).toString();
ORDER BY (UPDATE / DELETE)
MySQL supports optional 'order by' clause in UPDATE and DELETE queries:
let query = sql.update('main.some_table', { grade: 90})
.where(sql.gt('grade', 90))
.orderBy('age desc')
.limit(100)
.toString();
Contribute
Supported MySQL specific functions are the most common ones as used by me, but if you find something missing - feel free to open an issue, or better yet - a pull request!