Eggjs Sequelize (ORM) MySQL

Eggjs Sequelize (ORM) MySQL

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, //All camel case naming format }, }; Copy code

Create database

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

data migration

sequelize provides the sequelize-cli tool to implement Migrations

Install sequelize-cli

npm install --save-dev sequelize-cli duplicated code

Put all database Migrations related content in

database
Under the directory, create a new one in the project root directory
.sequelizerc
Configuration file

.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 code

Initialize Migrations configuration files and directories

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

Will be generated after execution

database/config.json
Files and
database/migrations
table of Contents

modify

database/config.json
The content in and 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 code

Create and write a migration file to create a table

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

After execution, it will be in

database/migrations
A migration file (
${timestamp}-init-users.js
)

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

'use strict' ; module .exports = { //A function called when performing a database upgrade to create a 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 : 'Gender (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 code

Execute migrate to make database changes and create tables

# Upgrade database npx sequelize db:migrate # If you have a problem and need to roll back, you can roll back a change through `db:migrate:undo` # npx sequelize db:migrate:undo # You can roll back to the initial state through `db:migrate:undo:all` # npx sequelize db :migrate:undo:allCopy code

Practice demo

One-to-many relationship between users and fans

Create a follow relationship table

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

Write 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's id' , }, follower_id : { type : INTEGER, allowNull : false , comment : 'id of the followed user' , //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 code
# Upgrade database npx sequelize db:migrate Copy code

Create a follow relationship model

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 a user Userfollow.belongsTo(app.model.User, { foreignKey : 'followerId' , targetKey : 'id' , }); }; return Userfollow; }; Copy code

Create 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 ,only' , }, passWord : STRING, gender : { type : DECIMAL, allowNull : false , defaultValue : 3 , comment : 'Gender (1 male, 2 female, 3 confidential)' , }, birthday : DATE, city : STRING, picture : STRING, }); User.associate = () => { //One-to-many relationship, a user has multiple fans User.hasMany(app.model.Userfollow, { foreignKey : 'userId' , targetKey : 'followerId' , }); }; return User; }; Copy code

Implement query fans and follow API

Query 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 successfully' , data }; } Copy code

app/service/userfollow.js

//*** const {Op} = require ( 'sequelize' ); const {toInt} = require ( '../extend/utils' ); //toInt converts a string to a numeric value //*** 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 code

Query user's attention

app/controller/userfollow.js

//Get the follower list async getFollowers () { const {ctx} = this ; const data = await ctx.service.userfollow.getFollowersByUser(ctx.params.id, ctx.request.query); ctx.body = { status : true , msg : 'Watch list obtained successfully' , data }; } Copy 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 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 watchlist copy the code

Insert several pieces of test data in the table

// * * * Copy the code

Access routing

//GET http://127.0.0.1:7001/api/v1/user/1/fans?pageNumber=1&pageSize=10 { "status" : true , "msg" : "Follower list obtained successfully" , "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" : "The watch list was successfully obtained" , "data" : { "count" : 1 , "rows" : [ { "user" : { "id" : 1 , "userName" : "test1" , "picture" : null , "city" : "Beijing" , "gender" : "3" } }, ] } } Copy code

Sequelize's crud

increase

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

delete

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

change

//... const user = await this .ctx.model.User.findByPk (ID); //... the await user.update ({the userName, Gender, Birthday, City, Picture}); duplicated code

check

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

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

---END---