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