coroutine-mysql

Table of contents

• Installation
• Introduction
• Connection pool
• Connections
• Select
• Insert
• Delete
• Update
• Query
• Transaction

Installation

$ npm install coroutine-mysql

Introduction

This module is designed to be used together with "co". The installation for "co" is followed:

$ npm install co

For more information on "co": https://www.npmjs.com/package/co

Here is an example on how to use it:

var mysql = require('coroutine-mysql');
var co = require('co');
co(function*(){
	var connection = yield mysql.createConnection({
		host: 'localhost',
		user: 'me',
		password: 'secret',
		database: 'my_db'
	});
	try
	{
		var results = yield connection.select('show databases');
		console.log(results);
	}
	catch(err)
	{
		console.log(err);
	}
	yield connection.end();
}).then(function(){
	// Some code on success
}).catch(function(err){
	/* Some code on failures
	 * In this case, there are only three statement in the generator function above, 
	 * one of which(i.e. the "select") is inside a try-catch block. Therefore, 
	 * failures catched here can only belong to "createConnection" or "end".
	 */
});

Connection pool

Since the Pool class in this module is a simple wrap on the Pool class in node-mysql, the parameter "options" is compatible.

APIs:

mysql.createPool(options) => Pool; //create a connection pool

Pool.getConnection() => Connection;  //get a connection from the pool
Pool.end() => Pool;  //terminate the pool

To create a connection pool and to terminate one:

co(function*(){
	var pool = yield mysql.createPool({
		host: 'localhost',
		user: 'me',
		password: 'secret',
		database: 'my_db',
		connectionLimit : 10
	});
	yield pool.end();
});

Here's a full example:

var mysql = require('coroutine-mysql');
var co = require('co');
co(function*(){
	var pool = yield mysql.createPool({
		host: 'localhost',
		user: 'me',
		password: 'secret',
		database: 'my_db',
		connectionLimit : 10
	});

	//To get a connection for the pool
	var connection = yield pool.getConnection();
	try
	{
		var sql = "select * from post where id>:id"
		var results = yield connection.select(sql, {id: 2});
		console.log(results);
	}
	catch(err)
	{
		console.log(err);
	}
	yield connection.release();
	yield pool.end();

}).then(function(){
	// Some code on success
}).catch(function(err){
	// Some code on failures
});

Connections

Similar to Pool, parameter "options" is also compatible.

APIs:

mysql.createConnection(options) => Connection;  //create a connection

Connection.end() => Connection;  //end the connection
Connection.release() => Connection;  //release the connection back to the pool
Connection.destroy() => Connection;  //destroy the connection
Connection.select(sqlString, vars) => results;  
Connection.insert(tableName, vars) => inserted id;
Connection.delete(tableName, whereClause, whereVars) => affected rows;
Connection.update(tableName, vars, whereClause, whereVars) => changed rows;
Connection.query(sqlString, vars) => results;
Connection.transaction() => Connection;
Connection.commit() => Connection;
Connection.rollback() => Connection;

To create a connection and to terminate one:

co(function*(){
	var conn = yield mysql.createConnection({
		host: 'localhost',
		user: 'me',
		password: 'secret',
		database: 'my_db'
	});

	// some code using the connection

	yield conn.end();
});

Select

Method "select" in Connection.

APIs:

Connection.select(sqlString, vars) => results; 

Params:

• 'sqlString': a String represents the sql statement with variables
• 'vars': OPTIONAL, a Object of variables

co(function*(){
	var connection = yield mysql.createConnection(options);
	var results = yield connection.select('show databases');
	console.log(results);
	var sql = 'select title from post where id>:post_id';
	results = yield connection.select(sql, {post_id: 2});
	console.log(results);
	yield connection.end();
});

Insert

Method "insert" in Connection.

APIs:

Connection.insert(tableName, vars) => inserted id;

Params:

• 'tableName': the name of the table
• 'vars': a Object of variables to be inserted

co(function*(){
	var connection = yield mysql.createConnection(options);
	var results = yield connection.insert('post', {title: 'new title'});
	console.log('inserted id: ' + results);
	yield connection.end();
});

Delete

Method "delete" in Connection.

APIs:

Connection.delete(tableName, whereClause, whereVars) => affected rows;

Params:

• 'tableName': the name of the table
• 'whereClause': OPTIONAL, a String represents the "WHERE" clause
• 'whereVars': OPTIONAL, a Object of variables for whereClause

co(function*(){
	var connection = yield mysql.createConnection(options);
	var results = yield connection.delete('post', 'id=:id_to_be_delete', {id_to_be_delete: 1});
	console.log('deleted rows: ' + results);
	yield connection.end();
});

Update

Method "update" in Connection.

APIs:

Connection.update(tableName, vars, whereClause, whereVars) => changed rows;

Params:

• 'tableName': the name of the table
• 'vars': a Object of variables to be updated
• 'whereClause': OPTIONAL, a String represents the "WHERE" clause
• 'whereVars': OPTIONAL, a Object of variables for whereClause

co(function*(){
	var connection = yield mysql.createConnection(options);
	var results = yield connection.update('post', 'id=:id_to_be_delete', {id_to_be_delete: 1});
	console.log('changed rows: ' + results);
	yield connection.end();
});

Query

Method "query" in Connection.

APIs:

Connection.query(sqlString, vars) => results;

Params:

• 'sqlString': a String represents the sql statement with variables
• 'vars': OPTIONAL, a Object of variables

co(function*(){
	var connection = yield mysql.createConnection(options);
	var sql = "select * from post where id>(select AVG(id) from post)";
	var results = yield connection.query(sql);
	console.log(results);
	yield connection.end();
});

Transaction

Methods for using transaction.

APIs:

Connection.transaction() => Connection;
Connection.commit() => Connection;
Connection.rollback() => Connection;

co(function*(){
	var connection = yield mysql.createConnection(options);

	yield connection.transaction();
	try
	{
		var lastId = yield connection.insert('post', {title: 'new title'});
		yield connction.delete('post', 'id<:id', {id: lastId - 1});
		yield connection.commit();
	}
	catch(err)
	{
		yield connection.rollback();
	}
	yield connection.end();
});