Understanding Data Seeding
Data seeding is like planting a garden - you're creating the initial growth that will help your application flourish. Just as a garden needs certain basic plants to get started, your application needs certain basic data to function properly and be tested effectively.
Creating Effective Seed Files
Let's explore how to create seed files that populate your database with initial data. Think of seed files as recipes - they contain exact instructions for creating the data your application needs:
Basic Seed File Structure
// seeders/YYYYMMDDHHMMSS-demo-users.js
'use strict';
module.exports = {
up: async (queryInterface, Sequelize) => {
return queryInterface.bulkInsert('Users', [
{
firstName: 'John',
lastName: 'Doe',
email: 'john@example.com',
createdAt: new Date(),
updatedAt: new Date()
},
{
firstName: 'Jane',
lastName: 'Smith',
email: 'jane@example.com',
createdAt: new Date(),
updatedAt: new Date()
}
]);
},
down: async (queryInterface, Sequelize) => {
return queryInterface.bulkDelete('Users', null, {});
}
};
Advanced Seeding with Relationships
// seeders/YYYYMMDDHHMMSS-demo-products.js
'use strict';
module.exports = {
up: async (queryInterface, Sequelize) => {
// First, insert categories
const categories = await queryInterface.bulkInsert(
'Categories',
[
{
name: 'Electronics',
createdAt: new Date(),
updatedAt: new Date()
},
{
name: 'Books',
createdAt: new Date(),
updatedAt: new Date()
}
],
{ returning: true }
);
// Then, use category IDs to insert products
const products = [];
const [electronics, books] = categories;
// Add electronic products
products.push({
name: 'Laptop',
price: 999.99,
categoryId: electronics.id,
createdAt: new Date(),
updatedAt: new Date()
});
// Add books
products.push({
name: 'Programming Guide',
price: 29.99,
categoryId: books.id,
createdAt: new Date(),
updatedAt: new Date()
});
return queryInterface.bulkInsert('Products', products);
},
down: async (queryInterface, Sequelize) => {
await queryInterface.bulkDelete('Products', null, {});
await queryInterface.bulkDelete('Categories', null, {});
}
};
Managing Migrations in Production
Managing database changes in production is like performing maintenance on a busy highway - you need to be careful not to disrupt the traffic while making necessary improvements. Let's explore the best practices for handling these changes:
Pre-Deployment Checklist
// 1. Back up your database
pg_dump -U username -d database_name > backup.sql
// 2. Review pending migrations
npx sequelize-cli db:migrate:status
// 3. Test migrations in staging
npx sequelize-cli db:migrate --env staging
// 4. Prepare rollback plan
npx sequelize-cli db:migrate:undo
// or for a specific migration:
npx sequelize-cli db:migrate:undo --name YYYYMMDDHHMMSS-migration-name.js
Safe Migration Practices
// Example of a safe migration that adds a column
module.exports = {
up: async (queryInterface, Sequelize) => {
// Step 1: Add new column as nullable
await queryInterface.addColumn('Users', 'phoneNumber', {
type: Sequelize.STRING,
allowNull: true
});
// Step 2: Update existing records (if needed)
await queryInterface.sequelize.query(`
UPDATE "Users"
SET "phoneNumber" = 'Unknown'
WHERE "phoneNumber" IS NULL;
`);
// Step 3: Change column to not nullable (if required)
await queryInterface.changeColumn('Users', 'phoneNumber', {
type: Sequelize.STRING,
allowNull: false
});
},
down: async (queryInterface, Sequelize) => {
await queryInterface.removeColumn('Users', 'phoneNumber');
}
};
Real-World Example: E-commerce Product Launch
Let's walk through a complete example of preparing and deploying a new product category system:
Step 1: Create the Migration
// migrations/YYYYMMDDHHMMSS-add-product-categories.js
module.exports = {
up: async (queryInterface, Sequelize) => {
// Create categories table
await queryInterface.createTable('Categories', {
id: {
type: Sequelize.INTEGER,
primaryKey: true,
autoIncrement: true
},
name: {
type: Sequelize.STRING,
allowNull: false,
unique: true
},
description: Sequelize.TEXT,
createdAt: {
type: Sequelize.DATE,
allowNull: false
},
updatedAt: {
type: Sequelize.DATE,
allowNull: false
}
});
// Add categoryId to products
await queryInterface.addColumn('Products', 'categoryId', {
type: Sequelize.INTEGER,
references: {
model: 'Categories',
key: 'id'
},
onUpdate: 'CASCADE',
onDelete: 'SET NULL'
});
},
down: async (queryInterface, Sequelize) => {
await queryInterface.removeColumn('Products', 'categoryId');
await queryInterface.dropTable('Categories');
}
};
Step 2: Create the Seeder
// seeders/YYYYMMDDHHMMSS-initial-categories.js
module.exports = {
up: async (queryInterface, Sequelize) => {
const categories = [
{
name: 'Electronics',
description: 'Electronic devices and accessories',
createdAt: new Date(),
updatedAt: new Date()
},
{
name: 'Clothing',
description: 'Apparel and fashion items',
createdAt: new Date(),
updatedAt: new Date()
}
];
await queryInterface.bulkInsert('Categories', categories);
// Get inserted categories
const insertedCategories = await queryInterface.sequelize.query(
`SELECT id, name FROM "Categories";`,
{ type: Sequelize.QueryTypes.SELECT }
);
// Create a mapping of category names to their IDs
const categoryMap = {};
insertedCategories.forEach(cat => {
categoryMap[cat.name] = cat.id;
});
// Update existing products with category IDs
await queryInterface.sequelize.query(`
UPDATE "Products"
SET "categoryId" = CASE
WHEN name LIKE '%phone%' OR name LIKE '%laptop%'
THEN ${categoryMap['Electronics']}
WHEN name LIKE '%shirt%' OR name LIKE '%shoe%'
THEN ${categoryMap['Clothing']}
ELSE NULL
END
WHERE "categoryId" IS NULL;
`);
},
down: async (queryInterface, Sequelize) => {
await queryInterface.bulkDelete('Categories', null, {});
}
};
Step 3: Deployment Script
#!/bin/bash
# deploy.sh
# Backup the database
echo "Creating database backup..."
pg_dump -U $DB_USER -d $DB_NAME > backup_$(date +%Y%m%d_%H%M%S).sql
# Run migrations
echo "Running migrations..."
npx sequelize-cli db:migrate
# Check migration status
if [ $? -eq 0 ]; then
echo "Migrations successful, running seeders..."
npx sequelize-cli db:seed:all
else
echo "Migration failed, rolling back..."
npx sequelize-cli db:migrate:undo
exit 1
fi
Monitoring and Maintenance
After deployment, it's crucial to monitor your database's health and performance. Think of this as regular health check-ups for your application:
Migration History Table
// Query to check migration history
SELECT name, run_on
FROM "SequelizeMeta"
ORDER BY run_on DESC;
// Query to check seeder history
SELECT name, run_on
FROM "SequelizeData"
ORDER BY run_on DESC;
Common Maintenance Tasks
// Reset and rebuild database (development only)
npx sequelize-cli db:drop
npx sequelize-cli db:create
npx sequelize-cli db:migrate
npx sequelize-cli db:seed:all
// Selective seeding
npx sequelize-cli db:seed --seed YYYYMMDDHHMMSS-seed-name.js
// Undo specific seeder
npx sequelize-cli db:seed:undo --seed YYYYMMDDHHMMSS-seed-name.js
Best Practices and Tips
Here are some key guidelines to ensure successful database management in production:
Version Control: Always keep your migrations and seeders in version control alongside your application code. This ensures that your database schema evolves in sync with your application.
Testing: Always test migrations and seeders in a staging environment that mirrors production as closely as possible. This helps catch potential issues before they affect real users.
Backup Strategy: Implement a robust backup strategy that includes both automated regular backups and manual backups before significant database changes.
Documentation: Maintain clear documentation of your database schema, relationships, and any special considerations for deployments. This helps team members understand the database structure and makes troubleshooting easier.
Monitoring: Set up alerts for failed migrations or unusual database behavior. Early detection of issues can prevent major problems down the line.