What do you have to put in your migration files (and model files) to create a M:N association in sequelize? I suspect I specifically mean, what references need to be set, and what do the required keys in those objects mean, but I'm not 100% sure that's what I mean.
I'm trying to create a users<->rolls association, similar to what was asked here.
Initially, I added references to my user and roll migration files. Then I realised that the userrolls join table migration file probably needed references the other direction as well. I suspect I'm nearing every possible combination of settings, but somehow still haven't gotten this to work :)
The associations from my model files:
role.js
static associate(models) {
models.Role.belongsToMany(models.User, {
through: models.UserRole
})
}
user.js
static associate(models) {
models.User.belongsToMany(models.Role, {
through: models.UserRole
})
}
userrole.js
static associate(models) {
UserRole.belongsTo(models.User, { foreignKey: 'id' });
UserRole.belongsTo(models.Role, { foreignKey: 'id' });
// UserRole.belongsTo(models.User, { foreignKey: 'userId' }); // <- *
// UserRole.belongsTo(models.Role, { foreignKey: 'roleId' });
}
* Here, and below, commented out lines are various things I've tried, though they probably don't account for all attempts.
My migration files:
create-user
'use strict';
module.exports = {
async up(queryInterface, Sequelize) {
await queryInterface.createTable('Users', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER,
/*
references: {
model: 'UserRoles',
key: 'id',
as: 'userId'
}
*/
references: {
model: 'UserRoles',
key: 'userId'
}
},
username: {
type: Sequelize.STRING
},
...
});
},
async down(queryInterface, Sequelize) {
await queryInterface.dropTable('Users');
}
};
The create-role.js file is congruent to the above.
create-user-role.js
'use strict';
module.exports = {
async up(queryInterface, Sequelize) {
await queryInterface.createTable('UserRoles', {
/*
userId: {
// allowNull: false,
primaryKey: true,
type: Sequelize.INTEGER,
references: {
model: 'Users',
key: 'id'
}
},
*/
userId: {
// allowNull: false,
primaryKey: true,
type: Sequelize.INTEGER,
references: {
model: 'Users',
key: 'id',
as: 'userId'
}
},
/*
roleId: {
// allowNull: false,
primaryKey: true,
type: Sequelize.INTEGER,
references: {
model: 'Roles',
key: 'id'
}
},
*/
roleId: {
// allowNull: false,
primaryKey: true,
type: Sequelize.INTEGER,
references: {
model: 'Roles',
key: 'id',
as: 'roleId'
}
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
}
});
},
async down(queryInterface, Sequelize) {
await queryInterface.dropTable('UserRoles');
}
};
I had/have this working, using the standard, documented method where you use sequelize.define and rely on sync to create the tables, but it seems I can't find the right incantation to get it to work with the seqeulize-cli migration method that autoloads all the models. To my (rather untrained) eye, the tables that are generated look right:
sqlite> .schema
CREATE TABLE `Users` (`id` INTEGER PRIMARY KEY AUTOINCREMENT REFERENCES `UserRoles` (`userId`), `username` VARCHAR(255), `email` VARCHAR(255), `password` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL);
CREATE TABLE `Roles` (`id` INTEGER PRIMARY KEY AUTOINCREMENT REFERENCES `UserRoles` (`roleId`), `name` VARCHAR(255), `createdAt` DATETIME, `updatedAt` DATETIME NOT NULL);
CREATE TABLE `UserRoles` (`userId` INTEGER NOT NULL REFERENCES `Users` (`id`), `roleId` INTEGER NOT NULL REFERENCES `Roles` (`id`), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`userId`, `roleId`));
At least that last entry looks analogous to the PostgreSQL example they give in the docs, but they don't seem to provide examples of the main tables.
But, no matter what I do, if I go to insert a user, it complains about a foreign key mismatch:
Executing (default): INSERT INTO `Users` (`id`,`username`,`email`,`password`,`createdAt`,`updatedAt`) VALUES (NULL,$1,$2,$3,$4,$5);
SQLITE_ERROR: foreign key mismatch - "Users" referencing "UserRoles"
I'd take a simple "change this in your code" answer, but, as I mentioned in the beginning, I suspect this is a fundamental misunderstand of what needs to be set and why, so ... some explanation of what I'm supposed to be doing would be greatly appreciated.
If you need M:N relationship between User and Role using UserRole then references option should be used only in UserRole because only it has foreign keys to both User and Role.
Also you should indicate the same foreign keys in belongsToMany as you indicated in belongsTo:
role.js
models.Role.belongsToMany(models.User, {
through: models.UserRole,
foreignKey: 'roleId',
otherKey: 'userId'
})
user.js
models.User.belongsToMany(models.Role, {
through: models.UserRole,
foreignKey: 'userId',
otherKey: 'roleId'
})
userrole.js
static associate(models) {
UserRole.belongsTo(models.User, { foreignKey: 'userId' });
UserRole.belongsTo(models.Role, { foreignKey: 'roleId' });
}
createuser.js
'use strict';
module.exports = {
async up(queryInterface, Sequelize) {
await queryInterface.createTable('Users', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER,
},
username: {
type: Sequelize.STRING
},
...
});
},
async down(queryInterface, Sequelize) {
await queryInterface.dropTable('Users');
}
};
A primary key should be a separate single column as it is in User model.
create-user-role.js
'use strict';
module.exports = {
async up(queryInterface, Sequelize) {
await queryInterface.createTable('UserRoles', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER,
},
userId: {
type: Sequelize.INTEGER,
references: {
model: 'Users',
key: 'id'
}
},
roleId: {
type: Sequelize.INTEGER,
references: {
model: 'Roles',
key: 'id'
}
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
}
});
},
async down(queryInterface, Sequelize) {
await queryInterface.dropTable('UserRoles');
}
};