MySQL driver wrapper for easy work (with promises)
Initial:
const mysqlEasy = ;
Static methods:
Method "createConnection" and "createPool":
(arguments are similar to static methods with same names in module "mysql": https://www.npmjs.com/package/mysql#establishing-connections)
const db = mysqlEasy;
Method "wrap":
const db = mysqlEasy;
Method "format":
(method is similar to "format" in module "mysql": https://www.npmjs.com/package/mysql#escaping-query-values)
- sqlQuery: string
- params: Array (optional)
mysqlEasy> "SELECT * FROM `users` WHERE id = '12'"
Method "escapeId":
- id: string
mysqlEasy> "`myTableName`"
Method "escape":
- value: string
mysqlEasy> '"myValue"'
Constructor:
Constructor is not directly accessible.
Methods:
Method "query":
(method is similar to "query" in module "mysql": https://www.npmjs.com/package/mysql#performing-queries)
- sqlQuery: string
- params: Array (optional)
db ;
Method "select":
- params: Object
- table: string
- distinct: boolean (optional)
- fields: Array|Object|string (optional)
- where: Object|string (optional)
- order: Object|string (optional)
- limit: number (optional)
- offset: number (optional)
db;
Method "selectOne":
- params: Object
- table: string
- fields: Array|Object|string (optional)
- where: Object|string (optional)
- order: Object|string (optional)
db;
Method "selectExactOne" (like selectOne but throw error if item not found):
- params: Object
- table: string
- fields: Array|Object|string (optional)
- where: Object|string (optional)
- order: Object|string (optional)
db;
Method "insert":
- tableName: string
- data: Object
- flags: Object (optional)
db;
Method "update":
- tableName: string
- data: Object
- where: Object|string (optional)
- flags: Object (optional)
db;
Method "delete" or "deleteFrom":
- tableName: string
- where: Object|string
db;
Method "end" or "close":
db;
Method "unwrap":
const underlyingMysqlConnection = db;
Transactions:
Method "createTransaction"
returns MySQL Promise with like instance with 2 additional methods: "commit" and "rollback"
db;
You must call commit either rollback at once.
"Select" specific options:
Parameter fields can be one of:
- Array -
['id', 'user_name']
- Object - key - result field name, value - column name
{ id: 'id', userName: 'user_name' }
- String -
'id, user_name AS userName'
(raw format)
Note:
- if you use "group" you can select with aggregation:
{ fieldName: { $count: 'field_name' } }
{ fieldName: { $avg: 'field_name' } }
{ fieldName: { $min: 'field_name' } }
{ fieldName: { $max: 'field_name' } }
{ fieldName: { $sum: 'field_name' } }
Parameter order can be one of:
- Object -
{ id: 1, age: -1 }
equal isORDER BY id, age DESC
- String -
'ORDER BY balance DESC, user_id'
(raw format)
Parameter join can be:
- Object -
{ table: 'another_table', on: { 'main_table.field_name1: { $field: 'another_table.field_name2' } }, type: 'left' }
- String -
'LEFT JOIN another_table ON main_table.field_name1 = another_table.field_name2'
(raw format)
Parameter group can be:
- Array -
['field_name','field_name_2']
- String -
'field_name, field_name_2'
General options:
Parameter where in all methods can be one of:
- Object -
{ id: 12, age: 27 }
- String -
'id = 12 AND age < 27'
(raw format)
Option "where" has "equal" default compare method, but you can use another:
- $in -
{ id: { $in: [1,2,5] } }
- $is -
{ product_id: { $is: null } }
- $isNot -
{ product_id: { $isNot: null } }
- $gt -
{ age: { $gt: 25 } }
- $gte -
{ age: { $gte: 25 } }
- $lt -
{ age: { $lt: 25 } }
- $lte -
{ age: { $lte: 25 } }
- $field -
{ id: { $field: 'another_id' } }
=>'id = another_id'
- $raw -
{ position: { $raw: 'POINT(1,3)' } }
- $val -
{ position: { val: 'hello' } }
You can combine few conditions (they will be combined by AND):
{ age: { $gt: 10, $lte: 25 } }
Also you can use $or and $and:
{ $or: [{ field1: 3 }, { field1: 5 }] }
{ $and: [{ field1: 3 }, { field2: 'hello' }] }
{ $or: [ $or: [{ field1: 3 }, { field1: 5 }], { field2: 'hello' }] }
Note:
{ id: 3 }
and{ id: { $eq: 3 } }
takes same result{ product_id: null }
and{ product_id: { $is: null } }
takes same result- in join.on
{ 't1.id': 't2.user_id' }
will bet1.id = t2.user_id
(field comparison)
Flags
insert and update can take last argument "flags":
ignore: true
for statements like INSERT IGNORE INTO table1 ...