introduce

Object Relational Mapping (ORM) is the automatic persistence of objects in an object-oriented language program to a Relational database by using metadata that describes the Mapping between objects and databases

An overview of the

Sequelize is a Promise-based Node.js ORM that supports Postgres, MySQL, MariaDB, SQLite, and Microsoft SQL Server. It has powerful transaction support, association, preread and lazy load, read replication and other functions.

Why did you choose it?

Node.js to connect to the database needs to establish and manage the connection, the operation is tedious, but also need to write their OWN SQL statements. Simple projects may be ok, when the project design is more complex, more tables, for the SQL statement is more expensive to write.

There are already lightweight database frameworks or solutions in Java, C#, and other languages. You can use Sequelize in Node.js, which is a very mature framework with great speed and performance advantages. And the key is that development only needs to create the management object, query method call, and so on, very little need to write SQL statements. This not only saves complex SQL statement maintenance, but also avoids unnecessary bugs caused by SQL statements.

The installation

cnpm i sequelize mysql2 -S

The basic use

Connecting to a Database

/ / import sequelize
const Sequelize = require('sequelize');

// Connect to the database
// Parameter: Database user name and password
const sequelize = new Sequelize('db1'.'root'.'* * * * * *', {
    host: 'localhost'.// Host address
    dialect: 'mysql'./ / language
})
Copy the code

Test the connection

// Test the connection
async function test() {
    try {
        await sequelize.authenticate();
        console.log('Connection has been established successfully.');
    } catch (error) {
        console.error('Unable to connect to the database:', error);
    }
}

test();
Copy the code

Model synchronization

By calling an asynchronous function (which returns a Promise)model.sync(options). With this call,Sequelize automatically executes SQL queries against the database.

• Books.sync()– If the table does not exist, create the table (if it already exists, do nothing)
• Books.sync({ force: true })– The table will be created, and if the table already exists, it will be deleted first
• Books.sync({ alter: true })– This checks the current state of the table in the database (which columns it has, their data types, etc.) and then makes the necessary changes in the table to match the model

Create the model (table) and add it to the database

