node使用sequelize那些坑

链接数据库🔗

//不要奇奇怪怪的字符，注释，不要标点符号
DB_HOST= 127.0.0.1
DB_DATABASE= fendou 
DB_USERNAME= root
DB_PASSWORD= 123456
DB_PORT= 3306

//db.js
const path = require('path')
require('dotenv').config({
  path: path.join(process.cwd(), '/config', '.env'),
})

const Sequelize = require('sequelize')

const sequelize = new Sequelize(
  process.env.DB_DATABASE,
  process.env.DB_USERNAME,
  process.env.DB_PASSWORD,
  {
    port: process.env.DB_PORT,
    host: process.env.DB_HOST, //数据库地址
    dialect: 'mysql', //指定连接的数据库类型
    pool: {
      max: 5, //连接池最大连接数量
      min: 0, //最小连接数量
      idle: 10000, //如果一个线程 10秒内么有被使用过的话，就释放
    },
    logging: true, // 执行过程会log一些SQL的logging，设为false不显示
  }
)

//对连接进行测试，查看控制台
sequelize
  .authenticate()
  .then(() => {
    console.log('******Connection has been established successfully.********')
    console.log('******测试结束，即将退出！！！********')
    // process.exit() //结束进程
  })
  .catch((err) => {
    console.error(
      '***************Unable to connect to the database:***********',
      err
    )
  })

module.exports = sequelize

创建表格

方法一

const Sequelize = require('sequelize')
const sequelize = require('../config/db')

const UserModel = sequelize.define(
  'user',
  {
    username: {
      type: Sequelize.STRING,
      unique: true,
      primaryKey: true,
    },
    email: {
      type: Sequelize.STRING,
      unique: true,
      validate: {
        isEmail: true,
      }
    },
    password: {
      type: Sequelize.STRING,
    },
  },
  {
    freezeTableName: false,
    timestamps: true,
  }
)
//timestamp字段，默认为true，表示数据库中是否会自动更新createdAt和updatedAt字段，false表示不会增加这个字段。
//freezeTableName,默认为true,会自动给表名表示为复数: user => users，为false则表示，使用我设置的表名

//创建表，默认是false，true则是删除原有表，再创建.
UserModel.sync({
  force: false,
})
module.exports = User

方法二

const { Sequelize, Model, DataTypes } = require('sequelize')
const sequelize = require('../config/db')

class Product extends Model {}
Product.init(
  {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      allowNull: true,
      autoIncrement: true,
    },
    title: Sequelize.STRING,
  },
  { sequelize, modelName: 'product' }
)
module.exports = Product

链表 Associations

Sequelize provides four types of associations that should be combined to create them:

• The HasOne association
• The BelongsTo association
• The HasMany association
• The BelongsToMany association

Creating the standard relationships

As mentioned, usually the Sequelize associations are defined in pairs. In summary:

• To create a One-To-One relationship, the hasOne and belongsTo associations are used together;
• To create a One-To-Many relationship, the hasMany and belongsTo associations are used together;
• To create a Many-To-Many relationship, two belongsToMany calls are used together.
  • Note: there is also a Super Many-To-Many relationship, which uses six associations at once, and will be discussed in the Advanced Many-to-Many relationships guide.

This will all be seen in detail next. The advantages of using these pairs instead of one single association will be discussed in the end of this chapter.

关于as别名

目前测试只有 User.hasMany(Task,{as:'Instrument'}),不可以 Task.hasMany(User,{as:'errcause'})

const {Op} = require('sequelize')

User.hasMany(Task,{as:'Instrument'})
await User.findAll({
    where: { id: 6 },
    include: {
      model: Task,
      as:'Instrument',
      separate: true,
      where: {
        id: { [Op.ne]: 2 },
      },
      order: [['id', 'DESC']],
    },
  })

关联查询 join

This is the case because, when the where option is used inside an include, Sequelize automatically sets the required option to true. This means that, instead of an OUTER JOIN, an INNER JOIN is done, returning only the parent models with at least one matching children.

Note also that the where option used was converted into a condition for the ON clause of the INNER JOIN. In order to obtain a top-level WHERE clause, instead of an ON clause, something different must be done. This will be shown next.

This will produce an outer join. However, a where clause on a related model will create an inner join and return only the instances that have matching sub-models. To return all parent instances, you should add required: false.

RIGHT OUTER JOIN

**Note: right is only respected if required is false.

排序

默认排序

order: [['id', 'DESC']],

include内部排序

结论：在简单的排序中， the order option is used at the top-level. The only situation in which order also works inside the include option is when separate: true is used.

但是include中的separate的权限高于where，separate会采取单独语句进行mysql语句编排。并且如果top-level有where进行深层次的筛选语句，在include指定separate会发生冲突报错。

或者更为具体地讲，因为采用分离语句,tester数据库名字变化了。

await User.findAll({
    where: {
        '$tester.name$': { [Op.ne]: 1 },
      },
    include: {
      model: Task,
      required: true,
      separate: true,
    },
  })

如果要高级排序，详见涉及子查询的复杂排序

await User.findAll({
    include: {
      model: Task,
      separate: true,//必须指定才有效
      where: {
        id: { [Op.ne]: 2 },
      },
      order: [['id', 'DESC']],
    },
  })

//执行分离语句，逐个单独查找
Executing (default): SELECT `tester`.`id`, `tester`.`name` FROM `testers` AS `tester`;
Executing (default): SELECT `id`, `name`, `testerId` FROM `tasks` AS `task` WHERE (`task`.`testerId` IN (1, 2, 3, 4, 5, 6, 7) AND `task`.`id` != 1) ORDER BY `task`.`id` DESC;

await User.findAll({
    include: {
      model: Task,
      required: true,//include内部有where，默认为true
    //separate: true,
      where: {
        id: { [Op.ne]: 1 },
      },
      order: [['id', 'DESC']],//无效
    },
  })
//执行联合语句
Executing (default): SELECT `tester`.`id`, `tester`.`name`, `tasks`.`id` AS `tasks.id`, `tasks`.`name` AS `tasks.name`, `tasks`.`testerId` AS `tasks.testerId` FROM `testers` AS `tester` INNER JOIN `tasks` AS `tasks` ON `tester`.`id` = `tasks`.`testerId` AND `tasks`.`id` != 1;

Where语句冲突

await User.findAll({
    where: {
        '$tasks.name$': { [Op.ne]: 1 }, //要么使用as别名，要么看语句是否数据库plural
      },
    include: {
      model: Task,
      required: false,
    //   separate: true,//绝对不能写
    //   where: {
    //     id: { [Op.eq]: 2 },
    //   },
      order: [['id', 'DESC']],
    },
  })

指定返回字段 attributes

attributes:{
  exclude:['id']//or
  attributes:["id","name"]//默认包含
}

Op操作

引入Op

const {Op} = require('sequelize')