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?

Answer 1

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:

DELETE FROM `sqlite_sequence` WHERE `name` = 'table_name'

Answer 2

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