MySQL

NodeJS code to handle App logins via API (using MySQL connection pools (1000 connections) and query parameters)

August 7, 2017 by Leave a Comment

This code is part of what I use to handle logins in an app. You can use this as a base for a NodeJS login routine (in a file called app.js).

NodeJS code to handle App logins via API (using MySQL connection pools (1000 connections) and query parameters)

August 7, 2017 by su Leave a Comment

The below code is part of what I use to handle logins in an app. You can use this as a base for a NodeJS login routine (in a file called app.js). I was going to use StormPath API but was too slow and unreliable for (as it turns out StormPath are merging with Okta and are closing down services).

This code is not functionally complete but you can hack out what you need. Rolling your own solution can be faster and slow you to fully embed in server side logic but make sure it is secure.
I am using these node modules (app.js).

var app = require('express')();
var http = require('http').Server(app);
var mysql = require('mysql');
var uuid = require('node-uuid');
var Type = require('type-of-is');
var jsesc = require('jsesc');
var bcrypt = require('bcrypt');
var filessystem = require('fs');

var app = require('express')();

var http = require('http').Server(app);

var mysql = require('mysql');

var uuid = require('node-uuid');

var Type = require('type-of-is');

var jsesc = require('jsesc');

var bcrypt = require('bcrypt');

var filessystem = require('fs');

The following code is located in myutils.js is a handy place to place your common code. You don’t need it but it is demonstrated here.

module.exports = function(){ 
this.sum = function(a,b){
    return a+b
};
this.multiply = function(a,b){
    return a*b
    
  };
this.samplefunction = function(a){
    return 'Hello World'
    
  };
}

module.exports = function(){

this.sum = function(a,b){

return a+b

};

this.multiply = function(a,b){

return a*b

};

this.samplefunction = function(a){

return 'Hello World'

};

}

Don’t forget to include it myutils.js in app.js

require('./myutils.js')();  //Remove this if you dont use it

1	require('./myutils.js')(); //Remove this if you dont use it

Declare the body parser module (app.js).

app.use(bodyParser.urlencoded({ extended: false }));
app.use(bodyParser.json());

1 2	app.use(bodyParser.urlencoded({ extended: false })); app.use(bodyParser.json());

Set the MySQL connection details and you can setup the number of ready MySQL connections in the pool here (app.js).

console.log(' mysql_pool.createPool()');
var mysql_pool  = mysql.createPool({
  connectionLimit : 1000,
  host            : 'localhost',
  database        : 'thedatabasename',
  user            : 'thedatabaseuser',
  password        : 'thedatabasepassword'
  
});

console.log(' mysql_pool.createPool()');

var mysql_pool = mysql.createPool({

connectionLimit : 1000,

host : 'localhost',

database : 'thedatabasename',

user : 'thedatabaseuser',

password : 'thedatabasepassword'

});

Create a static API endpoint (app.js).

// Generic static API Point
app.get('/api/myappname/v1/hello/world/',function(req,res){
	var data = { "Version":"" };
	data["Version"] = "Hello World"; // Add a JSON value
	res.json(data); // Return the JSON
});

// Generic static API Point

app.get('/api/myappname/v1/hello/world/',function(req,res){

var data = { "Version":"" };

data["Version"] = "Hello World"; // Add a JSON value

res.json(data); // Return the JSON

});

Create MySQL functions (app.js).

mysql_pool.getConnection(function(err, connection){
	// See if a connection is available in the pool by querying the database
	console.log(' mysql_pool.getConnection()');
	  	if (err) {
	  		console.log(' error: ' + err);
	  		throw err;
	  	}
	connection.query('select * from thedatabasename;',  function(err2, rows){
	  	if (err2) {
	  		connection.release();
	  		console.log(' error: ' + err2);
	  		throw err2;
	  	} else {
	  		console.log( rows );
	  		console.log('Ready');
	  	}
	  	connection.release();
	});
	console.log(' mysql_pool.release()');
});

mysql_pool.on('connection', function (connection) {
  connection.query('SET SESSION auto_increment_increment=1')
  console.log(' mysql_pool.on(connection) - Set session increment');
});

mysql_pool.on('enqueue', function () {
  console.log(' mysql_pool.on(enqueue). Waiting for available connection slot.');
});

mysql_pool.getConnection(function(err, connection){

// See if a connection is available in the pool by querying the database

console.log(' mysql_pool.getConnection()');

if (err) {

console.log(' error: ' + err);

throw err;

}

connection.query('select * from thedatabasename;', function(err2, rows){

if (err2) {

connection.release();

console.log(' error: ' + err2);

throw err2;

} else {

console.log( rows );

console.log('Ready');

}

connection.release();

});

console.log(' mysql_pool.release()');

});

mysql_pool.on('connection', function (connection) {

connection.query('SET SESSION auto_increment_increment=1')

console.log(' mysql_pool.on(connection) - Set session increment');

});

mysql_pool.on('enqueue', function () {

console.log(' mysql_pool.on(enqueue). Waiting for available connection slot.');

});

/api/myappname/v1/login function (app.js).

