namshi-node-mysql

node-mysql

This wrapper provides some enhancements for node-mysql2

Installation

This module can be installed with either yarn or npm:

$ yarn add namshi-node-mysql

$ npm install namshi-node-mysql --save

Example Usage of query

query() uses prepared statements but does not support bulk operations.

 
let config = {
    host: "localhost",
    user: "foo",
    password: "bar",
    database: "db"
}
 
let db = require('namshi-node-mysql')(config);
 
db.query('UPDATE foo SET key = ?', ['value']).then(() => {
    return db.query('SELECT * FROM foo');
}).spread(rows => {
    console.log('Look at all the foo', rows);
});
 
// using multiple databases, you can add a "name" key to your config object. For example:
let config = {
    name: "second-db",
    host: "localhost",
    user: "foo",
    password: "bar",
    database: "db"
}
 
let db2 = require('namshi-node-mysql')(config);
 
db2.query('SELECT * FROM users').spread(users => {
    console.log('Hello users', users);
});
 
 

Enable DEBUG mode to log the query being executed and its parameters.

// You can enable debugging by passing the `debug` parameter as follow:
// by default it is set to false.
 
let config = {
    host: "localhost",
    user: "foo",
    password: "bar",
    database: "db",
    debug: true;
}

Example Usage of bulk

bulk() uses execute which supports prepared statements and we use prepared statements for bulk.

 
let config = {
    host: "localhost",
    user: "foo",
    password: "bar",
    database: "db"
}
 
var values = [
    ['demian', '[e-mail]', 1],
    ['john', '[e-mail]', 2],
    ['mark', '[e-mail]', 3],
    ['pete', '[e-mail]', 4]
];
let db = require('namshi-node-mysql')(config);
 
db.bulk('INSERT INTO foo (name, email, n) VALUES ?', values).then(() => {
    return db.query('SELECT * FROM foo');
}).spread(rows => {
    console.log('Look at all the foo', rows);
});
 

Example of prepareBulk

prepareBulk() can be used if you want to format a query for bulk operation with a connection reused for a transaction.

 
let config = {
    host: "localhost",
    user: "foo",
    password: "bar",
    database: "db"
}
 
var values = [
    ['demian', '[e-mail]', 1],
    ['john', '[e-mail]', 2],
    ['mark', '[e-mail]', 3],
    ['pete', '[e-mail]', 4]
];
let db = require('namshi-node-mysql')(config);
let connection;
 
db.startTransaction().then(conn => {
    connection = conn;
}).then(() => {
    let [query, params] = db.prepareBulk('INSERT INTO foo (name, email, n) VALUES ?', [values]);
    return connection.execute(query, params);
}).then(result => {
    return db.commit(connection);
}).then(result => {
    console.log('Rows committed');
}).catch(err => {
    db.rollback(connection).then(result => {
        console.log('Rollback executed due to ', err.message);
    });
})

Example usage of namedPlaceholders

let config = {
    host: "localhost",
    user: "foo",
    password: "bar",
    database: "db",
    namedPlaceholders: true
}
 
let db = require('namshi-node-mysql')(config);
 
db.query('SELECT * FROM users WHERE LIMIT = :limit', {limit: 10}).spread( users => {
    console.log('Hello users', users);
});
 

Example usage of startTransaction, commit and rollback

let config = {
    host: "localhost",
    user: "foo",
    password: "bar",
    database: "db"
}
 
let db = require('namshi-node-mysql')(config);
 
let connection;
 
db.startTransaction(30).then(conn => {
    connection = con;
}).catch(err) {
    //handle error
};
 
//default timeout here is set to 20
db.startTransaction().then(conn => {
    connection = con;
}).catch(err) {
    //handle error
};
 
db.commit(connection).catch(err => {
    //handle err
});
 
db.rollback(connection).catch(err => {
    //handle err
});

Credits

This library depends on node-mysql2. It is also considered a breaking-change upgrade of node-mysql2-promise.