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

一、背景

我使用阿里的开源项目 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'usestrict';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',//changedefaultdirmodeltomodelsqldialectOptions:{dateStrings:true,typeCast:true},define:{timestamps:false//don'taddthetimestampattributes(updatedAt,createdAt)},timezone:'+08:00'//timezonetolocaltime},{dialect:'postgres',database:'database',username:'username',password:'password',host:'host',port:'port',delegate:'postgres',baseDir:'postgres',//changedefaultdirmodeltopostgresdialectOptions:{dateStrings:true,typeCast:true},define:{"createdAt":"created_at","updatedAt":"updated_at"},timezone:'+08:00'}]}

这里有两点需要特别注意

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

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

四、生成数据库模型

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

module.exports=app=>{constDataTypes=app.Sequelize;constModel=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(){}returnModel;};

问题是我有三个数据库，每个库都有许多张表，我们不能针对每个表去手写 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.js、y.js、z.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.jsconstmoment=require('moment');//时间插件module.exports=app=>{constDataTypes=app.Sequelize;constModel=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格式，查询的时候想返回localtimereturnmoment(this.getDataValue('created_at')).format('YYYY-MM-DDHH: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_idapp.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'})}returnModel;};

在这里我遇到了一个问题，就是读不到 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中编写这个请求的逻辑，将车辆的全部信息返回给前端。

constController=require('egg').Controller;classUserControllerextendsController{asynclist(){let{ctx}=thisletres=awaitctx.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

作者：晴天同学