Eggjs使用Sequelize (ORM)插件操作MySQL

勇往直前go
1,777 阅读4分钟

Eggjs 链接 MySQL & Sequelize (ORM)

使用Eggjs链接MySQL可以使用Sequelize

sequelize 是一个广泛使用的 ORM 框架，它支持 MySQL、PostgreSQL、SQLite 和 MSSQL 等多个数据源。

npm install --save egg-sequelize mysql2

配置插件

config/plugin.js

sequelize: {
  enable: true,
    package: 'egg-sequelize',
},

设置参数

config/config.default.js

// sequelize
config.sequelize = {
  dialect: 'mysql',
  host: '127.0.0.1',
  port: 3306,
  database: 'egg-db',
  username: 'root', // 数据库用户名
  password: '12345678', // 数据库密码
  timezone: '+08:00', // 设置时区
  define: {
    timestamps: true, // 自动写入时间戳 created_at updated_at
    paranoid: true, // 字段生成软删除时间戳 deleted_at
    underscored: true, // 所有驼峰命名格式化
  },
};

创建数据库

CREATE DATABASE IF NOT EXISTS `egg-db`;

数据迁移

sequelize 提供了 sequelize-cli 工具来实现 Migrations

安装sequelize-cli

npm install --save-dev sequelize-cli

将所有数据库 Migrations 相关的内容都放在 database 目录下,在项目根目录下新建一个 .sequelizerc 配置文件

.sequelizerc

'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'),
};

初始化 Migrations 配置文件和目录

npx sequelize init:config
npx sequelize init:migrations

执行完后会生成 database/config.json 文件和 database/migrations 目录

修改 database/config.json 中的内容，将其改成项目中使用的数据库配置

database/config.json

{
  "development": {
    "dialect": "mysql",
    "host": "127.0.0.1",
    "port": 3306,
    "database": "egg-db",
    "username": "root",
    "password": "12345678"
  },
  "test": {
    "username": "root",
    "password": null,
    "database": "database_test",
    "host": "127.0.0.1",
    "dialect": "mysql"
  },
  "production": {
    "username": "root",
    "password": null,
    "database": "database_production",
    "host": "127.0.0.1",
    "dialect": "mysql"
  }
}

创建并编写Migration 文件来创建表

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

执行完后会在 database/migrations 目录下生成一个 migration 文件(${timestamp}-init-users.js)

database/migrations/202104****-init-users.js

'use strict';

module.exports = {
  // 在执行数据库升级时调用的函数，创建 users 表
  up: async (queryInterface, Sequelize) => {
    const { INTEGER, STRING, DATE, DECIMAL } = Sequelize;
    await queryInterface.createTable('users', {
      id: { type: INTEGER, primaryKey: true, autoIncrement: true },
      user_name: {
        type: STRING,
        allowNull: false,
        unique: true,
        comment: '用户名，唯一',
      },
      pass_word: STRING,
      gender: {
        type: DECIMAL,
        allowNull: false,
        defaultValue: 3,
        comment: '性别（1 男性，2 女性，3 保密）',
      },
      birthday: DATE,
      city: STRING,
      picture: STRING,
      created_at: DATE,
      updated_at: DATE,
      deleted_at: DATE,
    });
  },

  down: async queryInterface => {
    await queryInterface.dropTable('users');
  },
};

执行 migrate 进行数据库变更 创建表

# 升级数据库
npx sequelize db:migrate
# 如果有问题需要回滚，可以通过 `db:migrate:undo` 回退一个变更
# npx sequelize db:migrate:undo
# 可以通过 `db:migrate:undo:all` 回退到初始状态
# npx sequelize db:migrate:undo:all

实践demo

用户表 关注表 用户 粉丝 一对多关系

创建关注关系表

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

编写Migration

database/migrations/202104****-init-Userfollow.js

'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    const { INTEGER, DATE } = Sequelize;
    await queryInterface.createTable('userfollows', {
      id: { type: INTEGER, primaryKey: true, autoIncrement: true },
      user_id: {
        type: INTEGER,
        allowNull: false,
        comment: '用户的 id',
      },
      follower_id: {
        type: INTEGER,
        allowNull: false,
        comment: '被关注用户的 id',
        // 外键 用户表ID
        references: {
          model: 'users',
          key: 'id',
        },
      },
      created_at: DATE,
      updated_at: DATE,
      deleted_at: DATE,
    });
  },

  down: async queryInterface => {
    await queryInterface.dropTable('userfollows');
  },
};

# 升级数据库
npx sequelize db:migrate

创建关注关系模型

app/model/userfollow.js

'use strict';

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

  const Userfollow = app.model.define('userfollows', {
    id: { type: INTEGER, primaryKey: true, autoIncrement: true },
    userId: INTEGER,
  });

  Userfollow.associate = () => {
    // 一对一关系 每个 followerId 对应一个用户
    Userfollow.belongsTo(app.model.User, {
      foreignKey: 'followerId',
      targetKey: 'id',
    });
  };

  return Userfollow;
};

创建用户模型

app/model/users.js

