agents-inc/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 - Instance setup, model definitions, TypeScript patterns, CRUD
• examples/associations.md - Association types, eager loading, alias patterns
• examples/transactions.md - Managed/unmanaged transactions, CLS, error handling
• examples/advanced.md - Scopes, hooks, paranoid mode, raw queries, operators, migrations
• 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 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 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: 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 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.

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

// 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 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.

const result = await sequelize.transaction(async (t) => {
  const user = await User.create(
    { email: "[email protected]", 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

// BAD: Forgetting to pass transaction
await sequelize.transaction(async (t) => {
  const user = await User.create({ email: "[email protected]" }); // 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 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.

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

All code must follow project conventions in CLAUDE.md

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