api-database-sequelize

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

All code must follow project conventions in CLAUDE.md (kebab-case, named exports, import ordering, import type, named constants)

(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>
> {
  declare id: CreationOptional<number>;
  declare email: string;
  declare name: string | null;
  declare role: CreationOptional<string>;
  declare createdAt: CreationOptional<Date>;
  declare updatedAt: CreationOptional<Date>;
}

User.init(
  {
    id: { type: DataTypes.INTEGER, autoIncrement: true, primaryKey: true },
    email: { type: DataTypes.STRING, allowNull: false, unique: true },
    name: { type: DataTypes.STRING, allowNull: true },
    role: { type: DataTypes.STRING, allowNull: false, defaultValue: "user" },
    createdAt: DataTypes.DATE,
    updatedAt: DataTypes.DATE,
  },
  { sequelize, tableName: "users" },
);

Why good: declare prevents TS from emitting class fields, CreationOptional marks auto-generated fields, explicit tableName avoids pluralization surprises

// BAD: Missing declare keyword
export class User extends Model {
  id!: number; // Emitted as class field, conflicts with Sequelize internals
  email!: string;
}

Why bad: Without declare, TypeScript emits class fields that override Sequelize's internal getters/setters, causing silent data loss

See [examples/core.md](examples/core.md) for association mixin typing and NonAttribute usage.

Pattern 3: Associations

Define relationships between models. The as alias is critical for eager loading.

// One-to-Many: User has many Posts
User.hasMany(Post, { foreignKey: "authorId", as: "posts" });
Post.belongsTo(User, { foreignKey: "authorId", as: "author" });

// Many-to-Many: Post has many Tags through PostTag
Post.belongsToMany(Tag, { through: PostTag, foreignKey: "postId", as: "tags" });
Tag.belongsToMany(Post, { through: PostTag, foreignKey: "tagId", as: "posts" });

Why good: Explicit foreignKey prevents naming ambiguity, as enables clean eager loading

// BAD: No alias, then trying to include with one
User.hasMany(Post, { foreignKey: "authorId" });
// Later:
User.findAll({ include: { model: Po