Sequelize Model.update()无法更新SQLite数据库记录问题求助

使用Sequelize更新SQLite数据库的Channels表记录时，日志显示更新语句已执行，但重新查询后记录并未改变。其他表的操作均正常，仅该表出现此问题。

更新代码 #

import { MessageEmbed } from 'discord.js';
import { DisableSlashCommandData as slashCommandData } from '../SlashCommandData/disable.js';

export default {
    slashCommandData,
    name: 'disable',
    async execute(client, interaction) {
        if (!interaction.member.permissions.has('ADMINISTRATOR')) {
            return await interaction.reply({
                content: 'You must have the `ADMINISTRATOR` permission in order to use this command!',
                ephemeral: true,
            });
        }

        const channel = await client.data.Channels.findByPk(interaction.channel.id);

        // 代码一直卡在这个if分支，因为channel没有变化，条件始终为true
        if ((!channel) || !channel.get('disabled')) {
            /* 未禁用 - 禁用当前频道 */
            await client.data.Channels.update({
                disabled: true,
            }, {
                where: {
                    id: interaction.channel.id,
                },
            });

            return await interaction.reply({
                embeds: [
                    new MessageEmbed()
                        .setColor(client.config.colors.red)
                        .setAuthor({
                            name: interaction.user.tag,
                            iconURL: interaction.user.displayAvatarURL({ dynamic: true }),
                        })
                        .setDescription(`Interactions will now be ignored in this channel (${interaction.channel.toString()})`)
                        .setTimestamp(),
                ],
            });
        }
        else {
            /* 已禁用 - 启用频道 */
            await client.data.Channels.update({
                disabled: false,
            }, {
                where: {
                    id: interaction.channel.id,
                },
            });

            return await interaction.reply({
                embeds: [
                    new MessageEmbed()
                        .setColor(client.config.colors.green)
                        .setAuthor({
                            name: interaction.user.tag,
                            iconURL: interaction.user.displayAvatarURL({ dynamic: true }),
                        })
                        .setDescription(`Interactions will no longer be ignored in this channel (${interaction.channel.toString()})`)
                        .setTimestamp(),
                ],
            });
        }
    },
};

client.data定义 #

{
  sequelize: <ref *1> Sequelize {
    options: {
      dialect: 'sqlite',
      dialectModule: null,
      dialectModulePath: null,
      protocol: 'tcp',
      define: {},
      query: {},
      sync: {},
      timezone: '+00:00',
      standardConformingStrings: true,
      logging: [Function: log],
      omitNull: false,
      native: false,
      replication: false,
      ssl: undefined,
      pool: {},
      quoteIdentifiers: true,
      hooks: {},
      retry: [Object],
      transactionType: 'DEFERRED',
      isolationLevel: null,
      databaseVersion: 0,
      typeValidation: false,
      benchmark: false,
      minifyAliases: false,
      logQueryParameters: true,
      storage: 'C:\\Users\\User\\Documents\\GitHub\\Project\\assets\\db\\database.sqlite'
    },
    config: {
      database: 'database',
      username: 'user',
      password: 'password',
      host: 'localhost',
      port: undefined,
      pool: {},
      protocol: 'tcp',
      native: false,
      ssl: undefined,
      replication: false,
      dialectModule: null,
      dialectModulePath: null,
      keepDefaultTimezone: undefined,
      dialectOptions: undefined
    },
    dialect: SqliteDialect {
      sequelize: [Circular *1],
      connectionManager: [ConnectionManager],
      queryGenerator: [SQLiteQueryGenerator],
      queryInterface: [SQLiteQueryInterface]
    },
    queryInterface: SQLiteQueryInterface {
      sequelize: [Circular *1],
      queryGenerator: [SQLiteQueryGenerator]
    },
    models: { cases: cases, user: user, channels: channels },
    modelManager: ModelManager { models: [Array], sequelize: [Circular *1] },
    connectionManager: ConnectionManager {
      sequelize: [Circular *1],
      config: [Object],
      dialect: [SqliteDialect],
      versionPromise: null,
      pool: [Pool],
      connections: [Object],
      lib: [Object]
    }
  },
  Cases: cases,
  Users: user,
  Channels: channels
}

