Understanding Sequelize CLI Commands
Think of Sequelize CLI commands as a set of powerful tools in your database management toolbox. Just as a carpenter needs different tools for different aspects of woodworking, you'll use different Sequelize commands for various database operations. Let's explore these tools and understand when and how to use each one.
Project Initialization
Starting a new Sequelize project is like setting up your workshop. You need to organize your tools and create the proper structure before you can begin working:
Initialize Sequelize in Your Project
npx sequelize-cli init
/* This command creates four directories:
- config: Database configuration
- models: Your data models
- migrations: Database change scripts
- seeders: Initial data files
Think of this as organizing your workspace with:
- config: Your blueprint storage
- models: Your templates
- migrations: Your construction plans
- seeders: Your starter materials */
Working with Models and Migrations
Creating models and migrations is like drawing up plans for building something new. Each model represents a blueprint for a type of data you want to store:
Generate Model and Migration
# Create a User model with specific attributes
npx sequelize model:generate \
--name User \
--attributes firstName:string,lastName:string,email:string
/* This creates two files:
1. models/user.js - The model definition
2. migrations/[timestamp]-create-user.js - The migration plan
The model is like a blueprint, while the migration is the actual
construction plan for building the database table */
# Create a more complex model with relationships
npx sequelize model:generate \
--name Post \
--attributes title:string,content:text,userId:integer
/* Tips for model generation:
- Use singular form for model names (Post, not Posts)
- Sequelize automatically adds id, createdAt, updatedAt
- Foreign keys should end with 'Id' (userId, not user_id) */
Database Migration Commands
Migrations are like version control for your database. They allow you to evolve your database schema over time:
Migration Management
# Run pending migrations
npx dotenv sequelize db:migrate
/* This command:
1. Checks for unmigrated files
2. Runs them in order
3. Updates migration history
Think of it as executing your construction plans */
# Undo the last migration
npx dotenv sequelize db:migrate:undo
# Undo all migrations
npx dotenv sequelize db:migrate:undo:all
/* When to use undo:
- During development for testing
- When you need to modify a recent migration
- Never in production without a backup! */
# Generate a blank migration
npx sequelize migration:generate \
--name add-email-verification
/* Use blank migrations for:
- Adding columns to existing tables
- Modifying data
- Creating indexes
- Any custom database changes */
Working with Seeders
Seeders are like your initial inventory stock. They help you populate your database with starter data:
Seeder Commands
# Generate a seeder file
npx sequelize seed:generate \
--name demo-users
/* Useful for creating:
- Test data for development
- Initial admin users
- Required application settings
- Sample content for testing */
# Run all seeders
npx dotenv sequelize db:seed:all
# Undo all seeds
npx dotenv sequelize db:seed:undo:all
/* Best practices for seeders:
- Keep development and production seeds separate
- Use realistic but obviously fake data
- Include a variety of test cases
- Make seeds idempotent (safe to run multiple times) */
Migration File Syntax Guide
Writing migrations requires understanding specific patterns for different database operations. Here's a comprehensive guide:
Creating Tables
module.exports = {
up: async (queryInterface, Sequelize) => {
await queryInterface.createTable('Users', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
firstName: {
type: Sequelize.STRING,
allowNull: false
},
email: {
type: Sequelize.STRING,
unique: true
},
createdAt: {
allowNull: false,
type: Sequelize.DATE,
defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE,
defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
}
});
},
down: async (queryInterface, Sequelize) => {
await queryInterface.dropTable('Users');
}
};
/* Key points about table creation:
- Table names should be plural
- Include timestamps unless explicitly not needed
- Consider adding indexes for frequently queried columns
- Always include a down method for reversibility */
Adding Relationships
// Creating a foreign key relationship
module.exports = {
up: async (queryInterface, Sequelize) => {
await queryInterface.addColumn('Posts', 'userId', {
type: Sequelize.INTEGER,
references: {
model: 'Users',
key: 'id',
},
onUpdate: 'CASCADE',
onDelete: 'SET NULL',
});
},
down: async (queryInterface, Sequelize) => {
await queryInterface.removeColumn('Posts', 'userId');
}
};
/* Understanding referential actions:
CASCADE: When parent is deleted/updated, do same to children
SET NULL: When parent is deleted/updated, set child to null
RESTRICT: Prevent parent deletion if children exist
NO ACTION: Similar to RESTRICT but checked differently */
Common Patterns and Best Practices
Here are some proven patterns for common database operations:
Adding Indexes
module.exports = {
up: async (queryInterface, Sequelize) => {
// Single-column index
await queryInterface.addIndex('Users', ['email']);
// Multi-column index
await queryInterface.addIndex('Posts',
['title', 'createdAt'],
{
name: 'posts_title_created_idx',
unique: false
}
);
},
down: async (queryInterface, Sequelize) => {
await queryInterface.removeIndex('Users', ['email']);
await queryInterface.removeIndex('Posts', 'posts_title_created_idx');
}
};
/* When to add indexes:
- Foreign key columns
- Frequently searched columns
- Unique constraint columns
- Sorting columns in large tables */
Modifying Existing Tables
module.exports = {
up: async (queryInterface, Sequelize) => {
// Adding a new column
await queryInterface.addColumn('Users', 'isAdmin', {
type: Sequelize.BOOLEAN,
defaultValue: false,
allowNull: false
});
// Modifying a column
await queryInterface.changeColumn('Users', 'email', {
type: Sequelize.STRING,
allowNull: false,
unique: true
});
},
down: async (queryInterface, Sequelize) => {
await queryInterface.removeColumn('Users', 'isAdmin');
// Restore original email column definition
await queryInterface.changeColumn('Users', 'email', {
type: Sequelize.STRING,
allowNull: true,
unique: false
});
}
};
/* Best practices for table modifications:
- Always provide down migrations
- Consider data migration needs
- Test thoroughly with sample data
- Handle null values appropriately */