Sequelize(Sqlite)中Discord机器人User与Collection模型关联失效问题

开发Discord机器人时，创建新用户账户需要为用户生成金币余额，并存储其购买角色的Collection（每个User对应一个Character集合，Character是MasterCharacter的独立副本）。控制台日志显示User与Collection表已建立关联，DB Browser中也能看到关联，但执行时始终抛出以下错误：

Error in execute: EagerLoadingError [SequelizeEagerLoadingError]: Collection is not associated to User!
    at Function._getIncludedAssociation (C:\Users\headm\code\wicked-after-death-bot\node_modules\sequelize\lib\model.js:565:13)
    at Function._validateIncludedElement (C:\Users\headm\code\wicked-after-death-bot\node_modules\sequelize\lib\model.js:502:53)
    at C:\Users\headm\code\wicked-after-death-bot\node_modules\sequelize\lib\model.js:421:37    
    at Array.map (<anonymous>)
    at Function._validateIncludedElements (C:\Users\headm\code\wicked-after-death-bot\node_modules\sequelize\lib\model.js:417:39)
    at Function.findAll (C:\Users\headm\code\wicked-after-death-bot\node_modules\sequelize\lib\model.js:1124:12)
    at async Function.findOne (C:\Users\headm\code\wicked-after-death-bot\node_modules\sequelize\lib\model.js:1240:12)
    at async Function.findOrCreate (C:\Users\headm\code\wicked-after-death-bot\node_modules\sequelize\lib\model.js:1411:21)
    at async Object.execute (C:\Users\headm\code\wicked-after-death-bot\commands\Account\CreateAccount.js:20:31)
    at async Object.execute (C:\Users\headm\code\wicked-after-death-bot\events\InteractionCreate.js:43:7)

相关代码文件如下：

dbInit.js #

const Sequelize = require('sequelize');
const sequelize = require('./Utils/sequelize');

const User = require('./Models/User.js')(sequelize, Sequelize.DataTypes);
const Shop = require('./Models/Shop.js')(sequelize, Sequelize.DataTypes);
const Collection = require('./Models/Collection.js')(sequelize, Sequelize.DataTypes);
const Enemy = require('./Models/Enemy.js')(sequelize, Sequelize.DataTypes);
const Character = require('./Models/Character.js')(sequelize, Sequelize.DataTypes);
const MasterCharacter = require('./Models/MasterCharacter.js')(sequelize, Sequelize.DataTypes);

// Model connections
User.hasOne(Collection, { foreignKey: 'user_id', as: 'collection' });
Collection.belongsTo(User, { foreignKey: 'user_id', as: 'user' });


Collection.hasMany(Character, { foreignKey: 'collection_id', as: 'characters' });
Character.belongsTo(Collection, { foreignKey: 'collection_id' });

Character.belongsTo(MasterCharacter, { foreignKey: 'master_character_id', as: 'masterCharacter' });
MasterCharacter.hasMany(Character, { foreignKey: 'master_character_id', as: 'instances' });

const shopData = require('./db/dbShop');
const characterData = require('./db/dbCharacters');
const enemyData = require('./db/dbEnemies');

// Authenticates connection to the database.
sequelize.authenticate()
  .then(() => {
    console.log('Connection has been established successfully.');
  })
  .catch(console.error);

// Sync changes and populate database
sequelize.sync({ force: true })
  .then(async () => {
    const shopPromises = shopData.map(item => Shop.upsert(item));
    const characterPromises = characterData.map(async item => {
      try {
        await Collection.findCreateFind({
          where: { collection_id: item.character_id },
          defaults: item,
          onDuplicate: ['collection_id'],
        });
      } catch (error) {
        console.error('Error syncing Collection:', error);
      }
    });
    const enemyPromises = enemyData.map(item => Enemy.upsert(item));
    console.log("User Associations:", Object.keys(User.associations));
console.log("Collection Associations:", Object.keys(Collection.associations));
    return Promise.all([...shopPromises, ...characterPromises, ...enemyPromises]);
  })
  .then(() => {
    console.log('All databases synced');
  })
  .catch((error) => {
    console.error('Error syncing databases:', error);
  });

async function fetchUserWithCollections(userId) {
  try {
    const user = await User.findOne({
      where: { user_id: userId },
      include: [{
        model: Collection,
        as: 'collections'
      }],
    });
    return user;
  } catch (error) {
    console.error(error);
    return null;
  }
}


module.exports = { User, Shop, Collection, Enemy, fetchUserWithCollections };

User.js Model #

module.exports = (sequelize, DataTypes) => {
  const User = sequelize.define('User', {
    user_id: {
      type: DataTypes.STRING,
      allowNull: false,
      primaryKey: true,
    },
    balance: {
      type: DataTypes.INTEGER,
      allowNull: false,
    },
    createdAt: {
      type: DataTypes.DATE,
      defaultValue: DataTypes.NOW,
    },
    updatedAt: {
      type: DataTypes.DATE,
      allowNull: true,
    },
  });

  return User;
};

Collection.js Model #

module.exports = (sequelize, DataTypes) => {
    const Collection = sequelize.define('Collection', {
      collection_id: {
        type: DataTypes.INTEGER,
        allowNull: false,
        primaryKey: true,
        autoIncrement: true,
      },
      user_id: {
        type: DataTypes.STRING,
        references: {
          model: 'Users',
          key: 'user_id'
        }
      },
      // ... any other attributes specific to the collection itself
    }, {
      timestamps: false,
    });
  
    return Collection;
  };

