'Sequelize queryInterface bulkDelete does not reset id sequence

I use pg_dump to make a copy of the database, copy1.sql.

I run an up migration to create a new instance

up: asyn (queryInterface) => {
  return await queryInterface.bulkInsert('keys', [{ clientKey: 'key123' }]);
}

I run a down migration to delete the instance

down: async (queryInterface) => {
  return await queryInterface.bulkDelete('keys', { clientKey: ['key123'] });
}

I do another pg_dump of the database, copy2.sql. I compare the first copy of the database with the second copy of the database to show that the down migration worked properly by running a bash script diff "copy1.sql" "copy2.sql"

The difference is

-SELECT pg_catalog.setval('public.keys_id_seq', 6, true);
+SELECT pg_catalog.setval('public.keys_id_seq', 7, true);

This makes my test fail because the copies of both databases are not identical due to this difference. Even though I deleted that key, it's saying the next id sequence is going to start at 8 instead of 7 according to this document. The table rows that currently exists are 1 through 6. Is there a way to delete the instance so that the sequence will start at 7 instead of 8? Meaning both copies of the database should have

SELECT pg_catalog.setval('public.keys_id_seq', 6, true);

Are there options I can include? Maybe something like

down: async (queryInterface) => {
  return await queryInterface.bulkDelete('keys', { clientKey: ['key123'] }, { resetIdSequence: true });
}
sqlpostgresqlsequelize.jsqueryinterfacebulk-delete


Solution 1:[1]

You can reset sequence using the truncate table command. Truncate table command erases all table data. For example:

truncate table table_name restart identity;

Second way manual resetting using setval. Example:

select setval('your_table_id_seq', 1, false);

If you don't delete all table data, then recommended set sequence value to the maximum id of records. Example:

select setval('your_table_id_seq', COALESCE((select max(id)+1 from your_table), 1), false);

Solution 2:[2]

I know it might be too late for you but I had the same problem and resolved it by adding {restartIdentity: true} on my migration file, like this (example of one of my tables):

async down(queryInterface, Sequelize) {
    await queryInterface.dropTable('card', {restartIdentity: true});
  }

To be sure it works I tried several "turns" with those commands on the terminal :

  • npx sequelize db:migrate, npx sequelize db:seed:all : everything is on place :)
  • npx sequelize db:migrate:undo:all : no tables, good!
  • npx sequelize db:migrate, npx sequelize db:seed:all : everything is good, foreign keys are still the good ones, great !

So for your code you could try this :

down: async (queryInterface) => {
  return await queryInterface.bulkDelete('keys', { clientKey: ['key123'] }, {restartIdentity: true});
}

Hope this helps ;)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Ramin Faracov
Solution 2 Virginie L

Related Questions