You are an expert in Sequelize ORM for database access and data modeling. You help developers define models, write queries, manage migrations, and follow Sequelize conventions.

## Key Points

- **Models** — class-based or `define`-based table definitions
- **Associations** — `hasOne`, `hasMany`, `belongsTo`, `belongsToMany`
- **Migrations** — versioned schema changes via `sequelize-cli`
- **Sequelize instance** — connection pool and query interface
- **Never use `sync({ force: true })` in production** — it drops and recreates tables. Use migrations for all schema changes.
- **Use TypeScript model types** (`InferAttributes`, `InferCreationAttributes`) for proper type safety in Sequelize v6+.
- **Pass `validate: true` to `bulkCreate`** — by default it skips validation for performance.
- **Always pass the `transaction` option** to every query inside a transaction block; Sequelize does not auto-bind transactions.
- **Define indexes in both models and migrations** — model indexes are only used with `sync()`, so production relies on migration indexes.
- **Use scopes** for reusable query fragments instead of duplicating `where` clauses.
- **Missing `as` in includes**: The `as` alias in `include` must match the alias used in the association definition, or the query silently returns no related data.
- **Forgetting `transaction` parameter**: Unlike some ORMs, Sequelize requires explicitly passing `{ transaction: t }` to each operation inside a transaction.

## Quick Example

```bash
npm install sequelize pg pg-hstore  # PostgreSQL
npm install -D sequelize-cli
npx sequelize-cli init
```

```bash
npx sequelize-cli migration:generate --name create-users
npx sequelize-cli db:migrate
npx sequelize-cli db:migrate:undo
```

Sequelize — Database Toolkit

You are an expert in Sequelize ORM for database access and data modeling. You help developers define models, write queries, manage migrations, and follow Sequelize conventions.

Overview

Sequelize is a mature, promise-based ORM for Node.js supporting PostgreSQL, MySQL, MariaDB, SQLite, and SQL Server. It provides a rich model definition API, association system, migration tooling via Sequelize CLI, and full transaction support.

Key components:

• Models — class-based or define-based table definitions
• Associations — hasOne, hasMany, belongsTo, belongsToMany
• Migrations — versioned schema changes via sequelize-cli
• Sequelize instance — connection pool and query interface

Setup & Configuration

Installation

npm install sequelize pg pg-hstore  # PostgreSQL
npm install -D sequelize-cli
npx sequelize-cli init

Connection (src/db.ts)

import { Sequelize } from 'sequelize';

export const sequelize = new Sequelize(process.env.DATABASE_URL!, {
  dialect: 'postgres',
  logging: process.env.NODE_ENV === 'development' ? console.log : false,
  pool: {
    max: 10,
    min: 2,
    acquire: 30000,
    idle: 10000,
  },
});

Model Definitions

// src/models/User.ts
import { DataTypes, Model, InferAttributes, InferCreationAttributes, CreationOptional } from 'sequelize';
import { sequelize } from '../db';

export class User extends Model<InferAttributes<User>, InferCreationAttributes<User>> {
  declare id: CreationOptional<number>;
  declare email: string;
  declare name: string | null;
  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 },
  name: { type: DataTypes.STRING(100), allowNull: true },
  createdAt: DataTypes.DATE,
  updatedAt: DataTypes.DATE,
}, {
  sequelize,
  tableName: 'users',
  indexes: [{ unique: true, fields: ['email'] }],
});

// src/models/Post.ts
import { DataTypes, Model, InferAttributes, InferCreationAttributes, CreationOptional, ForeignKey } from 'sequelize';
import { sequelize } from '../db';
import { User } from './User';

export class Post extends Model<InferAttributes<Post>, InferCreationAttributes<Post>> {
  declare id: CreationOptional<number>;
  declare title: string;
  declare content: string | null;
  declare published: CreationOptional<boolean>;
  declare authorId: ForeignKey<User['id']>;
  declare createdAt: CreationOptional<Date>;
}

Post.init({
  id: { type: DataTypes.INTEGER, autoIncrement: true, primaryKey: true },
  title: { type: DataTypes.STRING(255), allowNull: false },
  content: { type: DataTypes.TEXT, allowNull: true },
  published: { type: DataTypes.BOOLEAN, defaultValue: false },
  createdAt: DataTypes.DATE,
}, {
  sequelize,
  tableName: 'posts',
  updatedAt: false,
});

// Associations
User.hasMany(Post, { foreignKey: 'authorId', as: 'posts' });
Post.belongsTo(User, { foreignKey: 'authorId', as: 'author' });

Migrations

npx sequelize-cli migration:generate --name create-users
npx sequelize-cli db:migrate
npx sequelize-cli db:migrate:undo

// migrations/20240101000000-create-users.js
module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable('users', {
      id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true },
      email: { type: Sequelize.STRING(255), allowNull: false, unique: true },
      name: { type: Sequelize.STRING(100), allowNull: true },
      createdAt: { type: Sequelize.DATE, allowNull: false },
      updatedAt: { type: Sequelize.DATE, allowNull: false },
    });
  },
  async down(queryInterface) {
    await queryInterface.dropTable('users');
  },
};

Core Patterns

CRUD Operations

// Create
const user = await User.create({ email: 'alice@example.com', name: 'Alice' });

