Nodejs sequelize how to truncate a foreign key referenced table

Question

[Important: this is only relevant for Sequelize Version < 3]

I have a "myTable" mysql table in which myTable.id is referenced by a foreign key on another table. I need to truncate "myTable". Normally with mysql shell I would do:

mysql> SET FOREIGN_KEY_CHECKS = 0; truncate table myTable; SET FOREIGN_KEY_CHECKS = 1;

Is there any way of doing this with sequelize?

I have tried to execute

sequelize.query('SET FOREIGN_KEY_CHECKS = 0; truncate table myTable; SET FOREIGN_KEY_CHECKS = 1;')

but I have the error:

`Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'truncate table myTable; SET FOREIGN_KEY_CHECKS = 1' at line 1`

If I execute the queries serially, I cannot truncate the table:

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint

score 27 · Answer 1 · answered Nov 21 '17 at 14:06

27

I got this by looking at another question and it worked for me on v4.13.2

MyTableModel.destroy({ truncate: { cascade: true } });

answered Nov 21 '17 at 14:06

galki

8,149
7
50
62

This worked for me on sequelize 6.x – Catfish Jan 31 '23 at 18:04

score 15 · Accepted Answer · answered Apr 09 '14 at 06:12

This is happening, because sequelize will not allow you to execute multiple queries with a single sequelize.query call. The described scenario is handled right now only for sequelize.sync({ force: true }) which drops all tables and recreates them afterwards. The method is internally using the following code:

https://github.com/sequelize/sequelize/blob/a014bd8d172fb8fd9881cee866abfcab842c30fc/lib/query-interface.js#L227-228

It basically loads every table and checks if there are foreign keys. If that is the case, sequelize will drop them before the other table. You could probably adopt the logic. Furthermore: If you decide to implement that stuff, you could probably open a pull request on github. That would rock hard. Another option which would probably work, is the following:

sequelize.transaction(function(t) {
  var options = { raw: true, transaction: t }

  sequelize
    .query('SET FOREIGN_KEY_CHECKS = 0', null, options)
    .then(function() {
      return sequelize.query('truncate table myTable', null, options)
    })
    .then(function() {
      return sequelize.query('SET FOREIGN_KEY_CHECKS = 1', null, options)
    })
    .then(function() {
      return t.commit()
    })
}).success(function() {
  // go on here ...
})

This works because transactions are using a dedicated connection, meaning you can easily execute commands in a row.

So, in other words we have to care about our FKs ourselves because Sequelize fails on that badly all the time https://github.com/sequelize/sequelize/issues/6894 — Green, Nov 19 '16 at 04:37
I have an error: `Error: Sequelize.query was refactored to only use the parameters 'sql' and 'options'. Please read the changelog about BC.` — Eduardo Cuomo, Mar 03 '17 at 17:53
NOTE: For V5 and above users, the `options` parameter should be the second argument now, like so: sequelize.query('your query here', options) — chesscov77, Aug 06 '20 at 10:54

score 11 · Answer 3 · answered Sep 19 '16 at 07:12

11

I'm using sequelize 3.24.3 and you can do

MyTableModel.truncate({ cascade: true });

here is the documentation of the cascade option

Only used in conjunction with TRUNCATE. Truncates all tables that have foreign-key references to the named table, or to any tables added to the group due to CASCADE.

answered Sep 19 '16 at 07:12

ThomasThiebaud

11,331
6
54
77

8

Doesn't work. `SequelizeDatabaseError: ER_TRUNCATE_ILLEGAL_FK: Cannot truncate a table referenced in a foreign key constraint` – Green Nov 19 '16 at 07:29
1

Are you using a compatible version ? – ThomasThiebaud Nov 19 '16 at 07:51
1

Worked with `sequelize`: `3.24.3`, FK constraints were configured as `ON DELETE SET NULL`. – Dr1Ku Nov 21 '16 at 13:15

score 6 · Answer 4 · edited May 23 '17 at 12:00

I'm using sequelise version 3.5.1 and sdepolds solution doesn't work anymore because the sequelise api has changed. Adapting his solution would result in:

sequelize.transaction(function(t) {
  var options = { raw: true, transaction: t }

  return sequelize
    .query('SET FOREIGN_KEY_CHECKS = 0', options)
    .then(function() {
      return sequelize.query('truncate table myTable', options)
    })
    .then(function() {
      return sequelize.query('SET FOREIGN_KEY_CHECKS = 1', options)
    })
}).then(function() {
  // go on here ...
})

According to Sequelise's documentation the last promise in the chain triggers the commit of the transaction. Also the second parameter (null) in sdepolds answer is not needed anymore. Furthermore sequelize.query must be returned for the outta then.

I have an error: `Error: Sequelize.query was refactored to only use the parameters 'sql' and 'options'. Please read the changelog about BC.` — Eduardo Cuomo, Mar 03 '17 at 17:54

score 4 · Answer 5 · answered Jan 14 '20 at 16:35

If you have used Sequelize 5, and you already have the associations defined in the models and have set the 'onDelete' property to 'CASCADE', then there is no need to add neither the property 'truncate' nor 'cascade' to the 'destroy' method argument. Instead just do this:

MyTableModel.destroy({ where: {}});

The table will be truncated.

score 2 · Answer 6 · answered Dec 24 '20 at 23:45

Sequelize 6.3.5 and none of these did not work for me in the case of onDelete: CASCADE

As a last resort, I managed to truncate tables in my unit tests like the following:

await MyModel.sequelize.query("SET FOREIGN_KEY_CHECKS = 0", null);
await MyModel.truncate();
await MyModel.sequelize.query("SET FOREIGN_KEY_CHECKS = 1", null);

Nodejs sequelize how to truncate a foreign key referenced table

6 Answers6