NodeJS

Getting started with Sequelize, Postgres and Express

I’ve been playing with NodeJS for a while and one of my favorite libraries to work with has been Sequelize. It has been a very handy ORM and pretty easy to get rolling with, so I thought I would share the steps I took to get my app up and rolling with a Postgres database.

Setting up the config

I created a config file at the root of my project called “config.js”. This will store all of the details needed for starting the app and connecting to Postgres.

/config/config.js

With the following config setup, I can have multiple environment configs in one file but only use the one I need.

var path = require('path'),
    rootPath = path.normalize(__dirname + '/..'),
    env = process.env.NODE_ENV || 'development';
    
var config = {
    development: {
        root: rootPath,
        app: {
            name: 'stlsoundnet'
        },
        port: 3000,
        db: {
            database: "mydatabase",
            user: "user",
            password: "password1",
            options: {
                host: 'localhost',
                dialect: 'postgres',
                
                pool: {
                    max: 100,
                    min: 0,
                    idle: 10000
                }
            }
        }
    }
}

module.exports = config[env];

/config/express.js

Compliments of Sequelize docs, here is the express.js config I used.

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

var favicon = require('serve-favicon');
var logger = require('morgan');
var cookieParser = require('cookie-parser');
var bodyParser = require('body-parser');
var compress = require('compression');
var methodOverride = require('method-override');

module.exports = function(app, config) {
    var env = process.env.NODE_ENV || 'development';
    app.locals.ENV = env;
    app.locals.ENV_DEVELOPMENT = env == 'development';

    app.set('views', config.root + '/app/views');
    app.set('view engine', 'ejs');

    // app.use(favicon(config.root + '/public/img/favicon.ico'));
    app.use(logger('dev'));
    app.use(bodyParser.json());
    app.use(bodyParser.urlencoded({
    extended: true
    }));
    app.use(cookieParser());
    app.use(compress());
    app.use(express.static(config.root + '/public'));
    app.use(methodOverride());

    var controllers = glob.sync(config.root + '/app/controllers/*.js');
    controllers.forEach(function (controller) {
        require(controller)(app);
    });

    app.use(function (req, res, next) {
        var err = new Error('Not Found');
        err.status = 404;
        next(err);
    });

    if(app.get('env') === 'development') {
        app.use(function (err, req, res, next) {
            res.status(err.status || 500);
            res.render('error', {
                message: err.message,
                error: err,
                title: 'error'
            });
        });
    }

    app.use(function (err, req, res, next) {
        res.status(err.status || 500);
        res.render('error', {
            message: err.message,
            error: {},
            title: 'error'
        });
    });

};

Creating the models

First, I created a “models” folder. In my “models” folder I created the following:

models/index.js

// thanks to the sequelize docs
var fs = require('fs'),
    path = require('path'),
    Sequelize = require('sequelize'),
    config = require('../config/config'),
    db = {};

var sequelize = new Sequelize(config.db.database, config.db.user, config.db.password, config.db.options);

fs.readdirSync(__dirname).filter(function (file) {
    return (file.indexOf('.') !== 0) && (file !== 'index.js');
}).forEach(function (file) {
    var model = sequelize['import'](path.join(__dirname, file));
    db[model.name] = model;
});

Object.keys(db).forEach(function (modelName) {
    if ('associate' in db[modelName]) {
        db[modelName].associate(db);
    }
});

db.sequelize = sequelize;
db.Sequelize = Sequelize;

module.exports = db;

models/user.js

module.exports = function (sequelize, DataTypes) {
    var User = sequelize.define('user', {
        id: {
            type:DataTypes.UUID,
            primaryKey: true,
            defaultValue: DataTypes.UUIDV4
        },
        email: DataTypes.STRING,
        password: DataTypes.STRING,
        salt: DataTypes.STRING,
        token: DataTypes.STRING,
    }, {
        classMethods: {
            associate: function (models) {
                User.hasMany(models.CigarRating, { as: "cigarRatings" });
            }
        }
    });

    return User;
};

models/cigarRating.js

module.exports = function (sequelize, DataTypes) {
    var CigarRating = sequelize.define('cigarRating', {
        id: {
            type:DataTypes.UUID,
            primaryKey: true,
            defaultValue: DataTypes.UUIDV4
        },
        rating: DataTypes.INTEGER,
        details: DataTypes.STRING,
        createDate: DataTypes.DATE
    }, {
        classMethods: {
            associate: function (models) {
                CigarRating.belongsTo(User, { foreignKey: 'userId', as: 'user'  });
            }
        }
    });

    return CigarRating;
};

This gave me my entity models. I then created a folder called “data” that would hold my repositories (I’m a fan of the repository pattern). I created the following:

data/userRepository.js

var db = require('../models');

var UsersRepository = {
    findByEmail: function(email) {
        return db.User.findAll({
            where: {
                email: email
            }
        })
    }
}

module.exports = UsersRepository;

Creating a test API using Express JS

With the config and the data modeling in place, I’m ready to set up my NodeJS app and create a basic controller to receive and respond to a GET request.

/app.js

I used some code provided by the Sequelize docs to give me the following app.js file.

var express = require('express'),
    config = require('./config/config'),
    db = require('./models'),
    UsersController = require('./controllers/usersController.js');

var app = express();

require('./config/express')(app, config);

function startApp() {
    app.listen(config.port, function () {
        console.log('Express server listening on port ' + config.port);
    });
}

db.sequelize.sync()
    .then(startApp)
    .catch(function (e) {
        throw new Error(e);
    });
    
app.get('/users', UsersController.get);

The .sync() method updates the Postgres database with the models that we defined in the previous step. If you’re a .NET developer, it’s kind of like running an Entity Framework migration update.

/controllers/usersController.js

Now I can set up my API controller.

"using strict";

var userRepo = require('../data/usersRepository');
var crypt = require("../crypt");

function getUser(req, res) {
    var email = req.query.email;
    
    userRepo.findByEmail(email)
        .then(function(users) {
            if (users.length) {
                return res.send(user);
            }
            return res.send({
                message: "Could not find user."
            });
        })
        .catch(function(error) {
            return res.send({
                message: "Error retrieving user."
            });
        });
}

module.exports = { get: getUser };

Try it out!

With this setup, you should be able to hit “/users?email=[someExistingEmail]” and get a response like the following (assuming there is a result):

{ 
    id: "somevalue",
    email: "somevalue",
    password: "somevalue",
    salt: "somevalue",
    token: "somevalue",
    cigarRatings: [] // related cigarRating entities should show up here!
}

Problems? Questions? Did I save you some time? Let me know on Twitter@kerryritter or shoot me an email at ritter@kerryritter.com.

Leave a Reply

Your email address will not be published. Required fields are marked *