CreateAccount.js 命令文件 #

const { SlashCommandBuilder } = require('discord.js')
const { DataTypes, Sequelize } = require('sequelize')
const sequelize = require('../../Utils/sequelize')
const User = require('../../Models/User')(sequelize, DataTypes)
const Collection = require('../../Models/Collection')(sequelize, DataTypes)
const CharacterList = require('../../db/dbCharacters')

const specificCharacterIds = [0, 1, 2]

module.exports = {
  cooldown: 5,
  data: new SlashCommandBuilder()
    .setName('create')
    .setDescription('Create your economy account'),
  async execute(interaction) {
    const userId = interaction.user.id
    const t = await sequelize.transaction() // Initialize transaction

    try {
      const [user, created] = await User.findOrCreate({
        where: { user_id: userId },
        defaults: {
          balance: 730,
        },
        include: [{ model: Collection, as: 'collection' }], // Explicitly specify the foreignKey
        transaction: t,
      });

      // Function to calculate XP needed for next level
      const calculateXpNeeded = (level) => {
        if (level >= 1 && level <= 6) return 1000 * (level + 1)
        if (level >= 7 && level <= 8) return 6000 + (level + 1 - 6) * 2000
        if (level >= 9 && level <= 10) return 21000 + (level + 1 - 8) * 2000
        // more levels here
        return 1000 + (level - 1) * 1000
      }

      if (created) {
        await Promise.all(
          specificCharacterIds.map((id) => {
            const character = CharacterList.find((c) => c.character_id === id)
            return Collection.create(
              {
                collection_id: id,
                user_id: userId,
              },
              { transaction: t }
            )
          })
        )

        await t.commit() // Commit transaction

        return interaction.reply(
          `Your economy account has been created. You have 730 credits in your balance.`
        )
      } else {
        await t.rollback() // Rollback transaction
        return interaction.reply(
          `You already have an account. You currently have ${user.balance} coins in your account.`
        )
      }
    } catch (error) {
      await t.rollback() // Rollback transaction in case of error
      console.error('Error in execute:', error)
      return interaction.reply(
        'Something went wrong while creating your account.'
      )
    }
  },
}

核心问题：模型重复初始化导致关联丢失 #

在CreateAccount.js中，你直接调用require('../../Models/User')(sequelize, DataTypes)和require('../../Models/Collection')(sequelize, DataTypes)重新创建了User和Collection模型实例，这和dbInit.js中已经建立关联的模型是完全独立的两个实例，所以在执行findOrCreate时，Sequelize找不到这两个新实例之间的关联。

修复步骤 #

1. 修改CreateAccount.js，引入dbInit中已关联的模型 #

替换掉文件中重新初始化的User和Collection，直接从dbInit导入：

const { SlashCommandBuilder } = require('discord.js')
const sequelize = require('../../Utils/sequelize')
// 直接从dbInit导入已经建立关联的模型
const { User, Collection, Character } = require('../../dbInit')
const CharacterList = require('../../db/dbCharacters')

// ... 其余代码保持不变

2. 修正fetchUserWithCollections中的关联别名错误 #

在dbInit.js的fetchUserWithCollections函数中，你用了as: 'collections'，但实际关联定义的别名是collection（因为User.hasOne(Collection, {as: 'collection'})），别名不匹配会导致查询失败，需要修改：

async function fetchUserWithCollections(userId) {
  try {
    const user = await User.findOne({
      where: { user_id: userId },
      include: [{
        model: Collection,
        as: 'collection' // 改成和关联定义一致的别名
      }],
    });
    return user;
  } catch (error) {
    console.error(error);
    return null;
  }
}

3. 修正Collection创建时的主键冲突与逻辑错误 #

Collection的collection_id是自增主键，但你在CreateAccount.js中手动指定collection_id: id，会和自增逻辑冲突；同时你的设计是每个用户对应一个Collection，再关联多个Character，而非每个角色对应一个Collection。修改创建逻辑：

if (created) {
  // 先为用户创建一个Collection（不指定collection_id，让数据库自增）
  const userCollection = await Collection.create(
    { user_id: userId },
    { transaction: t }
  );

  // 基于这个Collection创建Character实例（关联到MasterCharacter）
  await Promise.all(
    specificCharacterIds.map((id) => {
      return Character.create(
        {
          collection_id: userCollection.collection_id,
          master_character_id: id,
          level: 1,
          xp: 0,
          xp_needed: calculateXpNeeded(1)
        },
        { transaction: t }
      )
    })
  )

  await t.commit()
  return interaction.reply(`你的经济账户已创建，初始余额730金币，同时获得了初始角色！`)
}

4. 修正dbInit中同步MasterCharacter的错误逻辑 #

在sequelize.sync的回调中，你用Collection同步master character数据，这应该同步到MasterCharacter表：

const characterPromises = characterData.map(async item => {
  try {
    // 同步到MasterCharacter表，而不是Collection
    await MasterCharacter.upsert(item);
  } catch (error) {
    console.error('Error syncing MasterCharacter:', error);
  }
});

验证修复 #

1. 重启机器人，确保dbInit.js中的关联代码正确执行
2. 执行/create命令，检查是否再抛出关联错误
3. 查看数据库，确认User、Collection、Character表之间的关联数据正确插入

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