It provides a basic interface for operating the database. We can operate through the query method of the mysql module, but it requires writing SQL statements. It is difficult for people who are not proficient in SQL statements, and it is also dangerous to retain SQL statements in the code. In order to facilitate database operations, the ORM framework came into being, and Sequelize is such a module.
Install Sequelize
First, use the cd command to locate the terminal console to the root of the project, and then install it using npm:
npm install sequelize --save
Install mysql2
Since sequelize depends on mysql2, mysql2 needs to be installed:
npm install mysql2 --save
Write configuration
Create a new "configs" folder in the project root directory, and create a new file named "" in the folder. The code is as follows:
var config = {
dbname: 'testdb',
uname: 'root',
upwd: 'root',
host: 'localhost',
port: 3306,
dialect: 'mysql',
pool: {
max: 5,
min: 0,
idle: 10000
}
};
= config;
Configuration instructions
- dbname: database name
- uname: database login name
- upwd: database login password
- host: database host
- port: database port, mysql default is 3306
- dialect: database type, here is mysql
- pool: connection pool configuration
[See MySQL related blog for details]
Simple encapsulation of sequelize
Create a new "data" folder in the project root directory and create a new "" file in the modified folder. Add the following code in.
First, create a sequelize instance:
//Introduce moduleconst Sequelize = require('sequelize');
// Read configurationconst mysqlConfig = require('../configs/mysql-config');
// Instantiate seq according to configurationvar seq = new Sequelize(, , , {
host: ,
dialect: ,
pool:
});
Define a defineModel function to define the data model:
/**
* Define the data model
*
* @param {any} name Model name [database table name]
* @param {any} attributes Data field collection
* @returns Data Model Object
*/
function defineModel (name, attributes) {
var attrs = {};
for (let key in attributes) {
let value = attributes[key];
if (typeof value === 'object' && value['type']) {
= || false;
attrs[key] = value;
} else {
attrs[key] = {
type: value,
allowNull: false
};
}
}
// Attach public fields // = {
// type: ID_TYPE,
// primaryKey: true
// };
= {
type: ,
allowNull: false
};
= {
type: ,
allowNull: false
};
= {
type: ,
allowNull: false
};
// Status: 0 means valid, 1 means invalid, 2 means deleted, default is 0. = {
type: ,
allowNull: false
};
// Call seq method to define the model and return return (name, attrs, {
tableName: name,
timestamps: false,
hooks: {
beforeValidate: function (obj) {
let now = ();
if () {
= now;
= now;
= 0;
} else {
= now;
++;
}
}
}
});
}
Here we can make some modifications to the model definition, such as adding id, createAt, updateAt, version and status fields. In this way, the model defined by this function will have these fields, and usually a database table should contain these fields.
By default, sequelize will add id fields to the model, increase and primary key. So here you don't have to worry about the definition of this field.
When calling the() method, "timestamps" is set to false. When timesstamps is true, two fields createdAt and updateAt will be added to the model by default. The data type is, which we define here as used to store timestamps.
At the same time, setting the tableName and the model name are consistent, which conforms to our inertial thinking.
Finally, the module exports the defineModel function:
= defineModel;
Define the model
Create a new "model" folder in the data directory and add "" to the model folder. In Notices, first introduce the required modules:
var db = require('../db');
var seq = require('sequelize');
Then define the data model object:
var Model = ('Notices', {
content: ,
title: (30),
startDate: ,
expireDate: ,
gmId: (10),
});
// Export model objects = Model;
The introduced db module is the encapsulation of sequelize in the previous case, and then the model is defined through the defineModel() function and the public fields are added.
The sequelize module is introduced here mainly to specify the data type when defining the model.
Synchronize data structures to database
After the model is defined, the corresponding data table needs to be established in the database. At this time, the structure needs to be synchronized. You can use the following methods to synchronize:
();
If the table corresponding to the model already exists in the database, the synchronization operation will not be performed. If you want to force synchronization of the data structure, you can specify it through the force parameter:
({force: true});
Using the model
You can use Model objects to operate directly in the file. If it is an external file, you need to first introduce the Notices module.
var Notices = require('../data/model/Notices');
Add data
You can add a piece of data through the create method of the data model object. The return value of the method is a Promise object, so you can directly call the then method of the Promise object for subsequent operations.
({
content: 'I'm the announcement. ',
title: 'Title of System Announcement',
gmId: '10086',
status: 0,
expireDate: 1527396599123,
startDate: ()
}).then((data) => {
({ code: 0, msg: 'Announcement was successfully released', result: data });
});
The parameter of the then method is a function object, which has a data parameter. This data parameter is the data object operated by the previous create method. You can obtain the id of the data in the database from the data.
Await and async to achieve synchronous programming effect
The method through then is very similar to callback-based asynchronous programming of nested functions. Some people who don’t like nested functions can use await to achieve the effect of synchronous programming. More about the usage of await is not discussed here.
(async () => {
var data = await ({
content: 'I'm the announcement. ',
title: 'Title of System Announcement',
gmId: '10086',
status: 0,
expireDate: 1527396599123,
startDate: ()
});
({ code: 0, msg: 'Announcement was successfully released', result: data });
})();
The function call modified by await must be written in the async modified function, otherwise an error will be reported, so an instant function is included in the outermost layer.
For the concept of real-time functions, please refer to the book "JavaScript Object-Oriented Programming Guide", which is quite detailed. Simply put, it is a function called immediately after definition.
Modify data
Use the update method of the model object to modify the data:
({
status: 2
}, {
where: {id: 100}
});
The first parameter of the update(values, opts) method is the data set to be modified, and the field name corresponds to the data table. The second parameter is some related operation parameters, where is used to limit the conditions for modifying data, and the function of SQL statements is the same.
The above effect is to modify the value of the status field of data with id 100 to 2.
Query data
Query effect can be achieved through the find-related methods of the data model object:
Query all data
();
The method will return all the data in the Notices table, and the return value is still a Promise object.
Conditional query
({order: [['createAt', 'DESC']], limit: 10, where: {'status': 0}});
- The order field is used to specify the sorting rules, here it specifies the descending order of the createAt field.
- The limit field is used to specify the amount of data for the query, which means that the first 10 pieces of data are returned.
- The where field is used to specify the conditional query, which means querying data with status 0.
Delete data
The destroy method of the data model object can be destroyed, and the specific usage is similar to query, modification, etc.
However, when we do the deletion operation, we do not really erase the data from the database, but identify it through the data status field to facilitate subsequent maintenance. So each table usually defines a status field.
Complex where conditions
In sequelize, there is also an Op object for handling complex conditional operations.
[Case 1]
var seq = require('sequelize');
var Op = ;
// Other codes...
({
status: 2,
gmId: 10086
}, {
where: {
id: {
[]: [1, 4, 2, 8, 13, 20]
}
}
});
The above code means that the value of modifying status is 2 and the value of modifying gmId is 10086. For the modified condition, the id field of the data must be the data in the array [1, 4, 2, 8, 13, 20].
[Case 2]
var date = ();
({
where: {
status: 0,
sendDate: {[]: date},
expireDate: {[]: date},
[]: [
{to: 1000017},
{to: 0}
]
}
});
The above code indicates that all sendDate is less than or equal to the current time, and expireDate is greater than or equal to the current time, and the value of status is 0, and the value of to field is 1000017 or 0.
Reference Documents
The above is just a few examples for a brief explanation. You can check this document in detail. Although it is in English, it is understandable if you take a little time.
/
[Written at the end of the article: ORM facilitates database operations, but for many complex businesses, it may not be well solved, so some specific needs still need SQL to implement. 】
The above is all the content of this article. I hope it will be helpful to everyone's study and I hope everyone will support me more.