Claude Skill

api-database-sequelize

Sequelize ORM, model definitions, associations, queries, transactions, migrations

LLM Mart · 0 points · 2 views 0 listing impressions 0 install-command copies
Download agents-inc-skills-dist_plugins_api-database-sequelize_skills_api-database-sequelize-707467a.zip · 24 KB

Install

skills CLI npx skills add https://github.com/agents-inc/skills/tree/main/dist/plugins/api-database-sequelize/skills/api-database-sequelize
Claude Code claude plugin marketplace add https://llmmart.ai/marketplace.json && claude plugin install agents-inc-skills@llmmart
Git git clone https://github.com/agents-inc/skills.git

The skills CLI installs just this skill, for any of its supported agents. Claude Code installs the whole agents-inc/skills collection as a plugin from our marketplace. Git is the plain clone.

Skill manifest

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:




<red_flags>

RED FLAGS

High Priority Issues:

  • Using model properties without declare --- TypeScript emits class fields that override Sequelize getters/setters, causing silent data corruption
  • Forgetting { transaction: t } on queries inside transaction callbacks --- operations run outside the transaction and skip rollback
  • N+1 queries in loops --- use include to eager load associations in a single query
  • Mismatched as alias between association definition and include --- silently returns null for the association

Medium Priority Issues:

  • Using paranoid: true with timestamps: false --- paranoid mode silently does nothing without timestamps
  • Defining association without as then using as in include --- Sequelize cannot find the association
  • Not defining both sides of an association --- only the model that calls hasMany/belongsTo gets accessor methods
  • Missing foreignKey on associations --- Sequelize auto-generates names that may not match your database columns
  • Using findAll without limit in production --- unbounded queries can crash the server

Gotchas & Edge Cases:

  • bulkCreate/update/destroy (static) do NOT fire individual hooks (beforeCreate, afterUpdate) by default --- pass { individualHooks: true } to enable (performance cost: loads all instances into memory)
  • defaultScope is applied to ALL queries including findByPk --- use .unscoped() when you need unfiltered access
  • Scopes with where on the same field overwrite (not AND) by default --- enable whereMergeStrategy: 'and' for combining
  • required: true on include converts LEFT JOIN to INNER JOIN --- parent records without the association are excluded
  • save() on a parent does NOT cascade to eager-loaded children --- save each child individually
  • belongsToMany through junction table data is accessible via record.JunctionModel but easy to miss
  • Op.not in v6 sometimes produces unexpected SQL depending on dialect --- test complex operator combinations
  • Sequelize pluralizes table names by default (User -> Users) --- always set explicit tableName
  • BIGINT and DECIMAL return strings in JavaScript, not numbers --- parse them at your boundary
  • afterCommit hook only fires on successful commit, not on rollback --- don't use it for cleanup that must always run
  • findOrCreate can fail with race conditions if no unique constraint exists on the where field
  • upsert returns [instance, created] but created is unreliable on some dialects (MySQL/SQLite may always return true or null)
  • Paranoid findAll with where on included paranoid models may unexpectedly return soft-deleted items
  • Not calling sequelize.close() on shutdown leaks connections from the pool

</red_flags>


<critical_reminders>

CRITICAL REMINDERS

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

Failure to follow these rules will cause silent data corruption, broken transactions, N+1 performance degradation, and missing association data.

</critical_reminders>

