node-mysql-helper

node-mysql-helper

A lightweight Promise-based wrapper and helper for felixge's node-Mysql.

##Features:

• Very slim library, only 2 dependancies (Q and felixge's node-mysql).
• Convenience functions for record selecting, inserting, updating and insert (on duplicate) updating.
• Connection pooling.
• Everything based on Promises.

##Initialize

Format you connection options based on felixge's options.

var Mysql = require('node-mysql-helper');

var mysqlOptions = {
	host: 'localhost',
	user: 'myuser',
	password: 'chicken',
	database: 'mydb',
	socketPath: false,
	connectionLimit: 5
};

//For 5 pooled connections
Mysql.connect(mysqlOptions);

Selecting a record

//find user id 35
Mysql.record('user', 35)
	.then(function(record){
		console.log(record);
	})
	.catch(function(err){
		console.log('Error fetching record, mysql error:', err.message);
	});

//or select with an object
Mysql.record('user', {id: 35})
	.then(function(record){
		console.log(record);
	})
	.catch(function(err){
		console.log('Error fetching record, mysql error:', err.message);
	});

Inserting a record

var insert = {
	email: '[e-mail]',
	firstName: 'Greg',
	lastName: 'Bate',
	createdAt: '2015-10-07 21-22:31'
};

Mysql.insert('user', insert)
	.then(function(info){
		console.log('New User Entered!', info);
	})
	.catch(function(err){
		console.log('Error creating new user, mysql error:', err.message);
	});

//info is an object with affectedRows and insertId

There is also a boolean 3rd argument, true if you want "INSERT IGNORE"

Updating a record

var where = {
	firstName: 'Greg',
	lastName: 'Bate'
};

var update = {
	lastSeen: '2015-10-07 21-54:58'
};

Mysql.update('user', where, update)
	.then(function(info){
		console.log('User Updated!', info);
	})
	.catch(function(err){
		console.log('Error updating record, mysql error:', err.message);
	});

//info is an object with affectedRows, changedRows

Insert (on duplicate) update a record

Used in case entering data conflicts with a unique index

var insert = {
	email: '[e-mail]',
	firstName: 'Greg',
	lastName: 'Bate',
	lastSeen: '2015-10-07 21-49:58'
};

var update = {
	lastSeen: '2015-10-07 21-49:58'
};

Mysql.insertUpdate('user', insert, update)
	.then(function(info){
		console.log('New User Entered (or updated)!', info);
	})
	.catch(function(err){
		console.log('Error creating new user, mysql error:', err.message);
	});
//info is an object with affectedRows, changedRows and insertId (where applicable)

Custom Queries

Don't forget to release the pooled connection so another process can use it.

//query has sql structure
//values will be placed in the query when escaped, and are optional
Mysql.query(query, values)
	.then(function(results){
		console.log('my query results', results);
	})
	.catch(function(err){
		reject(err);
	});

The query values are used in the same way felixge's module expects it. They are also optional.

Deleting a record

//or select with an object
Mysql.delete('user', {id: 35})
	.then(function(record){
		console.log(record);
	})
	.catch(function(err){
		console.log('Error deleting record, mysql error:', err.message);
	});

Utilities

//Get last query (after escaping values)
var last = Mysql.getLastQuery();

//Escape a database,table or column name
var table = Mysql.escapeId(tableName);

//Escape a string
var noSqlInject = Mysql.escapeId(req.body.firstName);

To Do

• Support ES6 Promises
• Inline documenation
• Some Spelcheking
• Limited convenience functions like YMD and DATETIME Functions
• Compatibility with RDB Select Module
• Make sure values are optional on custom query
• More Testing (heh)