Sequelize: Create instance with existing association

Question

Trying to get sequelize to return the newly created object with an existing association. My goal is to create a new user with an association to an existing organisation and return both.

in user.model.js:

export default (sequelize, DataTypes) => {
  const User = sequelize.define('user', {
    email: {
      type: DataTypes.STRING,
      unique: true,
    },
  });
  User.associate = (models) => {
    User.belongsTo(models.Organisation, {
      foreignKey: {
        name: 'organisationId',
        field: 'organisation_id',
      },
    });
  };
  return User;
};

in organisation.model.js:

export default (sequelize, DataTypes) => {
  const Organisation = sequelize.define('organisation', {
    name: {
      type: DataTypes.STRING,
      unique: true,
    },
  });
  return Organisation;
};

---

SUCCEEDS: Getting an existing user

I'm able to retrieve an existing user and its organisation using the include option for the query.

const user = models.User.findOne({
  where: {
    email: 'existing@user.com',
  },
  include: [
    { model: models.Organisation, attributes: ['name'] },
  ],
});

FAILS: Creating a new user

Instance is inserted but query fails to return organisation attributes.

const user = models.User.create({
  email: 'new@user.com',
  organisationId: 'existing-organisation-id'
}, {
  include: [
    { model: models.Organisation, attributes: ['name'] },
  ],
});

SUCCEEDS: Creating a new user and querying it

Instance is inserted and then using another query retrieved with organisation data. The problem is that it takes two queries to the database. It should only take one,

models.User.create({
  email: 'new@user.com',
  organisationId: 'existing-organisation-id'
}).then(() => {
  models.User.findOne({
    where: {
      email: 'new@user.com',
    },
    include: [
      { model: models.Organisation, attributes: ['name'] },
    ],
  });
});

I've been reading through the docs thoroughly but I think I must have missed something. The database I'm connecting to is PostgreSQL. Would really appreciate someone to point me in the right direction.

Answer 1

One thing i caught is that you are using organisation_id in the user model relationship but didn't define it in the model. Sequelize will automatically create an id field for models that don't have PK defined. As far as the field name for it, i don't know how your config is setup to where if it may prefix it or leave it as id.

When i define relationships, i specify an alias using the as options. I also specify the relationship between both models. Here is how i would do it:

Organisation.hasMany(User, {
  as: 'members',
  foreignKey: '<FK field on User model>',
  sourceKey: '<PK field on Organisation model>' 
});

User.belongsTo(Organisation, {
  as: 'organisation',
  foreignKey: '<FK field on User model>',
  targetKey: '<PK field on Organisation model>'
});

When a model hasOne/hasMany other model(s), the options object needs the sourceKey prop (the key field of the parent model, usually the PK); when a model belongsTo another model, the options object needs the targetKey prop (same as the sourceKey). The targetKey and sourceKey just specifies the relationship: which model is the child or parent of the other.

So in your case, i would do it like this:

Organisation.hasMany(User, {
  as: 'members',
  foreignKey: 'organisation_id',
  name: 'organisationId',
  sourceKey: 'id' // assuming PK of Organisation is id
});

User.belongsTo(Organisation, {
  as: 'organisation',
  foreignKey: 'organisation_id', // assuming PK of Organisation is id
  name: 'organisationId',
  targetKey: 'id'
});

So now doing this should work:

const user = models.User.create({
  email: 'new@user.com',
  organisationId: 'existing-organisation-id'
}, {
  include: [
    { association: models.Organisation, as: 'organisation', attributes: ['name'] },
  ],
});

i was following along this page when i started learning it - https://sequelize.org/master/manual/creating-with-associations.html

Answer 2

I think that is not yet supported yet :

options.include : an array of include options - Used to build prefetched/included model instances.

you can use include to create a associated data at same time , but you can't just fetch it like that

But shorter way of doing is :

models.User.create({
  email: 'new@user.com',
  organisationId: 'existing-organisation-id'
}).then((user) => {
    user.getOrganisation().then(org => {
        console.log(user,org); // <------ Check the console
    });
});