egg sequelize postgres 关联查询及分页问题

·  阅读 375
egg  sequelize  postgres  关联查询及分页问题

这是我参与8月更文挑战的第20天,活动详情查看:8月更文挑战

一、背景

我使用阿里的开源项目 egg 为模版做 node 中间层开发。

简单而言,就是在底层和前端做一层接口层面的开发。

由于项目的数据来源十分复杂,需要使用不同的数据库进行数据存储,所以在配置中需要同时连接 mysql,mongoDB 和 postgres。

二、环境和技术栈

egg 创建模板还是非常方便的,不会的童鞋可以到官网看一下。

但我比较喜欢自己搭建开发的项目环境,这样可以清楚的剔除冗余代码,也可以自由使用熟悉的配置,出现问题能快读定位,不必花时间去理解别人的代码是怎样构建的,逻辑是如何实现的。

搭建环境

根据 egg 官方文档,创建如下项目目录

egg-example
├── app
│   ├── controller
│   │   └── XXXX.js
│   └── router.js
├── config
│   └── config.default.js
└── package.json
复制代码

安装必要的插件

$ npm i egg-sequelize -S

$ npm i egg-mongoose -S

$ npm i mysql2 -S

$ npm i pg -S

这里对上面安装的插件做个简单解释

  • egg-bin 是开发环境用到的启动命令
  • egg-mongoose 是连接和操作 mongoDB 需要的插件,使用时需要仔细阅读文档
  • egg-sequelize 是连接和操作 mysql 和 postgres 需要的插件,使用时需要仔细阅读文档
  • mysql2 是 egg-sequelize 需要的依赖
  • pg 是 egg-postgres 需要的依赖

上面就是最基础的包安装,这样我们的项目就十分的清爽了。

然后就可以开心的写 bug 了。

三、配置连接数据库

所有的配置都在 config/config.default.js中。 当然,如果想使用某个第三方包,需要在config/plugin.js 中注册一下:

// plugin.js
'use strict';

exports.sequelize = {
  enable: true,
  package: 'egg-sequelize',
};
exports.mongoose = {
  enable: true,
  package: 'egg-mongoose',
};
复制代码

我们注册好 plugin 之后,安装的包就可以在项目中引用并使用,但是我们还需要指定一些最基础的配置,比如链接各个数据库账号和密码等。这部分代码其实属于配置类的代码,所以放到config.default.js下。

首先是链接 mongoDB 的配置

exports.mongoose = {
  client: {
    url: 'mongodb://username:password@host:port/db',
    options: {
      useUnifiedTopology: true,
      useNewUrlParser: true, // 必须参数
    },
  },
};

复制代码

然后添加 sequelize 的相关配置来链接 mysql 和 postgres,这两个数据库的配置基本是一样的

exports.sequelize = {
  datasources: [
    {
      dialect: 'mysql',
      host: 'host',
      port: 'port',
      database: 'database',
      username: 'username',
      password: 'password',
      delegate: 'modelsql',
      baseDir: 'modelsql', // change default dir model to modelsql
      dialectOptions: {
        dateStrings: true,
        typeCast: true
      },
      define: {
        timestamps: false // don't add the timestamp attributes (updatedAt, createdAt)
      },
      timezone: '+08:00'  //timezone to local time
    },{
      dialect: 'postgres',
      database: 'database',
      username: 'username',
      password: 'password',
      host: 'host',
      port: 'port',
      delegate: 'postgres',
      baseDir: 'postgres',// change default dir model to postgres
      dialectOptions: {
        dateStrings: true,
        typeCast: true
      },
      define: {
        "createdAt": "created_at",
        "updatedAt": "updated_at"
      },
      timezone: '+08:00'
    }
  ]
}
复制代码

这里有两点需要特别注意

  1. egg 中默认将数据库的 model 文件放在app/model中,因此 mysql、postgres、mongoDB 会争夺 model 文件夹的权限而产生冲突和报错,所以,一定要配置baseDirdelegate 选项,为每个数据库指定不同的文件夹来存放各自的 model 文件

  2. postgres 数据库表中存储的是字段created_atupdated_at这种下划线格式,但是查询使用的是驼峰式的命名,因此,要利用define属性将二者对应起来

四、生成数据库模型

数据库模型其实就是针对每个 table 的描述文件,比如我有一个 user 表,它的模型可能是这样的

module.exports = app => {
  const DataTypes = app.Sequelize;
  const Model = app.modelsql.define('users', {
    id: {
      type: DataTypes.INTEGER,
      allowNull: false,
      primaryKey: true,
      autoIncrement: true
    },
    username: {
      type: DataTypes.STRING(18),
      allowNull: true
    },
    password: {
      type: DataTypes.STRING(255),
      allowNull: true
    },
  }, {
    tableName: 'users'
  });
  Model.associate = function() {}
  return Model;
};
复制代码

问题是我有三个数据库,每个库都有许多张表,我们不能针对每个表去手写 model,会累死人的,也很蠢,所以,我利用一个插件来自动生成所有的 model

