How to reset autoIncrement primary key with sequelize?

Question

I use sqlite3 as my DB and use sequelize library to handle it. I have a module and want to reset autoIncrement primary key after truncating it.

    var logModule = db.logModule()
    logModule.destroy({

        where:{},
        truncate: true
    })

But I found this way just clear all records in my table, didn't reset the autoIncrement primary key to zero.

Is there any way to reset primary key after clear all records in my table?

The whole point of autoincrement in sqlite is that a rowid never ever gets reused. Leave out the autoincrement when defining an `INTEGER PRIMARY KEY` column, or just live with it. What the rowid is shouldn't matter anyways. It's not like you're ever going to get close to the maximum value if you're letting sqlite pick the rowid... — Shawn, May 03 '19 at 09:35

score 12 · Answer 1 · edited May 09 '23 at 07:09

12

You should pass restartIdentity: true to destroy / truncate methods in sequelize.

   models[modelName]
     .destroy({ truncate: true, restartIdentity: true })

restartIdentity - only used in conjunction with TRUNCATE. Automatically restart sequences owned by columns of the truncated table.

This does not work as expected with the id field generated by sequelize for sqlite.

You have to explicitly invoke the SQL:

await sequelize.query(`DELETE FROM "sqlite_sequence" WHERE "name" = 'table_name'`)

edited May 09 '23 at 07:09

Ciro Santilli OurBigBook.com

347,512
102
1,199
985

answered Aug 07 '19 at 10:49

Gapur Kassym

1,131
12
10

This does not work as expected with the id field generated by sequelize for sqlite. You have to [explicitly invoke the SQL](https://www.designcise.com/web/tutorial/how-to-reset-autoincrement-number-sequence-in-sqlite#:~:text=DELETE%20FROM%20%60sqlite_sequence%60%20WHERE%20%60,is%20inserted%20in%20that%20table.): ``` DELETE FROM `sqlite_sequence` WHERE `name` = 'table_name' ``` – Linus Fernandes May 24 '21 at 13:53
1

[Issue raised](https://github.com/sequelize/sequelize/issues/13286) – Linus Fernandes May 27 '21 at 06:53

score 6 · Answer 2 · answered May 03 '19 at 09:24

SQLite keeps track of the largest ROWID that a table has ever held using the special SQLITE_SEQUENCE table. The SQLITE_SEQUENCE table is created and initialized automatically whenever a normal table that contains an AUTOINCREMENT column is created.

After destroy the table, you have to execute a query to reset SQE value for the table

sequelize.query("UPDATE SQLITE_SEQUENCE SET SEQ=0 WHERE NAME='log_module_table_name'");

score 0 · Answer 3 · answered May 03 '19 at 09:16

0

Use this;

Object.values(sequelize.models).map(function(model) {
  return model.destroy({ truncate: true });
});

answered May 03 '19 at 09:16

narayansharma91

2,273
1
12
20

How to reset autoIncrement primary key with sequelize?

3 Answers3

Linked