Sequelize Many to Many - How to create a new record and update join table

Question

I'm building a simple database with node, express and sequelize. I have created my models, and sequelize created the tables in my database.

I have the models User and City, with a many to many relationship. Sequelize created the tables Users, Cities and a join table CitiesUsers: with UserId and CityId.

My question is when I create a new user how do I update that join table? The CityId property gets ignored on create.

   //Models use 
   //City.hasMany(User);
   //User.hasMany(City);

   var user = User.build({
      first_name: 'John',
      last_name: 'Doe',
      CityId: 5
    });

    user.save();

Answer 1

After digging further into the documentation, I believe I've found the answer.

When creating a many to many relationship sequelize creates get, set and add methods to each model.

From the docs assuming models User and Project with many to many: http://docs.sequelizejs.com/en/latest/docs/associations/#belongs-to-many-associations

This will add methods getUsers, setUsers, addUsers to Project, and getProjects, setProjects and addProject to User.

So in my case I did the following where "city" is a specific City model returned from City.find...

//user.setCities([city]);

models.User.find({ where: {first_name: 'john'} }).on('success', function(user) {
  models.City.find({where: {id: 10}}).on('success', function(city){
    user.setCities([city]);
  });      
});

Answer 2

You can create a new instance of the model used as the join table once both City and User models have been created.

const User = sequelize.define('user')
const City = sequelize.define('city')
const UserCity = sequelize.define('user_city')

User.belongsToMany(City, { through: UserCity })
City.belongsToMany(User, { through: UserCity })


const user = await User.create()
const city = await City.create()

const userCity = await UserCity.create({
  userId: user.userId,
  cityId: city.cityId,
})

Answer 3

Just to add on to the many excellent answers in this thread, I find generally that when I have one entity referencing another, I want to create the referenced entity if (and only if) it does not already exist. For this I like to use findOrCreate().

So imagine you were storing articles, and each article could have any number of tags. What you'd typically want to do is:

1. Iterate through all the desired tags, and check if they exist. Create them if they don't already exist.
2. Once all the tags have been found or created, create your article.
3. Once your article has been created, link it to the tags you looked up (or created) in step 1.

For me, this winds up looking like:

const { article, tags } = model.import("./model/article");

let tagging = [
  tags.findOrCreate({where: {title: "big"}}),
  tags.findOrCreate({where: {title: "small"}}),
  tags.findOrCreate({where: {title: "medium"}}),
  tags.findOrCreate({where: {title: "xsmall"}})
];

Promise.all(tagging).then((articleTags)=> {
  article.create({
    title: "Foo",
    body: "Bar"    
  }).then((articleInstance) => {
    articleInstance.setTags(articleTags.map((articleTag) => articleTag[0]));
  })
})

Answer 4

From The docs v3:

// Either by adding a property with the name of the join table model to the object, before creating the association
project.UserProjects = {
  status: 'active'
}
u.addProject(project)

// Or by providing a second argument when adding the association, containing the data that should go in the join table
u.addProject(project, { status: 'active' })


// When associating multiple objects, you can combine the two options above. In this case the second argument
// will be treated as a defaults object, that will be used if no data is provided
project1.UserProjects = {
    status: 'inactive'
}

u.setProjects([project1, project2], { status: 'active' })
// The code above will record inactive for project one, and active for project two in the join table