// Define the model
const Books = sequelize.define('books' /* Customize the table name */ , {
    // Define fields in tables, attributes in objects
    id: {
        type: Sequelize.INTEGER, // Define the data type
        primaryKey: true.// Set the primary key
        autoIncrement: true.// Set automatic growth
        comment: 'on the id'.// Add a description
    },
    name: {
        type: Sequelize.STRING,
        allowNull: false.// Whether to allow null, the default is true
    },
    price: {
        type: Sequelize.FLOAT,
        allowNull: false,},count: {
        type: Sequelize.INTEGER,
        defaultValue: 0.// Set the default value}}, {// Other model parameters
    timestamps: false.// Disable timestamps, remove createdAt and updatedAt fields, default to true
    freezeTableName: true.// Table name frozen, Model corresponds to the same table name as defined 'books', default true
});

// Model synchronization, migrate model mapping to database
Books.sync({
    force: true // The table will be created, and if it already exists, it will be dropped first
})
Copy the code

Synchronize all models at once

You can use sequelize.sync() to automatically synchronize all models.

await sequelize.sync({ force: true });
console.log("All models have been successfully synchronized.");
Copy the code

More data types

string

Sequelize.STRING             // VARCHAR(255), the default length is 255
Sequelize.STRING(1234)       // VARCHAR(1234), a string of specified length
Sequelize.STRING.BINARY      // VARCHAR BINARY
Sequelize.TEXT               // TEXT, a TEXT string
Sequelize.TEXT('tiny')       // TINYTEXT, a small text string
Copy the code

Boolean

Sequelize.BOOLEAN            // TINYINT(1), small int, length 1 (0,1)
Copy the code

digital

Sequelize.INTEGER            // INTEGER, int
Sequelize.BIGINT             // BIGINT, the larger int type
Sequelize.BIGINT(11)         // BIGINT(11), specifies the length of BIGINT

Sequelize.FLOAT              // FLOAT
Sequelize.FLOAT(11)          // FLOAT(11), specifies the length of the floating point type
Sequelize.FLOAT(11.10)      // FLOAT(11,10), specifying the length and number of digits after the decimal point

Sequelize.DOUBLE             // DOUBLE
Sequelize.DOUBLE(11)         // DOUBLE(11)
Sequelize.DOUBLE(11.10)     / / DOUBLE (11, 10)

Sequelize.DECIMAL            // DECIMAL, a more accurate type of DECIMAL point
Sequelize.DECIMAL(10.2)     // DECIMAL(10,2), specifying the length and number of digits after the DECIMAL point
Copy the code

The date of

Sequelize.DATE       // DATETIME applies to mysql/SQLite, TIMESTAMP with time zone applies to Postgres
Sequelize.DATE(6)    // DATETIME(6) applies to mysql 5.6.4+. Supports 6 decimal seconds of precision
Sequelize.DATEONLY   // DATE with no time, only the DATE part
Copy the code

The enumeration

Sequelize.ENUM('value 1'.'value 2'.'value 3')   // Enumeration type, from value 1, value 2, value 3
Copy the code

JSON

Sequelize.JSON       // Applies to mysql 5.7.8+, JSON type
Copy the code

Delete table

Delete tables associated with the model:

await Books.drop();
console.log("User table deleted!");
Copy the code

Drop all tables:

await sequelize.drop();
console.log("All tables deleted!");
Copy the code

Add or delete check change

increase

Insert a piece of data using create({}).

Books.sync({
    force: true // The table will be created, and if it already exists, it will be dropped first
}).then(() = > {
    // Insert a piece of data using create({})
    return Books.create({
        name: 'JavaScript programming'.price: '36.6'.count: 14})})Copy the code

Use bulkCreate([{},{}]) to insert multiple data.

Books.sync({
    force: true // The table will be created, and if it already exists, it will be dropped first
}).then(() = > {
    // Insert multiple data using bulkCreate([{},{}])
    return Books.bulkCreate([{
        name: 'JavaScript programming'.price: 36.6.count: 36
    }, {
        name: 'Vue Combat Development '.price: 99.9.count: 64
    }, {
        name: React Development.price: 128.6.count: 87
    }, {
        name: 'Node. Js practical'.price: 136.2.count: 59})})Copy the code

check

To query all data use books.findall ().

Books.findAll().then(books= > {
    console.log('all books'.JSON.stringify(books, null.4)); // Use four Spaces for indentation
    // Calculate the total price
    console.log(books[0].price*50);
})
Copy the code

To query a piece of data use books.findone ().

// Query an item of data
Books.findOne().then(books= > {
    console.log(JSON.stringify(books, null.4));
})
Copy the code

The first data is queried by default, and the criteria for the query can be specified in findOne()

Books.findOne({
    where: {
        id: 2
    }
}).then(books= > {
    console.log(JSON.stringify(books, null.4));
})
Copy the code

More query operations

For details, see Sequelize model query

/ / import the Op
const Op = Sequelize.Op;

Books.findAll({
    //SELECT `name`, `price` FROM `books` AS `books` WHERE 
    //`books`.`price` > '40' ORDER BY `books`.`id` DESC LIMIT 2;
    where: {
        price: {
            // The price is greater than 40
            //SELECT `id`, `name`, `price`, `count` FROM `books` AS `books` 
            //WHERE `books`.`price` > '40';
            [Op.gt]: 40,}},order: [['id'.'DESC'].// by id, in reverse order].limit: 2.// The number of returns
    // Attributes: ['name', 'price'], // Query specific attributes, as long as name and price
    attributes: {
        exclude: ['id'.'name'] // Query specific attributes, excluding id and name
    },
}).then(books= > {
    console.log('all books'.JSON.stringify(books, null.4)); // Use four Spaces for indentation
})
Copy the code

Aggregation function

The count method only counts the number of occurrences of elements in the database.
Books.count('id').then(counts= > console.log(counts));
The Max method gets the maximum number of elements in the database
Books.max('price').then(maxPrice= > console.log(maxPrice));
The //min method gets the minimum value of an element in the database
Books.min('price').then(minPrice= > console.log(minPrice));
The sum method gets the sum of the elements in the database
Books.sum('count').then(sum= > console.log(sum));
Copy the code

extension

Sometimes you might need to do something else after the fetch, such as calculate the total price books[0].price* quantity purchased, but this is not convenient every time, so consider extending the method.

Instance extension

Extend a method for calculating total prices on the Books prototype

// Instance extension
Books.prototype.totalPrice = function (count) {
    return this.price * count;
}

Books.findAll().then(books= > {
    console.log('all books'.JSON.stringify(books, null.4)); // Use four Spaces for indentation
    // Just call the method on the prototype
    console.log(books[0].totalPrice(50));
})
Copy the code

Model extension

Extend methods directly on Books

// Model extension
Books.classify = function (name) {
    const XHPublisher = ['JavaScript programming'.'Node. Js practical'];
    return XHPublisher.includes(name) ? 'Xinhua Press' : 'Other Publishers';
}

const arr = [React Development.'JavaScript programming'.'Vue Combat Development '];
arr.forEach(item= > {
    // Get the publisher of each book
    console.log(Books.classify(item));
})
Copy the code

change

/ / update
Books.update({
    //UPDATE `books` SET `price`=? WHERE `id` = ?
    // The value to be modified
    price: 50
}, {
    / / conditions
    where: {
        id: 1}})Copy the code

delete

/ / delete
Books.destroy({
    / / conditions
    where: {
        id: 4}})Copy the code