sql-wrap

Makes working with sql easy

npm install sql-wrap

Instantiation

let pool = mysql.createPool(config.mysql); that.sql = createNodeMySQL(pool);

var sql = createNodeMySQL(mysql.createPool({
    host: config.mysql.host,
    user: config.mysql.user,
    password: config.mysql.password,
}));

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: config.mysql.host,
    port: 3306,
    user: config.mysql.user,
    password: config.mysql.password,
    database: config.mysql.database
});

poolCluster.add('SLAVE_1', {
    connectionLimit: 200,
    host: config.mysql.host,
    port: 3307,
    user: config.mysql.user,
    password: config.mysql.password,
    database: config.mysql.database
});

var sql = createNodeMySQL(poolCluster, {
    //uses the same pattern as node-mysql's getConnection patterns
    replication: {
        write: 'MASTER',
        read: 'SLAVE_*'
    }
});

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)

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
  }
});

queryStream(sqlStatement, *values)

sql.queryStream('SELECT name FROM fruit WHERE color = "yellow"')
.then(function (stream) {
    stream.on('data', row => {
        console.log(row);
        //example output: { name: "banana" }
    });


    stream.on('end', () => {
        console.log('end of stream';)
    });
});

one(sqlStatement, *values)

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)

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

selectStream(table, *whereEqualsObject)

sql.selectStream('fruit')
.then(function (stream) {
    stream.on('data', row => {
        console.log(row);
        //example output: { name: "banana" }
    });

    stream.on('end', () => {
        console.log('end of stream';)
    });
});

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 })