What is ORM?

ORM (Object Relational Mapping) is a technology to solve the mismatch between object-oriented and relational databases. By describing the metadata mapped between objects and databases, the objects in the program are automatically persisted to the relational database. Its function is to make a mapping between relational database and objects, so that when we operate the database, we don't need to deal with complex SQL statements, as long as we operate it as usual. This is like entering java's Mybatis and thinkphp's model class to simplify database operation by mapping the database, so that developers do not need to write complex SQL statements and spend more time on logic writing.
The following is a practical case
General packaging

const mysql = require('mysql')

const connection = mysql.createConnection({
    host: 'localhost',
    port: '3306',
    user: 'root',
    password: '893100',
    database: 'd_eam'
})

// Find multiple data and return array format
const findAll  = (sql, params) => {
  return new Promise((resolve, reject) => {
      connection.query(sql, params, (err, result) => {
        if (err) {
            reject(err)
          }
          console.log(result)
          resolve(JSON.stringify(result))
      })
  })
}

// Find single data return object format
const findOne = (sql, params) => {
    if (!sql) return null
    return new Promise((resolve, reject) => {
        connection.query(sql, params, (err, result) => {
            if (err) {
                reject(err)
              }
              resolve(JSON.stringify(result[0]))
          })
    })
}

// use
 // Query data
   async getAdmin (ctx)  {
      const sql = "select * from student where full name='fierce'"
      const params = []
      const res = await connection.findAll(sql, params)
          ctx.body = {
            data: JSON.parse(res),
            statusCode: 200,
            message: 'Data acquisition succeeded'
          }
   }

orm package

const Student = sequelize.define('student', {
    // Model properties are defined here
    name: {
      type: DataTypes.STRING,
      allowNull: false
    },
    sex: {
      type: DataTypes.STRING
      // allowNull defaults to true
    },
    QQ: {
        type: DataTypes.STRING
        // allowNull defaults to true
      },
    id: {
        type: DataTypes.STRING,
        primaryKey: true
        // allowNull defaults to true
      },
    number: {
        type: DataTypes.STRING
        // allowNull defaults to true
    },
    telphone: {
        type: DataTypes.STRING
        // allowNull defaults to true
    },
    classe: {
        type: DataTypes.STRING
        // allowNull defaults to true
    }
  }, {
    freezeTableName: true
  });
 module.exports = Student
// use

     const res = await Student.findAll()

orm can simplify database operation and better constrain data types

Common orm framework for node

• ORM2:https://github.com/dresende/node-orm2
• Serialize: the framework to be studied in this paper is more commonly used
• Knex.js: Official Website: https://knexjs.org/
• TypeORM: written in TypeScript, it supports the development of TypeScript or Javascript (ES5, ES6, ES7). The goal is to maintain support for the latest Javascript features to help develop applications for various user databases - whether light applications or enterprise level

Basic use of sequelize

Details can be viewed Sequenize official website , for more detailed configuration

1, Download dependent packages

Download serialize

npm install --save sequelize

To install the database driver:

$ npm install --save pg pg-hstore # Postgres
$ npm install --save mysql2
$ npm install --save mariadb
$ npm install --save sqlite3
$ npm install --save tedious # Microsoft SQL Server

Create database configuration

1. Connection configuration

const { Sequelize } = require('sequelize');

// Method 1: pass a connection URI
const sequelize = new Sequelize('sqlite::memory:') // Sqlite example
const sequelize = new Sequelize('postgres://user:pass@example.com:5432/dbname') // Postgres example

// Method 2: pass parameters separately (sqlite)
const sequelize = new Sequelize({
  dialect: 'sqlite',
  storage: 'path/to/database.sqlite'
});

// Method 3: pass parameters separately (other databases)
const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: /* Select one of 'MySQL' | 'MariaDB' | 'Postgres' |' MSSQL ' */
});

2. Connection test

use .authenticate() Function to test whether the connection is normal
 try {
  await sequelize.authenticate();
  console.log('Connection has been established successfully.');
} catch (error) {
  console.error('Unable to connect to the database:', error);
}

3. Synchronize database
sequelize.sync() - create the table if it does not exist (do nothing if it already exists)
sequelize. Sync ({force: true}) - the table will be created and deleted first if it already exists
sequelize. 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

sequelize.sync() 
sequelize.sync({ alter: true });
sequelize.sync({ force: true });

Actual case

const sequelize = new Sequelize('admin', 'root', '893100', {
    host: 'localhost',
    port: 3306,
    dialect: 'mysql'
  });

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

sequelize.sync({ alter: true });

  module.exports = sequelize

Create database model

const { DataTypes } = require('sequelize');
const sequelize = require('../config/db')

const Student = sequelize.define('student', {
    // Model properties are defined here
    name: {
      type: DataTypes.STRING,
      allowNull: false
    },
    sex: {
      type: DataTypes.STRING
      // allowNull defaults to true
    },
    QQ: {
        type: DataTypes.STRING
        // allowNull defaults to true
      },
    id: {
        type: DataTypes.STRING,
        primaryKey: true
        // allowNull defaults to true
      },
    number: {
        type: DataTypes.STRING
        // allowNull defaults to true
    },
    telphone: {
        type: DataTypes.STRING
        // allowNull defaults to true
    },
    classe: {
        type: DataTypes.STRING
        // allowNull defaults to true
    }
  }, {
    freezeTableName: true
  });


  module.exports = Student

Model query

// controller layer
const Student = require('../model/students')
 async getAdmin (ctx)  {
        const res = await Student.findAll()
        console.log(res)
          ctx.body = {
            data: res,
            statusCode: 200,
            message: 'Data acquisition succeeded'
          }
   }

Other query methods can view sequenced documents, View document