Egg Sequelize Postgres associated query and paging problems

This is the 20th day of my participation in the August Text Challenge.More challenges in August

The background,

I use Egg, an open source project of Ali, as a template for node middle tier development.

In simple terms, is to do a layer of interface level development at the bottom and front end.

Because the data source of the project is very complex and different databases are required to be used for data storage, mysql, mongoDB, and Postgres need to be connected in the configuration.

Environment and technology stack

If you don’t know how to create a template, go to the egg website.

However, I prefer to build my own development project environment, so that I can clearly eliminate redundant code, and I can use familiar configuration freely. When problems occur, I can quickly read and locate them, and I don’t have to spend time to understand how other people’s code is built and how the logic is implemented.

Set up the environment

From the official Egg documentation, create the following project directory

├─ ├─ ├─ ├─ download.txt ├─ download.txt ├─ download.txtCopy the code

Install the necessary plug-ins

$ npm i egg-sequelize -S

$ npm i egg-mongoose -S

$ npm i mysql2 -S

$ npm i pg -S

Here is a brief explanation of the plug-in installed above

• Egg-bin is the startup command used by the development environment
• Egg-mongoose is a plug-in needed to connect to and manipulate mongoDB, which requires careful documentation
• Egg-sequelize is a plug-in needed to connect to and manipulate mysql and Postgres, which requires careful documentation
• Mysql2 is the dependency required by egg-sequelize
• Pg is the dependency required by egg-Postgres

Above is the basic package installation, so our project is very clean.

Then you can have fun writing bugs.

Configure the connection to the database

All configurations are in config/config.default.js. Of course, if you want to use a third-party package, you need to register it in config/plugin.js:

// plugin.js
'use strict';

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

Once we have registered the plugin, the installed package can be referenced and used in the project, but we still need to specify some basic configurations, such as linking various database accounts and passwords. This part of the code actually belongs to the config class code, so put it under config.default.js.

The first is the configuration to link to mongoDB

exports.mongoose = {
  client: {
    url: 'mongodb://username:password@host:port/db'.options: {
      useUnifiedTopology: true.useNewUrlParser: true.// Mandatory parameters,}}};Copy the code

Then add the sequelize configuration to connect mysql and Postgres, which are basically the same configuration

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

There are two points to note here

1. Mysql, Postgres, and mongoDB will fight for permissions to the model folder, causing conflicts and errors, so be sure to configure baseDir and delegate options. Specify a different folder for each database to hold its respective Model files

2. The Postgres database tables store the underlined fields CREated_AT and updated_AT, but the query uses camel’s name, so use the define attribute to match the two

Generate database model

The database model is essentially a description file for each table. For example, if I have a table user, its model might look something like this

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

The problem was that I had three databases with many tables in each, and we couldn’t write models for each table by hand, which would be exhausting and stupid, so I used a plug-in to automatically generate all the models

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

Now, use this command to generate the model for the User table

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

If you want to generate a model for all tables in mysql

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

Here are some parameter descriptions

After using the above code to generate the description file of the data table, the project directory should look like this, where x.js, y.js, z.js are the description files of the database.

An egg - example ├ ─ ─ app │ ├ ─ ─ the model # mongo │ ├ ─ ─ modelsql # mysql │ ├ ─ ─ # postgres postgres │ │ └ ─ ─ x.j s │ │ └ ─ ─ y.j s │ │ ├─ ├─ ├─ ├─ ├─ ├─ download.txtCopy the code

Change the model to add a table association

Here’s Postgres as an example. Let’s first understand the relationship between the three tables: X table is the table of automobile brand, Y table is the table of brand country, Z table is the table of brand model. The diagram below.

Here you can clearly see that the country subparagraph corresponds to the country name, the name subparagraph corresponds to the brand name, and the Model subparagraph corresponds to the different car series under the brand

So this association has the following relationship

• When the country is known, passcs_idYou can find the car and the brand
• When you know the car, passcs_idWe can find out which brand made the car
• .

So how do these three tables relate?

This requires a method called Model. Associate, so I’m just going to put it in the code, and it’s in the comments.

// x.js
const moment = require('moment'); // Time plugin

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 stores the utc format by default
        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 () {
	// One-to-one association, the ID of table X uniquely corresponds to the CS_ID of table Y
    app.postgres.X.hasOne(app.postgres.Y,{
      foreignKey: 'cs_id'.sourceKey: 'id'.as: 'alias'.// Create an alias for table Y
    })
	The cs_id of the Z table has multiple ids corresponding to the id of the X table
    app.postgres.X.hasMany(app.postgres.Z, {
      foreignKey: 'cs_id'.sourceKey: 'id'})}return Model;
};
Copy the code

I encountered a problem here, that is, I could not read the descriptions of X, Y and Z. Then I went to the official website, which was as follows:

Then I scroll forward and there’s this:

All right, I’m out of line. See you!

So here’s how it works:

• App.postgres. X will look for app/postgres/x.js
• App.postgres. Example will look for app/postgres/ example.js
• App.postgres.exampleone looks for app/postgres/ example_one.js

Understand the corresponding relationship here, the table association is established.

Implement an interface

Now let’s try to implement an interface

Add a get request to app/router.js:

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

Then write the logic of this request in app/controller/XXXX.js to return all the vehicle information to the front end.

const Controller = require('egg').Controller;
class UserController extends Controller {
    async list() {
        let {
           ctx
        } = this
        let res = await ctx.postgres.models.x.findAndCountAll({
                distinct: true.// Remove the weight, otherwise the total quantity will be wrong
                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
    }
}
Copy the code

Such a simple query vehicle all the information of the code is finished, test it

npm run server

The curl – XGET ‘127.0.0.1:7001 / API/all_car_list