sequelize 基本使用

sequelize-basics.md

sequelize 基本使用

分三部分：

• 基本关系
• 批量插入、分页查询、scope和托管事务
• 综合示例，作者、文章、标签，1多多，多对多

基本关系

包括：一对一、一对多、多对多

const { Sequelize, DataTypes } = require("sequelize");

const sequelize = new Sequelize({
  dialect: "sqlite",
  storage: "./database.sqlite",
});

const User = sequelize.define("User", {
  email: {
    type: DataTypes.STRING,
    unique: true,
    allowNull: false,
  },
  password: {
    type: DataTypes.STRING,
  },
  nickname: {
    type: DataTypes.STRING,
  },
});

const Desk = sequelize.define("Desk", {
  type: {
    type: DataTypes.STRING,
    defaultValue: "class-1",
  },
});

const Computer = sequelize.define("Computer", {
  type: {
    type: DataTypes.STRING,
    defaultValue: "macbook mini",
  },
});

User.hasOne(Desk, {
  onDelete: "RESTRICT",
  onUpdate: "RESTRICT",
});
Desk.belongsTo(User);

User.hasMany(Computer);
Computer.belongsTo(User);

const Role = sequelize.define("Role", {
  name: {
    type: DataTypes.UUID,
    primaryKey: true,
    defaultValue: Sequelize.UUIDV4,
  },
  title: {
    type: DataTypes.STRING,
    unique: true,
  },
});

const UserRole = sequelize.define("UserRole", {
  UserId: {
    type: DataTypes.INTEGER,
    references: {
      model: User,
      key: "id",
    },
  },
  RoleId: {
    type: DataTypes.INTEGER,
    references: {
      model: Role,
      key: "name",
    },
  },
});

User.belongsToMany(Role, { through: UserRole });
Role.belongsToMany(User, { through: UserRole });

(async () => {
  try {
    await sequelize.sync({ force: true });

    let user = new User({
      email: "joe@qq.com",
      password: "123",
    });
    user.setDesk(new Desk());
    await user.save();

    user.setDesk(new Desk());
    let computer = new Computer();
    await computer.save();
    user.addComputer(computer);
    await user.save();

    user = new User({
      email: "tom@gmail.com",
      password: "123",
    });
    await user.save();
    computer = new Computer();
    await computer.save();
    user.addComputer(computer);
    await user.save();

    user = await User.findByPk(1);
    desk = await user.getDesk();
    console.log(`desk id: ${desk.id}`);
    console.log(`computer count: ${await user.countComputers()}`);

    let role = new Role();
    console.log(role.name);
    await role.save();

    user.addRole(role);
    await user.save();
    user = await User.findByPk(1);
    console.log(`role count: ${await user.countRoles()}`);

    role=await Role.findOne();
    console.log(`user count: ${await role.countUsers()}`);

    await sequelize.close();
  } catch (error) {
    console.error("Unable to connect to the database:", error);
  }
})();

批量插入、分页查询、scope和托管事务

const { Sequelize, DataTypes, Op } = require("sequelize");

const sequelize = new Sequelize({
  dialect: "sqlite",
  storage: ":memory:",
});

const User = sequelize.define("User", {
  email: {
    type: DataTypes.STRING,
    unique: true,
    allowNull: false,
  },
  password: {
    type: DataTypes.STRING,
  },
  nickname: {
    type: DataTypes.STRING,
  },
});

(async () => {
  try {
    await sequelize.sync({ force: true });

    // 批量添加
    let users = [];

    for (let i = 0; i < 10; i++) {
      let user = {
        email: `t_${i}@qq.com`,
        password: `p_${i}`,
        nickname: `t_${i}`,
      };
      users.push(user);
    }

    await User.bulkCreate(users);

    // 分页查询
    const { count, rows } = await User.findAndCountAll({
      where: {
        email: {
          [Op.like]: "t_%",
        },
      },
      offset: 9,
      limit: 3,
    });

    console.log(`==>${count}, ${rows.length}`);

    // model scope
    User.addScope("exclude_name", {
      where: {
        email: {
          [Op.not]: "t_0@qq.com",
        },
      },
    });

    User.addScope("nicknameList", function (name) {
      return {
        where: {
          nickname: {
            [Op.like]: `%${name}%`,
          },
        },
      };
    });

    let results = await User.scope("exclude_name").findAll();
    console.log(`===>scope: ${results.length}`);

    results = await User.scope("nicknameList", {
      method: ["nicknameList", "2"],
    }).findAll();
    console.log(`===>scope: ${results.length}`);

    // 托管事务
    await sequelize.transaction(async (t) => {
      users = [];

      for (let i = 0; i < 10; i++) {
        let user = {
          email: `s_${i}@qq.com`,
          password: `s_${i}`,
        };
        users.push(user);
      }

      try {
        await User.bulkCreate(users, { transaction: t });

        user = new User({
          email: "s_3@qq.com",
          password: "123",
        });
        // 下面语句会因为重复报错，事务回滚
        await user.save({ transaction: t });
      } catch (error) {
        throw error;
      }
    });

    await sequelize.close();
  } catch (error) {
    console.error(error);
  }
})();

综合示例，作者、文章、标签，1多多，多对多

const { Sequelize, DataTypes } = require("sequelize");

const sequelize = new Sequelize({
  dialect: "sqlite",
  storage: "./blog.sqlite",
});

// 用户定义
const User = sequelize.define("User", {
  email: {
    type: DataTypes.STRING,
    unique: true,
    allowNull: false,
  },
  password: {
    type: DataTypes.STRING,
  },
  nickname: {
    type: DataTypes.STRING,
  },
});

// 文章定义
const Post = sequelize.define("Post", {
  title: {
    type: DataTypes.STRING,
    allowNull: false,
  },
  content: {
    type: DataTypes.TEXT,
  },
});

// 文章作者关系，多对一
User.hasMany(Post);
Post.belongsTo(User);

// 标签定义
const Tag = sequelize.define("Tag", {
  name: {
    type: DataTypes.STRING,
    unique: true,
    allowNull: false,
  },
});

Post.belongsToMany(Tag, { through: "posts_tags" });
Tag.belongsToMany(Post, { through: "posts_tags" });

(async () => {
  await sequelize.sync({ force: true });

  // 创建用户
  let user = new User({
    email: "joe@qq.com",
    password: "123",
    nickname: "joe",
  });

  await user.save();

  // 创建文章
  let post = new Post({
    title: "Say Hi~",
    content: "This is first post.",
  });
  await post.save();

  // 设置 post->user 多对一
  await post.setUser(user);

  // eager loading posts
  user = await User.findByPk(user.id, { include: Post });
  console.log(JSON.stringify(user));
  console.log(JSON.stringify(user.Posts));

  // 创建多个tag
  let tags = [];

  for (let i = 0; i < 2; i++) {
    const tag = new Tag({
      name: `tag_${i}`,
    });
    await tag.save();
    tags.push(tag);
  }

  // 设置标签文章多对多
  post.addTags(tags);
  await post.save();

  post = await Post.findByPk(post.id);
  console.log(JSON.stringify(post));
  tags = await post.getTags();
  console.log(JSON.stringify(tags));
})();