How to integrate Express.js and Sequelize - Part 1

April 3, 2017 3 Min

Sequelize is an ORM for Node.js and it supports many databases such as MySQl, PostgreSQL, MSSQL, MariaDB and SQLite, I will show you how to using Sequelize in the Express. By the way, Sequelize is very different between v3 and v4, this article is focus on v4, for more information you can read official document.

0x01

First create a new Express app, you can build it yourself, but I recommended you use (express-generator)[https://expressjs.com/en/starter/generator.html], so let’s install generator first. In general, we using npm to help handle packages, for now, I recommended use Yarn, but this article is not about it so we just show you how to do.

# Install express-generator
yarn global add express-generator

# Create the app directory
mkdir express-sequelize
cd express-sequelize

# Initial and use pug template engine
express -f -v pug

# Install modules
yarn install

0x02

Install related modules about database, for this sample I will use SQLite.

# Install modules
yarn add sequelize sequelize-cli sqlite3

# Initial
node_modules/.bin/sequelize init

0x03

In this sample, we can manage masters, the master can owned many pets but pet only have a master, so generate two models first.

# Generate a table named Master have one filed named "name", the type is string
node_modules/.bin/sequelize model:create --name Master --attributes name:string(20)

# Generate a table named Pet have two fileds, it's name and MasterId, MasterId will be associate to Master table
node_modules/.bin/sequelize model:create --name Pet --attributes name:string(20),MasterId:integer

sequelize-cli will add primary key for you, the field named id and type is integer, so in the pet model we need add MasterId field, by default, sequelize use table name and Id be foreign key, and first word is uppercase.

Now you have two model files in the models directory and named master.js and pet.js, next step we will create a simple associate.

0x04

Open your master.js you will see the code likes below.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
'use strict';
module.exports = (sequelize, DataTypes) => {
  var Master = sequelize.define('Master', {
    name: DataTypes.STRING(20)
  }, {
    classMethods: {
      associate: function(models) {
        // associations can be defined here
      }
    }
  });
  return Master;
};

Let’s add associate.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
'use strict';

module.exports = (sequelize, DataTypes) => {
  const Master = sequelize.define('Master', {
    name: DataTypes.STRING(20)
  })

  Master.associate = models => {
    Master.hasMany(models.Pet)
  }

  return Master;
}

Next open your pet.js you will see the code likes below.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
'use strict';
module.exports = (sequelize, DataTypes) => {
  var Pet = sequelize.define('Pet', {
    name: DataTypes.STRING(20),
    MasterId: DataTypes.INTEGER
  }, {
    classMethods: {
      associate: function(models) {
        // associations can be defined here
      }
    }
  });
  return Pet;
};

Now add associate to Pet model.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
'use strict'

module.exports = (sequelize, DataTypes) => {
  const Pet = sequelize.define('Pet', {
    name: DataTypes.STRING(20),
    MasterId: DataTypes.INTEGER
  })

  Pet.associate = models => {
    Pet.belongsTo(models.Master)
  }

  return Pet
}

It’s so easy right?

0x05

In this step, we need to edit database config, open your config.json, you can find it in the config directory.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
{
  "development": {
    "dialect": "sqlite",
    "storage": "./express-sequelize.development.sqlite"
  },
  "test": {
    "dialect": "sqlite",
    "storage": "memory"
  },
  "production": {
    "username": "root",
    "password": null,
    "database": "database_production",
    "host": "127.0.0.1",
    "dialect": "mysql"
  }
}

0x06

This is last step in part 1, now we need to initial database, create table and some else.

node_modules/.bin/sequelize db:migrate

You will find a sqlite file created in your app root.

If you need full source code you can find below:
https://github.com/ShawnOY/express-sequelize/tree/part1

Let me know if you have any question, thanks.