How to use Sequelize with Node and Express

This post will explore some common use cases of Sequelize, a promise-based ORM for Node. Writing is geared towards readers familiar with JavaScript and back-end development using Node.js.

Like many other ORMs, Sequelize allows you to map your relational database to objects. Those objects have methods and properties that enable you to avoid writing SQL statements.

There is nothing wrong with writing SQL statements, but it is easier to make mistakes, it's less readable, and SQL statements are slightly different among different databases.

In a nutshell, ORM is a form of abstraction for common SQL operations.

We'll see how we can use Sequelize to build a simple (very, very simple) API for storing blog posts. We want to be able to create users, blog posts, and tags. Each user can have multiple blog posts, and we can tag those posts so that visitors can quickly find similar posts. Each post can have multiple tags.

Enough talk, let's see some code.

Create a folder for our little project.

mkdir sequelize_example && cd sequelize_example

Then we will need to install our dependencies.

npm install --save body-parser express mysql2 sequelize

Since I will be using MySQL, I'll be installing mysql2. If you are using a different database, please install the appropriate package (pg pg-hstore | sqlite3 | tedious // MSSQL). That will be the only thing you need to change — the rest of this example is platform agnostic.

mkdir models
touch ./models/user.js ./models/blog.js ./models/tag.js
touch sequelize.js 
touch index.js

These are all of the files we will need. sequelize.js will be the place to bootstrap our ORM and define relationships. We will define our models in their respective files and index.js will be our Express app.

sequelize.js

const Sequelize = require('sequelize')
const UserModel = require('./models/user')
const BlogModel = require('./models/blog')
const TagModel = require('./models/tag')

const sequelize = new Sequelize('codementor', 'root', 'root', {
  host: 'localhost',
  dialect: 'mysql',
  pool: {
    max: 10,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
})

const User = UserModel(sequelize, Sequelize)
// BlogTag will be our way of tracking relationship between Blog and Tag models
// each Blog can have multiple tags and each Tag can have multiple blogs
const BlogTag = sequelize.define('blog_tag', {})
const Blog = BlogModel(sequelize, Sequelize)
const Tag = TagModel(sequelize, Sequelize)

Blog.belongsToMany(Tag, { through: BlogTag, unique: false })
Tag.belongsToMany(Blog, { through: BlogTag, unique: false })
Blog.belongsTo(User);

sequelize.sync({ force: true })
  .then(() => {
    console.log(`Database & tables created!`)
  })

module.exports = {
  User,
  Blog,
  Tag
}

Let's walk through what's happening here.

After requiring our models and dependencies, we instantiate Sequelize and define all details necessary for connecting to the database. Your specific database and credentials will differ.

Next, we instantiate our models by passing a sequelize instance and library itself to required model files.

After models are created, we define their relationships. Blog.belongsTo(User) will create a foreign key on the Blog model — userId.
Foreign keys, unless you specify otherwise, will follow camelCase naming convention.

The fun part is the relationship between Blog and Tag models. We want to have a table for all tags so we can maintain uniqueness but we also want to allow many blog posts to have multiple tags.

Other association methods wouldn't work for us because we don't really want to create a foreign key on any of our models. What we want is a table that will hold connections between blogs and tags. This table doesn't have to have any fields other than blogId and tagId.

By creating an empty model, BlogTag, that we use as a through property, while setting up belongsToMany, Sequelize is actually adding two foreign keys to BlogTag — blogId and tagId.

Our BlogTag table will look like this

+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| createdAt | datetime | NO   |     | NULL    |       |
| updatedAt | datetime | NO   |     | NULL    |       |
| blogId    | int(11)  | NO   | PRI | NULL    |       |
| tagId     | int(11)  | NO   | PRI | NULL    |       |
+-----------+----------+------+-----+---------+-------+

sequelize.sync() will create all of the tables in the specified database. If you pass {force: true} as a parameter to sync method, it will remove tables on every startup and create new ones. Needless to say, this is a viable option only for development.

Lastly, we want to export our models so we can actually use them elsewhere in our app.

Moving on to our models.

models/blog.js

module.exports = (sequelize, type) => {
    return sequelize.define('blog', {
        id: {
          type: type.INTEGER,
          primaryKey: true,
          autoIncrement: true
        },
        text: type.STRING
    })
}

models/tag.js

module.exports = (sequelize, type) => {
    return sequelize.define('tag', {
        id: {
          type: type.INTEGER,
          primaryKey: true,
          autoIncrement: true
        },
        name: type.STRING
    })
}

models/user.js

module.exports = (sequelize, type) => {
    return sequelize.define('user', {
        id: {
          type: type.INTEGER,
          primaryKey: true,
          autoIncrement: true
        },
        name: type.STRING
    })
}

As you can see, all models look similar. This is where we define fields, types, and other relevant information concerning a table and its definition. We are exporting a function that will return a model instance. We need to pass two things to our model files.

First, the instance of Sequelize and Sequelize itself. We need the former to create (define) our model and the latter for type definition. Sequelize has a bunch of types available as static properties. I'm using INTEGER and STRING, but most any other type is also available.

Onto the API part.

We want a simple app that has a couple of endpoints:
POST /API/users // create user
GET /API/users // get all users

POST /API/blogs // create a blog post

GET /API/blogs/:userId? // get all blogs | get blogs of one user
GET /API/blogs/:tag/tag // get all blogs by a tag

Let's add the basic express.js boilerplate. For the purposes of this article, we will only have one file index.js. Our entire API will fit in there, but in a larger project, you would want to organize your code a bit better.

index.js

const express = require('express')
const bodyParser = require('body-parser')

const app = express()
app.use(bodyParser.json())

// API ENDPOINTS

const port = 3000
app.listen(port, () => {
    console.log(`Running on http://localhost:${port}`)
})

The first thing we want to do is import our models.
index.js

// dependencies
const { User, Blog, Tag } = require('./sequelize')

That should do it. Now our users API endpoints are really nothing special. We need it only so we can associate a blog with a user.

index.js

// create a user
app.post('/api/users', (req, res) => {
    User.create(req.body)
        .then(user => res.json(user))
})
// get all users
app.get('/api/users', (req, res) => {
    User.findAll().then(users => res.json(users))
})

While we are at it, let's look at the Sequelize methods we are using here. When creating a User, we use User, which is a reference to a model object we imported. create method will accept an argument (an object) containing properties we have in our models and their values. For example req.body will be:

{
    name: "Mirko Jotic"
}

Sequelize will map properties to columns, make an SQL statement, open a connection to the database, and execute that statement. After all that is done, it will return a Promise to which it will pass a user model. So when we give it an object, we get back a User model with all of the methods a model has in Sequelize.

Now comes the fun part. Let's see how we're going to save a blog post, associate a user with it, and attach a tag or two to it.

index.js

// create a blog post
app.post('/api/blogs', (req, res) => {
    const body = req.body
    // either find a tag with name or create a new one
    const tags = body.tags.map(tag => Tag.findOrCreate({ where: { name: tag.name }, defaults: { name: tag.name }})
                                         .spread((tag, created) => tag))
    User.findById(body.userId)
        .then(() => Blog.create(body))
        .then(blog => Promise.all(tags).then(storedTags => blog.addTags(storedTags)).then(() => blog))
        .then(blog => Blog.findOne({ where: {id: blog.id}, include: [User, Tag]}))
        .then(blogWithAssociations => res.json(blogWithAssociations))
        .catch(err => res.status(400).json({ err: `User with id = [${body.userId}] doesn\'t exist.`}))
})

We find or create necessary tags, then check if a user who wants to create this blog actually exists in our database. After the blog and the tags are created, we load the model from the database with all of its associations.

Let us look at another use case. What about searching for and retrieving data.

Say we want to retrieve all blog posts or all blogs belonging to a certain user. We could could do something like this:

index.js

// find blogs belonging to one user or all blogs
app.get('/api/blogs/:userId?', (req, res) => {
    let query;
    if(req.params.userId) {
        query = Blog.findAll({ include: [
            { model: User, where: { id: req.params.userId } },
            { model: Tag }
        ]})
    } else {
        query = Blog.findAll({ include: [Tag, User]})
    }
    return query.then(blogs => res.json(blogs))
})

Notice how we can query associated models in this case User model. Since userId is an optional parameter, we can query with or without condition.

Another similar use case is querying for blogs by tag.

index.js

// find blogs by tag
app.get('/api/blogs/:tag/tag', (req, res) => {
    Blog.findAll({
        include: [
            { model: Tag, where: { name: req.params.tag } }
        ]
    })
    .then(blogs => res.json(blogs))
})

That's it, folks. Hope it was helpful. You can find the whole code base for this little API here.

Disclaimer: this is a very rudimentary example made to demonstrate how to use Sequelize. It leaves a lot to be desired in terms of: authentication, RBAC, validation, error handling, etc.