// Login ----------------------------------------------------------------------- 
app.post('/api/myappname/v1/login',function(req,res){
	console.log('API CALL: /api/myappname/v1/login');

	var errData = "";

	// Check to see if the users posted token is valid
	var useraccesstoken = req.headers['x-user-access-token'];
	if (appaccesstoken == undefined) {
		// If a user token is missng then block the login, we only process logins whre valid user tokens are posted. Users are generated a token at acount creation
		console.log(" 498 token required");
		errData = {"Status": "498", "Error": "Invalid user token. Please login again (error 498)."};
		res.statusCode = 499;
		res.send(errData);
		res.end();
	}

	// I usually send an x-access-token in the posted JSON payload to eliminate older apps.
	var appaccesstoken = req.headers['x-access-token'];
	if (appaccesstoken == undefined) {
		// If an app  token is missng then block the login, we only process logins whre valid app tokens are posted.
		console.log(" 499 token required");
		errData = {"Status": "499", "Error": "Invalid app token. Please update your app (error 499)."};
		res.statusCode = 499;
		res.send(errData);
		res.end();

	} else {
		// ok we have a token posted
		var appaccesstokenpassed = false;

		// Check the API Key against known API Keys
		if (appaccesstoken == "appname33ffda6d-4303-4c65-81ca-0ccf0f172a37") { appaccesstokenpassed = true;} // Debug API Key for ICS 0.9.1
		
		// OK Process the posted data
		if (appaccesstokenpassed == true) {
			
			var userenteredemail = req.body.email;
			var userenteredpass = req.body.password; //todo: Add Encryption/Decryption (currenty a BCrypt Hash is sent from the app)

			// If this is Yes a new session token is sent to the user later, this will in valitae older logged in sessions.
			var resetsessions = req.body.resetsessions;
			if (req.body.resetsessions == undefined) {
				resetsessions = "Yes";
			}
			
			// Log for debugging
			console.log(' Email: ' + userenteredemail);
			console.log(' Password: ' + pass);
			console.log(' Reset Sessions: ' + resetsessions);
			
			var data = {}
			
			mysql_pool.getConnection(function(err, connection) {
			if (err) {
				console.log(' mysql_pool.release()');
				connection.release();
		  		console.log(' Error getting mysql_pool connection: ' + err);
		  		throw err;
		  	}
		  		// Revised SQL
		  		connection.query("SELECT * FROM usertablename WHERE usertablename.active=1 AND usertablename.lockedout=0 AND usertablename.email=? LIMIT 1",[userenteredemail],function(err, rows, fields){
				
					if (err) {

						// Log database errors
						console.log(" 503 Service unavailable (database error)");
						errData = {"Status": "503", "Error": "Service unavailable (database error)"};
						res.statusCode = 503;
						res.send(errData);	
						res.end();

					} else {
						
						// Do we have  a recordset
						if (rows.length != 0) {
							
							// Check password hash - https://github.com/ncb000gt/node.bcrypt.js
							var salt = rows[0].password_salt;
							var hash = rows[0].password_hash; 		// bcrypt.hashSync(pass, salt);
							
							hash = hash.replace(/^\$2y(.+)$/i, '\$2a$1');
							var passwordok = false;
							console.log(' About to check password.');
							
							passwordok = bcrypt.compareSync(userenteredpass, hash);

							console.log(passwordok);
							if (passwordok == true) {
							
							// Allow access to records
								var userid = rows[0].id;									// User ID
								var user_guid = rows[0].user_guid;							// Internal App Guid
								var push_guid = rows[0].push_guid;							// Internal GUID user for Push Notifications
								
								
								var newkey = rows[0].usertokenkey;								// Generate a new API secret key
								var newpass = rows[0].usertokenpassword;								// Generate a a new API secret pass

								// This will create two guids to use for all future api hiots from the user
								if (resetsessions == "Yes") {
									newkey = uuid.v4();										// Generate a new API secret key
									newpass = uuid.v4();									// Generate a a new API secret pass
								}

								var stat_total_logins = rows[0].stat_total_logins;			// Read total Logins are stored in my usedatabase
									stat_total_logins = stat_total_logins + 1;				// Increment total logins for later saving 
								
								var username = rows[0].username;							// Read the user Username from the database
								var usertag = rows[0].usertagline;							// Read the user Tagling from the database
								var email = rows[0].email;									// Read the user Email from the database
								
								var block_transactions = rows[0].block_transactions;		// Has the User Blocked Transactions
								var credit = rows[0].credit;								// Credit Remaining
								var currency = rows[0].currency;							// Users Currency (used for the log)

								var smssendalertatlogin = rows[0].user_pref_sms_alert_at_login;  	// Does the user want SMS Sent a Login
								var emailsendalertatlogin = rows[0].user_pref_email_alert_at_login; 	// Does the user want an email
								var pushsendalertatlogin = rows[0].user_pref_push_alert_at_login;	// Push alert the user at login
								
								var user_pref_timezone = rows[0].user_pref_timezone;
								var user_pref_timezone_int = rows[0].user_pref_timezone_int;
								
								var stat_total_password_resets = rows[0].stat_total_password_resets;		// Pass this throgh to the App
								
								var active = rows[0].active;								// Is the user active
								var lockedout = rows[0].lockedout;							// Is the user locked ut
								var changing_password = rows[0].changing_password;

								var querylimit5min = rows[0].QueryLimit5Min;				// Read from he databse how manu API hits the user has a minute
								
								// I like to return the api version and version int with all api JSON payloads (makes checking on the App side easier)
								var jsonvalid = "yes";
								var jsonversion = "2017.01.01.22.00";
								var jsonversionint = "102";
								
								//Update API Keys
								mysql_pool.getConnection(function(err, connection) {
									if (err) {
										console.log(' mysql_pool.release()');
										connection.release();
								  		console.log(' Error getting mysql_pool connection: ' + err);
								  		throw err;
								  	}
								  	// Update the users logins and new keys if thye asked to wipe all past logins.
									connection.query('UPDATE usertablename SET usertokenkey = ?, usertokenpassword = ?, stat_total_logins = ?, last_login = NOW() WHERE id = ?', [newkey, newpass, stat_total_logins, userid],function(errupdate, rowsupdate, fieldsupdate){
										if (errupdate) {
											console.log(" 503 service unavailable (database update error)");
											errData = {"Status": "503", "Error": "503 service unavailable (database update error)"};
											res.statusCode = 503;
											res.send(errData);
											res.end();
										} else {
											if (rowsupdate.length != 0) {
												// Inform User of Valid  Login
												res.statusCode = 200;
												data["Data"] = "Successfully logged in..";									// statis string thta reports the successfull login
												
												// The Key and Pass are used liek OAUTH tokens and are stored in the app and user database. All API hits check these tokens. 
												data["Key"] = newkey;                       								//LOADS IN XCODE v0.9.1
												data["Pass"] = newpass;                      								//LOADS IN XCODE v0.9.1
												
												// User guids are used to store the unique user rather than the database record ID
												data["user_guid"] = user_guid;
												data["push_guid"] = push_guid;												// Internal GUID user for Push Notifications

												data["Logins"] = stat_total_logins;											// Total Logins are stored in my usedatabase
												data["Username"] = username;												// Username
												data["Email"] = email;														// Email
												
												// The notify user code ay ogin has been removed byt can be called here
												data["SMSAtLogin"] = smssendalertatlogin;
												data["EmailAtLogin"] = emailsendalertatlogin;
												data["PushAtLogin"] = pushsendalertatlogin;
												
												// What is the users timezome
												data["timezone"] = user_pref_timezone;
												data["timezoneint"] = user_pref_timezone_int;
												
												
												data["stat_total_password_resets"] = stat_total_password_resets;			// How many times has the user changed passwords
												
												data["active"] = active;													// Is the user active
												data["lockedout"] = lockedout;												// Is th user currently locked out
												data["changing_password"] = changing_password;								// Is the user currebtky changing a password
												
												data["levelquerylimit5min"] = querylimit5min;								// How any times can the user hit the API (handled by the app)
	
												data["jsonvalid"] = jsonvalid;												// JSON ID
												data["jsonversion"] = jsonversion;											// JSON Version
												data["jsonversionint"] = jsonversionint;									// JSON Version
												
												// Send the JSON back to the user
												res.json(data);
												res.end();
												
												//Login Confirmed
												console.log("User " + username + " / " + email + " logged in.");
												
											} else {
												console.log(" 504 service unavailable (database update error)");
												errData = {"Status": "504", "Error": "504 service unavailable (database update error)"};
												res.statusCode = 504;
												res.send(errData);
												res.end();		
											}
										}
										console.log(' mysql_pool.release()');
										connection.release();				// release the MySQL connection pool connection
									});	
								});
								
							} else {
								console.log('402 Invalid Login (' +  req.body.email + ', ' + req.body.password + ')');
								errData = {"Status": "402", "Error": "Invalid Login"};
								res.statusCode = 402;
								res.send(errData);
								res.end();								
							}
	
						} else {
							console.log('403 Account is locked out, not active or unknown. Please visit https://www.myappname.com to reactivate your account. (' +  req.body.email + ')');
							errData = {"Status": "403", "Error": "Account is locked out, not active or unknown."};
							res.statusCode = 403;
							res.send(errData);
							res.end();	
						}
					}
					console.log(' mysql_pool.release()');
					connection.release();
					
				});
			});

		} else {
			console.log("499 token required");
			errData = {"Status": "499", "Error": "Invalid app token. Please update your app (error 499)."};
			res.statusCode = 409;
			res.send(errData);	
			res.end();
		}
	}
});

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

// Login -----------------------------------------------------------------------

app.post('/api/myappname/v1/login',function(req,res){

console.log('API CALL: /api/myappname/v1/login');

var errData = "";

// Check to see if the users posted token is valid

var useraccesstoken = req.headers['x-user-access-token'];

if (appaccesstoken == undefined) {

// If a user token is missng then block the login, we only process logins whre valid user tokens are posted. Users are generated a token at acount creation

console.log(" 498 token required");

errData = {"Status": "498", "Error": "Invalid user token. Please login again (error 498)."};

res.statusCode = 499;

res.send(errData);

res.end();

}

// I usually send an x-access-token in the posted JSON payload to eliminate older apps.

var appaccesstoken = req.headers['x-access-token'];

if (appaccesstoken == undefined) {

// If an app token is missng then block the login, we only process logins whre valid app tokens are posted.

console.log(" 499 token required");

errData = {"Status": "499", "Error": "Invalid app token. Please update your app (error 499)."};

res.statusCode = 499;

res.send(errData);

res.end();

} else {

// ok we have a token posted

var appaccesstokenpassed = false;

// Check the API Key against known API Keys

if (appaccesstoken == "appname33ffda6d-4303-4c65-81ca-0ccf0f172a37") { appaccesstokenpassed = true;} // Debug API Key for ICS 0.9.1

// OK Process the posted data

if (appaccesstokenpassed == true) {

var userenteredemail = req.body.email;

var userenteredpass = req.body.password; //todo: Add Encryption/Decryption (currenty a BCrypt Hash is sent from the app)

// If this is Yes a new session token is sent to the user later, this will in valitae older logged in sessions.

var resetsessions = req.body.resetsessions;

if (req.body.resetsessions == undefined) {

resetsessions = "Yes";

}

// Log for debugging

console.log(' Email: ' + userenteredemail);

console.log(' Password: ' + pass);

console.log(' Reset Sessions: ' + resetsessions);

var data = {}

mysql_pool.getConnection(function(err, connection) {

if (err) {

console.log(' mysql_pool.release()');

connection.release();

console.log(' Error getting mysql_pool connection: ' + err);

throw err;

}

// Revised SQL

connection.query("SELECT * FROM usertablename WHERE usertablename.active=1 AND usertablename.lockedout=0 AND usertablename.email=? LIMIT 1",[userenteredemail],function(err, rows, fields){

if (err) {

// Log database errors

console.log(" 503 Service unavailable (database error)");

errData = {"Status": "503", "Error": "Service unavailable (database error)"};

res.statusCode = 503;

res.send(errData);

res.end();

} else {

// Do we have a recordset

if (rows.length != 0) {

// Check password hash - https://github.com/ncb000gt/node.bcrypt.js

var salt = rows[0].password_salt;

var hash = rows[0].password_hash; // bcrypt.hashSync(pass, salt);

hash = hash.replace(/^\$2y(.+)$/i, '\$2a$1');

var passwordok = false;

console.log(' About to check password.');

passwordok = bcrypt.compareSync(userenteredpass, hash);

console.log(passwordok);

if (passwordok == true) {

// Allow access to records

var userid = rows[0].id; // User ID

var user_guid = rows[0].user_guid; // Internal App Guid

var push_guid = rows[0].push_guid; // Internal GUID user for Push Notifications

var newkey = rows[0].usertokenkey; // Generate a new API secret key

var newpass = rows[0].usertokenpassword; // Generate a a new API secret pass

// This will create two guids to use for all future api hiots from the user

if (resetsessions == "Yes") {

newkey = uuid.v4(); // Generate a new API secret key

newpass = uuid.v4(); // Generate a a new API secret pass

}

var stat_total_logins = rows[0].stat_total_logins; // Read total Logins are stored in my usedatabase

stat_total_logins = stat_total_logins + 1; // Increment total logins for later saving

var username = rows[0].username; // Read the user Username from the database

var usertag = rows[0].usertagline; // Read the user Tagling from the database

var email = rows[0].email; // Read the user Email from the database

var block_transactions = rows[0].block_transactions; // Has the User Blocked Transactions

var credit = rows[0].credit; // Credit Remaining

var currency = rows[0].currency; // Users Currency (used for the log)

var smssendalertatlogin = rows[0].user_pref_sms_alert_at_login; // Does the user want SMS Sent a Login

var emailsendalertatlogin = rows[0].user_pref_email_alert_at_login; // Does the user want an email

var pushsendalertatlogin = rows[0].user_pref_push_alert_at_login; // Push alert the user at login

var user_pref_timezone = rows[0].user_pref_timezone;

var user_pref_timezone_int = rows[0].user_pref_timezone_int;

var stat_total_password_resets = rows[0].stat_total_password_resets; // Pass this throgh to the App

var active = rows[0].active; // Is the user active

var lockedout = rows[0].lockedout; // Is the user locked ut

var changing_password = rows[0].changing_password;

var querylimit5min = rows[0].QueryLimit5Min; // Read from he databse how manu API hits the user has a minute

// I like to return the api version and version int with all api JSON payloads (makes checking on the App side easier)

var jsonvalid = "yes";

var jsonversion = "2017.01.01.22.00";

var jsonversionint = "102";

//Update API Keys

mysql_pool.getConnection(function(err, connection) {

if (err) {

console.log(' mysql_pool.release()');

connection.release();

console.log(' Error getting mysql_pool connection: ' + err);

throw err;

}

// Update the users logins and new keys if thye asked to wipe all past logins.

connection.query('UPDATE usertablename SET usertokenkey = ?, usertokenpassword = ?, stat_total_logins = ?, last_login = NOW() WHERE id = ?', [newkey, newpass, stat_total_logins, userid],function(errupdate, rowsupdate, fieldsupdate){

if (errupdate) {

console.log(" 503 service unavailable (database update error)");

errData = {"Status": "503", "Error": "503 service unavailable (database update error)"};

res.statusCode = 503;

res.send(errData);

res.end();

} else {

if (rowsupdate.length != 0) {

// Inform User of Valid Login

res.statusCode = 200;

data["Data"] = "Successfully logged in.."; // statis string thta reports the successfull login

// The Key and Pass are used liek OAUTH tokens and are stored in the app and user database. All API hits check these tokens.

data["Key"] = newkey; //LOADS IN XCODE v0.9.1

data["Pass"] = newpass; //LOADS IN XCODE v0.9.1

// User guids are used to store the unique user rather than the database record ID

data["user_guid"] = user_guid;

data["push_guid"] = push_guid; // Internal GUID user for Push Notifications

data["Logins"] = stat_total_logins; // Total Logins are stored in my usedatabase

data["Username"] = username; // Username

data["Email"] = email; // Email

// The notify user code ay ogin has been removed byt can be called here

data["SMSAtLogin"] = smssendalertatlogin;

data["EmailAtLogin"] = emailsendalertatlogin;

data["PushAtLogin"] = pushsendalertatlogin;

// What is the users timezome

data["timezone"] = user_pref_timezone;

data["timezoneint"] = user_pref_timezone_int;

data["stat_total_password_resets"] = stat_total_password_resets; // How many times has the user changed passwords

data["active"] = active; // Is the user active

data["lockedout"] = lockedout; // Is th user currently locked out

data["changing_password"] = changing_password; // Is the user currebtky changing a password

data["levelquerylimit5min"] = querylimit5min; // How any times can the user hit the API (handled by the app)

data["jsonvalid"] = jsonvalid; // JSON ID

data["jsonversion"] = jsonversion; // JSON Version

data["jsonversionint"] = jsonversionint; // JSON Version

// Send the JSON back to the user

res.json(data);

res.end();

//Login Confirmed

console.log("User " + username + " / " + email + " logged in.");

} else {

console.log(" 504 service unavailable (database update error)");

errData = {"Status": "504", "Error": "504 service unavailable (database update error)"};

res.statusCode = 504;

res.send(errData);

res.end();

}

console.log(' mysql_pool.release()');

connection.release(); // release the MySQL connection pool connection

});

} else {

console.log('402 Invalid Login (' + req.body.email + ', ' + req.body.password + ')');

errData = {"Status": "402", "Error": "Invalid Login"};

res.statusCode = 402;

res.send(errData);

res.end();

}

} else {

console.log('403 Account is locked out, not active or unknown. Please visit https://www.myappname.com to reactivate your account. (' + req.body.email + ')');

errData = {"Status": "403", "Error": "Account is locked out, not active or unknown."};

res.statusCode = 403;

res.send(errData);

res.end();

}

console.log(' mysql_pool.release()');

connection.release();

});

} else {

console.log("499 token required");

errData = {"Status": "499", "Error": "Invalid app token. Please update your app (error 499)."};

res.statusCode = 409;

res.send(errData);

res.end();

}

});

/api/myappname/v1/verifylogin verify login function (app.js).

// Verify Login ----------------------------------------------------------------------- 
app.post('/api/myappname/v1/verifylogin',function(req,res){
	console.log('API CALL: /api/myappname/v1/verifylogin');
	
	// check to see if the App included an api key
	var appaccesstoken = req.headers['x-access-token'];

	var errData = "";
	if (appaccesstoken == undefined) {
		console.log(" 499 token required");
		errData = {"Status": "499", "Error": "valid token required"};
		res.statusCode = 499;
		res.send(errData);
		res.end();

	} else {

	//console.log(" Received appaccesstoken: " + appaccesstoken);
		var appaccesstokenpassed = false;
		// Check the API Key against known API Keys
		if (appaccesstoken == "ics201701-1d95-9271-a6c7-a9a33770287") { appaccesstokenpassed = true;} // Debug API Key
		if (appaccesstokenpassed == true) {
			var key = req.body.key;
			var pass = req.body.pass;
			var data = {} 

			console.log(" - Key: " + key )
			console.log(" - Pass: " + pass )
			
			// Grab the user IP and log it
			var ip = req.headers['x-forwarded-for'] || req.connection.remoteAddress;
			console.log(" - IP: " + ip );
			
			mysql_pool.getConnection(function(err, connection) {
			if (err) {
				console.log(' mysql_pool.release()');
				connection.release();
		  		console.log(' Error getting mysql_pool connection: ' + err);
		  		throw err;
		  	}
		  	// Check the user record (only if the user is active and provided thier token key and passowrd)
		    connection.query("SELECT * FROM usertablename WHERE active=1 AND usertokenkey=? AND usertokenpassword=? LIMIT 1",[key, pass],function(err, rows, fields){
				if (err) {
					console.log(" 405 Login Session Expired");
					errData = {"Status": "405", "Error": "Login Session Expired"};
					res.statusCode = 405;
					res.send(errData);	
					res.end();
				} else {
					if (rows.length != 0) {
						console.log(" 200 Login Session OK");
						res.statusCode = 200;
						data["Status"] = "OK";
						res.json(data);
						res.end();
					} else {
						console.log(" 405 Login Session Expired");
						errData = {"Status": "405", "Error": "Login Session Expired"};
						res.statusCode = 405;
						res.send(errData);	
						res.end();
					}
				}
				console.log(' mysql_pool.release()');
				connection.release();
			});
			});
		} else {
			console.log("499 token required");
			errData = {"Status": "499", "Error": "valid token required"};
			res.statusCode = 409;
			res.send(errData);	
			res.end();
		}
	}
});

// Verify Login -----------------------------------------------------------------------

app.post('/api/myappname/v1/verifylogin',function(req,res){

console.log('API CALL: /api/myappname/v1/verifylogin');

// check to see if the App included an api key

var appaccesstoken = req.headers['x-access-token'];

var errData = "";

if (appaccesstoken == undefined) {

console.log(" 499 token required");

errData = {"Status": "499", "Error": "valid token required"};

res.statusCode = 499;

res.send(errData);

res.end();

} else {

//console.log(" Received appaccesstoken: " + appaccesstoken);

var appaccesstokenpassed = false;

// Check the API Key against known API Keys

if (appaccesstoken == "ics201701-1d95-9271-a6c7-a9a33770287") { appaccesstokenpassed = true;} // Debug API Key

if (appaccesstokenpassed == true) {

var key = req.body.key;

var pass = req.body.pass;

var data = {}

console.log(" - Key: " + key )

console.log(" - Pass: " + pass )

// Grab the user IP and log it

var ip = req.headers['x-forwarded-for'] || req.connection.remoteAddress;

console.log(" - IP: " + ip );

mysql_pool.getConnection(function(err, connection) {

if (err) {

console.log(' mysql_pool.release()');

connection.release();

console.log(' Error getting mysql_pool connection: ' + err);

throw err;

}

// Check the user record (only if the user is active and provided thier token key and passowrd)

connection.query("SELECT * FROM usertablename WHERE active=1 AND usertokenkey=? AND usertokenpassword=? LIMIT 1",[key, pass],function(err, rows, fields){

if (err) {

console.log(" 405 Login Session Expired");

errData = {"Status": "405", "Error": "Login Session Expired"};

res.statusCode = 405;

res.send(errData);

res.end();

} else {

if (rows.length != 0) {

console.log(" 200 Login Session OK");

res.statusCode = 200;

data["Status"] = "OK";

res.json(data);

res.end();

} else {

console.log(" 405 Login Session Expired");

errData = {"Status": "405", "Error": "Login Session Expired"};

res.statusCode = 405;

res.send(errData);

res.end();

}

console.log(' mysql_pool.release()');

connection.release();

});

} else {

console.log("499 token required");

errData = {"Status": "499", "Error": "valid token required"};

res.statusCode = 409;

res.send(errData);

res.end();

}

});

Start the web service (app.js).

http.listen(3000,function(){
	console.log("Connected & Listen to port 3000 at /api/myappname/v1/ ..");
	console.log(" Do..");
	
});

http.listen(3000,function(){

console.log("Connected & Listen to port 3000 at /api/myappname/v1/ ..");

console.log(" Do..");

});

Hope this helps

Donate and make this blog better

Ask a question or recommend an article

V1.0 Initial Post

How to setup pooled MySQL connections in Node JS that don’t disconnect

August 29, 2015 by

How to setup pooled MySQL connections in Node JS that don’t disconnect

August 29, 2015 by Simon Fearby 2 Comments

Using NodeJS/NGINX to power a custom API or website is quite simple. At some stage you will want to connect NodeJS to a MySQL database.

I have a dedicated Ubuntu server on Digital Ocean. You can easily deploy an SSD cloud server in 55 seconds for $5 a month. Sign up using my link and receive $10 in credit: https://www.digitalocean.com/?refcode=99a5082b6de5.

Here is a quick sample of the minimum code needed to get a Node Server up with a API target of “/api/database/status”. What better way to know if a database is up than to read a setting from a table to confirm it is up.

var app = require('express')();
var http = require('http').Server(app);
var mysql = require('mysql');
var bodyParser = require("body-parser");

app.use(bodyParser.urlencoded({ extended: false }));
app.use(bodyParser.json());

var db_config = { host : 'localhost', user : 'your_db_username', password : 'your_db_password', database : 'your_databse_name',};
var connection = mysql.createConnection(db_config);

app.get('/api/database/status',function(req,res){
	var retvalSettingValue = "?";
    connection.query('SELECT SettingValue FROM your_status_table WHERE SettingKey =\'DatabaseStatus\'', function(err, rows, fields) {	
    	if (err) {
			var data = { "Time":"", "DatabaseStatus":"" };
			data["Time"] = (new Date()).getTime();
			data["DatabaseStatus"] = "Down";
			res.json(data); 
		} else {
			var dbretval = rows[0].SettingValue;
			if (dbretval == 1 ) {
				var data = { "Time":"", "DatabaseStatus":"" };
				data["Time"] = (new Date()).getTime();
				data["DatabaseStatus"] = "Up";
				res.json(data); 
			} else {
				var data = { "Time":"", "DatabaseStatus":"" };
				data["Time"] = (new Date()).getTime();
				data["DatabaseStatus"] = "Down";
				res.json(data); 
			}
		}
    });

http.listen(3000,function(){
	console.log("Connected & Listen to port 3000 at /api ..");
});

var app = require('express')();

var http = require('http').Server(app);

var mysql = require('mysql');

var bodyParser = require("body-parser");

app.use(bodyParser.urlencoded({ extended: false }));

app.use(bodyParser.json());

var db_config = { host : 'localhost', user : 'your_db_username', password : 'your_db_password', database : 'your_databse_name',};

var connection = mysql.createConnection(db_config);

app.get('/api/database/status',function(req,res){

var retvalSettingValue = "?";

connection.query('SELECT SettingValue FROM your_status_table WHERE SettingKey =\'DatabaseStatus\'', function(err, rows, fields) {

if (err) {

var data = { "Time":"", "DatabaseStatus":"" };

data["Time"] = (new Date()).getTime();

data["DatabaseStatus"] = "Down";

res.json(data);

} else {

var dbretval = rows[0].SettingValue;

if (dbretval == 1 ) {

var data = { "Time":"", "DatabaseStatus":"" };

data["Time"] = (new Date()).getTime();

data["DatabaseStatus"] = "Up";

res.json(data);

} else {

var data = { "Time":"", "DatabaseStatus":"" };

data["Time"] = (new Date()).getTime();

data["DatabaseStatus"] = "Down";

res.json(data);

}

});

http.listen(3000,function(){

console.log("Connected & Listen to port 3000 at /api ..");

});

This should return the following JSON data.

{"Time":1440835363892,"DatabaseStatus":"Up"}

1	{"Time":1440835363892,"DatabaseStatus":"Up"}

The problem for me with the code above was that MySQL kept disconnecting after a few days. Adjusting timeouts, setting up keep alive cron jobs failed to keep the connection up. I then tried introducing the following code solve the disconnected MySQL connection.

function handleDisconnect() {
	console.log('handleDisconnect()');
	connection.destroy();
	connection = mysql.createConnection(db_config);
	connection.connect(function(err) {
	    if(err) {
			console.log(' Error when connecting to db  (DBERR001):', err);
			setTimeout(handleDisconnect, 1000);
	    }
	});

}

function handleDisconnect() {

console.log('handleDisconnect()');

connection.destroy();

connection = mysql.createConnection(db_config);

connection.connect(function(err) {

if(err) {

console.log(' Error when connecting to db (DBERR001):', err);

setTimeout(handleDisconnect, 1000);

}

});

}

I added the following before and connection.query in existing code.

	connection.connect(function(err) {
	if(err) {
		console.log('Connection is asleep (time to wake it up): ', err);
		setTimeout(handleDisconnect, 1000);
		handleDisconnect();
	}
	});

connection.connect(function(err) {

if(err) {

console.log('Connection is asleep (time to wake it up): ', err);

setTimeout(handleDisconnect, 1000);

handleDisconnect();

}

});

I ended up swapping over to using pooled MySQL connections and this code has been working for days. Pooled connections reserves a number of connections ready for use in the background. You simply ask for a connection and when you are done you release it. The pooled connection closes and refreshed the connection and puts it at the end of the queue.

var app = require('express')();
var http = require('http').Server(app);
var mysql = require('mysql');
var bodyParser = require("body-parser");

app.use(bodyParser.urlencoded({ extended: false }));
app.use(bodyParser.json());

var mysql_pool  = mysql.createPool({
  connectionLimit : 100,
  host            : 'localhost',
  user            : 'your_database_user',
  password        : 'your_database_password',
  database        : 'your_database'
});

app.get('/api/database/status',function(req,res) {
	console.log('API CALL: /api/database/status');
	var retvalSettingValue = "?";
	mysql_pool.getConnection(function(err, connection) {
		if (err) {
			connection.release();
	  		console.log(' Error getting mysql_pool connection: ' + err);
	  		throw err;
	  	}
	    connection.query('SELECT SettingValue FROM your_database_table WHERE SettingKey =\'DatabaseStatus\'', function(err2, rows, fields) {	
	    	if (err2) {
				var data = { "Time":"", "DatabaseStatus":"" };
				data["Time"] = (new Date()).getTime();
				data["DatabaseStatus"] = "Down";
				res.json(data); 
			} else {
				var dbretval = rows[0].SettingValue;
				if (dbretval == 1 ) {
					var data = { "Time":"", "DatabaseStatus":"" };
					data["Time"] = (new Date()).getTime();
					data["DatabaseStatus"] = "Up";
					res.json(data); 
				} else {
					var data = { "Time":"", "DatabaseStatus":"" };
					data["Time"] = (new Date()).getTime();
					data["DatabaseStatus"] = "Down";
					res.json(data); 
				}
			}
			console.log(' mysql_pool.release()');
			connection.release();
	    });
	});
});

http.listen(3000,function(){
	console.log("Connected & Listen to port 3000 at /api ..");
});

var app = require('express')();

var http = require('http').Server(app);

var mysql = require('mysql');

var bodyParser = require("body-parser");

app.use(bodyParser.urlencoded({ extended: false }));

app.use(bodyParser.json());

var mysql_pool = mysql.createPool({

connectionLimit : 100,

host : 'localhost',

user : 'your_database_user',

password : 'your_database_password',

database : 'your_database'

});

app.get('/api/database/status',function(req,res) {

console.log('API CALL: /api/database/status');

var retvalSettingValue = "?";

mysql_pool.getConnection(function(err, connection) {

if (err) {

connection.release();

console.log(' Error getting mysql_pool connection: ' + err);

throw err;

}

connection.query('SELECT SettingValue FROM your_database_table WHERE SettingKey =\'DatabaseStatus\'', function(err2, rows, fields) {

if (err2) {

var data = { "Time":"", "DatabaseStatus":"" };

data["Time"] = (new Date()).getTime();

data["DatabaseStatus"] = "Down";

res.json(data);

} else {

var dbretval = rows[0].SettingValue;

if (dbretval == 1 ) {

var data = { "Time":"", "DatabaseStatus":"" };

data["Time"] = (new Date()).getTime();

data["DatabaseStatus"] = "Up";

res.json(data);

} else {

var data = { "Time":"", "DatabaseStatus":"" };

data["Time"] = (new Date()).getTime();

data["DatabaseStatus"] = "Down";

res.json(data);

}

console.log(' mysql_pool.release()');

connection.release();

});

http.listen(3000,function(){

console.log("Connected & Listen to port 3000 at /api ..");

});

Throughput

To test the connections/throughput it’s time to fire up more than 100 connections using siege over 60 seconds. I am using HTTPS connections so his would slow down the throughput a fair bit (but I am sticking with HTTPS for customer security/privacy). Read my guide here on adding a A+ level SSL cert to a digital ocean VM.

#!/bin/bash
free -m

siege -b -c1 -t1M https://yourserver.com/api/database/status
cat /var/log/siege.log

free -m

#!/bin/bash

free -m

siege -b -c1 -t1M https://yourserver.com/api/database/status

cat /var/log/siege.log

free -m

Here are the results from a siege single client hitting the NodeJS/pooled connections for 60 seconds. I am able to get about 6,150 database reads in 60 seconds with no dropped connections.

root@yourserver:/home/scripts# bash _Benchmark.sh n
             total       used       free     shared    buffers     cached
Mem:          2001       1784        217         53        186        980
-/+ buffers/cache:        617       1384
Swap:         4095          0       4095

** SIEGE 3.0.5
** Preparing 1 concurrent users for battle.
The server is now under siege...
Lifting the server siege...      done.

Transactions:                   6150 hits
Availability:                 100.00 %
Elapsed time:                  59.69 secs
Data transferred:               0.26 MB
Response time:                  0.01 secs
Transaction rate:             103.03 trans/sec
Throughput:                     0.00 MB/sec
Concurrency:                    0.97
Successful transactions:        6150
Failed transactions:               0
Longest transaction:            0.05
Shortest transaction:           0.00
 
FILE: /var/log/siege.log
You can disable this annoying message by editing
the .siegerc file in your home directory; change
the directive 'show-logfile' to false.
      Date & Time,  Trans,  Elap Time,  Data Trans,  Resp Time,  Trans Rate,  Throughput,  Concurrent,    OKAY,   Failed
2015-08-29 04:18:35,   6150,      59.69,           0,       0.01,      103.03,        0.00,        0.97,    6150,       0
             total       used       free     shared    buffers     cached


Mem:          2001       1806        195         53        186        981
-/+ buffers/cache:        638       1363
Swap:         4095          0       4095

root@yourserver:/home/scripts# bash _Benchmark.sh n

total used free shared buffers cached

Mem: 2001 1784 217 53 186 980

-/+ buffers/cache: 617 1384

Swap: 4095 0 4095

** SIEGE 3.0.5

** Preparing 1 concurrent users for battle.

The server is now under siege...

Lifting the server siege... done.

Transactions: 6150 hits

Availability: 100.00 %

Elapsed time: 59.69 secs

Data transferred: 0.26 MB

Response time: 0.01 secs

Transaction rate: 103.03 trans/sec

Throughput: 0.00 MB/sec

Concurrency: 0.97

Successful transactions: 6150

Failed transactions: 0

Longest transaction: 0.05

Shortest transaction: 0.00

FILE: /var/log/siege.log

You can disable this annoying message by editing

the .siegerc file in your home directory; change

the directive 'show-logfile' to false.

Date & Time, Trans, Elap Time, Data Trans, Resp Time, Trans Rate, Throughput, Concurrent, OKAY, Failed

2015-08-29 04:18:35, 6150, 59.69, 0, 0.01, 103.03, 0.00, 0.97, 6150, 0

total used free shared buffers cached

Mem: 2001 1806 195 53 186 981

-/+ buffers/cache: 638 1363

Swap: 4095 0 4095

2 siege clients hitting the server delivers 8,120 database reads with 0 dropped connections.

      Date & Time,  Trans,  Elap Time,  Data Trans,  Resp Time,  Trans Rate,  Throughput,  Concurrent,    OKAY,   Failed
2015-08-29 04:25:13,   8120,      59.83,           0,       0.01,      135.72,        0.00,        1.96,    8120,       0

1 2	Date & Time, Trans, Elap Time, Data Trans, Resp Time, Trans Rate, Throughput, Concurrent, OKAY, Failed 2015-08-29 04:25:13, 8120, 59.83, 0, 0.01, 135.72, 0.00, 1.96, 8120, 0

10 siege clients hitting the server delivers 16,383 database reads with 0 dropped connections.

      Date & Time,  Trans,  Elap Time,  Data Trans,  Resp Time,  Trans Rate,  Throughput,  Concurrent,    OKAY,   Failed
2015-08-29 04:29:50,  16383,      59.51,           0,       0.04,      275.30,        0.00,        9.86,   16383,       0

1 2	Date & Time, Trans, Elap Time, Data Trans, Resp Time, Trans Rate, Throughput, Concurrent, OKAY, Failed 2015-08-29 04:29:50, 16383, 59.51, 0, 0.04, 275.30, 0.00, 9.86, 16383, 0

20 siege clients hitting the server delivers 15440 database reads with 0 dropped connections. This looks like the max the server can handle (257 delivered requests a second (19.7 concurrent connections)).

      Date & Time,  Trans,  Elap Time,  Data Trans,  Resp Time,  Trans Rate,  Throughput,  Concurrent,    OKAY,   Failed
2015-08-29 04:33:06,  15440,      59.93,           0,       0.08,      257.63,        0.00,       19.77,   15440,       0

1 2	Date & Time, Trans, Elap Time, Data Trans, Resp Time, Trans Rate, Throughput, Concurrent, OKAY, Failed 2015-08-29 04:33:06, 15440, 59.93, 0, 0.08, 257.63, 0.00, 19.77, 15440, 0

My SQL, Linux Memory and Cache tweaks

I have tweaked my stock MySQL config a bit (changed values below)

#
# The MySQL database server configuration file.
#
...
[mysqld]
...
key_buffer		= 32M
key_buffer_size = 32M
max_allowed_packet	= 16M
max-connect-errors             = 1000000

#max_connections        = 100
max-connections = 500
thread_stack		= 256K
thread_cache_size       = 8
myisam-recover         = FORCE,BACKUP
#table_cache            = 64
#thread_concurrency     = 10
...
query_cache_limit	= 1M
query_cache_size        = 0
...
expire_logs_days	= 10
max_binlog_size         = 100M
...
# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
thread-cache-size              = 50
open-files-limit               = 65535
# table-definition-cache         = 1024
# table-open-cache               = 2048
..

# The MySQL database server configuration file.

...

[mysqld]

...

key_buffer = 32M

key_buffer_size = 32M

max_allowed_packet = 16M

max-connect-errors = 1000000

#max_connections = 100

max-connections = 500

thread_stack = 256K

thread_cache_size = 8

myisam-recover = FORCE,BACKUP

#table_cache = 64

#thread_concurrency = 10

...

query_cache_limit = 1M

query_cache_size = 0

...

expire_logs_days = 10

max_binlog_size = 100M

...

# CACHES AND LIMITS #

tmp-table-size = 32M

max-heap-table-size = 32M

thread-cache-size = 50

open-files-limit = 65535

# table-definition-cache = 1024

# table-open-cache = 2048

Some may notice a 20mb memory jump in memory usage after I benchmarked, I have set my ubuntu to use as much memory as possible and not release too soon. I also have a 4GB swap file (that is ready in reserve but not used much).

swappiness this control is used to define how aggressively the kernel swaps out anonymous memory relative to pagecache and other caches. Increasing the value increases the amount of swapping. The default value is 60.
vfs_cache_pressure this variable controls the tendency of the kernel to reclaim the memory which is used for caching of VFS caches, versus pagecache and swap. Increasing this value increases the rate at which VFS caches are reclaimed.

/etc/sysctl.conf

vm.swappiness = 10
vm.vfs_cache_pressure = 50
vm.min_free_kbytes= 131072

vm.swappiness = 10

vm.vfs_cache_pressure = 50

vm.min_free_kbytes= 131072

Tips on setting linux cache usage and snappiness here.

More performance tweaks here: https://gist.github.com/dakull/5629740

Donate and make this blog better

Ask a question or recommend an article

Main navigation

MySQL