npm install -g egg-sequelize-auto npm install -g mysql2

现在,使用这个命令来生成 user 表的 model

egg-sequelize-auto -o "./modelsql" -h host -d db -u username -x password -p port -t user

如果要生成 mysql 中所有表的模型

egg-sequelize-auto -o "./modelsql" -h host -d db -u username -x password -p port -e mysql

下面是一些参数说明

optiondescription
-h--host 数据库的IP地址 [required]
-d--database 数据库名 [required]
-u--user 用户名
-x--pass 密码
-p--port 端口
-c--config 配置文件 [require json file]
-o--output 目标文件夹
-t,--tableNames 数据表表名
-e--dialect 数据库类型: postgres, mysql, sqlite ..et

用上面的代码生成数据表的描述文件以后,项目目录应该是这样的,其中的x.jsy.jsz.js就是数据库的描述文件。

egg-example
├── app
│   ├── model       # mongoDB 
│   ├── modelsql    # mysql
│   ├── postgres    # postgres
│   │   └── x.js
│   │   └── y.js
│   │   └── z.js
│   ├── controller
│   │   └── XXXX.js
│   └── router.js
├── config
│   └── config.default.js
└── package.json
复制代码

五、更改模型添加表关联

这里以 postgres 为例。 先来理解三张表的关系:X 表是汽车品牌表,Y 表是品牌国别表,Z 表是品牌车型表。 如下图。

在这里插入图片描述

这里你能清楚的看出来,country子段对应着国家名称,name子段对应着品牌名称,model子段对应着品牌下的不同车系

所以这个关联具有如下的关系

  • 当知道国家时,通过cs_id可以查到车系和品牌
  • 当知道车系时,通过cs_id可以查到车辆时哪个品牌生产的
  • ...

那么这三个表如何建立关联呢?

这需要一个方法叫做 Model.associate,我就直接放代码了,需要注意的地方在注释里。

// x.js
const moment = require('moment'); // 时间插件

module.exports = app => {
  const DataTypes = app.Sequelize;

  const Model = app.postgres.define('x', {
    id: {
      type: DataTypes.INTEGER,
      allowNull: false,
      defaultValue: 'nextval(x_id_seq::regclass)',
      primaryKey: true
    },
    created_at: {
      type: DataTypes.TIME,
      get() { // postgres 默认存储 utc 格式,查询的时候想返回 local time
        return moment(this.getDataValue('created_at')).format('YYYY-MM-DD HH:mm:ss')
      },
      allowNull: false
    },
    updated_at: {
      type: DataTypes.TIME,
      allowNull: false
    },
    name: {
      type: DataTypes.STRING,
      allowNull: false
    }
  }, {
    tableName: 'x'
  });

  Model.associate = function () {
	// 一对一关联,X 表的 id 唯一对应 Y 表的 cs_id
    app.postgres.X.hasOne(app.postgres.Y,{
      foreignKey: 'cs_id',
      sourceKey: 'id',  
      as: 'alias',  // 查询结果中,给 Y 表配置一个别名
    })
	// 一对多关联,Z 表的 cs_id 有多个与 X 表的 id 对应
    app.postgres.X.hasMany(app.postgres.Z, {
      foreignKey: 'cs_id',
      sourceKey: 'id'
    })
  }

  return Model;
};
复制代码

在这里我遇到了一个问题,就是读不到 X、Y、Z 这几个描述,然后我去看官网,官网是这样的:

在这里插入图片描述

然后我往前翻,发现前面有个: 在这里插入图片描述

好吧,是我唐突了,告辞!

所以它的规则是这样的:

  • app.postgres.X 会寻找 app/postgres/x.js
  • app.postgres.Example 会寻找 app/postgres/example .js
  • app.postgres.ExampleOne 会寻找 app/postgres/example_one .js

搞明白这里面的对应关系,表关联就建立好了。

六、实现一个接口

现在我们尝试实现一个接口

app/router.js下添加一个 get 请求:

router.get('/api/all_car_list', controller.XXXX.list)

然后在app/controller/XXXX.js中编写这个请求的逻辑,将车辆的全部信息返回给前端。

const Controller = require('egg').Controller;
class UserController extends Controller {
    async list() {
        let {
           ctx
        } = this
        let res = await ctx.postgres.models.x.findAndCountAll({
                distinct: true, // 去重,否则总数量会出错
                include: [
                        {
                                model: ctx.postgres.models.y,
                                as: 'alias',
                                attributes: ['country']
                        },{
                                model: ctx.postgres.models.z,
                                attributes: ['model']
                        }
                ],
                attributes: [
                        'id', 'name', 'created_at'
                ]
        })
        ctx.body = res
    }
}
复制代码

这样一个简单的查询车辆的全部信息的代码就写完了,测试一下吧

npm run server

curl -XGET '127.0.0.1:7001/api/all_car_list

分类:
后端
标签: