/api-database-sequelize

name: api-database-sequelize
description: Sequelize ORM, model definitions, associations, queries, transactions, migrations

Database with Sequelize ORM

> **Quick Guide:** Sequelize is a promise-based ORM for PostgreSQL, MySQL, MariaDB, SQLite, and MS SQL Server. Use class-based models with `Model.init()` (v6) or decorators (v7) for type-safe definitions. Always use `InferAttributes`/`InferCreationAttributes` with `declare` for TypeScript models. Use `include` for eager loading to avoid N+1. Prefer managed transactions (auto-commit/rollback). Association alias (`as`) must match between definition and `include`. Paranoid mode requires `timestamps: true`. v7 is alpha --- most production code uses v6.

---

<critical_requirements>

CRITICAL: Before Using This Skill

**(You MUST use `declare` on all model class properties to prevent TypeScript from emitting class fields that conflict with Sequelize's internal attribute storage)**

**(You MUST pass `{ transaction: t }` to every query inside a transaction callback --- missing this causes operations to run outside the transaction and skip rollback)**

**(You MUST use `include` for eager loading related models --- fetching associations in loops creates N+1 query problems)**

**(You MUST match the `as` alias in `include` with the alias used in the association definition --- mismatches silently return `null` for the association)**

</critical_requirements>

---

**Auto-detection:** sequelize, Sequelize, Model.init, DataTypes, InferAttributes, InferCreationAttributes, CreationOptional, belongsTo, hasMany, hasOne, belongsToMany, findAll, findByPk, Op.and, Op.or, sequelize-cli, queryInterface, paranoid

**When to use:**

• SQL database access with model-based ORM (PostgreSQL, MySQL, MariaDB, SQLite, MSSQL)
• Projects needing fine-grained control over generated SQL and query composition
• Legacy codebases already using Sequelize
• Applications needing raw SQL escape hatches alongside ORM queries

**When NOT to use:**

• Greenfield TypeScript projects wanting schema-first design with auto-generated types
• Edge/serverless with cold-start sensitivity (Sequelize has heavy initialization)
• Projects needing auto-generated TypeScript types from schema (Sequelize types are manual)

**Key patterns covered:**

• Model definitions with TypeScript (InferAttributes, CreationOptional, declare)
• Associations (hasOne, hasMany, belongsTo, belongsToMany) and alias gotchas
• Eager loading (include), lazy loading, and N+1 prevention
• Transactions (managed vs unmanaged) and CLS auto-pass
• Scopes (defaultScope, named scopes, merging behavior)
• Paranoid mode (soft deletes) and its interaction with queries
• Hooks/lifecycle and their bulk operation gaps
• Migrations with queryInterface
• Raw queries and operators (Op)

**Detailed Resources:**

• [examples/core.md](examples/core.md) - Instance setup, model definitions, TypeScript patterns, CRUD
• [examples/associations.md](examples/associations.md) - Association types, eager loading, alias patterns
• [examples/transactions.md](examples/transactions.md) - Managed/unmanaged transactions, CLS, error handling
• [examples/advanced.md](examples/advanced.md) - Scopes, hooks, paranoid mode, raw queries, operators, migrations
• [reference.md](reference.md) - Decision frameworks, operator tables, hook order, anti-patterns

---

<philosophy>

Philosophy

**Sequelize** is a traditional, feature-rich ORM that maps JavaScript classes to database tables. Unlike schema-first ORMs, you define models in code and optionally generate migrations from them.

**Core principles:**

1. **Model-first design** --- Define models as classes, then sync or migrate the database 2. **Explicit over implicit** --- Associations, hooks, and scopes are declared manually 3. **SQL escape hatch** --- Raw queries available when ORM abstractions are insufficient 4. **Dialect abstraction** --- Same API across PostgreSQL, MySQL, SQLite, MariaDB, MSSQL

**v6 vs v7:**

• **v6** is the current stable release used in production. Uses `Model.init()` for model definitions.
• **v7** is in alpha. Uses decorators (`@Attribute`, `@PrimaryKey`), scoped packages (`@sequelize/core`), and CLS is enabled by default via `AsyncLocalStorage`. The CLI is not yet ready for v7.
• All examples in this skill default to **v6 patterns** with v7 differences noted where significant.

</philosophy>

---

<patterns>

Core Patterns

Pattern 1: Sequelize Instance Setup

Configure the connection with dialect, pool, and logging options.

import { Sequelize } from "sequelize";

const MIN_POOL_SIZE = 0;
const MAX_POOL_SIZE = 10;
const POOL_ACQUIRE_TIMEOUT_MS = 30000;
const POOL_IDLE_TIMEOUT_MS = 10000;

export const sequelize = new Sequelize({
  dialect: "postgres",
  host: process.env.DB_HOST,
  port: Number(process.env.DB_PORT),
  database: process.env.DB_NAME,
  username: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  logging: process.env.NODE_ENV === "development" ? console.log : false,
  pool: {
    min: MIN_POOL_SIZE,
    max: MAX_POOL_SIZE,
    acquire: POOL_ACQUIRE_TIMEOUT_MS,
    idle: POOL_IDLE_TIMEOUT_MS,
  },
});

**Why good:** Named constants for pool config, conditional logging, explicit pool sizing

// BAD: Connection string with no pool config
const sequelize = new Sequelize("postgres://user:pass@localhost:5432/db");

**Why bad:** Default pool settings may exhaust connections under load, no logging control

> See [examples/core.md](examples/core.md) for connection URI patterns and graceful shutdown.

---

Pattern 2: Model Definition with TypeScript

Use `InferAttributes`, `InferCreationAttributes`, and `declare` for type-safe models.

import {
  Model,
  DataTypes,
  type InferAttributes,
  type InferCreationAttributes,
  type CreationOptional,
} from "sequelize";
import { sequelize } from "./connection";

export class User extends Model<
  InferAttributes<User>,
  InferCreationAttributes<User