Bootcamp
Search…
4.1.8: Multiple Foreign Key Columns Referencing Same Table

Introduction

Sometimes we have a table whose primary key is referenced more than once in another table. How do we properly access those keys? We'll walk through this common pattern of messages, where one primary key of the users table is used twice in the messages table, once for sender and once for receiver.

Setup Packages and Folders, Configure DB

Set up Sequelize with a new Node application and configure the DB in the same way we did in Module 4.1.1: Intro to Sequelize. Update config.js to use a new DB name, messages_development to distinguish the DB from other modules. Stop after creating the DB and follow the steps below to create the example.

Migrations

Create a migration for the messages table
npx sequelize migration:generate --name create-messages-table
module.exports = {
up: async (queryInterface, Sequelize) => {
await queryInterface.createTable('users', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER,
},
name: {
type: Sequelize.TEXT,
allowNull: false,
},
created_at: {
allowNull: false,
type: Sequelize.DATE,
},
updated_at: {
allowNull: false,
type: Sequelize.DATE,
},
});
​
await queryInterface.createTable('messages', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER,
},
receiver_id: {
type: Sequelize.INTEGER,
references: {
model: 'users',
key: 'id',
},
},
sender_id: {
type: Sequelize.INTEGER,
references: {
model: 'users',
key: 'id',
},
},
message: {
type: Sequelize.TEXT,
},
created_at: {
allowNull: false,
type: Sequelize.DATE,
},
updated_at: {
allowNull: false,
type: Sequelize.DATE,
},
});
},
down: (queryInterface, Sequelize) => {
queryInterface.dropTable('messages');
queryInterface.dropTable('users');
},
};

Models

models/message.mjs

export default function initMessageModel(sequelize, DataTypes) {
return sequelize.define(
'message',
{
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: DataTypes.INTEGER,
},
receiverId: {
type: DataTypes.INTEGER,
references: {
model: 'users',
key: 'id',
},
},
senderId: {
type: DataTypes.INTEGER,
references: {
model: 'users',
key: 'id',
},
},
message: {
type: DataTypes.TEXT,
},
createdAt: {
allowNull: false,
type: DataTypes.DATE,
},
updatedAt: {
allowNull: false,
type: DataTypes.DATE,
},
},
{
// The underscored option makes Sequelize reference snake_case names in the DB.
underscored: true,
}
);
}

models/user.mjs

export default function initUserModel(sequelize, DataTypes) {
return sequelize.define(
'user',
{
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: DataTypes.INTEGER,
},
name: {
allowNull: false,
type: DataTypes.STRING,
},
createdAt: {
allowNull: false,
type: DataTypes.DATE,
},
updatedAt: {
allowNull: false,
type: DataTypes.DATE,
},
},
{
// The underscored option makes Sequelize reference snake_case names in the DB.
underscored: true,
}
);
}

Seeders

module.exports = {
up: async (queryInterface) => {
// Define category data
const users = [
{
name: 'kai',
created_at: new Date(),
updated_at: new Date(),
},
{
name: 'jimmy',
created_at: new Date(),
updated_at: new Date(),
},
];
​
queryInterface.bulkInsert('users', users);
},
​
down: async (queryInterface) => {
await queryInterface.bulkDelete('users', null);
},
};

Relationships

models/index.mjs

import sequelizePackage from 'sequelize';
import allConfig from '../config/config.js';
​
import initUserModel from './user.mjs';
import initMessageModel from './message.mjs';
​
const { Sequelize } = sequelizePackage;
const env = process.env.NODE_ENV || 'development';
const config = allConfig[env];
const db = {};
​
const sequelize = new Sequelize(
config.database,
config.username,
config.password,
config
);
​
db.User = initUserModel(sequelize, Sequelize.DataTypes);
db.Message = initMessageModel(sequelize, Sequelize.DataTypes);
​
// creates a method in the
// user object with getSentMessages, etc.
// allows the use of include with sentMessages
db.User.hasMany(db.Message, {
as: 'sentMessages',
foreignKey: 'sender_id',
});
​
db.User.hasMany(db.Message, {
as: 'receivedMessages',
foreignKey: 'receiver_id',
});
​
// creates a method in the
// message object that has a user - the sender of the message
db.Message.belongsTo(db.User, {
as: 'sender',
foreignKey: 'sender_id',
});
​
db.Message.belongsTo(db.User, {
as: 'receiver',
foreignKey: 'receiver_id',
});
​
db.sequelize = sequelize;
db.Sequelize = Sequelize;
​
export default db;

Using the Models

message.mjs

import sequelizePackage from 'sequelize';
import db from './models/index.mjs';
​
const { Op } = sequelizePackage;
​
const getModels = async () => {
try {
const receiverName = process.argv[3];
const senderName = process.argv[2];
​
// get all the users in a single query
const users = await db.User.findAll({
where: {
[Op.or]: [{ name: receiverName }, { name: senderName }],
},
});
​
// separate out the instances into sender and receiver
const senderUser = users.find((user) => user.name === senderName);
const receiverUser = users.find((user) => user.name === receiverName);
​
console.log('sender');
console.log(senderUser);
console.log('recpient');
console.log(receiverUser);
​
const message = await db.Message.create({
receiverId: receiverUser.id,
senderId: senderUser.id,
message: process.argv[4],
});
​
console.log(message);
} catch (error) {
console.log(error);
}
};
​
getModels();

Send a message

node message.mjs kai jimmy "hey! hows it going"

Retrieving Messages

Note how we use eager loading so that inside each message we have a formatted key to get access to the user name.

Outbox

outbox.mjs

import db from './models/index.mjs';
​
const getModels = async () => {
try {
const user = await db.User.findOne({
where: {
name: process.argv[2],
},
include: {
as: 'sentMessages', // say which group of messages we are getting
model: db.Message,
include: {
as: 'receiver', // get the receiver of the message
model: db.User,
},
},
});
​
console.log(`${user.name}'s outbox`);
// print out each message
user.sentMessages.forEach(async (msg) => {
// without the nested include above, we can
// get the recipient (name) like this:
// const sender = await msg.getSender();
// console.log(JSON.stringify(msg));
console.log(`to: ${msg.receiver.name}`);
console.log(msg.message);
console.log('-----');
});
} catch (error) {
console.log(error);
}
};
​
getModels();

Inbox

inbox.mjs

import db from './models/index.mjs';
​
const getModels = async () => {
try {
const user = await db.User.findOne({
where: {
name: process.argv[2],
},
include: {
as: 'receivedMessages', // say which group of messages we are getting
model: db.Message,
include: {
as: 'sender', // get the sender of the message
model: db.User,
},
},
});
​
console.log(`${user.name}'s inbox`);
// print out each message
user.receivedMessages.forEach(async (msg) => {
// without the nested include above we can get the
// sender (name) like this:
// const sender = await msg.getSender();
// console.log(JSON.stringify(msg));
console.log(`from: ${msg.sender.name}`);
console.log(msg.message);
console.log('-----');
});
} catch (error) {
console.log(error);
}
};
​
getModels();
Copy link
On this page
Introduction
Setup Packages and Folders, Configure DB
Migrations
Models
Seeders
Relationships
Using the Models
Retrieving Messages
Outbox
Inbox