如何在Sequelize.js+Postgres.js中配置User与Media表的主外键关联并查询

Hey there! Let's sort out this association issue you're facing. The core problem here is that you've got the association direction reversed, and a couple of small tweaks to your model setup will get your include queries working properly.

What's Wrong with the Current Setup? #

Your User table has the media_id foreign key that references the Media table's id. This means a User belongs to a Media record (since each user's profile links to one media entry). But right now, you've defined Media.belongsTo(User), which would imply the foreign key lives on the Media table—this is backwards.

Corrected Model Code #

First, make sure both models are defined before setting up the association (to avoid reference errors). Here's the fixed version:

Media Model

const Media = sequelize.sequelize.define('media', {
  id: { type: sequelize.Sequelize.INTEGER, primaryKey: true },
  path: { type: sequelize.Sequelize.STRING },
  type: sequelize.Sequelize.STRING
});

User Model

const User = sequelize.sequelize.define('users', {
  id: { type: sequelize.Sequelize.INTEGER, autoIncrement: true, primaryKey: true },
  firstName: { type: sequelize.Sequelize.STRING },
  lastName: { type: sequelize.Sequelize.STRING },
  email: { type: sequelize.Sequelize.STRING },
  media_id: { type: sequelize.Sequelize.INTEGER } // Remove `foreignKey: true` here—Sequelize handles this via association
});

// Correct association: User belongs to Media, using media_id as the foreign key
User.belongsTo(Media, { 
  as: 'profile', // Alias to match your "profile field" description
  foreignKey: { 
    name: 'media_id', 
    allowNull: false 
  } 
});

Syncing the Tables #

Since User depends on Media (because of the foreign key), sync Media first to avoid database errors:

// Sync Media first, then User
await Media.sync();
await User.sync();

Performing the Associated Query #

Now you can query Users and include their linked Media records using the alias we set (profile):

const usersWithProfile = await User.findAll({
  where: {},
  include: [{ model: Media, as: 'profile' }]
});

Key Notes #

• The as alias makes your query results more readable—instead of a generic media key, you'll get a profile key with the linked media data, which aligns with your original requirement.
• Remove foreignKey: true from the media_id field definition in the User model; Sequelize recognizes the foreign key through the association setup.
• Always define associations after both models are declared to prevent "undefined" reference errors.

内容的提问来源于stack exchange，提问作者Ajay P.