An example of sending JSON from MySQL as chunked content

app.js

var mysql   	= require('mysql');
var express 	= require('express');
var app 		= express();
var controllers	= module.exports.controllers = {};
var models		= module.exports.models = {};

if (!process.env.dbpass){
	app.use(express.logger('dev'));
}

app.set('port', process.env.PORT || 3000);

var dbConfig = module.exports.dbConfig = {
	host     			: 'localhost',
	user     			: process.env.dbuser || 'root',
	password 			: process.env.dbpass || 'password',
	database 			: process.env.dbname || 'mysql',
	insecureAuth		: process.env.dbpass? false : true,
	connectionLimit		: 20,
	supportBigNumbers	: true
};

var pool = module.exports.pool = mysql.createPool( dbConfig );

process.on('exit', function () {
	console.log('Express server exiting.');
});

app.use(function(err, req, res, next) {
	res.send(500, {
		status:500,
		message: 'Error '+e
	});
});

app.get('/', function(req, res){
	models.list_tables(
		function( rv ){ res.json( rv ) }
	);
});

app.get('/:table', function(req, res){
	models.select_all(
		req.params.table,
		function( rv ){ res.json( rv ) }
	);
});

app.get('/XXX:table/*', function(req, res){
	models.select(
		req.params.table,
		req.params.slice(0)[0],
		function( rv ){ res.json( rv ) }
	);
});

app.get('/:table/*', function(req, res){
	res.writeHead(200, {
		'Content-Type': 'application/json',
        'Transfer-Encoding': 'chunked'
	});
	res.write('{"results":[');
    var onRow  = function(chunk) { res.write(chunk) };
	var onEnd   = function() {
		res.write("\n]}");
		res.statusCode = 200;
		res.end();
	};
	var onError = function(err ) {
		res.write( JSON.stringify( err ) );
		res.statusCode = 500;
		res.end();
	};

	models.select(
		req.params.table,
		req.params.slice(0)[0],
		onRow,
		onEnd,
		onError
	);
});




module.exports.server = app.listen( app.get('port') );
console.log('Express server listening on port ' + app.get('port'));


/* Models —  too few for a module */
function formatResponse(rows,err){
	var rv = { results : rows };
	if (err) rv.error = err;
	return rv;
}

function _select(sql,  onRow, onEnd, next){
	pool.getConnection(function(err, dbh) {
	    if (err) next(err);
		else {
			firstRow = true;

			dbh.query( sql )
			.on('error', function(err) {
				dbh.release();
				next(err);
			})
			.on('result', function(row) {
				dbh.pause();
				onRow(
					(!firstRow? ",\n":"")
					 + JSON.stringify(row)
				);
				firstRow = false;
				dbh.resume();
			})
			.on('end', function() {
				dbh.release();
				onEnd();
			});
		}
	});
}

models.select = function(table, params, onRow, onEnd, next, err){
	params = params.split('/'); // URL decode?
	var sql;
	if (params.length==1){
		sql = 'SELECT `'+params[0]+'` FROM `'+table +'`';
	}
	else {
		sql = 'SELECT * FROM `'+table +'` WHERE ';
		for (var i=0; i < params.length; i+=2){
			sql += '`' + params[0] + '` = ' + pool.escape( params[1] );
			if (i < params.length - 2) sql += ' AND ';
		}
	}
	_select(sql, onRow, onEnd, next);
};

models.list_tables = function(next){
	pool.getConnection(function(err, dbh) {
	    if (err) next(err);
		else dbh.query('SHOW TABLES', function(err, rows, fields) {
			dbh.release();
			var rv = formatResponse(rows,err);
			if (next) return next(rv)
			else return rv;
		});
	});
}

models.select_all = function(table, next){
	pool.getConnection(function(err, dbh) {
	    if (err) next(err);
		else dbh.query('SELECT * FROM `'+table+'`', function(err, rows, fields) {
			dbh.release();
			var rv = formatResponse(rows,err);
			if (next) return next(rv)
			else return rv;
		});
	});
}