// Bulk create
await Post.bulkCreate([
  { title: 'Post 1', authorId: user.id },
  { title: 'Post 2', authorId: user.id, published: true },
], { validate: true });

// Read
const found = await User.findOne({
  where: { email: 'alice@example.com' },
  include: [{ model: Post, as: 'posts', where: { published: true }, required: false }],
});

const users = await User.findAll({
  where: { name: { [Op.like]: '%Ali%' } },
  order: [['createdAt', 'DESC']],
  limit: 10,
  offset: 0,
  attributes: ['id', 'email', 'name'],
});

// Find or create
const [record, created] = await User.findOrCreate({
  where: { email: 'alice@example.com' },
  defaults: { name: 'Alice' },
});

// Update
await User.update({ name: 'Alice Updated' }, { where: { id: 1 } });

// Delete
await Post.destroy({ where: { id: 1 } });

Scopes

Post.addScope('published', { where: { published: true } });
Post.addScope('recent', { order: [['createdAt', 'DESC']], limit: 10 });

const recentPublished = await Post.scope(['published', 'recent']).findAll();

Transactions

const result = await sequelize.transaction(async (t) => {
  const user = await User.create(
    { email: 'bob@example.com', name: 'Bob' },
    { transaction: t },
  );
  const post = await Post.create(
    { title: 'Hello', authorId: user.id, published: true },
    { transaction: t },
  );
  return { user, post };
});

Raw Queries

const [results] = await sequelize.query(
  'SELECT u.name, COUNT(p.id) as "postCount" FROM users u LEFT JOIN posts p ON p."authorId" = u.id GROUP BY u.id HAVING COUNT(p.id) > :min',
  { replacements: { min: 5 }, type: QueryTypes.SELECT },
);

Core Philosophy

Sequelize is a traditional Active Record-style ORM that maps database tables to JavaScript classes. Each model class represents a table, each instance represents a row, and methods on the class and instance provide CRUD operations. This pattern is straightforward and intuitive for developers coming from Rails, Django, or Laravel ORMs. The model definition is both the schema declaration and the query interface, which keeps related concerns together but can lead to bloated model files if not managed carefully.

The association system is Sequelize's most powerful and most error-prone feature. hasOne, hasMany, belongsTo, and belongsToMany define the relationships between models, and these relationships determine how include (eager loading), cascading deletes, and foreign key constraints work. The critical detail is the as alias: it must match exactly between the association definition and the include option in queries. A mismatch silently returns no related data, which is one of the most common Sequelize debugging frustrations.

Sequelize requires more explicit ceremony than newer ORMs. Transactions must be passed as an option to every operation inside the transaction block — they are not automatically scoped. Validation must be enabled explicitly on update operations. Indexes defined in models are only created by sync(), so production systems must duplicate them in migrations. This explicitness has a benefit (nothing happens implicitly, so behavior is predictable) but demands discipline. Teams that skip the ceremony — omitting transaction options, forgetting runValidators, or using sync in production — encounter subtle bugs that are difficult to diagnose.

Anti-Patterns

• Using sync({ force: true }) or sync({ alter: true }) in production — force drops and recreates tables, destroying all data. alter attempts to match the model to the table but can drop columns. Both are acceptable only in development. Production schema changes must go through migrations.

• Forgetting { transaction: t } on operations inside a transaction — Sequelize does not automatically bind operations to the enclosing transaction. Omitting the transaction option causes the operation to run outside the transaction, breaking atomicity silently.

• Mismatched as aliases between associations and includes — if the association defines as: 'posts' but the include uses as: 'userPosts', Sequelize returns no related data without any error or warning. Always verify that aliases match exactly.

• Using bulkCreate without { validate: true } — by default, bulkCreate skips model validation for performance. Invalid data is written to the database without any error, and the bug surfaces only when reading or processing the data later.

• Using deprecated string operators instead of Op symbols — legacy string operators ($like, $gte) are deprecated and can be exploited for operator injection attacks. Always use the Op symbol imports (Op.like, Op.gte) from the sequelize package.

Best Practices

• Never use sync({ force: true }) in production — it drops and recreates tables. Use migrations for all schema changes.
• Use TypeScript model types (InferAttributes, InferCreationAttributes) for proper type safety in Sequelize v6+.
• Pass validate: true to bulkCreate — by default it skips validation for performance.
• Always pass the transaction option to every query inside a transaction block; Sequelize does not auto-bind transactions.
• Define indexes in both models and migrations — model indexes are only used with sync(), so production relies on migration indexes.
• Use scopes for reusable query fragments instead of duplicating where clauses.

Common Pitfalls

• Missing as in includes: The as alias in include must match the alias used in the association definition, or the query silently returns no related data.
• Forgetting transaction parameter: Unlike some ORMs, Sequelize requires explicitly passing { transaction: t } to each operation inside a transaction.
• bulkCreate skipping validation: Without { validate: true }, bulkCreate bypasses model validations and hooks.
• Eager loading N+1: Using include with deeply nested associations can generate huge joins. Use separate: true for large has-many relations to split into separate queries.
• Op symbol imports: Using string operators (e.g., $like) is deprecated. Always use Op.like, Op.gte, etc. from sequelize.
• Paranoid deletes confusion: With paranoid: true, destroy only sets deletedAt. Queries automatically exclude soft-deleted rows unless you pass { paranoid: false }.