Library

Sequelize

Overview

Sequelize is a mature Promise-based ORM library for Node.js developed with the concept of "HTTP for humans." With over 9 years of production-proven experience, it supports PostgreSQL, MySQL, MariaDB, SQLite, and Microsoft SQL Server, establishing itself as one of the most popular ORMs in the Node.js ecosystem. Enhanced TypeScript support through sequelize-typescript provides comprehensive features needed for enterprise-level application development.

Details

Sequelize 2025 edition maintains its position as a definitive ORM for Node.js databases with over 15 years of development experience. With mature APIs and excellent stability, it's widely adopted in major web framework environments like Express, NestJS, and Koa. The design emphasizes clean and readable code with powerful transaction management, relationship systems, connection pooling, validation framework, and enterprise-grade requirements for HTTP communication.

Key Features

• Promise/Async-Await Support: Complete support for asynchronous operations
• Extensive Database Support: Compatible with 9 major database engines
• Migration System: Systematic schema change management
• Validation Features: Rich validators (@sequelize/validator.js)
• Connection Pooling: Efficient database connection management
• TypeScript Integration: Decorator support via sequelize-typescript

Pros and Cons

Pros

• Most established ORM in Node.js ecosystem with proven track record
• Comprehensive feature set (transactions, relations, migrations)
• Support for multiple database engines
• Active community and extensive documentation
• Enterprise-level features (read replication, etc.)
• Gradual adoption possible (can be added to existing projects)

Cons

• TypeScript type safety inferior to newer ORMs
• Verbose API with high learning curve
• Complex configuration requirements
• Performance tuning requires specialized knowledge
• Developer experience inferior to newer alternatives (Prisma, etc.)
• Insufficient documentation for some features

Reference Pages

• Sequelize Official Site
• Sequelize GitHub
• Sequelize TypeScript

Code Examples

Installation and Basic Setup

npm install @sequelize/core
npm install @sequelize/postgres  # For PostgreSQL
npm install sequelize-typescript  # For TypeScript

Model Definition (JavaScript)

const { DataTypes } = require('@sequelize/core')

const User = sequelize.define('User', {
  firstName: {
    type: DataTypes.STRING,
    allowNull: false,
    validate: {
      notEmpty: true
    }
  },
  lastName: {
    type: DataTypes.STRING,
    allowNull: false
  },
  email: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: true,
    validate: {
      isEmail: true
    }
  },
  age: {
    type: DataTypes.INTEGER,
    validate: {
      min: 0,
      max: 120
    }
  }
}, {
  timestamps: true,
  paranoid: true,  // Soft delete
  tableName: 'users'
})

module.exports = User

TypeScript Model Definition

import { Table, Column, Model, DataType, PrimaryKey, AutoIncrement, AllowNull, Unique, Validate } from 'sequelize-typescript'

@Table({
  tableName: 'users',
  timestamps: true,
  paranoid: true
})
export class User extends Model {
  @PrimaryKey
  @AutoIncrement
  @Column(DataType.INTEGER)
  id!: number

  @AllowNull(false)
  @Validate({ notEmpty: true })
  @Column(DataType.STRING)
  firstName!: string

  @AllowNull(false)
  @Column(DataType.STRING)
  lastName!: string

  @AllowNull(false)
  @Unique
  @Validate({ isEmail: true })
  @Column(DataType.STRING)
  email!: string

  @Validate({ min: 0, max: 120 })
  @Column(DataType.INTEGER)
  age?: number
}

Database Connection Configuration

import { Sequelize } from 'sequelize-typescript'
import { User } from './models/User'

