Sequelize: Like Mongoose but for SQL

Introduction

Everyone who's ever built a MEAN or MERN stack app is probably familiar with Mongoose, the helpful object data modeling library that makes working with a MongoDB much, much easier. And since this application stack is so popular, there's about a thousand tutorials (some much better than others) illustrating how to use MongoDB, Express, React (or Angular) and Node.js together.

Imagine my surprise though, when I decided to build a simple full stack app using MySQL (or SQLite or PostgreSQL or really any type of SQL database), and found almost nothing that made much sense on how to do so using Express and Node. Really? Nothing recent? Nothing with a deliberate, modularized architecture? No one seemed to have a good way of doing this - nothing short of actual SQL queries written out in the server.js file to perform create, read, update, and delete (CRUD) operations on the database.

Then, I found Sequelize, and everything changed.

I wanted to share what I learned here, in hopes of saving some other people from the masses of outdated tutorials out there, unaware of how much easier Sequelize can make implementing a SQL database into their own Node apps.

What is Sequelize?

Sequelize's home page best sums up what exactly it is:

Sequelize is a promise-based ORM for Node.js v4 and up. It supports the dialects PostgreSQL, MySQL, SQLite and MSSQL and features solid transaction support, relations, read replication and more.  -  Sequelize

It is the easiest and most straightforward tool I've found for working with databases in SQL inside of a JavaScript application. There are other options that offer similar functionality. Knex.js is one that comes to mind, but after comparing their GitHub stats, I chose to move forward with Sequelize.

Why Sequelize in particular?

Here's some stats that helped me make my decision:

• Sequelize has over 8,000 commits (Knex has just under 2,000),
• It has 375 releases (Knex has 119),
• 694 contributors (Knex has 237),
• It recently released the beta version 5 (Knex is on v0.15),
• And it has a CLI, TypeScript and GraphQL integration (Knex does not),
• Plus 883,000 downloads per month from NPM (Knex has just under 600,000 per month).

In almost every way (by the numbers), Sequelize is superior to Knex, so I decided to go with what seems to be a very stable, well documented, long maintained and frequently updated library.

I will say that Knex offers native Oracle and Amazon Redshift support, but since I wasn't looking to use an Oracle DB, this wasn't a major selling point for me.

Sequelize compared to Mongoose

So, when you use Sequelize, you get a lot of the same types of benefits Mongoose offers to MongoDB users plus some bonuses:

• The ability to connect with very little code changes to multiple different types of SQL databases,
• Object models (similar to Mongoose schemas) which are typed, mapped to tables and can be required,
• Basic and more advanced querying options like findAll/findOne, destroy, update, etc. plus things like pagination, nested objects, and associations,
• Connection pooling,
• Raw queries,
• And more I don't have time to cover.

The documentation is very thorough and shows just how much Sequelize can offer over having to manage it all yourself.

I'd also like to note that by using Sequelize you can save yourself the trouble of having to rewrite SQL queries to fit the slight variations in database dialects  -  your find and create statements need no modifications to work regardless of which type of DB you're connecting to. I don't know about you, but I'm a big fan of less effort on my end.

Sequelize also uses promises via Bluebird to control its async control flow. All this means is that when you query the database via Sequelize, it will return a promise like this one.

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

// returns user object's first name, like 'John'

It also means that Sequelize works well with the ES6 async/await functionality like so.

user = await User.findOne();  

console.log(user.get('firstName'));

// also returns user object's first name, like 'John'

How to implement Sequelize in Express.js

Great, so now you can see some of the reasons to use Sequelize, how do you use it in your project?

This is always my biggest question whenever I see samples or tutorials. How do I use it? And how do I organize my files and my program structure so it's easy to swap pieces of functionality in and out with minimal modifications on my part?

Here's the source file structure I came up with (including just server (API) folders). It's pretty simple because the project I was trying to do wasn't particularly complex. But it offers enough organization to make adding CRUD functionality through the routes folder easy and defining one or more database models is as well thanks to the models folder.

root/
├── api/ 
| ├── server.js 
| ├── sequelize.js 
| ├── package.json
| ├── models/ 
| | ├── user.js
| ├── routes/ 
| | ├── deleteUser.js 
| | ├── findUser.js 
| | ├── loginUser.js 
| | ├── registerUser.js
| | ├── updateUser.js
| ├── data/ (optional depending on storage choice - like SQLite) 
| | ├── db/ 
| | | ├── storage.sqlite
| ├── node-modules/

So, now that you see the file structure, let me show you what your files will contain. I'll start with the user.js file because it gets imported into the sequlize.js and routes files.

Define the models

Below is the code for a user model.

user.js

module.exports = (sequelize, type) => {
  return sequelize.define('user', {
    id: {
      type: type.INTEGER,
      primaryKey: true,
      autoIncrement: true
    },
    first_name: type.STRING,
    last_name: type.STRING,
    email: type.STRING,
    username: {
      type: type.STRING,
      allowNull: false
    }, 
    password: {
      type: type: STRING,
      allowNull: false
    }
  })
};

Since I was building a user registration application, my user model file was very straightforward. Just an ID that's been set as the primary key and will auto increment, a first name, last name, email, username and password (both of which are required). It's pretty easy to follow.

Set up the database connection(s)

Next up is the sequelize.js file, this is where I kept the majority of my database setup to make it easy to switch out databases if you ever wanted to migrate from say a MySQL DB to a SQLite DB. Here's what a MySQL connection looked like:

sequelize.js

import Sequelize from 'sequelize';
import UserModel from './modes/user';

const sequelize = new Sequelize('dbname', 'admin', 'password', {
  host: 'db',
  dialect: 'mysql'
});

const User = UserModel(sequelize, Sequelize);

sequelize.sync()
  .then(() => {
    console.log('Users db and user table have been created');
  });

module.exports = User;  

As you can see, first, I initiate the connection to the MySQL database with the sequelize variable, giving it a database name, username, password, and then specifying the host and dialect. That's all there is to it.

Then I added the user model defined earlier, and the sequelize.sync() function syncs all defined models to the database. In this way, it creates the database and tables based on the information you've given it and models you've passed to it.

Finally, I export the User constant that I defined earlier, for use in other parts of the project, like the routes.

Here's another example of a SQLite connection so you can see the differences:

sqliteSequelize.js

import Sequelize from 'sequelize';
import UserModel from './modes/user';

const sequelizeSqlite = new Sequelize('dbname', 'admin', 'password', {
  host: 'sqlite',
  dialect: 'data/db/storage.sqlite'
});

const UserSqlite = UserModel(sequelizeSqlite, Sequelize);

sequelizeSqlite.sync()
  .then(() => {
    console.log('Users db and user table have been created with Sqlite');
  });

module.exports = UserSqlite;  

A simple connection to a SQLite database with storage persisted within the project itself.

Besides the slight changes to the database set up, everything else in the file, stays the same.

Define the routes

Ok, I'm almost done with setting this up.

To keep the code modular (and make it easier for me to add or subtract functionality from the application), I added each piece of the CRUD functionality to the database as a separate file. You could add them all in the same file and keep it divided up by model, or whatever is easiest for you to keep things organized.

Here's an example of my user registration route (the create functionality). I added in password hashing through the use of bcrypt, but that's not important right now.

registerUser.js

import User from '../sequelize';
import bcrypt from 'bcrypt';

const BCRYPT_SALT_ROUNDS = 12;

module.exports = (app) => {
  app.post('/registerUser', (req, res) => {
    const data = {
      fist_name: req.body.first_name,
      last_name: req.body.last_name,
      email: req.body.email,
      username: req.body.username,
      password: req.body.password
    };

    if(data.password === '' || data.username === ''){
      res.json('username and password required');
    }
    User.findOne({
      where: {
        username: data.username
      }
    })
    .then(user => {
      if (user !== null) {
        console.log('username already taken');
        res.json('username already taken');
      } else {
        bcrypt.hash(data.password, BCRYPT_SALT_ROUNDS)
        .then((hashedPassword) => {
          User.create({
            first_name: data.first_name,
            last_name: data.last_name,
            email: data.email,
            username: data.username,
            password: hashedPassword
          })
        })
        .then(() => {
          console.log('user created');
          res.json('user created');
        })
      }
    })
    .catch(err => {
      console.log('problem communicating with db');
      res.status(500).json(err);
    })
  })
};

As you can see from the code above, the User model is imported from the sequelize.js file, the user input data is passed from the client side of the app through the req.body.xyz fields, and then I do a number of checks to the database with the help of Sequelize before adding a new user.

The first check is when I check to make sure the user supplied both a username and password (as required by the model), if they're not both there, the server sends back the error message 'username and password required'.

After that check, I query the database to see if that username is already taken, if it is, once again the server sends 'username is already taken' back to the client.

If it passes that check, the password is hashed and the user info is saved to the database. Once it's successfully saved it sends back a success message to the client 'user created'.

And the whole creation operation is wrapped so in case there's a problem communicating with the server (it's down, there's network issues, etc.), it can still send a message to the client letting it know there was a problem.

Add the routes to the server

Now I'm ready to add these routes to the server. This is easiest part of all.

server.js

import express from 'express';
import Cors from 'cors';
import bodyParser from 'body-parser';
import logger from 'morgan';

const app = express();

app.use(Cors());
app.use(bodyParser.urlencoded({ extended: true }));
app.use(bodyParser.json());
app.use(logger('dev'));

require('./routes/loginUser')(app);
require('./routes/registerUser')(app);
require('./routes/findUsers')(app);
require('./routes/deleteUser')(app);
require('./routes/updateUser')(app);

app.listen(API_PORT, () => console.log(`Listening on port ${API_PORT}`));

module.exports = app;

All you have to do to make this work, is require the routes in your server.js file like I did, and have them take in your Express application, and you're set. It doesn't get much simpler than that.

Now, you should be able to use Sequelize to your heart's content with any kind of SQL database in your Node.js, Express project.

If you'd like to see a full MERN stack example using Sequelize, you can see my repo here. From the master branch you can run docker-compose build, followed by docker-compose up, and from the sqlite feature branch you can start both the client and server files individually with npm start from the api/ and client/ folders.

Conclusion

Sequelize brings order and flexibility to using SQL databases in JavaScript applications. It reduces the need to write raw queries, manage connections, guard against harmful SQL attacks, and more. And with some fairly basic setup, you too, can use Sequelize in your own JS projects - and hopefully speed up your development time. I will definitely continue to use this tool going forward.

Check back in a few weeks — I’ll be writing more about JavaScript, React, IoT, or something else related to web development.

Thanks for reading, I hope this proves helpful in your future database-driven projects.

References & Further Resources

• MongooseJS
• Sequelize
• Knex.js
• bcrypt
• GitHub repo of MERN app using Sequelize