0

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 });
}
ﾤ ﾡ
  • 5,669
  • 6
  • 43
  • 57
C. Yee
  • 127
  • 2
  • 15

2 Answers2

0

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);
Ramin Faracov
  • 3,032
  • 1
  • 2
  • 8
  • Thanks for the comment. But I don't want to delete the whole table. And was looking for a way to perform the delete or pg_dump so that I wouldn't manually have to reset `setval`. Looks like there's no solution to this :( – C. Yee Jan 03 '22 at 14:12
0

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

Virginie L
  • 1
  • 2