const sequelize = new Sequelize({
  database: 'myapp',
  dialect: 'postgres',
  username: 'username',
  password: 'password',
  host: 'localhost',
  port: 5432,
  models: [User],
  logging: console.log,
  pool: {
    max: 10,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
})

// Connection test
async function testConnection() {
  try {
    await sequelize.authenticate()
    console.log('Database connection successful')
  } catch (error) {
    console.error('Database connection failed:', error)
  }
}

Basic CRUD Operations

// Create user
const user = await User.create({
  firstName: 'John',
  lastName: 'Doe',
  email: '[email protected]',
  age: 30
})

// Find users
const allUsers = await User.findAll()
const userById = await User.findByPk(1)
const userByEmail = await User.findOne({
  where: { email: '[email protected]' }
})

// Multi-condition search
const { Op } = require('@sequelize/core')
const adultUsers = await User.findAll({
  where: {
    age: {
      [Op.gte]: 18
    },
    firstName: {
      [Op.like]: '%John%'
    }
  },
  order: [['createdAt', 'DESC']],
  limit: 10,
  offset: 0
})

// Update user
await User.update(
  { age: 31 },
  { where: { id: 1 } }
)

// Update specific user
const user = await User.findByPk(1)
user.age = 32
await user.save()

// Delete user
await User.destroy({
  where: { id: 1 }
})

Relations Definition and Operations

import { Table, Column, Model, HasMany, BelongsTo, ForeignKey } from 'sequelize-typescript'

@Table({ tableName: 'posts' })
export class Post extends Model {
  @Column
  title!: string

  @Column(DataType.TEXT)
  content!: string

  @ForeignKey(() => User)
  @Column
  userId!: number

  @BelongsTo(() => User)
  author!: User
}

// Add relation to User model
@Table({ tableName: 'users' })
export class User extends Model {
  // ... other properties

  @HasMany(() => Post)
  posts!: Post[]
}

// Find with relations
const userWithPosts = await User.findOne({
  where: { id: 1 },
  include: [Post]
})

// Nested relations
const postsWithAuthors = await Post.findAll({
  include: [{
    model: User,
    as: 'author',
    attributes: ['firstName', 'lastName', 'email']
  }]
})

Advanced Queries and Transactions

// Complex queries (JOIN)
const results = await sequelize.query(`
  SELECT u.firstName, u.lastName, COUNT(p.id) as postCount
  FROM users u
  LEFT JOIN posts p ON u.id = p.userId
  WHERE u.age > :age
  GROUP BY u.id
  HAVING COUNT(p.id) > :postCount
`, {
  replacements: { age: 25, postCount: 5 },
  type: QueryTypes.SELECT
})

// Transactions
const transaction = await sequelize.transaction()

try {
  const user = await User.create({
    firstName: 'Jane',
    lastName: 'Smith',
    email: '[email protected]'
  }, { transaction })

  const post = await Post.create({
    title: 'First Post',
    content: 'Hello, World!',
    userId: user.id
  }, { transaction })

  await transaction.commit()
  console.log('Transaction successful')
} catch (error) {
  await transaction.rollback()
  console.error('Transaction failed:', error)
}

// Managed transactions (recommended)
await sequelize.transaction(async (t) => {
  const user = await User.create({
    firstName: 'Bob',
    lastName: 'Johnson',
    email: '[email protected]'
  }, { transaction: t })

  await Post.create({
    title: 'Auto Transaction',
    content: 'Automatic rollback on error',
    userId: user.id
  }, { transaction: t })
})

Migrations

# Install Sequelize CLI
npm install --save-dev @sequelize/cli

# Create config file
npx sequelize-cli init

# Generate migration
npx sequelize-cli migration:generate --name create-users-table

# Run migrations
npx sequelize-cli db:migrate

# Undo migration
npx sequelize-cli db:migrate:undo

// Migration file example
'use strict'

module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable('Users', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      firstName: {
        type: Sequelize.STRING,
        allowNull: false
      },
      lastName: {
        type: Sequelize.STRING,
        allowNull: false
      },
      email: {
        type: Sequelize.STRING,
        allowNull: false,
        unique: true
      },
      age: {
        type: Sequelize.INTEGER
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      deletedAt: {
        type: Sequelize.DATE
      }
    })

    await queryInterface.addIndex('Users', ['email'])
  },

  async down(queryInterface, Sequelize) {
    await queryInterface.dropTable('Users')
  }
}

Validation and Hooks

import { IsEmail, Length } from '@sequelize/validator.js'

@Table({ tableName: 'users' })
export class User extends Model {
  @AllowNull(false)
  @Length([2, 50])
  @Column
  firstName!: string

  @AllowNull(false)
  @IsEmail
  @Column
  email!: string

  // Hooks (lifecycle events)
  @BeforeCreate
  static async hashPassword(user: User) {
    if (user.password) {
      user.password = await bcrypt.hash(user.password, 10)
    }
  }

  @AfterCreate
  static async sendWelcomeEmail(user: User) {
    // Welcome email logic
    console.log(`Sending welcome email to ${user.email}`)
  }

  // Instance method
  async validatePassword(password: string): Promise<boolean> {
    return bcrypt.compare(password, this.password)
  }
}