Foreign Key Migrations in Sequelize

Understanding Foreign Keys

Imagine you're organizing a library where books need to be connected to their authors. The foreign key is like the library card that connects each book to its author:

// Basic foreign key migration example
module.exports = {
    up: async (queryInterface, Sequelize) => {
        await queryInterface.createTable('Books', {
            id: {
                allowNull: false,
                autoIncrement: true,
                primaryKey: true,
                type: Sequelize.INTEGER
            },
            title: {
                type: Sequelize.STRING,
                allowNull: false
            },
            authorId: {
                type: Sequelize.INTEGER,
                references: {
                    model: 'Authors',
                    key: 'id'
                },
                onDelete: 'CASCADE'
            }
        });
    },
    down: async (queryInterface, Sequelize) => {
        await queryInterface.dropTable('Books');
    }
};
                

Real-World Examples

E-commerce System

// Order and OrderItems relationship
module.exports = {
    up: async (queryInterface, Sequelize) => {
        await queryInterface.createTable('Orders', {
            id: {
                allowNull: false,
                autoIncrement: true,
                primaryKey: true,
                type: Sequelize.INTEGER
            },
            userId: {
                type: Sequelize.INTEGER,
                allowNull: false,
                references: {
                    model: 'Users',
                    key: 'id'
                },
                onDelete: 'RESTRICT'
            },
            status: {
                type: Sequelize.ENUM('pending', 'processing', 'shipped', 'delivered'),
                defaultValue: 'pending'
            },
            createdAt: {
                allowNull: false,
                type: Sequelize.DATE,
                defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
            }
        });

        await queryInterface.createTable('OrderItems', {
            id: {
                allowNull: false,
                autoIncrement: true,
                primaryKey: true,
                type: Sequelize.INTEGER
            },
            orderId: {
                type: Sequelize.INTEGER,
                allowNull: false,
                references: {
                    model: 'Orders',
                    key: 'id'
                },
                onDelete: 'CASCADE'
            },
            productId: {
                type: Sequelize.INTEGER,
                allowNull: false,
                references: {
                    model: 'Products',
                    key: 'id'
                },
                onDelete: 'RESTRICT'
            },
            quantity: {
                type: Sequelize.INTEGER,
                allowNull: false,
                defaultValue: 1
            }
        });
    },
    down: async (queryInterface, Sequelize) => {
        await queryInterface.dropTable('OrderItems');
        await queryInterface.dropTable('Orders');
    }
};
                

Social Media Platform

// Posts and Comments relationship
module.exports = {
    up: async (queryInterface, Sequelize) => {
        await queryInterface.createTable('Posts', {
            id: {
                allowNull: false,
                autoIncrement: true,
                primaryKey: true,
                type: Sequelize.INTEGER
            },
            userId: {
                type: Sequelize.INTEGER,
                allowNull: false,
                references: {
                    model: 'Users',
                    key: 'id'
                },
                onDelete: 'CASCADE'
            },
            content: {
                type: Sequelize.TEXT,
                allowNull: false
            }
        });

        await queryInterface.createTable('Comments', {
            id: {
                allowNull: false,
                autoIncrement: true,
                primaryKey: true,
                type: Sequelize.INTEGER
            },
            postId: {
                type: Sequelize.INTEGER,
                allowNull: false,
                references: {
                    model: 'Posts',
                    key: 'id'
                },
                onDelete: 'CASCADE'
            },
            userId: {
                type: Sequelize.INTEGER,
                allowNull: false,
                references: {
                    model: 'Users',
                    key: 'id'
                },
                onDelete: 'CASCADE'
            },
            content: {
                type: Sequelize.TEXT,
                allowNull: false
            }
        });
    }
};
                

Understanding onDelete Options

Think of these options like setting rules for what happens to children's toys when their parent moves away:

// Different onDelete behaviors
{
    // CASCADE: Delete child records when parent is deleted
    categoryId: {
        type: Sequelize.INTEGER,
        references: {
            model: 'Categories',
            key: 'id'
        },
        onDelete: 'CASCADE'
    },

    // RESTRICT: Prevent deletion of parent if child exists
    departmentId: {
        type: Sequelize.INTEGER,
        references: {
            model: 'Departments',
            key: 'id'
        },
        onDelete: 'RESTRICT'
    },

    // SET NULL: Set foreign key to null when parent is deleted
    supervisorId: {
        type: Sequelize.INTEGER,
        references: {
            model: 'Employees',
            key: 'id'
        },
        onDelete: 'SET NULL'
    }
}
                

Adding Foreign Keys to Existing Tables

// Adding a foreign key to an existing table
module.exports = {
    up: async (queryInterface, Sequelize) => {
        await queryInterface.addColumn('Products', 'categoryId', {
            type: Sequelize.INTEGER,
            references: {
                model: 'Categories',
                key: 'id'
            },
            onDelete: 'SET NULL'
        });
    },
    down: async (queryInterface, Sequelize) => {
        await queryInterface.removeColumn('Products', 'categoryId');
    }
};
                

Many-to-Many Relationships

// Creating a junction table for many-to-many relationship
module.exports = {
    up: async (queryInterface, Sequelize) => {
        await queryInterface.createTable('UserRoles', {
            userId: {
                type: Sequelize.INTEGER,
                primaryKey: true,
                references: {
                    model: 'Users',
                    key: 'id'
                },
                onDelete: 'CASCADE'
            },
            roleId: {
                type: Sequelize.INTEGER,
                primaryKey: true,
                references: {
                    model: 'Roles',
                    key: 'id'
                },
                onDelete: 'CASCADE'
            },
            createdAt: {
                allowNull: false,
                type: Sequelize.DATE,
                defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
            }
        });
    },
    down: async (queryInterface, Sequelize) => {
        await queryInterface.dropTable('UserRoles');
    }
};
                

Best Practices

• Choose Appropriate onDelete Behavior: Consider the implications of parent record deletion
• Use Meaningful Column Names: Make foreign key names clear and consistent
• Include Proper Indexes: Add indexes on foreign key columns
• Handle Migrations Carefully: Consider the order of table creation and deletion
• Document Relationships: Maintain clear documentation of table relationships

Common Pitfalls to Avoid

• Forgetting to handle migration rollbacks
• Incorrect table or column references
• Missing or incorrect onDelete behaviors
• Not considering the impact on existing data
• Circular dependencies in migrations

Advanced Topics to Explore

• Composite foreign keys
• Self-referential relationships
• Polymorphic associations
• Migration strategies for production
• Performance considerations