I've written a lot about building REST API's and a few of my favorite include Rails-only API, ExpressJS, Amazon Lambda, and SailsJS. This article is going to focus on something slightly different –but still interconnected, Object Relational Models.

Although it's good practice to learn how to write SQL statements, at some point, you'll realize it's a terrible practice that will lead to buggy software. Instead, it's generally better to use an ORM. In the Ruby world, Rails owns ActiveRecord and Sinatra uses DataMapper respectively. In the Node world, my favorite ORM to date is Sequelize.

Why Sequelize?

Sequelize is great because they've made it super easy to integrate with MySQL, Postgres, sqlite3.

Sequelize also uses Bluebird Promises so you're code can look super pretty like this:

User.findOne()
    .then(user => {
      console.log(user.get('firstName'));
    });

We have .then! Woot woot!

Note: Some people may prefer something other than bluebird and that's fine. But for me, I prefer not to obsess over granular topics –unless a client asks for it or it will make me a lot of money.

Getting Started

Step 0 - Setting up your environment

The first thing you'll need to do is first pick a database. Here's my short tutorial on how to install Postgres on your local Mac in just a few steps.

You might also want to create a folder for our quick project.

mkdir ~/Desktop/my-sequelize-app

cd ~/Desktop/my-sequelize-app

Step 1 - Installing Sequelize

As always, you can install Sequelize through Node Package Manager.

npm i sequelize --save-dev

Step 2 - Install Jake

For my demo, I will use Sequelize with JakeJS. Again, inspired by the Ruby community, Jake is the Javascript equivalent of Rake.

I prefer to install Rake globally.

npm i -g jake

Step 3 - Create a Jakefile

Once you've installed Jake, you'll need to create a jake file.

nano Jakefile.js

Paste this skeletal template of Jakefile.js.

var fs = require('fs'),
util   = require('util'),
path   = require('path'),
Sequelize = require('sequelize')

desc('Jakefile default.');
task('default', { async: true }, function(){

});

Step 4 - Creating a Namespace

Jake is great because you can organize your commands using namespaces. For example, we're going to create a namespace titled db and it will allow me to create a suite of commands with the same organizational structure.

Append this to Jakefile.js. The code below will help you create a connection to a Postgres database. Before this can work, you will need to create a new postgres user with the username changeme and password password.

namespace('db', function () {
	// A. Configure your database information
	const db = {
                // Type of database
		type: "postgres",
                // Server host
		host: "localhost",
                // Name of table
		name: "postgres",
                // User info
		user: {
			name: "changeme",
			password: "password",
		}
	}
	// B. Create a Sequelize ORM instance
	const sequelize = new Sequelize(db.name, db.user.name, db.user.password, {
	  host: db.host,
	  dialect: db.type,
	  pool: {
	    max: 5,
	    min: 0,
	    acquire: 30000,
	    idle: 10000
	  },
	  
	  // SQLite only
	  storage: 'path/to/database.sqlite',

	  // http://docs.sequelizejs.com/manual/tutorial/querying.html#operators
	  operatorsAliases: false
	});
});

Step 5 - Adding commands

The next step is to append some new commands.

  • The first command will two models. I intentionally designed the models to show you how you can run validations and interconnected tables.
  • The second command will create a new entry for our model.
  • The third command will show you how to drop a model.
var fs = require('fs'),
util   = require('util'),
path   = require('path'),
Sequelize = require('sequelize')

desc('Jakefile default.');
task('default', { async: true }, function(){

});

namespace('db', function () {
	// A. Configure your database information
	const db = {
                // Type of database
		type: "postgres",
                // Server host
		host: "localhost",
                // Name of table
		name: "postgres",
                // User info
		user: {
			name: "changeme",
			password: "password",
		}
	}
	// B. Create a Sequelize ORM instance
	const sequelize = new Sequelize(db.name, db.user.name, db.user.password, {
	  host: db.host,
	  dialect: db.type,
	  pool: {
	    max: 5,
	    min: 0,
	    acquire: 30000,
	    idle: 10000
	  },
	  
	  // SQLite only
	  storage: 'path/to/database.sqlite',

	  // http://docs.sequelizejs.com/manual/tutorial/querying.html#operators
	  operatorsAliases: false
	});

	desc('Create your database schemas. Ex: jake db:create');
	task('create', { async: true }, function(){
		// A. Create a Friend model with validation
		const Friend = sequelize.define("Friend", {
			alias: {
				type: Sequelize.ARRAY(Sequelize.TEXT),
				primaryKey: true,
				notNull: true
			},
			fname: {
				type: Sequelize.STRING,
			},
			lname: {
				type: Sequelize.STRING,
			},
			bio: Sequelize.TEXT
		});

		// B. Create an Image model and include a foreign key.
		const Image = sequelize.define("Image", {
			composer_id: {
			 type: Sequelize.INTEGER,
			    references: {
			      // This is a reference to another model
			      model: Friend,
			      // This is the column name of the referenced model
			      key: 'id',
			      // This declares when to check the foreign key constraint. PostgreSQL only.
			      deferrable: Sequelize.Deferrable.INITIALLY_IMMEDIATE
			    }
			},
			base64: Sequelize.BLOB,
			svg: Sequelize.JSONB 
		});
		sequelize.sync()
		.then( (results) => {
			process.exit();
		})
	});
	
	
	desc('Create a new model. Ex: jake db:create_friend[{ alias: "charlie", fname: "Charles", lname: "Barkely", bio: "lorem ipsum"  }]');
	task('create_friend', { async: true }, function(obj){
	  sequelize.sync()
	    .then(() => Composer.create({
	       alias: obj.alias,
	       fname: obj.fname,
	       lname: obj.lname,
	       bio: obj.bio
	    }))
	    .then(composer => {
	      console.log(composer.toJSON());
		process.exit();
	    });		
	});

	desc('Drop all databases models. Ex: jake db:reset');
	task('reset', { async: true }, function(){
		try{
			Friend.drop()			
		} catch(e){
			onError(e);			
		}
		try {
			Image.drop()
		} catch(e) {
			onError(e)
		}
		function onError(e){
			console.error(e.message)
		}
		process.exit();
	});	
});

Step 6 - Review your work

If you want to see if your file is looking good, run this command in Terminal.

jake -T

This will list out all of your commands.

Resources