Why Use an ORM?
Imagine you're trying to communicate with someone who speaks a different language. You could learn their language completely, or you could use a translator. An Object-Relational Mapping (ORM) tool like Sequelize acts as this translator between your JavaScript code and your SQL database.
Consider this example of the same operation with and without an ORM:
Without ORM (Raw SQL)
// Finding a user by email
const query = `
SELECT * FROM Users
WHERE email = ?
AND isActive = true
`;
const [results] = await connection.execute(query, [userEmail]);
const user = results[0];
// Creating a new user
const insertQuery = `
INSERT INTO Users (firstName, lastName, email, createdAt, updatedAt)
VALUES (?, ?, ?, NOW(), NOW())
`;
await connection.execute(insertQuery, [firstName, lastName, email]);
With Sequelize ORM
// Finding a user by email
const user = await User.findOne({
where: {
email: userEmail,
isActive: true
}
});
// Creating a new user
const newUser = await User.create({
firstName,
lastName,
email
});
The ORM provides a more intuitive, JavaScript-native way to interact with your database while handling many low-level details automatically.
Understanding Models and Migrations
Think of a model as a blueprint for a type of data in your application, while a migration is the construction plan for creating or modifying the database structure. Let's explore this through an example:
Model Definition
// models/user.js
module.exports = (sequelize, DataTypes) => {
const User = sequelize.define('User', {
firstName: {
type: DataTypes.STRING,
allowNull: false,
validate: {
notEmpty: true,
len: [2, 50] // Model-level validation
}
},
email: {
type: DataTypes.STRING,
unique: true, // Database-level constraint
validate: {
isEmail: true // Model-level validation
}
}
});
return User;
};
/* This model:
1. Defines the structure of User objects in your code
2. Specifies validations for data integrity
3. Provides methods for database interactions */
Corresponding Migration
// migrations/XXXXXXXXXXXXXX-create-user.js
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
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
}
});
},
down: async (queryInterface, Sequelize) => {
await queryInterface.dropTable('Users');
}
};
/* This migration:
1. Creates the actual database table
2. Sets up database-level constraints
3. Provides a way to undo changes */
Database Constraints vs. Model Validations
Understanding when to use database constraints versus model validations is crucial. Think of database constraints as security guards at the database level, while model validations are like form validators in your application:
Database Constraints (In Migrations)
// Use database constraints for:
await queryInterface.createTable('Users', {
email: {
type: Sequelize.STRING,
unique: true, // Enforced at database level
allowNull: false // Enforced at database level
},
age: {
type: Sequelize.INTEGER,
validate: {
min: 0, // Enforced at database level
max: 120
}
}
});
/* Database constraints are best for:
- Unique values (like email addresses)
- Required fields (NOT NULL)
- Foreign key relationships
- Data integrity that must be absolute */
Model Validations (In Models)
// Use model validations for:
const User = sequelize.define('User', {
email: {
type: DataTypes.STRING,
validate: {
isEmail: true, // Validates email format
notEmpty: true // Ensures non-empty string
}
},
password: {
type: DataTypes.STRING,
validate: {
len: [8, 100], // Password length validation
is: /^[0-9a-zA-Z]*$/ // Alphanumeric validation
}
}
});
/* Model validations are best for:
- Complex validation rules
- Business logic validation
- User input validation
- Validation that may need to change often */
Managing the Database Lifecycle
Understanding how to manage your database through its lifecycle is crucial. This includes creating tables, modifying them, and populating them with data:
Creating and Modifying Tables
// Initial table creation migration
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
}
});
},
down: async (queryInterface, Sequelize) => {
await queryInterface.dropTable('Products');
}
};
// Later modification migration
module.exports = {
up: async (queryInterface, Sequelize) => {
await queryInterface.addColumn('Products', 'description', {
type: Sequelize.TEXT,
allowNull: true
});
},
down: async (queryInterface, Sequelize) => {
await queryInterface.removeColumn('Products', 'description');
}
};
/* Key concepts:
1. Migrations should be reversible
2. Each change gets its own migration
3. Never modify existing migrations
4. Always test migrations thoroughly */
Seeding Data
// seeders/XXXXXXXXXXXXX-demo-products.js
module.exports = {
up: async (queryInterface, Sequelize) => {
await queryInterface.bulkInsert('Products', [
{
name: 'Basic Widget',
price: 9.99,
description: 'A simple widget',
createdAt: new Date(),
updatedAt: new Date()
},
{
name: 'Premium Widget',
price: 19.99,
description: 'A premium widget',
createdAt: new Date(),
updatedAt: new Date()
}
]);
},
down: async (queryInterface, Sequelize) => {
await queryInterface.bulkDelete('Products', null, {});
}
};
/* Seeding best practices:
1. Use realistic but obviously fake data
2. Include a variety of cases
3. Make seeders idempotent
4. Consider dependencies between tables */
Environment Configuration
Properly configuring your Sequelize environment is crucial for security and flexibility. Let's explore how to set this up:
Configuration Setup
// .sequelizerc
const path = require('path');
module.exports = {
'config': path.resolve('config', 'database.js'),
'models-path': path.resolve('db', 'models'),
'seeders-path': path.resolve('db', 'seeders'),
'migrations-path': path.resolve('db', 'migrations')
};
// config/database.js
module.exports = {
development: {
username: process.env.DB_USERNAME,
password: process.env.DB_PASSWORD,
database: process.env.DB_NAME,
host: process.env.DB_HOST,
dialect: 'postgresql'
},
test: {
// Test environment config
},
production: {
// Production environment config
use_env_variable: 'DATABASE_URL'
}
};
/* Configuration best practices:
1. Never commit sensitive information
2. Use environment variables
3. Different configs for different environments
4. Consider using connection pools */
Common Naming Conventions
Understanding naming conventions in Sequelize and SQL helps prevent confusion and errors:
Naming Patterns
/* Model Names (Singular, PascalCase):
- User
- BlogPost
- ProductCategory
Table Names (Plural, snake_case in SQL, PascalCase in Sequelize):
- SQL: users, blog_posts, product_categories
- Sequelize: Users, BlogPosts, ProductCategories
Column Names:
- SQL: first_name, created_at
- Sequelize: firstName, createdAt
Foreign Keys:
- SQL: user_id
- Sequelize: userId */
// Example model definition following conventions
const BlogPost = sequelize.define('BlogPost', {
title: DataTypes.STRING,
userId: { // Foreign key following convention
type: DataTypes.INTEGER,
references: {
model: 'Users',
key: 'id'
}
}
});