Sequelize

In the previous section, we showed how to access the database through the egg-mysql plugin in the framework. In some more complex applications, we may need an ORM framework to help us manage the data layer code. In the Node.js community, sequelize is a widely used ORM framework that supports multiple data sources such as MySQL, PostgreSQL, SQLite, and MSSQL.

In this chapter, we will walk through the steps of how to use sequelize in an egg project by developing an example of doing CURD on the data in the users table in MySQL.

# Preparing

In this example, we will use sequelize to connect to the MySQL data source, so we need to install MySQL on the machine before we start writing code. If it is MacOS, we can quickly install it via homebrew:

brew install mysql
brew services start mysql

# Initialization

Init project by npm:

$ mkdir sequelize-project && cd sequelize-project
$ npm init egg --type=simple
$ npm i

Install and configure the egg-sequelize plugin (which will help us load the defined Model object onto app and ctx ) and the mysql2 module:

• Install

npm install --save egg-sequelize mysql2

• Import egg-sequelize in config/plugin.js

exports.sequelize = {
  enable: true,
  package: 'egg-sequelize',
};

• Write the sequelize configuration in config/config.default.js

config.sequelize = {
  dialect: 'mysql',
  host: '127.0.0.1',
  port: 3306,
  database: 'egg-sequelize-doc-default',
};

We can configure different data source addresses in different environment configurations to distinguish the databases used by different environments. For example, we can create a new config/config.unittest.js configuration file and write the following configuration. The connected database points to egg-sequelize-doc-unittest.

exports.sequelize = {
  dialect: 'mysql',
  host: '127.0.0.1',
  port: 3306,
  database: 'egg-sequelize-doc-unittest',
};

After completing the above configuration, a project using sequelize is initialized. egg-sequelize and sequelize also support more configuration items, which can be found in their documentation.

# Database and Migrations Initialization

Next, let's temporarily leave the code of the egg project, design and initialize our database. First, we quickly create two databases for development and testing locally using the mysql command:

mysql -u root -e 'CREATE DATABASE IF NOT EXISTS `egg-sequelize-doc-default`;'
mysql -u root -e 'CREATE DATABASE IF NOT EXISTS `egg-sequelize-doc-unittest`;'

Then we started designing the users table, which has the following data structure:

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `name` varchar(30) DEFAULT NULL COMMENT 'user name',
  `age` int(11) DEFAULT NULL COMMENT 'user age',
  `created_at` datetime DEFAULT NULL COMMENT 'created time',
  `updated_at` datetime DEFAULT NULL COMMENT 'updated time',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='user';

We can build the table directly through the mysql command, but this is not a good practist for multiplayer collaboration. During the evolution of the project, each iteration is possible to make changes to the database data structure, how to track the data changes of each iteration, and quickly change the data structure in different environments (development, testing, CI) and switch bettween iterative? At this point we need Migrations to help us manage the changes in the data structure.

Sequelize provides the sequelize-cli tool to implement Migrations, and we can also introduce sequelize-cli in the egg project.

• Install sequelize-cli

npm install --save-dev sequelize-cli

In the egg project, we want to put all the database Migrations related content in the database directory, so we create a new .sequelizerc configuration file in the project root directory:

'use strict';

const path = require('path');

module.exports = {
  config: path.join(__dirname, 'database/config.json'),
  'migrations-path': path.join(__dirname, 'database/migrations'),
  'seeders-path': path.join(__dirname, 'database/seeders'),
  'models-path': path.join(__dirname, 'app/model'),
};

• Init Migrations Configuration Files and Directories

npx sequelize init:config
npx sequelize init:migrations

After the execution, the database/config.json file and the database/migrations directory will be generated. We will modify the contents of database/config.json. It was changed to the database configuration used in our project:

{
  "development": {
    "username": "root",
    "password": null,
    "database": "egg-sequelize-doc-default",
    "host": "127.0.0.1",
    "dialect": "mysql"
  },
  "test": {
    "username": "root",
    "password": null,
    "database": "egg-sequelize-doc-unittest",
    "host": "127.0.0.1",
    "dialect": "mysql"
  }
}

At this point sequelize-cli and related configuration are also initialized, we can start writing the project's first Migration file to create one of our users table.

npx sequelize migration:generate --name=init-users

After execution, a migration file (${timestamp}-init-users.js) is generated in the database/migrations directory. We modify it to handle initializing the users table:

'use strict';

module.exports = {
  // The function called when performing a database upgrade, create a `users` table
  up: async (queryInterface, Sequelize) => {
    const { INTEGER, DATE, STRING } = Sequelize;
    await queryInterface.createTable('users', {
      id: { type: INTEGER, primaryKey: true, autoIncrement: true },
      name: STRING(30),
      age: INTEGER,
      created_at: DATE,
      updated_at: DATE,
    });
  },
  // The function called when performing a database downgrade, delete the `users` table
  down: async queryInterface => {
    await queryInterface.dropTable('users');
  },
};

• Execute migrate for database changes

# upgrade database
npx sequelize db:migrate
# if there is a problem that needs to be rolled back, you can roll back a change via `db:migrate:undo`
# npx sequelize db:migrate:undo
# can be rolled back to the initial state via `db:migrate:undo:all`
# npx sequelize db:migrate:undo:all

After execution, our database initialization is complete.

# Coding

Finally we can start writing code to implement business logic. First, let's write the user model in the app/model/ directory:

