Understanding the Migration Flow
Imagine you're building a house using Lego blocks. The 'up' migration is like following the instruction manual to build something new, while the 'down' migration is like following those same instructions backward to take it apart. Each step in both directions needs to be precise and purposeful.
Think of migrations as a series of transformations to your database, where:
'Up' migrations are like adding new rooms to your house - they build new structures and add new features.
'Down' migrations are like having an "undo" button - they let you safely remove those changes if needed.
Together, they ensure you can move your database both forward and backward without losing or corrupting data.
Creating Your First Migration
Let's create a practical example by building a simple users table. We'll walk through each part of the process, understanding what each piece does and why it's important.
Basic User Table Migration
'use strict';
module.exports = {
up: async (queryInterface, Sequelize) => {
// The 'up' function creates our new table
await queryInterface.createTable('Users', {
// An ID to uniquely identify each user
id: {
type: Sequelize.INTEGER,
primaryKey: true,
autoIncrement: true,
allowNull: false
},
// The user's username
username: {
type: Sequelize.STRING(50),
allowNull: false,
unique: true
},
// The user's email address
email: {
type: Sequelize.STRING(255),
allowNull: false,
unique: true
},
// When the record was created
createdAt: {
type: Sequelize.DATE,
allowNull: false,
defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
},
// When the record was last updated
updatedAt: {
type: Sequelize.DATE,
allowNull: false,
defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
}
});
},
down: async (queryInterface, Sequelize) => {
// The 'down' function removes our table
await queryInterface.dropTable('Users');
}
};
Let's break down what's happening in this migration:
The 'up' function creates a new table with specific columns and constraints. Each field is carefully defined with its type and rules.
The 'down' function provides a way to completely remove the table if we need to reverse this change.
Understanding the QueryInterface
The queryInterface is like your construction foreman - it knows how to interpret your plans and turn them into actual database structures. Let's explore its capabilities with more complex examples.
Advanced Table Creation
module.exports = {
up: async (queryInterface, Sequelize) => {
// First, create the main table
await queryInterface.createTable('Products', {
id: {
type: Sequelize.INTEGER,
primaryKey: true,
autoIncrement: true
},
name: {
type: Sequelize.STRING(100),
allowNull: false
},
price: {
type: Sequelize.DECIMAL(10, 2),
allowNull: false,
validate: {
min: 0.01
}
}
});
// Then, add an index for faster searches
await queryInterface.addIndex('Products', ['name'], {
name: 'products_name_idx'
});
// Finally, add a unique constraint
await queryInterface.addConstraint('Products', {
fields: ['name'],
type: 'unique',
name: 'unique_product_name'
});
},
down: async (queryInterface, Sequelize) => {
// Remove everything in reverse order
await queryInterface.removeConstraint('Products', 'unique_product_name');
await queryInterface.removeIndex('Products', 'products_name_idx');
await queryInterface.dropTable('Products');
}
};
Notice how the 'down' function removes everything in the reverse order of creation. This is like dismantling a building - you start from the top and work your way down.
Running Migrations
Now that we understand how to write migrations, let's learn how to execute them. Think of this as moving from the blueprint stage to actual construction.
Basic Migration Commands
# Run all pending migrations
npx dotenv sequelize db:migrate
# Undo the most recent migration
npx dotenv sequelize db:migrate:undo
# Undo all migrations
npx dotenv sequelize db:migrate:undo:all
# Check migration status
npx dotenv sequelize db:migrate:status
Each of these commands serves a specific purpose:
'db:migrate' applies all pending changes, like following a construction plan step by step
'migrate:undo' reverses the last change, like undoing the last step in construction
'migrate:status' shows what's been built and what's still pending
Best Practices and Common Patterns
Making Migrations Reversible
Always ensure your migrations can be completely reversed. Here's an example of a well-structured migration:
module.exports = {
up: async (queryInterface, Sequelize) => {
// Add a new column
await queryInterface.addColumn('Users', 'phoneNumber', {
type: Sequelize.STRING(15),
allowNull: true
});
// Add an index for the new column
await queryInterface.addIndex('Users', ['phoneNumber'], {
name: 'users_phone_idx'
});
},
down: async (queryInterface, Sequelize) => {
// Remove in reverse order
await queryInterface.removeIndex('Users', 'users_phone_idx');
await queryInterface.removeColumn('Users', 'phoneNumber');
}
};
Handling Complex Changes
For more complex changes, break them into multiple migrations:
// First migration: Add new column
module.exports = {
up: async (queryInterface, Sequelize) => {
await queryInterface.addColumn('Users', 'fullName', {
type: Sequelize.STRING,
allowNull: true
});
},
down: async (queryInterface, Sequelize) => {
await queryInterface.removeColumn('Users', 'fullName');
}
};
// Second migration: Copy data from existing columns
module.exports = {
up: async (queryInterface, Sequelize) => {
await queryInterface.sequelize.query(`
UPDATE Users
SET fullName = CONCAT(firstName, ' ', lastName)
`);
},
down: async (queryInterface, Sequelize) => {
await queryInterface.sequelize.query(`
UPDATE Users
SET fullName = NULL
`);
}
};
Advanced Migration Patterns
Using Transactions
For operations that need to be atomic (all or nothing), use transactions:
module.exports = {
up: async (queryInterface, Sequelize) => {
const transaction = await queryInterface.sequelize.transaction();
try {
await queryInterface.createTable('Categories', {
id: {
type: Sequelize.INTEGER,
primaryKey: true,
autoIncrement: true
},
name: {
type: Sequelize.STRING,
allowNull: false
}
}, { transaction });
await queryInterface.addColumn('Products', 'categoryId', {
type: Sequelize.INTEGER,
references: {
model: 'Categories',
key: 'id'
}
}, { transaction });
await transaction.commit();
} catch (error) {
await transaction.rollback();
throw error;
}
},
down: async (queryInterface, Sequelize) => {
const transaction = await queryInterface.sequelize.transaction();
try {
await queryInterface.removeColumn('Products', 'categoryId', { transaction });
await queryInterface.dropTable('Categories', { transaction });
await transaction.commit();
} catch (error) {
await transaction.rollback();
throw error;
}
}
};