Goal:

Connect to the mysql database using egg and Sequelize

Preparations:

1. Initialize the project and install BREW and mysql

(1) Install BREW

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
Copy the code

(2) Install mysql

brew install mysql
brew services start mysql
Copy the code

(3) Initialize a project through NPM:

> $ mkdir sequelize-project && cd sequelize-project 
> $ npm init egg
> --type=simple $ npm i
Copy the code

Install and configure the egg-Sequelize plugin (which will help us load the defined Model object into app and CTX) and mysqL2 module:

The installation

npm install --save egg-sequelize mysql2
Copy the code

Introduce the egg-sequelize plug-in in config/plugin.js

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

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'};Copy the code

Once the above configuration is complete, a project using Sequelize is initialized. Egg-sequelize and Sequelize also support additional configuration items, which can be found in their documentation.

(5) Write code

Now we are ready to write the code to implement the business logic. First, we will write the user model in app/model/ :

'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;
};
Copy the code

This Model can be accessed in 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(a); ctx.status = 200;
  }
}

module.exports = UserController;
Copy the code

Finally we mount the controller to the route:

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

The interface for CURD operations on the Users table is developed.

Trampling guide:

1. The table name is automatically changed to plural. The user table in the demo I created was automatically appended with an S to make it users.Solution (Choose one of the following two methods) :

  • When defining the Model, set to disallow conversions to complex numbers
const { STRING, INTEGER, DATE } = app.Sequelize;

  const User = app.model.define('user', {
    id: { type: INTEGER, primaryKey: true, autoIncrement: true },
    real_name: STRING(10),
    emp_id: INTEGER,
    emp_name: STRING(6), created_at: DATE, updated_at: DATE,},{// Do not change table names. By default, Sequelize will automatically convert all passed model names (the first parameter of define) to complex numbers. true });Copy the code
  • Disable conversion to plural in the configuration file
// app/config/config.default.js
config.sequelize = {
     "username": "root"."password": null,
    "database": "egg-sequelize-doc-default"."host": "127.0.0.1"."dialect": "mysql"Define: {freezeTableName: true,};Copy the code

2. Turn the underline into a hump. Error reported because created_AT will be converted to createdAt.Solution: Set not to hump in configuration

// app/config/config.default.js
config.sequelize = {
     "username": "root"."password": null,
    "database": "egg-sequelize-doc-default"."host": "127.0.0.1"."dialect": "mysql"Define: {schema: true,// prohibit conversion to hump},};Copy the code

Mysql will automatically save time in UTC format. You can configure it in config:

exports.sequelize = {
    dialect: 'mysql'. timezone:'+ 08:00'// Save as local time zone}Copy the code

Egg-sequelize, however, returns UTC when reading the time. You need to change the configuration to add:

exports.sequelize = {
    dialect: 'mysql'. timezone:'+ 08:00',// Save as local time zone dialectOptions: {dateStrings: true,typeCast(field, next) {
        // for reading from database
        if (field.type === "DATETIME") {
          return field.string(a); } return next(); }}}Copy the code

Created_at and UPDATed_AT fields these two fields are required to create a database table.

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

  const User = app.model.define('user', {
    id: { type: INTEGER, primaryKey: true, autoIncrement: true },
    real_name: STRING(10),
    emp_id: INTEGER,
    emp_name: STRING(6),
    created_at: DATE,
    updated_at: DATE,
  });

  return User;
};
Copy the code