JOIN Table Migrations in Sequelize

Understanding JOIN Tables

Imagine you're organizing a university course system. Students can take multiple courses, and courses can have multiple students. The JOIN table is like the course registration system that connects them:

// First, create the main tables
// Students table migration
module.exports = {
    up: async (queryInterface, Sequelize) => {
        await queryInterface.createTable('Students', {
            id: {
                allowNull: false,
                autoIncrement: true,
                primaryKey: true,
                type: Sequelize.INTEGER
            },
            name: {
                type: Sequelize.STRING,
                allowNull: false
            },
            email: {
                type: Sequelize.STRING,
                unique: true
            },
            createdAt: {
                allowNull: false,
                type: Sequelize.DATE,
                defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
            }
        });
    }
};

// Courses table migration
module.exports = {
    up: async (queryInterface, Sequelize) => {
        await queryInterface.createTable('Courses', {
            id: {
                allowNull: false,
                autoIncrement: true,
                primaryKey: true,
                type: Sequelize.INTEGER
            },
            title: {
                type: Sequelize.STRING,
                allowNull: false
            },
            code: {
                type: Sequelize.STRING,
                unique: true
            },
            createdAt: {
                allowNull: false,
                type: Sequelize.DATE,
                defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
            }
        });
    }
};

// Now, create the JOIN table
module.exports = {
    up: async (queryInterface, Sequelize) => {
        await queryInterface.createTable('StudentCourses', {
            id: {
                allowNull: false,
                autoIncrement: true,
                primaryKey: true,
                type: Sequelize.INTEGER
            },
            studentId: {
                type: Sequelize.INTEGER,
                allowNull: false,
                references: {
                    model: 'Students',
                    key: 'id'
                },
                onDelete: 'CASCADE'
            },
            courseId: {
                type: Sequelize.INTEGER,
                allowNull: false,
                references: {
                    model: 'Courses',
                    key: 'id'
                },
                onDelete: 'CASCADE'
            },
            enrollmentDate: {
                type: Sequelize.DATE,
                defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
            }
        });
    }
};
                

Real-World Examples

E-commerce Product Tags System

// Products table
module.exports = {
    up: async (queryInterface, Sequelize) => {
        await queryInterface.createTable('Products', {
            id: {
                allowNull: false,
                autoIncrement: true,
                primaryKey: true,
                type: Sequelize.INTEGER
            },
            name: {
                type: Sequelize.STRING,
                allowNull: false
            },
            price: {
                type: Sequelize.DECIMAL(10, 2),
                allowNull: false
            }
        });

        await queryInterface.createTable('Tags', {
            id: {
                allowNull: false,
                autoIncrement: true,
                primaryKey: true,
                type: Sequelize.INTEGER
            },
            name: {
                type: Sequelize.STRING,
                allowNull: false,
                unique: true
            }
        });

        // JOIN table
        await queryInterface.createTable('ProductTags', {
            productId: {
                type: Sequelize.INTEGER,
                references: {
                    model: 'Products',
                    key: 'id'
                },
                onDelete: 'CASCADE'
            },
            tagId: {
                type: Sequelize.INTEGER,
                references: {
                    model: 'Tags',
                    key: 'id'
                },
                onDelete: 'CASCADE'
            },
            createdAt: {
                allowNull: false,
                type: Sequelize.DATE,
                defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
            }
        });
    }
};
                

Social Media Skills and Users

module.exports = {
    up: async (queryInterface, Sequelize) => {
        // Skills table
        await queryInterface.createTable('Skills', {
            id: {
                allowNull: false,
                autoIncrement: true,
                primaryKey: true,
                type: Sequelize.INTEGER
            },
            name: {
                type: Sequelize.STRING,
                allowNull: false,
                unique: true
            },
            category: {
                type: Sequelize.STRING
            }
        });

        // UserSkills JOIN table
        await queryInterface.createTable('UserSkills', {
            userId: {
                type: Sequelize.INTEGER,
                references: {
                    model: 'Users',
                    key: 'id'
                },
                onDelete: 'CASCADE'
            },
            skillId: {
                type: Sequelize.INTEGER,
                references: {
                    model: 'Skills',
                    key: 'id'
                },
                onDelete: 'CASCADE'
            },
            proficiencyLevel: {
                type: Sequelize.ENUM('beginner', 'intermediate', 'expert'),
                defaultValue: 'beginner'
            },
            endorsements: {
                type: Sequelize.INTEGER,
                defaultValue: 0
            }
        });
    }
};
                

JOIN Table with Additional Attributes

// Event Registration System
module.exports = {
    up: async (queryInterface, Sequelize) => {
        await queryInterface.createTable('EventParticipants', {
            eventId: {
                type: Sequelize.INTEGER,
                references: {
                    model: 'Events',
                    key: 'id'
                },
                onDelete: 'CASCADE'
            },
            participantId: {
                type: Sequelize.INTEGER,
                references: {
                    model: 'Users',
                    key: 'id'
                },
                onDelete: 'CASCADE'
            },
            registrationDate: {
                type: Sequelize.DATE,
                defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
            },
            status: {
                type: Sequelize.ENUM('pending', 'confirmed', 'cancelled'),
                defaultValue: 'pending'
            },
            paymentStatus: {
                type: Sequelize.BOOLEAN,
                defaultValue: false
            },
            notes: {
                type: Sequelize.TEXT
            }
        });
    }
};
                

Composite Primary Keys in JOIN Tables

module.exports = {
    up: async (queryInterface, Sequelize) => {
        await queryInterface.createTable('TeamMembers', {
            teamId: {
                type: Sequelize.INTEGER,
                primaryKey: true,
                references: {
                    model: 'Teams',
                    key: 'id'
                },
                onDelete: 'CASCADE'
            },
            userId: {
                type: Sequelize.INTEGER,
                primaryKey: true,
                references: {
                    model: 'Users',
                    key: 'id'
                },
                onDelete: 'CASCADE'
            },
            role: {
                type: Sequelize.STRING,
                allowNull: false
            }
        });
    }
};
                

Best Practices

• Meaningful Names: Use clear, descriptive names for JOIN tables
• Consider Additional Fields: Add relevant metadata to JOIN tables
• Use Appropriate Constraints: Implement proper foreign key constraints
• Handle Deletions: Choose appropriate onDelete behaviors
• Index Properly: Add indexes for frequently queried columns

Common Pitfalls to Avoid

• Forgetting to add foreign key constraints
• Missing indexes on frequently joined columns
• Not handling deletion cases properly
• Overlooking unique constraints when needed
• Incorrect migration order

Advanced Topics to Explore

• Self-referential many-to-many relationships
• Polymorphic associations
• Temporal tables for historical data
• Optimization strategies
• Complex migration patterns