Wipe all tables in a schema - sequelize nodejs

Question

To perform unit testing on a mysql database using sequelize as ORM, I need to refresh my database when each test start to run. Actually I wrote a solution like this:

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

but every time I create tables I have to add another instruction. I would implement a single instruction to perform a complete wipe of the entire schema

Something like:

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

Answer 1

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

This is documented at: https://sequelize.org/api/v6/class/src/sequelize.js~sequelize#instance-method-truncate this truncates all tables, which seems the closest to what you want:

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

and for the option argument:

The options passed to Model.destroy in addition to truncate

Parallel testing can then be handled with one of the techniques mentioned at: https://sqa.stackexchange.com/questions/16854/designing-database-reliant-tests-for-parallel-execution/47244#47244 Will's mocking suggestion also solves the parallel issue, so might be worth looking into as well.

The {truncate: cascade} is required when foreign keys are involved, otherwise PostgreSQL 13.4 complains with:

cannot truncate a table referenced in a foreign key constraint

{truncate: cascade} makes it run TRUNCATE "Mytable" CASCADE instead of just TRUNCATE "MyTable", related:

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

The {restartIdentity: true} can help make tests more reproducible by also resetting the primary key counter: How to reset autoIncrement primary key with sequelize? But note that it is broken on SQLite: https://github.com/sequelize/sequelize/issues/13286

Minimal runnable example:

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();
})();

When we run this, Sequelize logs the following two DB lines among others:

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

which seems to be its version of the TRUNCATE statement according to: https://sqlite.org/lang_delete.html#the_truncate_optimization

Tested on 6.5.1, Node v14.16.0.

Another testing approach: SQLite in-memory database

For testing on SQLite, this is a good approach that ensures that everything is completely clean each time, you don't even have to worry about truncating or creating unique database names

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

Answer 2

You should not touch the database when doing unit testing.

If you're testing business logic that talks to sequelize, create a mock of the interface to sequelize and inject that into the unit being tested. You can then make assertions or expectations with regard to calls to the mock interface's methods. Without more details of the testing environment, it's impossible to provide more concrete direction than that, but you might investigate the sequelize-mocking package to facilitate that.

If you're testing that sequelize is actually talking to your database, then you are doing a whole lot more than unit testing, and I would argue that you would want an out-of-band way of initializing and managing the environment, but I'd also point out that sequelize has a whole suite of tests already.