What is ORM?

Object Relational Mapping (ORM) is a technology to solve the mismatch between object-oriented and Relational databases. By describing the metadata of the Mapping between objects and databases, the objects in the program are automatically persisted in the Relational database. Its function is to make a mapping between relational database and object, so that we do not need to deal with complex SQL statements in the specific operation of the database, as long as the usual operation of the object operation it can be. This is just like Mybatis in Java, ThinkPHP model class, through the mapping database, simplify database operation, so that developers do not need to write complex SQL statements, will spend more time on the writing of logic. The following is a general encapsulation of the actual case

const mysql = require('mysql') const connection = mysql.createConnection({ host: 'localhost', port: '3306', user: 'root', password: '893100', database: Const findAll = (SQL, params) => {return new Promise((resolve, resolve)) reject) => { connection.query(sql, params, (err, Result) => {if (err) {reject(err)} console.log(result) resolve(json.stringify (result))})} // Find a 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])})})} async getAdmin (CTX) { Const params = [] const res = await connection.findall (SQL, Params) ctx.body = {data: json. parse(res), statusCode: 200, message: 'Data obtained successfully'}}Copy the code

The orm package

Const Student = sequelize.define(' Student ', {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. 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 // uses const res = await student.findall ()Copy the code

Orm makes it easier to manipulate databases and better constrain data types

Node uses the ORM framework

  • ORM2:github.com/dresende/no…
  • Sequelize: The framework to be studied in this article
  • Knex.js: Official website: knexjs.org/
  • TypeORM: Written in TypeScript, supports development in TypeScript or Javascript (ES5, ES6, ES7). The goal is to maintain support for the latest Javascript features to help develop applications with a variety of user databases – both light and enterprise-wide

Basic use of Sequelize

For details, see the Sequelize website

Download the dependency package

Download sequelize

npm install --save sequelize
Copy the code

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
Copy the code

Creating a database configuration

1. Connection configuration

const { Sequelize } = require('sequelize'); / / method 1: Const Sequelize = new Sequelize(' SQLite :: Memory :') // sqLite example const Sequelize = new Sequelize('postgres://user:[email protected]:5432/dbname') Const sequelize = new Sequelize({dialect: 'sqlite', storage: 'path/to/database.sqlite'}); Const sequelize = new sequelize ('database', 'username', 'password', {host: const sequelize = new sequelize ('database', 'username', 'password', {host: 'localhost', the dialect: / * choose 'mysql' | 'mariadb' | 'postgres' |' MSSQL one * /});Copy the code

2. Connection test

Use.authenticate() 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); }Copy the code

Sequelize.sync () – If the table does not exist, create the table (if it already exists, do not perform any operations) sequelize.sync({force: True}) – the table will be created, and if the table already exists, it will first be deleted sequelize.sync({alter: true}) – this will check the current state of the table in the database (what columns it has, their data types, etc.), and then do what is necessary in the table

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

The 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
Copy the code

Creating a database model

const { DataTypes } = require('sequelize'); const sequelize = require('.. /config/db') const Student = sequelize.define(' Student ', {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 = StudentCopy the code

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 fetched successfully'}}Copy the code

Other query methods can be used to view the sequelize documents