'use strict';

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

  const User = app.model.define('users', {
    id: { type: INTEGER, primaryKey: true, autoIncrement: true },
    userName: {
      type: STRING,
      allowNull: false,
      unique: true,
      comment: '用户名，唯一',
    },
    passWord: STRING,
    gender: {
      type: DECIMAL,
      allowNull: false,
      defaultValue: 3,
      comment: '性别（1 男性，2 女性，3 保密）',
    },
    birthday: DATE,
    city: STRING,
    picture: STRING,
  });

  User.associate = () => {
    // 一对多关系 一个用户有多个粉丝
    User.hasMany(app.model.Userfollow, {
      foreignKey: 'userId',
      targetKey: 'followerId',
    });
  };

  return User;
};

实现查询粉丝、关注 API

查询用户的粉丝

app/controller/userfollow.js

// 获取粉丝列表
async getFans() {
  const { ctx } = this;
  const data = await ctx.service.userfollow.getUsersByFollower(ctx.params.id, ctx.request.query);
  ctx.body = { status: true, msg: '粉丝列表获取成功', data };
}

app/service/userfollow.js

// ***
const { Op } = require('sequelize');
const { toInt } = require('../extend/utils'); // toInt 将字符串转为数值型
// ***

async getUsersByFollower(followerId, query) {
  const { pageNumber = 1, pageSize = 10 } = query;
  const result = await this.ctx.model.User.findAndCountAll({
    limit: toInt(pageSize),
    offset: toInt(pageSize) * (toInt(pageNumber) - 1),
    attributes: [ 'id', 'userName', 'picture', 'city', 'gender' ],
    order: [[ 'id', 'desc' ]],
    include: [
      {
        model: this.ctx.model.Userfollow,
        attributes: [ ],
        where: {
          followerId,
          userId: {
            [ Op.ne]: followerId,
          },
        },
      },
    ],
  });
  return result;
}

查询用户的关注

app/controller/userfollow.js

// 获取关注列表
async getFollowers() {
  const { ctx } = this;
  const data = await ctx.service.userfollow.getFollowersByUser(ctx.params.id, ctx.request.query);
  ctx.body = { status: true, msg: '关注列表获取成功', data };
}

app/service/userfollow.js

async getFollowersByUser(userId, query) {
  const { pageNumber = 1, pageSize = 10 } = query;
  const result = await this.ctx.model.Userfollow.findAndCountAll({
    limit: toInt(pageSize),
    offset: toInt(pageSize) * (toInt(pageNumber) - 1),
    order: [[ 'id', 'desc' ]],
    attributes: [ ],
    include: [
      {
        model: this.ctx.model.User,
        attributes: [ 'id', 'userName', 'picture', 'city', 'gender' ],
      },
    ],
    where: {
      userId,
      followerId: {
        [Op.ne]: userId,
      },
    },
  });
  return result;
}

路由

app/router.js

router.get('/api/v1/user/:id/fans', controller.userfollow.getFans); // 获取用户的粉丝列表
router.get('/api/v1/user/:id/follow', controller.userfollow.getFollowers); // 获取用户的关注列表

在表中插入几条测试数据

// ***

访问路由

// GET http://127.0.0.1:7001/api/v1/user/1/fans?pageNumber=1&pageSize=10
{
    "status": true,
    "msg": "粉丝列表获取成功",
    "data": {
        "count": 2,
        "rows": [
            {
                "id": 3,
                "userName": "test3",
                "picture": null,
                "city": "杭州",
                "gender": "1"
            },
            {
                "id": 2,
                "userName": "test2",
                "picture": null,
                "city": "上海",
                "gender": "3"
            }
        ]
    }
}

// http://127.0.0.1:7001/api/v1/user/3/follow?pageNumber=1&pageSize=10
{
    "status": true,
    "msg": "关注列表获取成功",
    "data": {
        "count": 1,
        "rows": [
            {
                "user": {
                    "id": 1,
                    "userName": "test1",
                    "picture": null,
                    "city": "北京",
                    "gender": "3"
                }
            },
        ]
    }
}

Sequelize 的 crud

增

const result = await this.ctx.model.Userfollow.create({
  userId,
  followerId,
});

删

const result = await this.ctx.model.Userfollow.destroy({
  where: {
    userId,
    followerId,
  },
});

改

// ...
const user = await this.ctx.model.User.findByPk(id);
// ...
await user.update({ userName, gender, birthday, city, picture });

查

const result = await this.ctx.model.Userfollow.findOne({
  where: {
    userId,
    followerId,
  },
});

关于更多Sequelize的用法 访问 www.sequelize.com.cn/

image.png

---END---

avatar
文章
阅读
粉丝
相关推荐
egg-sequelize插件使用与拓展
2.6k阅读
 · 
3点赞
egg sequelize 实践
13k阅读
 · 
59点赞
Node.js后端开发系列之sequelize零基础快速入门实战
5.6k阅读
 · 
43点赞
一文看懂 Eggjs-基础全面讲解（完结）
5.3k阅读
 · 
30点赞
自定义Egg.js的请求级别日志
7.8k阅读
 · 
51点赞