Sequelize Development Guidelines
You are an expert in Sequelize ORM, Node.js, and database design with a focus on model associations, migrations, and data integrity.
Core Principles
- Sequelize is a promise-based ORM for Node.js and TypeScript
- Supports PostgreSQL, MySQL, MariaDB, SQLite, and Microsoft SQL Server
- Uses model definitions with DataTypes for schema declaration
- Provides comprehensive support for associations, transactions, and hooks
- Migrations should be used for all schema changes in production
Database Connection
Basic Setup
import { Sequelize } from "sequelize";
// Option 1: Connection URI
const sequelize = new Sequelize(process.env.DATABASE_URL!, {
dialect: "postgres",
logging: process.env.NODE_ENV === "development" ? console.log : false,
pool: {
max: 10,
min: 0,
acquire: 30000,
idle: 10000,
},
});
// Option 2: Individual parameters
const sequelize = new Sequelize({
dialect: "postgres",
host: process.env.DB_HOST,
port: parseInt(process.env.DB_PORT || "5432"),
username: process.env.DB_USERNAME,
password: process.env.DB_PASSWORD,
database: process.env.DB_NAME,
logging: false,
});
// Test connection
async function testConnection() {
try {
await sequelize.authenticate();
console.log("Connection established successfully.");
} catch (error) {
console.error("Unable to connect to the database:", error);
}
}Model Definition
Basic Model with TypeScript
import {
Model,
DataTypes,
InferAttributes,
InferCreationAttributes,
CreationOptional,
} from "sequelize";
import { sequelize } from "./database";
class User extends Model<InferAttributes<User>, InferCreationAttributes<User>> {
declare id: CreationOptional<number>;
declare email: string;
declare name: string | null;
declare isActive: CreationOptional<boolean>;
declare createdAt: CreationOptional<Date>;
declare updatedAt: CreationOptional<Date>;
}
User.init(
{
id: {
type: DataTypes.INTEGER,
autoIncrement: true,
primaryKey: true,
},
email: {
type: DataTypes.STRING(255),
allowNull: false,
unique: true,
validate: {
isEmail: true,
},
},
name: {
type: DataTypes.STRING(255),
allowNull: true,
},
isActive: {
type: DataTypes.BOOLEAN,
defaultValue: true,
},
createdAt: DataTypes.DATE,
updatedAt: DataTypes.DATE,
},
{
sequelize,
tableName: "users",
modelName: "User",
underscored: true, // Use snake_case for column names
}
);
export { User };Data Types Reference
// String types
DataTypes.STRING(255) // VARCHAR(255)
DataTypes.TEXT // TEXT
DataTypes.TEXT("tiny") // TINYTEXT (MySQL)
// Numeric types
DataTypes.INTEGER // INTEGER
DataTypes.BIGINT // BIGINT
DataTypes.FLOAT // FLOAT
DataTypes.DOUBLE // DOUBLE
DataTypes.DECIMAL(10, 2) // DECIMAL(10,2)
// Boolean
DataTypes.BOOLEAN // BOOLEAN / TINYINT(1)
// Date/Time
DataTypes.DATE // DATETIME/TIMESTAMP
DataTypes.DATEONLY // DATE
DataTypes.TIME // TIME
// Binary
DataTypes.BLOB // BLOB
// JSON
DataTypes.JSON // JSON (if supported)
DataTypes.JSONB // JSONB (PostgreSQL)
// UUID
DataTypes.UUID // UUID
DataTypes.UUIDV4 // Auto-generate UUID v4
// Enum
DataTypes.ENUM("active", "inactive", "pending")
// Array (PostgreSQL only)
DataTypes.ARRAY(DataTypes.STRING)Associations
One-to-One
class User extends Model {
declare id: number;
declare profile?: Profile;
}
class Profile extends Model {
declare id: number;
declare userId: number;
declare bio: string;
declare user?: User;
}
// Define associations
User.hasOne(Profile, {
foreignKey: "userId",
as: "profile",
});
Profile.belongsTo(User, {
foreignKey: "userId",
as: "user",
});One-to-Many
class User extends Model {
declare id: number;
declare posts?: Post[];
}
class Post extends Model {
declare id: number;
declare authorId: number;
declare title: string;
declare author?: User;
}
// Define associations
User.hasMany(Post, {
foreignKey: "authorId",
as: "posts",
});
Post.belongsTo(User, {
foreignKey: "authorId",
as: "author",
});Many-to-Many
class Post extends Model {
declare id: number;
declare tags?: Tag[];
}
class Tag extends Model {
declare id: number;
declare name: string;
declare posts?: Post[];
}
// Define associations with junction table
Post.belongsToMany(Tag, {
through: "PostTags",
foreignKey: "postId",
otherKey: "tagId",
as: "tags",
});
Tag.belongsToMany(Post, {
through: "PostTags",
foreignKey: "tagId",
otherKey: "postId",
as: "posts",
});Querying
Basic Queries
// Find all
const users = await User.findAll();
// Find with conditions
const activeUsers = await User.findAll({
where: {
isActive: true,
},
});
// Find one
const user = await User.findOne({
where: { email: "user@example.com" },
});
// Find by primary key
const user = await User.findByPk(1);
// Find or create
const [user, created] = await User.findOrCreate({
where: { email: "user@example.com" },
defaults: {
name: "New User",
},
});Advanced Queries with Operators
import { Op } from "sequelize";
// Multiple conditions
const users = await User.findAll({
where: {
[Op.and]: [
{ isActive: true },
{ createdAt: { [Op.gte]: new Date("2024-01-01") } },
],
},
});
// OR condition
const users = await User.findAll({
where: {
[Op.or]: [{ name: "John" }, { name: "Jane" }],
},
});
// LIKE
const users = await User.findAll({
where: {
email: { [Op.like]: "%@example.com" },
},
});
// IN
const users = await User.findAll({
where: {
id: { [Op.in]: [1, 2, 3] },
},
});
// Comparison operators
const users = await User.findAll({
where: {
id: { [Op.gt]: 10 }, // Greater than
age: { [Op.gte]: 18 }, // Greater than or equal
score: { [Op.lt]: 100 }, // Less than
rank: { [Op.lte]: 5 }, // Less than or equal
status: { [Op.ne]: "inactive" }, // Not equal
},
});Eager Loading (Include)
// Load user with posts
const user = await User.findOne({
where: { id: 1 },
include: [
{
model: Post,
as: "posts",
},
],
});
// Nested includes
const user = await User.findOne({
where: { id: 1 },
include: [
{
model: Post,
as: "posts",
include: [
{
model: Tag,
as: "tags",
},
],
},
],
});
// Include with conditions
const users = await User.findAll({
include: [
{
model: Post,
as: "posts",
where: {
publishedAt: { [Op.ne]: null },
},
required: false, // LEFT JOIN (include users without posts)
},
],
});Pagination and Ordering
const page = 1;
const pageSize = 20;
const { count, rows: users } = await User.findAndCountAll({
where: { isActive: true },
order: [
["createdAt", "DESC"],
["name", "ASC"],
],
limit: pageSize,
offset: (page - 1) * pageSize,
});
const totalPages = Math.ceil(count / pageSize);Aggregations
// Count
const count = await User.count({
where: { isActive: true },
});
// Sum
const total = await Order.sum("amount", {
where: { status: "completed" },
});
// Max/Min
const maxPrice = await Product.max("price");
const minPrice = await Product.min("price");
// Group by
const stats = await Order.findAll({
attributes: [
"status",
[sequelize.fn("COUNT", sequelize.col("id")), "count"],
[sequelize.fn("SUM", sequelize.col("amount")), "total"],
],
group: ["status"],
});CRUD Operations
Create
// Create single record
const user = await User.create({
email: "user@example.com",
name: "John Doe",
});
// Bulk create
const users = await User.bulkCreate(
[
{ email: "user1@example.com", name: "User 1" },
{ email: "user2@example.com", name: "User 2" },
],
{
validate: true, // Run validations on each record
}
);
// Create with associations
const user = await User.create(
{
email: "user@example.com",
name: "John",
profile: {
bio: "Hello world",
},
},
{
include: [{ model: Profile, as: "profile" }],
}
);Update
// Update single record
const user = await User.findByPk(1);
if (user) {
user.name = "Jane Doe";
await user.save();
}
// Update with new data
await user.update({
name: "Jane Doe",
isActive: false,
});
// Bulk update
await User.update(
{ isActive: false },
{
where: {
lastLoginAt: { [Op.lt]: new Date("2024-01-01") },
},
}
);Delete
// Delete single record
const user = await User.findByPk(1);
if (user) {
await user.destroy();
}
// Bulk delete
await User.destroy({
where: {
isActive: false,
},
});
// Soft delete (requires paranoid: true in model options)
await user.destroy(); // Sets deletedAt instead of deleting
// Restore soft-deleted record
await user.restore();Transactions
// Managed transaction (recommended)
const result = await sequelize.transaction(async (t) => {
const user = await User.create(
{
email: "user@example.com",
name: "User",
},
{ transaction: t }
);
const post = await Post.create(
{
title: "First Post",
authorId: user.id,
},
{ transaction: t }
);
return { user, post };
});
// Unmanaged transaction
const t = await sequelize.transaction();
try {
const user = await User.create(
{ email: "user@example.com" },
{ transaction: t }
);
await Post.create(
{ title: "Post", authorId: user.id },
{ transaction: t }
);
await t.commit();
} catch (error) {
await t.rollback();
throw error;
}Hooks
User.init(
{
// ... columns
},
{
sequelize,
hooks: {
beforeValidate: (user) => {
// Normalize email
if (user.email) {
user.email = user.email.toLowerCase().trim();
}
},
beforeCreate: async (user) => {
// Hash password
if (user.password) {
user.password = await bcrypt.hash(user.password, 10);
}
},
afterCreate: async (user) => {
// Send welcome email
await sendWelcomeEmail(user.email);
},
beforeDestroy: async (user) => {
// Clean up related data
await Post.destroy({ where: { authorId: user.id } });
},
},
}
);
// Or define hooks separately
User.addHook("beforeSave", "hashPassword", async (user) => {
if (user.changed("password")) {
user.password = await bcrypt.hash(user.password, 10);
}
});Hook with Transaction Access
User.addHook("beforeCreate", async (user, options) => {
if (options.transaction) {
// Use the same transaction for related operations
await AuditLog.create(
{
action: "user_created",
userId: user.id,
},
{ transaction: options.transaction }
);
}
});Validations
User.init(
{
email: {
type: DataTypes.STRING,
allowNull: false,
validate: {
isEmail: {
msg: "Must be a valid email address",
},
notEmpty: true,
},
},
age: {
type: DataTypes.INTEGER,
validate: {
min: {
args: [0],
msg: "Age must be non-negative",
},
max: {
args: [150],
msg: "Age must be realistic",
},
},
},
username: {
type: DataTypes.STRING,
validate: {
len: {
args: [3, 30],
msg: "Username must be between 3 and 30 characters",
},
isAlphanumeric: {
msg: "Username must contain only letters and numbers",
},
// Custom validator
async isUnique(value: string) {
const existing = await User.findOne({
where: { username: value },
});
if (existing) {
throw new Error("Username already taken");
}
},
},
},
},
{ sequelize }
);Migrations
Creating Migrations
# Generate migration
npx sequelize-cli migration:generate --name create-users
# Run migrations
npx sequelize-cli db:migrate
# Undo last migration
npx sequelize-cli db:migrate:undo
# Undo all migrations
npx sequelize-cli db:migrate:undo:allMigration File Structure
// migrations/20240101000000-create-users.js
module.exports = {
async up(queryInterface, Sequelize) {
await queryInterface.createTable("users", {
id: {
type: Sequelize.INTEGER,
autoIncrement: true,
primaryKey: true,
},
email: {
type: Sequelize.STRING(255),
allowNull: false,
unique: true,
},
name: {
type: Sequelize.STRING(255),
allowNull: true,
},
is_active: {
type: Sequelize.BOOLEAN,
defaultValue: true,
},
created_at: {
type: Sequelize.DATE,
allowNull: false,
defaultValue: Sequelize.literal("CURRENT_TIMESTAMP"),
},
updated_at: {
type: Sequelize.DATE,
allowNull: false,
defaultValue: Sequelize.literal("CURRENT_TIMESTAMP"),
},
});
await queryInterface.addIndex("users", ["email"]);
},
async down(queryInterface) {
await queryInterface.dropTable("users");
},
};Best Practices
Use Eager Loading to Avoid N+1
// Bad: N+1 queries
const users = await User.findAll();
for (const user of users) {
const posts = await user.getPosts(); // Query per user
}
// Good: Single query with include
const users = await User.findAll({
include: [{ model: Post, as: "posts" }],
});Always Use Migrations in Production
// sequelize config
{
development: {
// ...
},
production: {
// ...
migrationStorageTableName: "sequelize_migrations",
seederStorageTableName: "sequelize_seeds",
}
}Use Aliases for Associations
// Good: Using aliases for clarity
User.hasMany(Post, { as: "posts", foreignKey: "authorId" });
// Query with alias
const user = await User.findOne({
include: [{ model: Post, as: "posts" }],
});Validate in Bulk Operations
// Always validate when using bulkCreate
await User.bulkCreate(users, { validate: true });Use Transactions for Data Integrity
// Wrap related operations in transactions
await sequelize.transaction(async (t) => {
// All operations use the same transaction
const order = await Order.create({ ... }, { transaction: t });
await OrderItem.bulkCreate(items, { transaction: t });
await Inventory.decrement("quantity", { ... }, { transaction: t });
});Scope Common Queries
User.addScope("active", {
where: { isActive: true },
});
User.addScope("withPosts", {
include: [{ model: Post, as: "posts" }],
});
// Use scopes
const activeUsers = await User.scope("active").findAll();
const usersWithPosts = await User.scope(["active", "withPosts"]).findAll();