node最佳实践4-sequelize关联关系

关系模型

• Author hasMany Books
• Category hasMany Books
• Book belongsTo Author
• Book belongsTo Category

初始化数据

创建Category和Author模型

sequelize model:generate --name Category --attributes name:st
ring

sequelize model:generate --name Author --attributes name:st
ring

自动创建对应的model和migration:

models/author.js

'use strict';
const {
  Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class Author extends Model {
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    static associate(models) {
      // define association here
    }
  }
  Author.init({
    name: DataTypes.STRING
  }, {
    sequelize,
    modelName: 'Author'
  });
  return Author;
};

migrations/xxxxxx-create-author.js

'use strict';
/** @type {import('sequelize-cli').Migration} */
module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable('Authors', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      name: {
        type: Sequelize.STRING
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  async down(queryInterface, Sequelize) {
    await queryInterface.dropTable('Authors');
  }
};

字段说明：

• id是自动创建的，约束非空，自增，主键，整型
• name是我们定义的名称，
• createdAt和updatedAt是sequelize为我们自动添加上去的，如果不需要这两个字段可以在模型中设置timestamps：false

class Foo extends Model {}
Foo.init({ /* attributes */ }, {
  sequelize,
  // 取消
  // timestamps: false
  
  // 或者如果要单独设置updateAt，createdAt
  // don't forget to enable timestamps!
  timestamps: true,
  // I don't want createdAt
  createdAt: false,
  // I want updatedAt to actually be called updateTimestamp
  updatedAt: 'updateTimestamp'
});

关系模型中，Book还需要新增两个字段categoryId和authorId

sequelize migration:generate --name add-id-to-book

执行指令后新增migration文件migrations/xxxxxx-add-id-to-book.js

'use strict';
const { DataTypes } = require('sequelize');

/** @type {import('sequelize-cli').Migration} */
module.exports = {
  async up (queryInterface, Sequelize) {
    await queryInterface.sequelize.transaction(t => {
      return Promise.all([
        queryInterface.addColumn('Books','categoryId',{
          type: DataTypes.INTEGER,
          allowNull: true
        },{transaction: t}),
        queryInterface.addColumn('Books','authorId',{
          type: DataTypes.INTEGER,
          allowNull: true
        },{transaction: t})
      ])
    })
  },

  async down (queryInterface, Sequelize) {
    await queryInterface.sequelize.transaction(t => {
      return Promise.all([
        queryInterface.removeColumn('Books','categoryId',{transaction:t}),
        queryInterface.removeColumn('Books','authorId',{transaction:t})
      ])
    })
  }
};

执行迁移sequelize db:migrate

更多的sequelize指令查看sequelize-cli

建立关联关系

models/author.js新增hasMany

'use strict';
const {
  Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class Author extends Model {
    static associate(models) {
      Author.hasMany(models.Book,{
        foreignKey: {
          name: "authorId"
        }
      })
    }
  }
  Author.init({
    name: DataTypes.STRING
  }, {
    sequelize,
    modelName: 'Author'
  });
  return Author;
};

models/category.js新增hasMany关联

'use strict';
const {
  Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class Category extends Model {
    static associate(models) {
      Category.hasMany(models.Book,{
        foreignKey: {
          name: "categoryId"
        }
      })
    }
  }
  Category.init({
    name: DataTypes.STRING
  }, {
    sequelize,
    modelName: 'Category',
  });
  return Category;
};

models/book.js新增belongsTo关联

'use strict';
const {
  Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class Book extends Model {
    static associate(models) {
      Book.belongsTo(models.Author,{
        foreignKey: {
          name: "authorId",
        }
      }),
      Book.belongsTo(models.Category,{
        foreignKey: {
          name: "categoryId",
        }
      })
    }
  }
  Book.init({
    name: DataTypes.STRING,
    author: DataTypes.STRING
  }, {
    sequelize,
    modelName: 'Book'
  });
  return Book;
};

创建seed文件

sequelize seed:generate --name init-data

seeders/xxxxx-init-data.js

'use strict';
const {Category,Author,Book} = require('../models');

/** @type {import('sequelize-cli').Migration} */
module.exports = {
  async up (queryInterface, Sequelize) {
   await queryInterface.bulkInsert('Authors',[
    {name: "Henry Hazlitt",createdAt: new Date(),updatedAt: new Date()},
    {name:"Charles Wheelan",createdAt: new Date(),updatedAt: new Date()},
    {name: "Gregg Olsen",createdAt: new Date(),updatedAt: new Date()}
   ])
   await queryInterface.bulkInsert('Categories',[
    {name: "social",createdAt: new Date(),updatedAt: new Date()},
    {name: "economics",createdAt: new Date(),updatedAt: new Date()},
    {name: "science",createdAt: new Date(),updatedAt: new Date()},
    {name: "memoirs",createdAt: new Date(),updatedAt: new Date()},
    {name: "literature",createdAt: new Date(),updatedAt: new Date()}
   ])
   await queryInterface.bulkInsert('Books',[
    {name: "Cruel deception",createdAt: new Date(),updatedAt: new Date()},
    {name: "Liying next to me",createdAt: new Date(),updatedAt: new Date()},
    {name:"Economics in one lesson",createdAt: new Date(),updatedAt: new Date()},
    {name: "Thinking as a science",createdAt: new Date(),updatedAt: new Date()},
    {name:"The wisdom of Henry Hazlitt",createdAt: new Date(),updatedAt: new Date()},
    {name: "Nacked ecnomics",createdAt: new Date(),updatedAt: new Date()},
    {name: "Nacked statistics",createdAt: new Date(),updatedAt: new Date()},
    {name: "We come, we saw, we left",createdAt: new Date(),updatedAt: new Date()}
   ])
   await queryInterface.bulkInsert('Categories',[
    {name: "economics",createdAt: new Date(),updatedAt: new Date()},
   ])
    const economics = await Category.findOne({where: {name: "economics"}})
    const science = await Category.findOne({where: {name: "science"}})
    const memoirs = await Category.findOne({where: {name: "memoirs"}})
    const literature = await Category.findOne({where: {name: "literature"}})

    const HH = await Author.findOne({where: {name: "Henry Hazlitt"}})
    const CW = await Author.findOne({where: {name: "Charles Wheelan"}})
    const GO = await Author.findOne({where: {name: "Gregg Olsen"}})
   
    await Book.findOne({where: {name: "If you tell"}}).then(async (book) => {
      await book.setAuthor(GO)
      await book.setCategory(literature)
    })
    await Book.findOne({where: {name: "Liying next to me"}}).then(async(book) => {
      await book.setAuthor(GO)
      await book.setCategory(literature)
    })
    await Book.findOne({where: {name: 'Cruel deception'}}).then(async(book) => {
      await book.setAuthor(GO)
      await book.setCategory(literature)
    })
    await Book.findOne({where: {name: 'Economics in one lesson'}}).then(async(book) => {
      await book.setAuthor(HH)
      await book.setCategory(economics)
    })
    await Book.findOne({where: {name: 'Thinking as a science'}}).then(async(book) => {
      await book.setAuthor(HH)
      await book.setCategory(science)
    })
    await Book.findOne({where: {name: 'The wisdom of Henry Hazlitt'}}).then(async(book) => {
      await book.setAuthor(HH)
      await book.setCategory(memoirs)
    })
    await Book.findOne({where: {name: 'Nacked ecnomics'}}).then(async(book) => {
      await book.setAuthor(CW)
      await book.setCategory(economics)
    })
    await Book.findOne({where: {name: 'Nacked statistics'}}).then(async(book) => {
      await book.setAuthor(CW)
      await book.setCategory(science)
    })
    await Book.findOne({where: {name: 'We come, we saw, we left'}}).then(async(book) => {
      await book.setAuthor(CW)
      await book.setCategory(literature)
    })
  },

  async down (queryInterface, Sequelize) {
    await queryInterface.bulkDelete('Categories',null,{})
    await queryInterface.bulkDelete('Authors',null,{})
    await queryInterface.bulkDelete('Books',null,{})
  }
};

执行seed文件sequelize db:seed xxxxxx-init-data

onDelete和onUpdate

Foo.hasOne(Bar, {
  onDelete: 'RESTRICT',
  onUpdate: 'RESTRICT'
});
Bar.belongsTo(Foo);

RESTRICT, CASCADE, NO ACTION, SET DEFAULT and SET NULL.