mysql - 如何在 sequelize 中执行事务和回滚？

Question

在文档中它说:

try {

  const result = await sequelize.transaction(async (t) => {

    const user = await User.create({
      firstName: 'Abraham',
      lastName: 'Lincoln'
    }, { transaction: t });

    await user.setShooter({
      firstName: 'John',
      lastName: 'Boothe'
    }, { transaction: t });

    return user;

  });

  // If the execution reaches this line, the transaction has been committed successfully
  // `result` is whatever was returned from the transaction callback (the `user`, in this case)

} catch (error) {

  // If the execution reaches this line, an error occurred.
  // The transaction has already been rolled back automatically by Sequelize!

}

我想我会为此创建一个包装器并提出一个像这样的函数:

export const executor = async (query: Function, db: any) => {
  try {
    const result = await db.sequelize.transaction(async t => {
      const resp = await query(t);

      return resp;
    });

    // If the execution reaches this line, the transaction has been committed successfully
    // `result` is whatever was returned from the transaction callback (the `user`, in this case)
    return result;
  } catch (error) {
    // If the execution reaches this line, an error occurred.
    // The transaction has already been rolled back automatically by Sequelize!

    // log here

    throw new Error('Internal server error');
  }
};

像这样执行:

const r = executor(param => {
  db.sequelize.query(
    'CALL registerSportUser (:email, :password, :roleId, :firstName, :lastName, :age, :jobTitle, :prefLanguaged, :assigned, :sportId, :tableId, :position, :image, :imageName)',
    {
      replacements: {
        email: args.input.Email,
        password: PasswordHash,
        roleId: args.input.RoleId,
        firstName: args.input.FirstName,
        lastName: args.input.LastName,
        age: new Date(new Date(args.input.Age).toUTCString()),
        jobTitle: args.input.JobTitle,
        prefLanguaged: args.input.PrefLanguaged,
        assigned: false,
        sportId: '11ea859b-e3f3-6ba2-bf71-00ff98e8d5ab',
        tableId: null,
        position: null,
        image: null,
        imageName: null,
      },
    },
    { transaction: param }
  );
}, db);

为了测试这一点，我发送了一个 NULL 值，该值被插入到一个不允许空值的表列中。
这应该回滚我所有的数据库更改。

问题是它不会回滚任何东西......

控制台显示:

Executing (6d1aaebb-5247-4b0d-a9c4-d5b81a6da8db): START TRANSACTION;
Executing (6d1aaebb-5247-4b0d-a9c4-d5b81a6da8db): COMMIT;
Executing (default): CALL registerSportUser('email', '$2b$12$i1mc.tLpG0HjhK0Y9C/gTO1ySwBguOa2Tlr1fFZHUf1IgIKESU3gC', 1, 'firstname', 'lastName', '1988-07-17 02:00:00', 'Jobtitle', 1, false, '11ea859b-e3f3-6ba2-bf71-00ff98e8d5ab', NULL, NULL, NULL, NULL)

错误:

Unhandled rejection SequelizeDatabaseError: Column 'TabelId' cannot be null

该错误是有效的，因为我试图在不应该有空值的地方插入一个空值，但为什么不续写回滚所有更改？

而不是我应该得到的错误:

Executing (e5fb0b6b-1340-4f9f-9bf0-f8913ba54751): ROLLBACK;

我认为我的包装器是问题所在，但我不知道在哪里。

Answer 1

你忘记从 db.sequelize.query 返回 Promise :

const r = executor(param => {
  return db.sequelize.query(
    'CALL registerSportUser (:email, :password, :roleId, :firstName, :lastName, :age, :jobTitle, :prefLanguaged, :assigned, :sportId, :tableId, :position, :image, :imageName)',
    {
      replacements: {
        email: args.input.Email,
        password: PasswordHash,
        roleId: args.input.RoleId,
        firstName: args.input.FirstName,
        lastName: args.input.LastName,
        age: new Date(new Date(args.input.Age).toUTCString()),
        jobTitle: args.input.JobTitle,
        prefLanguaged: args.input.PrefLanguaged,
        assigned: false,
        sportId: '11ea859b-e3f3-6ba2-bf71-00ff98e8d5ab',
        tableId: null,
        position: null,
        image: null,
        imageName: null,
      },
    },
    { transaction: param }
  );
}, db);