Skip to content

Instantly share code, notes, and snippets.

@twolfson
Created November 11, 2016 23:09
Show Gist options
  • Save twolfson/5f965a08be93b4e7a3dadbd42d7e8766 to your computer and use it in GitHub Desktop.
Save twolfson/5f965a08be93b4e7a3dadbd42d7e8766 to your computer and use it in GitHub Desktop.
Truncating tables with Sequelize

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);
});
@danpe91
Copy link

danpe91 commented May 30, 2017

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

@DanielRHayes
Copy link

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.

@mrded
Copy link

mrded commented Jan 5, 2018

You can also do it like that:

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

@az-iB
Copy link

az-iB commented Oct 7, 2018

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

@bradley
Copy link

bradley commented Jul 29, 2020

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.

@cirosantilli
Copy link

@PE4CE4ALL
Copy link

PE4CE4ALL commented Jun 29, 2023

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment