mysql-wrap

node-mysql-wrap

A lightweight wrapper for the node-mysql driver. Providing, select, insert, update, delete, row count, and support for promises.

npm install mysql-wrap

Instantiation

//create a node-mysql connection
var mysql = require('mysql');
var connection =  mysql.createConnection({
    host: 'your-host-name',
    user: 'your-user',
    password: 'your-password',
    database: 'your-database-name'
});
 
//and pass it into the node-mysql-wrap constructor
var createMySQLWrap = require('mysql-wrap');
var sql = createMySQLWrap(connection);

Enable connection pooling by passing a connection pool rather than a connection

//create a node-mysql pool
var mysql = require('mysql');
var pool =  mysql.createPool({
    host: 'your-host-name',
    user: 'your-user',
    password: 'your-password',
    database: 'your-database-name'
});
//and pass it into the node-mysql-wrap constructor
var createMySQLWrap = require('mysql-wrap');
var sql = createMySQLWrap(pool);

Pool Clusters with read write seperation is also supported

var poolCluster = mysql.createPoolCluster({
    canRetry: true,
    removeNodeErrorCount: 1,
    restoreNodeTimeout: 20000,
    defaultSelector: 'RR'
});
 
poolCluster.add('MASTER', {
    connectionLimit: 200,
    host: configuration.database.host,
    port: 3306,
    user: configuration.database.user,
    password: configuration.database.password,
    database: configuration.database.name
});
 
var sql = createNodeMySQL(poolCluster, {
    //uses the same pattern as node-mysql's getConnection patterns
    replication: {
        write: 'MASTER',
        read: '*'
    }
});

Methods

In general node-mysql-wrap exposes the same interface as node-mysql. All methods take callbacks with the same function (err, res) {} signature as node-mysql. In addition all methods also return q promises.

In the following examples, parameters marked with an asterik (*) character are optional.

query(sqlStatement, *values, *callback)

sql.query('SELECT name FROM fruit WHERE color = "yellow"')
.then(function (res) {
    console.log(res);
    //example output: [{ name: "banana" }, { name: "lemon" }]
});

query may take a configuration object in place of the sqlStatement parameter. this object allows for node-mysql's nested table join api, as well as pagination.

sql.query({
    sql: 'SELECT * FROM fruitBasket LEFT JOIN fruit ON fruit.basketID = fruitBasket.id',
    nestTables: true,
    paginate: {
        page: 3,
        resultsPerPage: 15
    }
});

one(sqlStatement, *values, *callback)

Works the same as sql.query except it only returns a single row instead of an array of rows. Adds a "LIMIT 1" clause if a LIMIT clause is not allready present in the sqlStatement.

select(table, *whereEqualsObject, *callback)

// equivalent to sql.query('SELECT * FROM fruit WHERE color = "yellow" AND isRipe = "true"')
sql.select('fruit', { color: 'yellow', isRipe: true })

selectOne(table, *whereEqualsObject, *callback)

Same as sql.select except selectOne returns a single row instead of an array of rows.

select and selectOne may take a configuration object in place of the table parameter. The configuration object add pagination and/or restrict which fields are selected.

sql.select({
    table: 'fruit',
    fields: ['color'],
    paginate: {
        page: 2,
        resultsPerPage: 15
    }
});

insert(table, insertObject, *callback)

sql.insert('fruit', { name: 'plum', color: 'purple' });

You can also pass sql.insert an array of insertObjects to insert multiple rows in a query

sql.insert('fruit', [
    { name: 'plum', color: 'purple'},
    { name: 'grape', color: 'green' }
])

replace(table, insertObject, *callback)

Supports Mysql "REPLACE INTO" syntax

sql.replace('fruit', { uniqueKey: 5, name: 'plum', isRipe: false, color: 'brown' });

save(table, insertObject, *callback)

Inserts a new row if no duplicate unique or primary keys are found, else it updates that row.

INSERT INTO fruit (uniqueKey, isRipe) VALUES (5, 0)
ON DUPLICATE KEY UPDATE uniqueKey=5, isRipe=0

sql.save('fruit', { uniqueKey: 5, isRipe: false });

update(table, setValues, *whereEqualsObject, *callback)

sql.update('fruit', { isRipe: false }, { name: 'grape' })

delete(table, *whereEqualsObject, *callback)

sql.delete('fruit', { isRipe: false })

Errors

Errors are the first parameter of a methods callback (same as in node-mysql), or using promises they are passed to the catch method

sql.insert('fruit', { name: 'banana' })
.catch(function (err) {
 
});

Error objects are wrapped in a custom Error object. A reference to this object can be gotten at sql.Error

Transactions

The node-mysql transaction methods beginTransaction, commit, and rollback are available, and return promises as well as take callbacks.

sql.beginTransaction()
.then(function () {
    return sql.insert(...)
 
})
.then(function () {
    sql.commit();
})
.catch(function (err) {
    return sql.rollback(function (err) {
        throw err;
    });
});

Other methods.

end, destroy, release, changeUser

same as in node-mysql. end and changeUser return a promise as well as taking a callback.