SQLite: sequelize.sync({ alter: true }) recreates INTEGER PRIMARY KEY without AUTOINCREMENT #18265

Issue Creation Checklist

• I understand that my issue will be automatically closed if I don't fill in the requested information
• I have read the contribution guidelines

Bug Description

When using SQLite with sequelize.sync({ alter: true }), an existing table with an auto-increment primary key loses the AUTOINCREMENT attribute after Sequelize performs the alter table migration.

The first table creation correctly generates:

CREATE TABLE IF NOT EXISTS `ban_word_dictionary_relation`
(
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `dataId` INTEGER NOT NULL,
  `groupId` INTEGER NOT NULL,
  `createTime` DATETIME,
  `updateTime` DATETIME
);

However, when the table already exists and Sequelize runs alter, it recreates the table without AUTOINCREMENT:

CREATE TABLE IF NOT EXISTS `ban_word_dictionary_relation`
(
  `id` INTEGER PRIMARY KEY,
  `dataId` INTEGER NOT NULL,
  `groupId` INTEGER NOT NULL,
  `createTime` DATETIME,
  `updateTime` DATETIME
);

As a result, SQLite may reuse deleted primary key values because the table is no longer using AUTOINCREMENT.

Reproducible Example

Model definition:

import { sequelize, DataTypes } from "@/service/db";
import type { BanWordDictionaryRelationEntity } from "@app/common";
import { Model } from "sequelize";

export default sequelize.define<Model<BanWordDictionaryRelationEntity>>(
  "BanWordDictionaryRelation",
  {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true,
    },
    dataId: {
      type: DataTypes.INTEGER,
      allowNull: false,
    },
    groupId: {
      type: DataTypes.INTEGER,
      allowNull: false,
    },
    createTime: {
      type: DataTypes.DATE,
    },
    updateTime: {
      type: DataTypes.DATE,
    },
  },
  {
    tableName: "ban_word_dictionary_relation",
    timestamps: false,
  }
);

Run:

await sequelize.sync({ alter: true });

First startup

Generated SQL:

CREATE TABLE IF NOT EXISTS `ban_word_dictionary_relation`
(
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `dataId` INTEGER NOT NULL,
  `groupId` INTEGER NOT NULL,
  `createTime` DATETIME,
  `updateTime` DATETIME
);

Second startup

When the table already exists:

CREATE TABLE IF NOT EXISTS `ban_word_dictionary_relation_backup`
(
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `dataId` INTEGER NOT NULL,
  `groupId` INTEGER NOT NULL,
  `createTime` DATETIME,
  `updateTime` DATETIME
);

INSERT INTO `ban_word_dictionary_relation_backup`
SELECT `id`, `dataId`, `groupId`, `createTime`, `updateTime`
FROM `ban_word_dictionary_relation`;

DROP TABLE `ban_word_dictionary_relation`;

CREATE TABLE IF NOT EXISTS `ban_word_dictionary_relation`
(
  `id` INTEGER PRIMARY KEY,
  `dataId` INTEGER NOT NULL,
  `groupId` INTEGER NOT NULL,
  `createTime` DATETIME,
  `updateTime` DATETIME
);

INSERT INTO `ban_word_dictionary_relation`
SELECT `id`, `dataId`, `groupId`, `createTime`, `updateTime`
FROM `ban_word_dictionary_relation_backup`;

DROP TABLE `ban_word_dictionary_relation_backup`;

The recreated table definition is missing AUTOINCREMENT.

What do you expect to happen?

When Sequelize recreates an SQLite table during sync({ alter: true }), the generated table should preserve the original AUTOINCREMENT attribute.

Expected:

CREATE TABLE IF NOT EXISTS `ban_word_dictionary_relation`
(
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  ...
);

What is actually happening?

The recreated table loses the AUTOINCREMENT attribute:

CREATE TABLE IF NOT EXISTS `ban_word_dictionary_relation`
(
  `id` INTEGER PRIMARY KEY,
  ...
);

This changes SQLite behavior:

• INTEGER PRIMARY KEY AUTOINCREMENT guarantees that deleted row IDs are never reused.
• INTEGER PRIMARY KEY may reuse previously deleted IDs.

Therefore, after sync({ alter: true }), inserting new rows can reuse old IDs.

Environment

• Sequelize version: 6.37.8
• Node.js version: 22.12.0
• TypeScript version: ~6.0.2
• Database & Version: SQLite
• Connector library & Version: sqlite3 6.0.1

Would you be willing to resolve this issue by submitting a Pull Request?

• Yes, I have the time and I know how to start.
• Yes, I have the time but I will need guidance.
• No, I don't have the time, but I understand that I will need to wait until someone is interested in fixing this issue.