Node Authentication, migration from mongoose to sequelize

Scotch.io has quite a complete tutorial on how to setup Node authentication with Passport, a great authentication Node.js module. The Scotch.io tutorial uses NOSQL database Mongodb to store user information. Mongodb is a handy database for this tutorial, but many projects already use an SQL database. So I decided to see if I could use the tutorial as a boilerplate for authentication based on user data stored in an SQL database. The original tutorial uses Mongoose, an Object-Releational-Mapping (ORM) middleware to map the javascripted 'User' object to data in NOSQL MongoDB. For SQL, I found an equivalent ORM-middleware called Sequelize. I started very optimistically, thinking I would connect the SQL database in an hour or so, but, in reality, it took me quite some time to do the job. The complete code is available on github at https://github.com/anneb/easy-node-authentication

I first completed the Scotch.io tutorial and checked that it worked. Then I started migration to Sequelize. This required new Node modules to be installed. I happened to choose Postgres as SQL database (Sequelize supports at least Mysql, Postgres, Sqlite, Mariadb and MSsql).

	npm install pg pg-hstore sequelize
	#next two lines necessary only for slightly faster native version of pg:
	sudo apt-get install libpq-dev
	npm install pg-native

view raw authinstallmodules.sh hosted with ❤ by GitHub

Setup the database connect string (replace dbuser, dbpasswd, dbhost, dbname by appropriate values):

 

	// file config/database.js
	module.exports = {
	'url' : 'postgres://dbuser:dbpasswd@dbhost:5432/dbname'
	};

view raw auth_db.js hosted with ❤ by GitHub

Then I had a look at the data model. MongoDB is more or less object based. Data are stored in collections, the NOSQL equivalent of SQL tables. Collections contain documents, the SQL-equivalent of records. All SQL records in a table share the same structure by design. The attributes of objects in the same NOSQL collection may vary from one object to the other. The scotch.io authentication tutorial uses this NOSQL multi-attribute feature to store different user properties for different identity providers. For example, in the case of local identities, we may need such attributes as 'username' and 'password', but for remote identity providers such as Google, we need to store a Google id and a Google OAuth token. To accommodate for the differences between NOSQL and SQL, the datamodel for the Sequelize version of the authentication example had to be modified.

	// file app/models/user.js
	// define the model for User
	// load the things we need
	var bcrypt = require('bcrypt-nodejs');
	module.exports = function(sequelize, DataTypes) {
	return sequelize.define('user', {
	localemail : DataTypes.STRING,
	localpassword : DataTypes.STRING,
	facebookid : DataTypes.STRING,
	facebooktoken : DataTypes.STRING,
	facebookemail : DataTypes.STRING,
	facebookname : DataTypes.STRING,
	googleid : DataTypes.STRING,
	googletoken : DataTypes.STRING,
	googleemail : DataTypes.STRING,
	googlename : DataTypes.STRING,
	windowsliveid : DataTypes.STRING,
	windowslivetoken : DataTypes.STRING(1024),
	windowsliveemail : DataTypes.STRING,
	windowslivename : DataTypes.STRING
	},
	{
	classMethods: {
	generateHash : function(password) {
	return bcrypt.hashSync(password, bcrypt.genSaltSync(8), null);
	},
	},
	instanceMethods: {
	validPassword : function(password) {
	return bcrypt.compareSync(password, this.localpassword);
	}
	}
	});
	}

view raw user.js hosted with ❤ by GitHub

The above Model is instantiated using sequelize.import. (not to be confused with 'require()'):

	// initialize Sequelize
	var Sequelize = require('sequelize');
	var pg = require('pg').native;
	var pghstore = require('pg-hstore');
	var sequelize = new Sequelize(configDB.url);</code>
	// Associate the model to variable 'User'
	var User = sequelize.import('../app/models/user');
	// Connect to persistant (SQL) storage
	User.sync();

view raw instantiate_model.js hosted with ❤ by GitHub

