Mysql-easy NPM

MySQL driver wrapper for easy work (with promises)

Initial:

const mysqlEasy = require('mysql-easy');

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.createPool({
    host:     'localhost',
    database: 'test',
    user:     'root',
    password: ''
});

Method "wrap":

const db = mysqlEasy.wrap(mysql.createPool(...));

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.format('SELECT * FROM ?? WHERE id = ?', ['users', 12])
> "SELECT * FROM `users` WHERE id = '12'"

Method "escapeId":

id: string

mysqlEasy.escapeId('myTableName')
> "`myTableName`"

Method "escape":

value: string

mysqlEasy.escape('myValue')
> '"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.query('SELECT * FROM `users` WHERE `id` = ?', [12])
    .then(results => ...).catch(err => ...);

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.select({
    table: 'users',
    fields: ['id', 'user_name'],
    where: { id: 12 },
    order: { id: -1 }
}).then(results => ...);

Method "selectOne":

params: Object
- table: string
- fields: Array|Object|string (optional)
- where: Object|string (optional)
- order: Object|string (optional)

db.selectOne({
    table: 'users',
    fields: { id: 'id', userName: 'user_name' },
    where: { id: 12 }
}).then(user => {
    console.log(user.id);
    console.log(user.userName);
});

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.selectExactOne({
    table: 'users',
    fields: ['id', 'user_name'],
    where: { id: 12 }
}).catch(err => {
    console.error(err);
});

Method "insert":

tableName: string
data: Object
flags: Object (optional)

db.insert('users', {
    user_name: 'user#331',
    age: 30
}).then(result => {
    console.log('New user ID: %s', result.insertId);
});

Method "update":

tableName: string
data: Object
where: Object|string (optional)
flags: Object (optional)

db.update('users', newUserState, {
    id: 12
}).then(...);

Method "delete" or "deleteFrom":

tableName: string
where: Object|string

db.delete('users', { id: 12 }).then(...);

Method "end" or "close":

db.end();

Method "unwrap":

const underlyingMysqlConnection = db.unwrap();

Transactions:

Method "createTransaction"

returns MySQL Promise with like instance with 2 additional methods: "commit" and "rollback"

db.createTransaction().then(transaction => {
    transaction.update(...).then(() => {
        return transaction.insert(...);
    }).then(() => {
        return transaction.commit();
    }).catch(err => {
        transaction.rollback();
        console.log('Something going wrong', err);
    });
});

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 is ORDER 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 be t1.id = t2.user_id (field comparison)