javascript - 删除模式中的所有表-sequelize nodejs

Question

要使用sequelize 作为 ORM 在 mysql 数据库上执行单元测试，我需要在每个测试开始运行时刷新我的数据库。实际上我写了一个这样的解决方案:

beforeEach(() => {
   table1.destroy({ where: {} })
   table2.destroy({ where: {} })
   table3.destroy({ where: {} })
})

但每次我创建表时，我都必须添加另一条指令。我将实现一条指令来执行整个架构的完全删除

类似于:

beforeEach(() => db.clean())

Answer 1

sequelize.truncate({cascade: true, restartIdentity: true })

此记录位于:https://sequelize.org/api/v6/class/src/sequelize.js~sequelize#instance-method-truncate这会 chop 所有表，这似乎最接近您想要的:

Truncate all tables defined through the sequelize models. This is done by calling Model.truncate() on each model.

对于选项参数:

The options passed to Model.destroy in addition to truncate

然后可以使用以下提到的技术之一来处理并行测试:https://sqa.stackexchange.com/questions/16854/designing-database-reliant-tests-for-parallel-execution/47244#47244威尔的mocking suggestion还解决了并行问题，因此可能也值得研究。

当涉及外键时，需要{truncate:cascade}，否则 PostgreSQL 13.4 会提示:

cannot truncate a table referenced in a foreign key constraint

{truncate:cascade} 使其运行 TRUNCATE "Mytable"CASCADE 而不仅仅是 TRUNCATE "MyTable"，相关:

• https://github.com/sequelize/sequelize/issues/11289
• Postgresql truncate table with foreign key constraint

{restartIdentity: true} 还可以通过重置主键计数器来帮助提高测试的可重复性:How to reset autoIncrement primary key with sequelize?但请注意，它在 SQLite 上已损坏:https://github.com/sequelize/sequelize/issues/13286

最小可运行示例:

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

const sequelize = new Sequelize({
  dialect: 'sqlite',
  storage: 'db.sqlite',
});
const IntegerNames = sequelize.define(
  'IntegerNames', {
  value: { type: DataTypes.INTEGER, allowNull: false },
  name: { type: DataTypes.STRING, },
});
const IntegerNames2 = sequelize.define(
  'IntegerNames2', {
  value: { type: DataTypes.INTEGER, allowNull: false },
  name: { type: DataTypes.STRING, },
});

(async () => {
// Create and populate databases.
await IntegerNames.sync({force: true})
await IntegerNames.create({value: 2, name: 'two'});
await IntegerNames.create({value: 3, name: 'three'});
await IntegerNames2.sync({force: true})
await IntegerNames2.create({value: 4, name: 'four'});
await IntegerNames2.create({value: 5, name: 'five'});

// Check that they were populated.
assert((await IntegerNames.findAll()).length === 2);
assert((await IntegerNames2.findAll()).length === 2);

// Truncate them and check that they are empty.
await sequelize.truncate({ cascade: true, restartIdentity: true });
assert((await IntegerNames.findAll()).length === 0);
assert((await IntegerNames2.findAll()).length === 0);

// Cleanup.
await sequelize.close();
})();

当我们运行它时，Sequelize 会记录以下两行数据库行:

Executing (default): DELETE FROM `IntegerNames2s`
Executing (default): DELETE FROM `IntegerNames`

这似乎是 TRUNCATE 语句的版本，根据:https://sqlite.org/lang_delete.html#the_truncate_optimization

在 6.5.1、 Node v14.16.0 上测试。

另一种测试方法:SQLite内存数据库

对于 SQLite 测试，这是一个很好的方法，可以确保每次都完全干净，您甚至不必担心 chop 或创建唯一的数据库名称

new Sequelize({
  dialect: 'sqlite',
  storage: ':memory:',
})