数据库初始化代码 #

/**
 * Starts the database and defines models.
 */

import Sequelize from 'sequelize';
import { join } from 'node:path';

import { Case } from '../../models/Case.js';
import { User } from '../../models/User.js';
import { Channel } from '../../models/Channel.js';

export function initDb() {
    const sequelize = new Sequelize('database', 'user', 'password', {
        host: 'localhost',
        dialect: 'sqlite',
        storage: join(process.cwd(), 'assets', 'db', 'database.sqlite'),
        logging: console.log,
        logQueryParameters: true,
    });

    const Cases = Case(sequelize);
    const Users = User(sequelize);
    const Channels = Channel(sequelize);

    if (process.argv.includes('--syncdb')) {
        // necessary to construct tables
        console.info('[Sequelize] Syncing database...');
        const start = Date.now();
        sequelize.sync({ force: true });
        console.info(`[Sequelize] Successfully synced sequelize database in ${start - Date.now()} ms`);
    }

    return Object.freeze({
        sequelize,
        Cases,
        Users,
        Channels,
    });
}

Channel模型定义 #

import { DataTypes } from 'sequelize';

/**
 * Defines the 'channel' model and returns it
 * @param {import("sequelize").Sequelize} sequelize Sequelize instance
 * @returns {import("sequelize").Model}
 */
export function Channel(sequelize) {
    return sequelize.define('channels', {
        id: {
            unique: true,
            primaryKey: true,
            allowNull: false,
            type: DataTypes.STRING,
        },
        disabled: {
            type: DataTypes.STRING,
            allowNull: false,
            defaultValue: '0',
        },
    }, {
        timestamps: true,
    });
}

SQLite执行日志 #

Executing (default): SELECT `id`, `disabled`, `createdAt`, `updatedAt` FROM `channels` AS `channels` WHERE `channels`.`id` = '985789702302994432';
Executing (default): UPDATE `channels` SET `disabled`=$1,`updatedAt`=$2 WHERE `id` = $3; {"$1":true,"$2":"2022-07-22 18:17:16.651 +00:00","$3":"985789702302994432"}

依赖版本 #

Project@1.0.0 C:\Users\User\Documents\GitHub\Project
├── @discordjs/rest@0.5.0
├── discord-api-types@0.34.0
├── discord.js@13.8.0
├── dotenv@16.0.1
├── erlpack@0.1.4
├── eslint@8.17.0
├── sequelize@6.20.1
├── sqlite3@5.0.8
├── utf-8-validate@5.0.9
└── zlib-sync@0.1.7

问题原因及解决方案 #

核心问题 #

Channel模型中disabled字段定义为DataTypes.STRING，默认值是字符串'0'，但更新操作传入的是布尔值true/false。SQLite是动态类型数据库，会将布尔值自动转换为整数1/0存储，但Sequelize读取时会按照模型定义的STRING类型尝试转换，导致类型不匹配：

• 数据库存储的是整数1/0，Sequelize读取后可能返回数字而非字符串
• 判断条件!channel.get('disabled')对数字0会返回true，对数字1返回false，但初始值是字符串'0'（真值），!channel.get('disabled')会返回false，后续更新后存储的是整数，又会导致判断逻辑混乱，最终出现更新后查询无变化的假象。

方案1：修正模型字段类型（推荐） #

将disabled字段改为布尔类型，符合开关字段的语义：

disabled: {
    type: DataTypes.BOOLEAN,
    allowNull: false,
    defaultValue: false,
},

修改后需重新同步数据库（执行--syncdb参数），后续更新操作传入布尔值true/false即可正常工作，判断逻辑!channel.get('disabled')也能正确生效。

方案2：保持字符串类型，统一数据格式 #

如果必须保留STRING类型，更新时需传入字符串'1'/'0'，并修改判断逻辑为字符串比较：

// 更新时
await client.data.Channels.update({
    disabled: '1', // 替换true为'1'，false为'0'
}, {
    where: { id: interaction.channel.id },
});

// 判断条件改为
if ((!channel) || channel.get('disabled') !== '1') {

内容的提问来源于stack exchange，提问作者Asad