'use strict';

module.exports = app => {
  const { STRING, INTEGER, DATE } = app.Sequelize;

  const User = app.model.define('user', {
    id: { type: INTEGER, primaryKey: true, autoIncrement: true },
    name: STRING(30),
    age: INTEGER,
    created_at: DATE,
    updated_at: DATE,
  });

  return User;
};

This model can be accessed in the Controller and Service via app.model.User or ctx.model.User, for example we write app/controller/users.js:

// app/controller/users.js
const Controller = require('egg').Controller;

function toInt(str) {
  if (typeof str === 'number') return str;
  if (!str) return str;
  return parseInt(str, 10) || 0;
}

class UserController extends Controller {
  async index() {
    const ctx = this.ctx;
    const query = { limit: toInt(ctx.query.limit), offset: toInt(ctx.query.offset) };
    ctx.body = await ctx.model.User.findAll(query);
  }

  async show() {
    const ctx = this.ctx;
    ctx.body = await ctx.model.User.findByPk(toInt(ctx.params.id));
  }

  async create() {
    const ctx = this.ctx;
    const { name, age } = ctx.request.body;
    const user = await ctx.model.User.create({ name, age });
    ctx.status = 201;
    ctx.body = user;
  }

  async update() {
    const ctx = this.ctx;
    const id = toInt(ctx.params.id);
    const user = await ctx.model.User.findByPk(id);
    if (!user) {
      ctx.status = 404;
      return;
    }

    const { name, age } = ctx.request.body;
    await user.update({ name, age });
    ctx.body = user;
  }

  async destroy() {
    const ctx = this.ctx;
    const id = toInt(ctx.params.id);
    const user = await ctx.model.User.findByPk(id);
    if (!user) {
      ctx.status = 404;
      return;
    }

    await user.destroy();
    ctx.status = 200;
  }
}

module.exports = UserController;

Finally we will mount this controller on the route:

// app/router.js
module.exports = app => {
  const { router, controller } = app;
  router.resources('users', '/users', controller.users);
};

The interface for the CURD operation of the users table is developed. To verify that the code logic is correct, we need to write some testcases to verify.

# Unit Test

Before writing the test, because in the previous egg configuration, we pointed the unit test environment and development environment to different databases, so we need to initialize the data structure of the test database through Migrations:

NODE_ENV=test npx sequelize db:migrate:up

Unit tests with database access are particularly cumbersome to write directly, We need to create a series of data to prepare the test data is a very cumbersome process. To simplify single testing, we can quickly create test data with the factory-girl module.

• Install factory-girl

npm install --save-dev factory-girl

• Define the data model of factory-girl into test/factories.js

// test/factories.js
'use strict';

const { factory } = require('factory-girl');

module.exports = app => {
  // Factory instance can be accessed via app.factory
  app.factory = factory;

  // Define user and default data
  factory.define('user', app.model.User, {
    name: factory.sequence('User.name', n => `name_${n}`),
    age: 18,
  });
};

• Initialize the file test/.setup.js, introduce the factory, and ensure that the data is cleaned after the test is executed to avoid being affected.

const { app } = require('egg-mock/bootstrap');
const factories = require('./factories');

before(() => factories(app));
afterEach(async () => {
  // clear database after each test case
  await Promise.all([
    app.model.User.destroy({ truncate: true, force: true }),
  ]);
});

Then we can start writing real test cases:

// test/app/controller/users.test.js
const { assert, app } = require('egg-mock/bootstrap');

describe('test/app/controller/users.test.js', () => {
  describe('GET /users', () => {
    it('should work', async () => {
      // Quickly create some users object into the database via factory-girl
      await app.factory.createMany('user', 3);
      const res = await app.httpRequest().get('/users?limit=2');
      assert(res.status === 200);
      assert(res.body.length === 2);
      assert(res.body[0].name);
      assert(res.body[0].age);
    });
  });

  describe('GET /users/:id', () => {
    it('should work', async () => {
      const user = await app.factory.create('user');
      const res = await app.httpRequest().get(`/users/${user.id}`);
      assert(res.status === 200);
      assert(res.body.age === user.age);
    });
  });

  describe('POST /users', () => {
    it('should work', async () => {
      app.mockCsrf();
      let res = await app.httpRequest().post('/users')
        .send({
          age: 10,
          name: 'name',
        });
      assert(res.status === 201);
      assert(res.body.id);

      res = await app.httpRequest().get(`/users/${res.body.id}`);
      assert(res.status === 200);
      assert(res.body.name === 'name');
    });
  });

  describe('DELETE /users/:id', () => {
    it('should work', async () => {
      const user = await app.factory.create('user');

      app.mockCsrf();
      const res = await app.httpRequest().delete(`/users/${user.id}`);
      assert(res.status === 200);
    });
  });
});

Finally, if we need to run unit tests in the CI, we need to ensure that we perform a migration to ensure data structure updates before executing the test code. For example, we declare scripts.ci in package.json to execute the unit test in the CI environment:

{
  "scripts": {
    "ci": "eslint . && NODE_ENV=test npx sequelize db:migrate && egg-bin cov"
  }
}

# Full Example

A more complete example can be found in eggjs/examples/sequelize.

# Boilerplate

We also provide sequelize boilerplate that integrates the modules egg-sequelize, sequelize-cli and factory-girl provided in this documentation. You can quickly initialize a new application based on it by npm init egg --type=sequelize.