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, InferCreationAttributes\> { declare id: CreationOptional; declare email: string; declare name: string | null; declare isActive: CreationOptional; declare createdAt: CreationOptional; declare updatedAt: CreationOptional; }

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:all

Migration 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();