MySQL & Sequelize (ORM)

Using Eggjs to connect MySQL can use Sequelize

Sequelize is a widely used ORM framework that supports multiple data sources such as MySQL, PostgreSQL, SQLite, and MSSQL.

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

Configure the plug-in

config/plugin.js

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

Set the parameters

config/config.default.js

// sequelize
config.sequelize = {
  dialect: 'mysql'.host: '127.0.0.1'.port: 3306.database: 'egg-db'.username: 'root'.// Database user name
  password: '12345678'.// Database password
  timezone: '+ 08:00'.// Set the time zone
  define: {
    timestamps: true.// Automatically write the timestamp created_at updated_at
    paranoid: true.// Field generation soft delete timestamp deleted_at
    underscored: true.// Format all humps}};Copy the code

Creating a database

CREATE DATABASE IF NOT EXISTS `egg-db`;
Copy the code

Data migration

Sequelize provides the sequelize-CLI tool to implement Migrations

Install sequelize – cli

npm install --save-dev sequelize-cli
Copy the code

Place all migrations-related content in the Database directory and create a new.Sequelizerc configuration file in the project root directory

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

Initializes Migrations configuration files and directories

npx sequelize init:config
npx sequelize init:migrations
Copy the code

The database/config.json file and the database/migrations directory are generated

Modify the contents of database/config.json to change it to the database configuration used in the project

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

Create and write a Migration file to create the table

npx sequelize migration:generate --name=init-users
Copy the code

A migration file (${timestamp}-init-users.js) is generated in the database/migrations directory.

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

'use strict';

module.exports = {
  // The function that is called when performing the database upgrade to create the Users table
  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: 'Username, unique',},pass_word: STRING,
      gender: {
        type: DECIMAL,
        allowNull: false.defaultValue: 3.comment: 'Sex (1 male, 2 female, 3 Confidential)',},birthday: DATE,
      city: STRING,
      picture: STRING,
      created_at: DATE,
      updated_at: DATE,
      deleted_at: DATE,
    });
  },

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

Execute MIGRATE to create tables

Update database
npx sequelize db:migrate
If you have problems and need to roll back a change, use 'db:migrate:undo' to roll back a change
# npx sequelize db:migrate:undo
You can use 'db:migrate:undo:all' to revert to the initial state
# npx sequelize db:migrate:undo:all
Copy the code

Practice the demo

The user table is concerned about the one-to-many relationship between users’ fans in the table

Create a concern table

npx sequelize migration:generate --name=init-userfollows
Copy the code

Write the 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: 'User ID',},follower_id: {
        type: INTEGER,
        allowNull: false.comment: 'Id of the user being followed'.// Foreign key user table ID
        references: {
          model: 'users'.key: 'id',}},created_at: DATE,
      updated_at: DATE,
      deleted_at: DATE,
    });
  },

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

Update database
npx sequelize db:migrate
Copy the code

Create a relationship model of concern

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 = () = > {
    // One-to-one relationship Each followerId corresponds to one user
    Userfollow.belongsTo(app.model.User, {
      foreignKey: 'followerId'.targetKey: 'id'}); };return Userfollow;
};
Copy the code

Creating a user model

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: 'Username, unique',},passWord: STRING,
    gender: {
      type: DECIMAL,
      allowNull: false.defaultValue: 3.comment: 'Sex (1 male, 2 female, 3 Confidential)',},birthday: DATE,
    city: STRING,
    picture: STRING,
  });

  User.associate = () = > {
    // One-to-many relationship a user has multiple followers
    User.hasMany(app.model.Userfollow, {
      foreignKey: 'userId'.targetKey: 'followerId'}); };return User;
};

Copy the code

Realize query fans, concern API

Query a user’s fans

app/controller/userfollow.js

// Get the list of fans
async getFans() {
  const { ctx } = this;
  const data = await ctx.service.userfollow.getUsersByFollower(ctx.params.id, ctx.request.query);
  ctx.body = { status: true.msg: 'Fan list obtained success', data };
}
Copy the code

app/service/userfollow.js

/ / * * *
const { Op } = require('sequelize');
const { toInt } = require('.. /extend/utils'); // toInt Converts a string to a numeric type
/ / * * *

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

Query user concerns

app/controller/userfollow.js

// Get the list of concerns
async getFollowers() {
  const { ctx } = this;
  const data = await ctx.service.userfollow.getFollowersByUser(ctx.params.id, ctx.request.query);
  ctx.body = { status: true.msg: 'Concern list obtained successfully', data };
}
Copy the code

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

routing

app/router.js

router.get('/api/v1/user/:id/fans', controller.userfollow.getFans); // Get the user's fan list
router.get('/api/v1/user/:id/follow', controller.userfollow.getFollowers); // Get the user's concern list
Copy the code

Insert some test data into the table

// ***
Copy the code

Access to the routing

/ / GET http://127.0.0.1:7001/api/v1/user/1/fans? pageNumber=1&pageSize=10
{
    "status": true."msg": "Fan list success"."data": {
        "count": 2."rows": [{"id": 3."userName": "test3"."picture": null."city": "Hangzhou"."gender": "1"
            },
            {
                "id": 2."userName": "test2"."picture": null."city": "Shanghai"."gender": "3"}}}]/ / http://127.0.0.1:7001/api/v1/user/3/follow? pageNumber=1&pageSize=10
{
    "status": true."msg": "Focus list success"."data": {
        "count": 1."rows": [{"user": {
                    "id": 1."userName": "test1"."picture": null."city": "Beijing"."gender": "3"}},]}}Copy the code

Sequelize crud

increase

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

delete

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

change

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

check

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

For more information on Sequelize usage, visit www.sequelize.com.cn/

—END—

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Eggjs operates MySQL using the Sequelize (ORM) plug-in

MySQL & Sequelize (ORM)

Configure the plug-in

Set the parameters

Creating a database

Data migration

Install sequelize – cli

Initializes Migrations configuration files and directories

Create and write a Migration file to create the table

Execute MIGRATE to create tables

Practice the demo

The user table is concerned about the one-to-many relationship between users’ fans in the table

Create a concern table

Create a relationship model of concern

Creating a user model

Realize query fans, concern API

Query a user’s fans

Query user concerns

routing

Insert some test data into the table

Access to the routing

Sequelize crud

increase

delete

change

check

Eggjs operates MySQL using the Sequelize (ORM) plug-in

MySQL & Sequelize (ORM)

Configure the plug-in

Set the parameters

Creating a database

Data migration

Install sequelize – cli

Initializes Migrations configuration files and directories

Create and write a Migration file to create the table

Execute MIGRATE to create tables

Practice the demo

The user table is concerned about the one-to-many relationship between users’ fans in the table

Create a concern table

Create a relationship model of concern

Creating a user model

Realize query fans, concern API

Query a user’s fans

Query user concerns

routing

Insert some test data into the table

Access to the routing

Sequelize crud

increase

delete

change

check

Related Posts

JavaSE collection class

Interviewer: I’ve never even heard of CopyOnWriteArrayList

A dynamic proxy