User.sync() creates table 'users' in the database if it does not yet exist. By default Sequelize silently adds three extra columns to the table: a primary key field id and fields createdAt and updatedAt, refer to the Sequelize documentation for more options.

Another difference between Sequelize and Mongoose is the use of Javascript Promises. Sequelize 2.0+ is Promisified, Mongoose is not (yet). Javascript Promises should make it easier to handle asynchronous events. Javascript Promises are a native feature of many Javascript engines since 2013. However, Microsoft Internet Explorer (IE) included native support for Promises only since version 12 and Promises will never be available in IE on Windows 8.1. For this reason, if you are a Javascript developer for browser based Javascript, you may not yet be fully acquainted with Promises. Sequelize has a separate documentation site, but I found the documentation not to be always very complete. For example, sometimes the documentation describes that a function requires an option object, but forgets to explain the possible options. Functions return Promises, but examples are not provided. It would be great if there were some tutorial on how to use Sequelize. They are probably around, but I found it hard to find. Many of the examples that I did find are for the pre-Promise version, so they don't work for the current version of Sequelize. Please comment a tip if you know of a good Sequelize tutorial or Sequelize example set!

The following example would have helped me a lot:

Mongoose, no Promises

	// Mongoose
	// the database query callbacks have an error parameter to be checked for errors
	//
	// the code below is called from a context that has an Express request 'req' and
	// requires callback 'done' to be called when done
	User.findOne({'local.email': email}, function(err, existingUser) {
	// if there are any errors, such as query error, db connect err, etc. return the error
	if (err)
	// some error occurred while executing the findone query
	return done(err);
	// check to see if there's already a user with that email
	if (existingUser)
	return done(null, false, req.flash('signupMessage', 'That email is already taken.'));
	// If we're logged in, we're connecting a new local account.
	if(req.user) {
	var user = req.user;
	user.local.email = email;
	user.local.password = user.generateHash(password);
	user.save(function(err) {
	if (err)
	// user save failed
	throw err;
	return done(null, user);
	});
	} else {
	// We're not logged in, so we're creating a brand new user.
	// create the user
	var newUser = new User();
	newUser.local.email = email;
	newUser.local.password = newUser.generateHash(password);
	newUser.save(function(err) {
	if (err)
	// newUser save failed
	throw err;
	return done(null, newUser);
	});
	}
	});

view raw mongoose_find.js hosted with ❤ by GitHub

Sequelize, Promisified

	// Sequelize is Promisified
	// the 'then' method is called after all callbacks and events are processed succesfully
	// the 'catch' method is called if an error occurs
	// the code below is called from a context that has an Express request 'req' and
	// requires callback 'done' to be called when done
	User.findOne({ where: { localemail: email }})
	.then(function(existingUser) { // findOne succesful, zero or one records found
	// check to see if there's already a user with that email
	if (existingUser)
	return done(null, false, req.flash('loginMessage', 'That email is already taken.'));
	// If we're logged in, we're connecting a new local account.
	if(req.user) {
	// update user properties
	var user = req.user;
	user.localemail = email;
	user.localpassword = User.generateHash(password);
	user.save()
	.then (function() {
	// user save succesful
	done(null, user);
	})
	.catch(function (err) {
	// user save failed
	done(null, false, req.flash('loginMessage', err));});
	});
	} else {
	// We're not logged in, so we're creating a brand new user.
	// create the user
	var newUser = User.build ({
	localemail: email,
	localpassword: User.generateHash(password)
	});
	// store the newUser to the database
	newUser.save()
	.then(function() {
	// newUser save succesful
	done (null, newUser);
	})
	.catch(function(err) {
	// newUser save failed
	done(null, false, req.flash('loginMessage', err));});
	}
	})
	.catch(function (e) {
	// some error occurred while executing the findone query
	done(null, false, req.flash('loginMessage',e.name + " " + e.message));
	})

view raw sequelize_find.js hosted with ❤ by GitHub

The complete code is available on github at https://github.com/anneb/easy-node-authentication