Truncating tables with Sequelize

README.md

In tests that use a database, it's necessary to clean out the tables before each run (we don't use after so we can debug a failed test's db).

Sometimes we forget the syntax though so here's what we do:

before(function truncateDatabase (done) {
  // http://docs.sequelizejs.com/en/v3/docs/raw-queries/
  // https://www.postgresql.org/docs/9.3/static/sql-truncate.html
  // DEV: PostgreSQL doesn't support truncating all tables via a `*`
  // DEV: Our query is vulnerable to SQL injection but we can't use bind and trust our table names more/less
  var tableNames = _.pluck(_.values(sequelize.models), 'tableName');
  sequelize.query('TRUNCATE TABLE ' + tableNames.join(', ')).asCallback(done);
});

Why don't you use a forced sync on your models?

I'm guessing that truncating is quicker than force sync? I don't imagine the structure of the tables should be changing in-between tests.

You can also do it like that:

Object.values(sequelize.models).map(function(model) {
  return model.destroy({ truncate: true });
}); 

model.destroy({
where: {},
truncate: true
})

Why don't you use a forced sync on your models?

It's been three years but a good reason not to do this is because sync ignores the schema migrations. This may sound inconsequential but there are things with sequelize like defaulting ids to UUIDs where the best solution remains configuring the database within migrations.

There is also a sequelize.truncate now that truncates all tables: https://stackoverflow.com/questions/47816162/wipe-all-tables-in-a-schema-sequelize-nodejs/66985334#66985334

Hi.
sequelize.truncate will fail if you have constrains checks, at least it happen to me.
Note: This is for MySql dialect, for another one the equivalent sentence must be used.
This work for me (in a .js file to be run by npm run script):

const db = require('../../models/index');
const truncate = async () =>{
try{
console.log(db.sequelize.models);
await db.sequelize.query("SET GLOBAL FOREIGN_KEY_CHECKS = 0", null);
await db.sequelize.truncate({cascade: true, restartIdentity: true});
console.log('Database tables has been truncated successfully');
await db.sequelize.query("SET GLOBAL FOREIGN_KEY_CHECKS = 1", null);
}catch(e){
console.log('Something went wrong: ', e);
}
};
truncate();