How do I reference an association when creating a row in sequelize without assuming the foreign key column name?

Question

I have the following code:

#!/usr/bin/env node
'use strict';
var Sequelize = require('sequelize');
var sequelize = new Sequelize('sqlite:file.sqlite');

var User = sequelize.define('User', { email: Sequelize.STRING});
var Thing = sequelize.define('Thing', { name: Sequelize.STRING});
Thing.belongsTo(User);

sequelize.sync({force: true}).then(function () {
  return User.create({email: 'asdf@example.org'});
}).then(function (user) {
  return Thing.create({
    name: 'A thing',
    User: user
  }, {
    include: [User]
  });
}).then(function (thing) {
  return Thing.findOne({where: {id: thing.id}, include: [User]});
}).then(function (thing) {
  console.log(JSON.stringify(thing));
});

I get the following output:

ohnobinki@gibby ~/public_html/turbocase1 $ ./sqltest.js
Executing (default): INSERT INTO `Users` (`id`,`email`,`updatedAt`,`createdAt`) VALUES (NULL,'asdf@example.org','2015-12-03 06:11:36.904 +00:00','2015-12-03 06:11:36.904 +00:00');
Executing (default): INSERT INTO `Users` (`id`,`email`,`createdAt`,`updatedAt`) VALUES (1,'asdf@example.org','2015-12-03 06:11:36.904 +00:00','2015-12-03 06:11:37.022 +00:00');
Unhandled rejection SequelizeUniqueConstraintError: Validation error
    at Query.formatError (/home/ohnobinki/public_html/turbocase1/node_modules/sequelize/lib/dialects/sqlite/query.js:231:14)
    at Statement.<anonymous> (/home/ohnobinki/public_html/turbocase1/node_modules/sequelize/lib/dialects/sqlite/query.js:47:29)
    at Statement.replacement (/home/ohnobinki/public_html/turbocase1/node_modules/sqlite3/lib/trace.js:20:31)

It seems that specifying {include: [User]} instructs Sequelize to create a new User instance matching the contents of user. That is not my goal. In fact, I find it hard to believe that such behaviour would ever be useful—I at least have no use for it. I want to be able to have a long-living User record in the database and at arbitrary times create new Things which refer to the User. In my shown example, I wait for the User to be created, but in actual code it would likely have been freshly loaded through User.findOne().

I have seen other questions and answers say that I have to explicitly specify the implicitly-created UserId column in my Thing.create() call. When Sequelize provides an API like Thing.belongsTo(User), I shouldn’t have to be aware of the fact that a Thing.UserId field is created. So what is the clean API-respecting way of creating a new Thing which refers to a particular User without having to guess the name of the UserId field? When I load a Thing and specify {include: [User]}, I access the loaded user through the thing.User property. I don’t think I’m supposed to know about or try to access a thing.UserId field. In my Thing.belongsTo(User) call, I never specify UserId, I just treat that like an implementation detail I shouldn’t care about. How can I continue to avoid caring about that implementation detail when creating a Thing?

The Thing.create() call that works but looks wrong to me:

Thing.create({
  name: 'A thing',
  UserId: user.id
});

Answer 1

Option 1 - risks DB inconsistency

Sequelize dynamically generates methods for setting associations on instances, e.g. thing.setUser(user);. In your use case:

sequelize.sync({force: true})
.then(function () {
  return Promise.all([
    User.create({email: 'asdf@example.org'}),
    Thing.create({name: 'A thing'})  
  ]);
})
.spread(function(user, thing) {
  return thing.setUser(user);
})
.then(function(thing) {
  console.log(JSON.stringify(thing));
});

Option 2 - does not work/buggy

It isn't documented, but from a code dive I think the following should work. It doesn't but that seems to be because of a couple of bugs:

// ...
.then(function () {
  return models.User.create({email: 'asdf@example.org'});
})
.then(function(user) {
  // Fails with SequelizeUniqueConstraintError - the User instance inherits isNewRecord from the Thing instance, but it has already been saved
  return models.Thing.create({
    name: 'thingthing',
    User: user
  }, {
    include: [{
      model: models.User
    }],
    fields: ['name'] // seems nec to specify all non-included fields because of line 277 in instance.js - another bug?
  });
})

Replacing models.User.create with models.User.build doesn't work because the built but not saved instance's primary key is null. Instance#_setInclude ignores the instance if its primary key is null.

Option 3

Wrapping the Thing's create in a transaction prevents an inconsistent state.

sq.sync({ force: true })
.then(models.User.create.bind(models.User, { email: 'asdf@example.org' }))
.then(function(user) {
  return sq.transaction(function(tr) {
    return models.Thing.create({name: 'A thing'})
    .then(function(thing) { return thing.setUser(user); });
  });
})
.then(print_result.bind(null, 'Thing with User...'))
.catch(swallow_rejected_promise.bind(null, 'main promise chain'))
.finally(function() {
  return sq.close();
});

I have uploaded a script demo'ing option 2 and option 3 here

Answer 2

Tested on sequelize@6.5.1 sqlite3@5.0.2 I can use User.associations.Comments.foreignKey as in:

const Comment = sequelize.define('Comment', {
  body: { type: DataTypes.STRING },
});
const User = sequelize.define('User', {
  name: { type: DataTypes.STRING },
});
User.hasMany(Comment)
Comment.belongsTo(User)
console.dir(User);
await sequelize.sync({force: true});
const u0 = await User.create({name: 'u0'})
const u1 = await User.create({name: 'u1'})
await Comment.create({body: 'u0c0', [User.associations.Comments.foreignKey]: u0.id});

The association is also returned during creation, so you could also:

const Comments = User.hasMany(Comment)
await Comment.create({body: 'u0c0', [Comments.foreignKey]: u0.id});

and on many-to-many through tables you get foreignKey and otherKey for the second foreign key.

User.associations.Comments.foreignKey contains the foreignKey UserId.

Or analogously with aliases:

User.hasMany(Post, {as: 'authoredPosts', foreignKey: 'authorId'});
Post.belongsTo(User, {as: 'author', foreignKey: 'authorId'});

User.hasMany(Post, {as: 'reviewedPosts', foreignKey: 'reviewerId'});
Post.belongsTo(User, {as: 'reviewer', foreignKey: 'reviewerId'});
await sequelize.sync({force: true});

// Create data.
const users = await User.bulkCreate([
  {name: 'user0'},
  {name: 'user1'},
])

const posts = await Post.bulkCreate([
  {body: 'body00', authorId: users[0].id, reviewerId: users[0].id},
  {body: 'body01', [User.associations.authoredPosts.foreignKey]: users[0].id, 
                   [User.associations.reviewedPosts.foreignKey]: users[1].id},
])

But that syntax is so long that I'm tempted to just hardcode the keys everywhere.