CODEWITHSUNDEEP
node.jssqlitesequelize.js
Ricky Parker
Ricky Parker

Reputation: 143

How to reset autoIncrement primary key with sequelize?

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?

Upvotes: 9

Views: 8217

Answers (3)

Gapur Kassym
Gapur Kassym

Reputation: 1201

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'`)

Upvotes: 13

hoangdv
hoangdv

Reputation: 16147

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

Upvotes: 6

narayansharma91
narayansharma91

Reputation: 2353

Use this; 

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

Upvotes: 0

Related Questions