node express 使用 Sequelize 操作数据库(2021-5-23)

1. mysql 链接配置文件

const conf = {
  database: 'node',
  user: 'root',
  password: 'xxxxx',
  options: {
    host: 'rm-2vcnsje1qrd9c966ago.mysql.cn-chengdu.rds.aliyuncs.com',
    dialect: 'mysql',
    define: {
      charset: 'utf8mb4',
      dialectOptions: {
        collate: 'utf8_general_ci',
      },
      freezeTableName: true,
      timestamps: false,
      engine: 'innoDB',
    },
    pool: {
      max: 15,
      min: 0,
    },
  },
};

module.exports = conf;

2. 初始化表数据结构模型

   module.exports = (sequelize, DataTypes) => {
     // 定义test模型
     const test = sequelize.define(
       'test',
       {
         id: { type: DataTypes.INTEGER, autoIncrement: true, primaryKey: true },
         name: DataTypes.STRING(30),
         mobile: DataTypes.STRING(15),
         auth: DataTypes.STRING(30),
         password: DataTypes.STRING(30),
       },
       {
         timestamps: false,
         freezeTableName: true
       },
     );
   
     // test.associate = function associate(_) { };
   
   
     return test;
   };
   

3. 初始化sequelize,

   const fs = require('fs');
   const path = require('path');
   const Sequelize = require('sequelize');
   const dbConf = require('../conn/mysql/conf');
   
   const db = {};
   
   const sequelize = new Sequelize(
     dbConf.database,
     dbConf.user,
     dbConf.password,
     dbConf.options,
   );
   
   //测试数据库链接
   // async function testConn() {
   //   try {
   //     await sequelize.authenticate();
   //     console.log('Connection has been established successfully.');
   //   } catch (error) {
   //     console.error('Unable to connect to the database:', error);
   //   }
   // }
   // testConn();
   
   fs.readdirSync(__dirname) // 获取当前文件所在文件夹下的所有文件名
     .filter((file) => file !== 'index.js') // 筛选
     .forEach((file) => {
       // eslint-disable-next-line import/no-dynamic-require, global-require
       const model = require(path.join(__dirname, file))(   // 导出模型类
         sequelize,  // 传入sequelize对象
         Sequelize.DataTypes, //传入sequelize 数据类型
       );
       db[model.name] = model;
     });
   
   // 当模型对应数据表不存在时自动创建表
   sequelize.sync();
   
   //测试数据库插入
   // (async () => {
   //   const jane = await db["test"].create({ name: "Jane" });
   //   console.log(jane.toJSON());
   // })();
   
   db.sequelize = sequelize;
   db.Sequelize = Sequelize;
   
   module.exports = db;
   

   4. 增删改查controller

      const { test } = require('../models');
      const { Op } = require("sequelize");
      
      // 增
      // const add = async function () {
      //   const addRes = await test.create({ name: "Jane", cc: "ccc" });
      //   console.log(addRes.toJSON());
      // }
      // add()
      
      // 删
      // const del = async function () {
      //   const delRes = await test.destroy({
      //     where: {
      //       id: 3
      //     }
      //   });
      //   console.log(delRes);
      // }
      // del()
      
      // 改
      // const up = async function () {
      //   const upRes = await test.update({ name: "liujie" }, {
      //     where: {
      //       id: 2
      //     }
      //   });
      //   console.log(upRes);
      // }
      // up()
      
      // 查
      const sel = async function (res, next) {
        const data = [];
        const selRes = await test.findAll(); // 查询全部
        // const selRes = await test.findAll({ attributes: ['name'] }); // 查询特定属性
        // const selRes = await test.findAll({ attributes: { exclude: ['name'] } }); // 排除特定属性
        // const selRes = await test.findAll({
        //   attributes: { exclude: ['name'] },
        //   where: {
        //     // id: 2 // id = 2
        //     id: {
        //       [Op.eq]: 2
        //     }, // id = 2
        //     mobile: null
        //   }
        // }); // where 子句
      
        Array.from(selRes).forEach(ele => {
          console.log(ele.toJSON());
          data.push(ele.toJSON());
        })
        res.json(data);
      }
      
      module.exports = {
        sel
      }