Files (skills)
  • examples
    • advanced.md 14.7 KB
      Loading…
    • associations.md 10.7 KB
      Loading…
    • core.md 12.5 KB
      Loading…
    • transactions.md 10.3 KB
      Loading…
  • reference.md 10 KB
    Loading…
  • SKILL.md 15 KB
    ---
    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.
    
    ```typescript
    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
    
    ```typescript
    // 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.
    
    ```typescript
    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
    
    ```typescript
    // 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.
    
    ```typescript
    // 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
    
    ```typescript
    // BAD: No alias, then trying to include with one
    User.hasMany(Post, { foreignKey: "authorId" });
    // Later:
    User.findAll({ include: { model: Post, as: "posts" } }); // Error or null!
    ```
    
    **Why bad:** If you define the association without `as`, you cannot use `as` in `include` --- Sequelize won't find the association. The alias must match exactly between definition and query.
    
    > See [examples/associations.md](examples/associations.md) for all association types, eager loading, and the include alias contract.
    
    ---
    
    ### Pattern 4: Eager Loading with Include
    
    Fetch related models in a single query to avoid N+1.
    
    ```typescript
    const DEFAULT_PAGE_SIZE = 20;
    
    // Include with alias (must match association definition)
    const users = await User.findAll({
      include: [{ model: Post, as: "posts" }],
      limit: DEFAULT_PAGE_SIZE,
    });
    
    // Nested includes
    const posts = await Post.findAll({
      include: [
        {
          model: User,
          as: "author",
          include: [{ model: Profile, as: "profile" }],
        },
        { model: Tag, as: "tags" },
      ],
    });
    ```
    
    **Why good:** Single query with JOINs, nested includes for deep relations, alias matches definition
    
    ```typescript
    // BAD: N+1 query pattern
    const users = await User.findAll();
    for (const user of users) {
      const posts = await Post.findAll({ where: { authorId: user.id } }); // N queries!
    }
    ```
    
    **Why bad:** 1 query for users + N queries for posts, performance degrades linearly with record count
    
    > See [examples/associations.md](examples/associations.md) for required includes (INNER JOIN), separate queries, and filtering included models.
    
    ---
    
    ### Pattern 5: Transactions (Managed)
    
    Prefer managed transactions --- Sequelize auto-commits on success and auto-rolls back on thrown errors.
    
    ```typescript
    const result = await sequelize.transaction(async (t) => {
      const user = await User.create(
        { email: "alice@example.com", name: "Alice" },
        { transaction: t },
      );
    
      await Profile.create(
        { userId: user.id, bio: "Developer" },
        { transaction: t },
      );
    
      return user;
    });
    // result is the return value of the callback
    ```
    
    **Why good:** Auto-commit/rollback, clean error propagation, return value passed through
    
    ```typescript
    // BAD: Forgetting to pass transaction
    await sequelize.transaction(async (t) => {
      const user = await User.create({ email: "a@b.com" }); // Missing { transaction: t }!
      await Profile.create({ userId: user.id }, { transaction: t });
    });
    ```
    
    **Why bad:** `User.create` runs outside the transaction --- if `Profile.create` fails and rolls back, the user record persists, leaving inconsistent data
    
    > See [examples/transactions.md](examples/transactions.md) for unmanaged transactions, CLS auto-pass, and isolation levels.
    
    ---
    
    ### Pattern 6: Paranoid Mode (Soft Deletes)
    
    Paranoid mode sets `deletedAt` instead of deleting the row. Requires `timestamps: true`.
    
    ```typescript
    export class Post extends Model<
      InferAttributes<Post>,
      InferCreationAttributes<Post>
    > {
      declare id: CreationOptional<number>;
      declare title: string;
      declare deletedAt: CreationOptional<Date | null>;
      // ...
    }
    
    Post.init(
      {
        id: { type: DataTypes.INTEGER, autoIncrement: true, primaryKey: true },
        title: { type: DataTypes.STRING, allowNull: false },
      },
      { sequelize, tableName: "posts", paranoid: true },
    );
    
    // Soft delete --- sets deletedAt
    await post.destroy();
    
    // Hard delete --- actually removes the row
    await post.destroy({ force: true });
    
    // Restore soft-deleted record
    await post.restore();
    
    // Include soft-deleted records in queries
    const allPosts = await Post.findAll({ paranoid: false });
    ```
    
    **Why good:** `paranoid: true` enables soft deletes, `force: true` for hard delete escape hatch, `paranoid: false` in queries to include deleted records, `restore()` to undo
    
    > See [examples/advanced.md](examples/advanced.md) for paranoid mode with eager loading gotchas.
    
    </patterns>
    
    ---
    
    <red_flags>
    
    ## RED FLAGS
    
    **High Priority Issues:**
    
    - Using model properties without `declare` --- TypeScript emits class fields that override Sequelize getters/setters, causing silent data corruption
    - Forgetting `{ transaction: t }` on queries inside transaction callbacks --- operations run outside the transaction and skip rollback
    - N+1 queries in loops --- use `include` to eager load associations in a single query
    - Mismatched `as` alias between association definition and `include` --- silently returns `null` for the association
    
    **Medium Priority Issues:**
    
    - Using `paranoid: true` with `timestamps: false` --- paranoid mode silently does nothing without timestamps
    - Defining association without `as` then using `as` in `include` --- Sequelize cannot find the association
    - Not defining both sides of an association --- only the model that calls `hasMany`/`belongsTo` gets accessor methods
    - Missing `foreignKey` on associations --- Sequelize auto-generates names that may not match your database columns
    - Using `findAll` without `limit` in production --- unbounded queries can crash the server
    
    **Gotchas & Edge Cases:**
    
    - `bulkCreate`/`update`/`destroy` (static) do NOT fire individual hooks (`beforeCreate`, `afterUpdate`) by default --- pass `{ individualHooks: true }` to enable (performance cost: loads all instances into memory)
    - `defaultScope` is applied to ALL queries including `findByPk` --- use `.unscoped()` when you need unfiltered access
    - Scopes with `where` on the same field **overwrite** (not AND) by default --- enable `whereMergeStrategy: 'and'` for combining
    - `required: true` on `include` converts LEFT JOIN to INNER JOIN --- parent records without the association are excluded
    - `save()` on a parent does NOT cascade to eager-loaded children --- save each child individually
    - `belongsToMany` `through` junction table data is accessible via `record.JunctionModel` but easy to miss
    - `Op.not` in v6 sometimes produces unexpected SQL depending on dialect --- test complex operator combinations
    - Sequelize pluralizes table names by default (`User` -> `Users`) --- always set explicit `tableName`
    - `BIGINT` and `DECIMAL` return strings in JavaScript, not numbers --- parse them at your boundary
    - `afterCommit` hook only fires on successful commit, not on rollback --- don't use it for cleanup that must always run
    - `findOrCreate` can fail with race conditions if no unique constraint exists on the `where` field
    - `upsert` returns `[instance, created]` but `created` is unreliable on some dialects (MySQL/SQLite may always return `true` or `null`)
    - Paranoid `findAll` with `where` on included paranoid models may unexpectedly return soft-deleted items
    - Not calling `sequelize.close()` on shutdown leaks connections from the pool
    
    </red_flags>
    
    ---
    
    <critical_reminders>
    
    ## CRITICAL REMINDERS
    
    **(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)**
    
    **Failure to follow these rules will cause silent data corruption, broken transactions, N+1 performance degradation, and missing association data.**
    
    </critical_reminders>
    

Comments (0)

Sign in to join the conversation.

No comments yet.

Reviews (0)

No reviews yet.