Juanra Juanra - 2 months ago 53
Node.js Question

Nodejs sequelize how to truncate a foreign key referenced table

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

Answer

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.