🎇🎇🎇 Happy New Year 🎇🎇
2020 rat you the most handsome, rat you strongest, rat you the best, rat you the most red, rat you the most beautiful, auspicious year of the rat
Ask and answer
❓: What can you learn? 🙋: Sequelize – The use of CLI and basic operations of Sequelize.
❓: Why sequelize- CLI? 🙋: Just as you use Git/SVN to manage source code changes, you can use migrations to track database changes.
❓: Why not design the data model and demonstrate it? 🙋: This article describes the development process using Sequelize – CLI and Sequelize.
❓: How can there be so much code? 🙋: I have posted the code for each step, as long as you follow the process to complete a quick example. Seeing is believing, and I believe learning works better this way.
❓: How about transactions, scopes, data types, etc.? 🙋: This is an introductory tutorial, but with this tutorial, transactions and scopes are easier to understand.
❓: Why is there no source code? 🙋: Do it again must be better than watching it.
The preparatory work
1. Initialize the project
cdProject directory NPM init-yCopy the code2. Install the module
npm i koa koa-body koa-router mysql2 sequelize sequelize-cli -S
Copy the code3. Add the server.js file
const Koa = require('koa');
const router = require('koa-router') (); const koaBody = require('koa-body');
const app = new Koa();
app.use(koaBody());
app.use(router.routes())
.use(router.allowedMethods(The '*'));
app.listen(3000, () => {
console.log('server is listening on 3000... ')});Copy the codeQuick start
1. Create the. Sequelizerc file
const path = require('path');
module.exports = {
'config': path.resolve('config'.'config.json'), // Database connection configuration file'models-path': path.resolve('db'.'models'), // Model file'seeders-path': path.resolve('db'.'seeders'), // Seed file'migrations-path': path.resolve('db'.'migrations'// Migrate files}Copy the code2. Initialize
npx sequelize-cli init
Copy the code3. Edit./db/config.js
"development": {
"username": "username"."password": "password"."database": "school"// Database name"host": "127.0.0.1"."dialect": "mysql"."timezone": "+ 08:00." "// Set the time zone to'East District 8'
}
Copy the codeCreate a database
npx sequelize-cli db:create
Copy the code5. Generate student model file and migrate file
npx sequelize-cli model:generate --name student --attributes student_name:string,student_age:integer,student_sex:boolean
Copy the code/db/migrations/ xxx-create-student.js
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable('student', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true.type: Sequelize.INTEGER
},
student_name: {
type: Sequelize.STRING(10),
allowNull:false
},
student_age: {
type: Sequelize.INTEGER,
allowNull:false
},
student_sex: {
type: Sequelize.BOOLEAN,
allowNull:false}}); }, down: (queryInterface, Sequelize) => {return queryInterface.dropTable('student'); }};Copy the codeOpen xxx-create-student. The first parameter to createTable is students. This is because Sequelize converts the table name to plural by default.
Create table student
npx sequelize-cli db:migrate
Copy the codeCreate student table seed file
npx sequelize-cli seed:generate --name init-student
Copy the code9, edit./db/seeders/ xxx-init-student.js file
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.bulkInsert('student', [{
student_name: Sun Wukong,
student_age: 20,
student_sex: 1
},{
student_name: 'Ghost of Bones',
student_age: 18,
student_sex: 0
},{
student_name: 'Pig Eight Quit',
student_age: 16,
student_sex: 1
}])
},
down: (queryInterface, Sequelize) => {
return queryInterface.bulkDelete('student', null, {}); }};Copy the codeAlter table student
npx sequelize-cli db:seed:all
Copy the codeDb /models/student.js
'use strict';
module.exports = (sequelize, DataTypes) => {
const student = sequelize.define('student', {
student_name: DataTypes.STRING,
student_age: DataTypes.INTEGER,
student_sex: DataTypes.BOOLEAN,
class_id:DataTypes.INTEGER
}, {
timestamps: false,// Do not automatically add time field (updatedAt,createdAt) freezeTableName:true// Use the singular form of the model name:true// Add an underscore to the column name}); student.associate =function(models) {};
return student;
};
Copy the code12. Edit the server.js file
. const Student = require('./db/models').student; // Add student information router.post('/student', async ctx => { ctx.body = await Student.create(ctx.request.body); }); // Update student information router.put('/student', async ctx => {
const { id } = ctx.request.body;
ctx.body = await Student.update(ctx.request.body, { where: { id } }); }); Router.get ('/students', async ctx => { ctx.body = await Student.findAll(); }); // Delete student information based on id router.delete('/student/:id', async ctx => {
const { id } = ctx.params;
ctx.body = await Student.destroy({ where: { id } }); }); .Copy the code13. Start the service and use the Postman test
node server.js
Copy the codeModel correlation
hasMany
There can be more than one student in a class, and the relationship between a class and a student is one-to-many. To complete this example we will do the following:
- Create a class table named _class
- _class Class table initializes data
- Alter table student add column class_id
- Reinitialize the student table data
- Query all students in a class
Let’s get started!
1. Generate **_class** model and migrate files
npx sequelize-cli model:generate --name _class --attributes class_name:string
Copy the code/db/migrations/ xxx-create-class.js
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable('_class', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true.type: Sequelize.INTEGER
},
class_name: {
type: Sequelize.STRING(10),
allowNull:false}}); }, down: (queryInterface, Sequelize) => {return queryInterface.dropTable('_class'); }};Copy the codeCreate **_class** table
npx sequelize-cli db:migrate
Copy the code4, generate **_class** table seed file
npx sequelize-cli seed:generate --name init-class
Copy the code/db/seeders/ xxx-init-class.js
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.bulkInsert('_class', [{
class_name: 'one'
}, {
class_name: 'second class'
}, {
class_name: '3'
}]);
},
down: (queryInterface, Sequelize) => {
return queryInterface.bulkDelete('_class', null, {}); }};Copy the code6. Initialize the _class table
npx sequelize-cli db:seed --seed xxxxx-init-class.js
Copy the code7. Generate the migration file that modifies the STUDnet table
npx sequelize-cli migration:generate --name add-column-class_id-to-student.js
Copy the code/db/migrations/ xxx-add-column-classid-to-student.js /db/migrations/ xxx-add-column-classid-to-student.js /db/migrations/ xxx-add-column-classid-to-student.js
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.addColumn('student'.'class_id', {
type: Sequelize.INTEGER,
allowNull:false
})
},
down: (queryInterface, Sequelize) => {
queryInterface.removeColumn('student'.'class_id'{}); }};Copy the codeAlter table student
npx sequelize-cli db:migrate
Copy the codeCreate the student table seed file
npx sequelize-cli seed:generate --name init-student-after-add-column-class_id
Copy the code/db/seeders/ xxx-init-student-after-add-column-class_id
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.bulkInsert('student', [{
student_name: Sun Wukong,
student_age: 20,
student_sex: 1,
class_id: 1
}, {
student_name: 'Ghost of Bones',
student_age: 18,
student_sex: 0,
class_id: 1
}, {
student_name: 'Pig Eight Quit',
student_age: 16,
student_sex: 1,
class_id: 2
}, {
student_name: "Tang's monk,
student_age: 22,
student_sex: 1,
class_id: 1
}, {
student_name: 'Sand Monk',
student_age: 25,
student_sex: 1,
class_id: 1
}, {
student_name: Red Boy,
student_age: 13,
student_sex: 1,
class_id: 2
}, {
student_name: Black Bear Monster,
student_age: 26,
student_sex: 1,
class_id: 2
}, {
student_name: 'Too white Venus',
student_age: 66,
student_sex: 1,
class_id: 3
}, {
student_name: 'the goddess of the moon',
student_age: 18,
student_sex: 0,
class_id: 3
}])
},
down: (queryInterface, Sequelize) => {
return queryInterface.bulkDelete('student', null, {}); }};Copy the codeAlter table student alter table student alter table student
npx sequelize-cli db:seed:undo --seed xxxxx-init-student.js
Copy the codeStuent table reinitialize data
npx sequelize-cli db:seed --seed xxxxx-init-student-after-add-column-class_id.js
Copy the code/db/models/_class.js file
'use strict';
module.exports = (sequelize, DataTypes) => {
const _class = sequelize.define('_class', {
class_name: DataTypes.STRING
}, {
timestamps: false,
freezeTableName: true,
underscored: true
});
_class.associate = function (models) {
_class.hasMany(models.student);
};
return _class;
};
Copy the code15. Edit server.js
. const Class = require('./db/models')._class; // Get the class information and all the students in the class router.get('/classes'Ctx.body = await class.findall ({include: [Student]}); ctx.body = await class.findall ({include: [Student]}); }); .Copy the codeBelongsTo (One-on-one)
A student can only belong to one class, so the relationship between student and class is one-to-one.
/db/models/student.js
. student.associate =function(models) { student.belongsTo(models._class); // one to one}; .Copy the code2. Modify the interface to get the student list in server.js
. Router.get ('/students', async ctx => { ctx.body = await Student.findAll({ include: [Class] }); }); .Copy the codebelongsTo VS hasOne
Student.belongsto (models._class) Where student is the source model and _class is the target model.
The student table contains the foreign key class_id of the _class table. That is, the foreign key is on the source model, so use belongsTo to create the association.
HasOne and belongsTo are both used to create one-to-one associations, and the proper way to use them is to see which model the foreign key is in.
- BelongsTo associates foreign keys on the source model
- HasOne associates foreign keys on the target model
BelongsToMany (many-to-many)
One class can have more than one substitute teacher, and one substitute teacher can lead multiple classes. To demonstrate this feature, we will do the following:
- Create a name
Let’s get started!
1. Generate teacher model and migrate files
npx sequelize-cli model:generate --name teacher --attributes teacher_name:string
Copy the code/db/migrations/xxxxx-teacher-class.js
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable('teacher', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true.type: Sequelize.INTEGER
},
teacher_name: {
type: Sequelize.STRING(10),
allowNull: false}}); }, down: (queryInterface, Sequelize) => {return queryInterface.dropTable('teacher'); }};Copy the code3, generate teacher table
npx sequelize-cli db:migrate
Copy the code4, generate teacher table seed file
npx sequelize-cli seed:generate --name init-teacher
Copy the code/db/seeders/ xxx-init-teacher.js
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.bulkInsert('teacher', [{
teacher_name: 'Miss Li'
}, {
teacher_name: 'Miss Zhang'
}]);
},
down: (queryInterface, Sequelize) => {
return queryInterface.bulkDelete('teacher', null, {}); }};Copy the code6, teacher table initialization data
npx sequelize-cli db:seed --seed xxxxx-init-teacher.js
Copy the code7. Generate teacher_class model and migrate files
npx sequelize-cli model:generate --name teacher_class --attributes teacher_id:integer,class_id:integer
Copy the code/db/migrations/xxxxx-create-teacher-class.js
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable('teacher_class', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true.type: Sequelize.INTEGER
},
teacher_id: {
type: Sequelize.INTEGER,
allowNull: false,
},
class_id: {
type: Sequelize.INTEGER,
allowNull: false,}}); }, down: (queryInterface, Sequelize) => {return queryInterface.dropTable('teacher_class'); }};Copy the codeCreate teacher_class table
npx sequelize-cli db:migrate
Copy the codeGenerate teacher_class table seed file
npx sequelize-cli seed:generate --name init-teacher_class
Copy the code/db/seeders/ xxx-init-teacher_class.js
'use strict'; /* Miss Li's classes are Class one and Class two. Module. Exports = {up: (queryInterface, Sequelize) => {/ module. Exports = {up: (queryInterface, Sequelize) => {return queryInterface.bulkInsert('teacher_class', [{
class_id: 1,
teacher_id: 1
}, {
class_id: 2,
teacher_id: 1
}, {
class_id: 3,
teacher_id: 2
}]);
},
down: (queryInterface, Sequelize) => {
return queryInterface.bulkDelete('teacher_class', null, {}); }};Copy the codeTeacher_class table initialization data
npx sequelize-cli db:seed --seed xxxxx-init-teacher_class.js
Copy the code/db/models/teacher.js
'use strict';
module.exports = (sequelize, DataTypes) => {
const teacher = sequelize.define('teacher', {
teacher_name: DataTypes.STRING
}, {
timestamps: false,
freezeTableName: true,
underscored: true
});
teacher.associate = function (models) {
teacher.belongsToMany(models._class, {
through: models.teacher_class,
foreignKey: 'teacher_id'}); };return teacher;
};
Copy the code/db/models/_class.js
'use strict';
module.exports = (sequelize, DataTypes) => {
const _class = sequelize.define('_class', {
class_name: DataTypes.STRING
}, {
timestamps: false,
freezeTableName: true,
underscored: true
});
_class.associate = function (models) {
_class.hasMany(models.student);
_class.belongsToMany(models.teacher, {
through: models.teacher_class,
foreignKey: 'class_id'}); };return _class;
};
Copy the code15. Edit server.js
const Teacher = require('./db/models').teacher; // Get the teacher's information and the class that the teacher takes router.get('/teachers'Ctx.body = await teacher.findall ({include: [Class]}); ctx.body = await teacher.findall ({include: [Class]}); })Copy the codeThe query
Based on the query
1. Return the specified column
Student.findAll({
attributes: ['id'.'student_name']}); // select id,student_name from studentCopy the code2, single condition query
Student.findAll({
where: {
id: 1
}
})
// select * from student where id = 1
Copy the code3, AND
// Return student.findall ({return student.findall ({return student.findall ());where: {
id: 4,
student_name:Sun Wukong
}
})
// select * from student where id = 1 and student_name = Sun Wukong
Copy the code4, the OR
Student.findall ({return student.findall ();where: {
student_age: {
[Op.or]: [12, 22]
}
}
})
// select * from student where studnet_age = 12 or studnet_age = 22
Copy the code>=,<,<=,=
// Return student.findall ({return student.findall ();where: {
student_age: {
[Op.gte]: 20
}
}
})
// select * from student where studnet_age >= 20
Copy the code[Op. Gt] : 6 / / than [Op. Gte] : 6 / / greater than or equal to 6 / Op. Lt: 10 / / less than 10 [Op. Lte] : 10 / / less than or equal to 10 [Op. Ne] : / / is not equal to 20 [Op. Eq] : 3 / / equal to 3Copy the code6, IN
Student.findall ({return student.findall ({return student.findall ());whereStudent_age: {[op. in]: [16,18]}}}) // select * from studentwhere studnet_age in(16, 18)Copy the code7 and the LIKE
// Return the name contained'sun'Student information student.findall ({where: {
student_name: {
[Op.like]: '% % sun',
}
}
})
// select * from student where studnet_name like '% % sun'
Copy the codeAggregation function
1. Obtain the average age of students
Student.findAll({
attributes: [[sequelize.fn('AVG', sequelize.col('student_age')), 'avg']]})Copy the code2. Obtain the total number of students
Student.findAll({
attributes: [[sequelize.fn('COUNT', sequelize.col('id')), 'count']]})Copy the codeNested query
1, Get all the students from class one and arrange them in descending order by age
Class.findAll({
include: [{model: Student}],
where:{id:1},
order:[[Student,'student_age'.'DESC']]});Copy the codereference
Sequelize Chinese document