gistfile1.txt

{
  "name": "express-mysql-chunked",
  "preferGlobal": false,
  "version": "0.0.1",
  "author": "Lee Goddard <[email protected]>",
  "description": "An example of sending JSON from MySQL as chunked content",
  "scripts": {
    "start": "node lib/app.js",
    "test": "mocha"
  },
  "main": "./lib/app.js",
  "dependencies" : {
    "express"   : "3.4.4",
    "mysql" :  "2.0.0-alpha9"
  },
  "analyze": false,
  "devDependencies": {
	"mocha" : "1.14.0",
	"should": "2.1.0"
  }
}

test.js

var should = require('should');
var http = require('http');

describe('app', function(){
	before (function (done) {
		app = require('app');
		done();
	});

	after(function (done) {
		app.server.close();
		app.pool.end( function(){
			console.log('MySQL cx pool terminating');
			done();
		});
	});

	it('should be defined', function (done) {
		should(app).be.type('object');
		done();
	});

	it('should be listening at localhost:3333', function (done) {
		http.get('http://localhost:3000/', function (res) {
			should(res.statusCode).be.equal(200);
			done();
		});
	});

	it('should error for a non-existant table', function (done) {
		http.get('http://localhost:3000/Notable'+(new Date().getTime()), function (res) {
			should(res.statusCode).be.equal(200);
			res.should.be.json;
			res.on('data', function (raw) {
				var body = JSON.parse(raw.toString('utf8'));
				body.should.have.keys('error');
				body.error.should.have.keys('errno', 'code', 'sqlState', 'index');
				body.error.should.have.property('errno');
				body.error.should.have.property('code').equal('ER_NO_SUCH_TABLE');
				done();
			});
		});
	});

	it('should list tables', function (done) {
		http.get('http://localhost:3000/', function (res) {
			should(res.statusCode).be.equal(200);
			res.should.be.json;
			res.on('data', function (raw) {
				var body = JSON.parse(raw.toString('utf8'));
				body.should.not.have.property('error');
				body.should.have.property('results');
				body.results.length.ok;
				body.results[0].should.have.keys('Tables_in_mysql');
				done();
			});
		});
	});

	it('should return wrapped results for an existant table', function (done) {
		http.get('http://localhost:3000/User', function (res) {
			should(res.statusCode).be.equal(200);
			res.should.be.json;
			res.on('data', function (raw) {
				var body = JSON.parse(raw.toString('utf8'));
				body.should.not.have.property('error');
				body.should.have.property('results');
				body.results.length.ok;
				done();
			});
		});
	});

	it('should select by column', function (done) {
		var columnName = 'password_expired';
		testGet('http://localhost:3000/User/'+columnName, function(body){
			body.should.not.have.property('error');
			body.should.have.property('results');
			body.results.length.ok;
			body.results[0].should.be.an.instanceOf( Object );
			body.results[0].should.have.keys( columnName );
			done();
		});
	});

	it('should select by column and column value', function (done) {
		var columnName = 'User';
		var userName = 'root';
		testGet('http://localhost:3000/User/'+columnName+'/'+userName, function(body){
			body.should.not.have.property('error');
			body.should.have.property('results');
			body.results.length.ok;
			body.results[0].should.be.an.instanceOf( Object );
			done();
		});
	});

	it('should select by two column and column value', function (done) {
		testGet('http://localhost:3000/User/User/root/User/root', function(body){
			body.should.not.have.property('error');
			body.should.have.property('results');
			body.results.length.ok;
			body.results[0].should.be.an.instanceOf( Object );
			done();
		});
	});

});



function testGet( uri, callback){
	var req = http.request(uri, function(res) {
		res.setEncoding('utf8');
	});
	req.on('response', function (res) {
		res.should.be.json;
		var raw = "";
		res.on('data', function (chunk) {
			raw += chunk;
		});
		res.on('end', function(){
			raw.should.be.type('string');
			var body = JSON.parse(raw);
			callback(body);
		});
	});
	req.end();
};