# Sequelize - Advanced Examples

> Scopes, hooks, paranoid mode, raw queries, operators, and migrations. See [SKILL.md](../SKILL.md) for core concepts.

**Prerequisites**: Understand model definitions from [core.md](core.md) and associations from [associations.md](associations.md) first.

---

## Scopes

### Good Example - Default and Named Scopes

```typescript
import { Op } from "sequelize";

const RECENT_DAYS = 30;

Post.init(
  {
    /* attributes */
  },
  {
    sequelize,
    tableName: "posts",
    defaultScope: {
      where: { published: true }, // Applied to ALL queries by default
    },
    scopes: {
      // Static scope
      drafts: {
        where: { published: false },
      },
      // Function scope with parameter
      byAuthor(authorId: number) {
        return { where: { authorId } };
      },
      // Scope with includes
      withAuthor: {
        include: [{ model: User, as: "author" }],
      },
      // Dynamic scope
      recent: {
        where: {
          createdAt: {
            [Op.gte]: new Date(Date.now() - RECENT_DAYS * 24 * 60 * 60 * 1000),
          },
        },
        order: [["createdAt", "DESC"]],
      },
    },
  },
);

// Usage:
const published = await Post.findAll(); // defaultScope applies
const drafts = await Post.scope("drafts").findAll(); // Named scope
const both = await Post.scope("drafts", "withAuthor").findAll(); // Combined
const unscoped = await Post.unscoped().findAll(); // No scope at all

// Function scope with arguments
const authorPosts = await Post.scope({
  method: ["byAuthor", userId],
}).findAll();
```

**Why good:** `defaultScope` for common filters, named scopes for reusable query presets, function scopes for dynamic parameters, `unscoped()` escape hatch

### Scope Gotchas

```typescript
// GOTCHA 1: defaultScope applies to findByPk too!
const post = await Post.findByPk(123);
// This only returns the post if published: true!
// To get any post by PK:
const post = await Post.unscoped().findByPk(123);

// GOTCHA 2: Same-field where clauses OVERWRITE, not AND
Post.init(
  {},
  {
    sequelize,
    scopes: {
      published: { where: { status: "published" } },
      featured: { where: { status: "featured" } }, // This REPLACES status, not ANDs
    },
  },
);
const posts = await Post.scope("published", "featured").findAll();
// WHERE status = 'featured' --- NOT WHERE status = 'published' AND status = 'featured'

// Fix: Use whereMergeStrategy (v6.18+)
Post.init(
  {},
  {
    sequelize,
    whereMergeStrategy: "and",
    scopes: {
      /* same scopes */
    },
  },
);
// Now: WHERE status = 'published' AND status = 'featured'
```

---

## Hooks (Lifecycle Events)

### Good Example - Common Hook Patterns

```typescript
User.init(
  {
    /* attributes */
  },
  {
    sequelize,
    tableName: "users",
    hooks: {
      // Hash password before create/update
      beforeCreate: async (user) => {
        if (user.changed("password")) {
          user.password = await hashPassword(user.password);
        }
      },
      beforeUpdate: async (user) => {
        if (user.changed("password")) {
          user.password = await hashPassword(user.password);
        }
      },
      // Normalize email
      beforeValidate: (user) => {
        if (user.email) {
          user.email = user.email.toLowerCase().trim();
        }
      },
    },
  },
);
```

**Why good:** `changed("field")` prevents unnecessary re-hashing, `beforeValidate` for normalization, `beforeCreate`/`beforeUpdate` for transforms

### Good Example - Hooks with Transactions

```typescript
Post.addHook("afterCreate", async (post, options) => {
  // CRITICAL: Pass the transaction from options
  await AuditLog.create(
    {
      action: "post_created",
      entityId: post.id,
      entityType: "Post",
    },
    { transaction: options.transaction }, // Use the caller's transaction!
  );
});
```

**Why good:** `options.transaction` ensures audit log is part of the same transaction --- rolls back together if something fails

**Gotcha:** If you omit `{ transaction: options.transaction }`, the audit log runs on a separate connection. If the parent transaction rolls back, the audit log persists as orphan data.

### Bulk Operations and Hooks

```typescript
// By default, bulkCreate does NOT fire individual hooks
await User.bulkCreate(users); // Only fires beforeBulkCreate / afterBulkCreate

// Enable individual hooks (performance cost: loads all into memory)
await User.bulkCreate(users, { individualHooks: true });
// Now fires: beforeValidate, afterValidate, beforeCreate, afterCreate for EACH record

// Same for update/destroy
await User.update(
  { role: "user" },
  {
    where: { role: "guest" },
    individualHooks: true, // Fires beforeUpdate/afterUpdate per record
  },
);
```

**Why good:** Explicit about when individual hooks fire, `individualHooks: true` when you need per-record behavior

**Gotcha:** `individualHooks: true` fetches ALL matching records into memory, then processes each one. On large datasets this can be very slow and memory-intensive.

---

## Paranoid Mode (Soft Deletes)

### Good Example - Setup and Usage

```typescript
Post.init(
  {
    id: { type: DataTypes.INTEGER, autoIncrement: true, primaryKey: true },
    title: { type: DataTypes.STRING, allowNull: false },
    content: { type: DataTypes.TEXT },
  },
  {
    sequelize,
    tableName: "posts",
    paranoid: true, // Enables soft delete
    timestamps: true, // REQUIRED for paranoid to work
    // deletedAt column is auto-created
  },
);

// Soft delete - sets deletedAt to current timestamp
await post.destroy();

// Hard delete - actually removes the row
await post.destroy({ force: true });

// Restore soft-deleted record
await post.restore();

// Static restore with conditions
await Post.restore({ where: { authorId: userId } });

// Normal queries exclude soft-deleted records
const posts = await Post.findAll(); // Only non-deleted

// Include soft-deleted records
const allPosts = await Post.findAll({ paranoid: false });
```

**Why good:** `paranoid: true` for soft deletes, `force: true` escape hatch, `restore()` to undo, `paranoid: false` in queries to see deleted records

### Paranoid Gotchas

```typescript
// GOTCHA 1: paranoid requires timestamps
Post.init(
  {},
  {
    sequelize,
    paranoid: true,
    timestamps: false, // Silently breaks paranoid!
  },
);
// destroy() will actually DELETE the row, not soft-delete

// GOTCHA 2: Eager loading paranoid models
const user = await User.findByPk(userId, {
  include: [
    {
      model: Post,
      as: "posts",
      // By default, soft-deleted posts are excluded from include too
      // To include them:
      paranoid: false,
    },
  ],
});

// GOTCHA 3: where on included paranoid models may return deleted records
// This is a known Sequelize issue --- test carefully when combining
// where clauses with paranoid includes
```

---

## Raw Queries

### Good Example - When ORM Isn't Enough

```typescript
import { QueryTypes } from "sequelize";

// SELECT with typed results
interface UserStats {
  authorId: number;
  postCount: number;
  avgLength: number;
}

const stats = await sequelize.query<UserStats>(
  `SELECT author_id AS "authorId",
          COUNT(*) AS "postCount",
          AVG(LENGTH(content)) AS "avgLength"
   FROM posts
   WHERE published = true
   GROUP BY author_id
   HAVING COUNT(*) > :minPosts
   ORDER BY "postCount" DESC`,
  {
    replacements: { minPosts: 5 },
    type: QueryTypes.SELECT,
  },
);
```

**Why good:** Complex aggregation not easily expressed in ORM, `replacements` for SQL injection prevention, `QueryTypes.SELECT` returns typed array

### Good Example - Bind Parameters vs Replacements

```typescript
// Replacements: Escaped and inserted into query string
const users = await sequelize.query(
  "SELECT * FROM users WHERE email = :email AND role = :role",
  {
    replacements: { email: "alice@example.com", role: "admin" },
    type: QueryTypes.SELECT,
  },
);

// Bind parameters: Sent separately to database (more secure, better for repeated queries)
const users = await sequelize.query(
  "SELECT * FROM users WHERE email = $email AND role = $role",
  {
    bind: { email: "alice@example.com", role: "admin" },
    type: QueryTypes.SELECT,
  },
);
```

**Why good:** Both prevent SQL injection, bind parameters are sent separately from query (better for prepared statements)

---

## Operators (Op)

### Good Example - Complex Where Clauses

```typescript
import { Op } from "sequelize";

const SEARCH_LIMIT = 50;

// Combined AND/OR
const posts = await Post.findAll({
  where: {
    [Op.and]: [
      { published: true },
      {
        [Op.or]: [
          { title: { [Op.iLike]: `%${search}%` } },
          { content: { [Op.iLike]: `%${search}%` } },
        ],
      },
    ],
  },
  limit: SEARCH_LIMIT,
});

// Range queries
const recentPosts = await Post.findAll({
  where: {
    createdAt: { [Op.between]: [startDate, endDate] },
    viewCount: { [Op.gte]: 100 },
  },
});

// NOT and NULL handling
const activePosts = await Post.findAll({
  where: {
    deletedAt: { [Op.is]: null },
    status: { [Op.notIn]: ["draft", "archived"] },
  },
});

// Subquery
const popularAuthors = await User.findAll({
  where: {
    id: {
      [Op.in]: sequelize.literal(
        "(SELECT DISTINCT author_id FROM posts WHERE view_count > 1000)",
      ),
    },
  },
});
```

**Why good:** `Op.and`/`Op.or` for composable conditions, `Op.iLike` for case-insensitive (PostgreSQL), `Op.is` for NULL checks, `sequelize.literal` for subqueries

---

## Migrations with queryInterface

### Good Example - Creating a Table

```typescript
// migrations/20240101-create-users.ts
import type { QueryInterface } from "sequelize";
import { DataTypes } from "sequelize";

export const up = async (queryInterface: QueryInterface) => {
  await queryInterface.createTable("users", {
    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.ENUM("user", "admin", "moderator"),
      allowNull: false,
      defaultValue: "user",
    },
    created_at: {
      type: DataTypes.DATE,
      allowNull: false,
      defaultValue: DataTypes.NOW,
    },
    updated_at: {
      type: DataTypes.DATE,
      allowNull: false,
      defaultValue: DataTypes.NOW,
    },
  });

  await queryInterface.addIndex("users", ["email"], { unique: true });
};

export const down = async (queryInterface: QueryInterface) => {
  await queryInterface.dropTable("users");
};
```

**Why good:** Explicit column types, snake_case column names in migration, separate index creation, reversible with `down`

### Good Example - Adding Columns and Indexes

```typescript
export const up = async (queryInterface: QueryInterface) => {
  // Add column
  await queryInterface.addColumn("posts", "slug", {
    type: DataTypes.STRING,
    allowNull: true,
    unique: true,
  });

  // Backfill data
  await queryInterface.sequelize.query(
    `UPDATE posts SET slug = LOWER(REPLACE(title, ' ', '-')) WHERE slug IS NULL`,
  );

  // Make non-nullable after backfill
  await queryInterface.changeColumn("posts", "slug", {
    type: DataTypes.STRING,
    allowNull: false,
    unique: true,
  });

  // Add composite index
  await queryInterface.addIndex(
    "posts",
    ["author_id", "published", "created_at"],
    {
      name: "idx_posts_author_published_date",
    },
  );
};

export const down = async (queryInterface: QueryInterface) => {
  await queryInterface.removeIndex("posts", "idx_posts_author_published_date");
  await queryInterface.removeColumn("posts", "slug");
};
```

**Why good:** Add nullable, backfill, then make non-nullable prevents migration failure on existing data, named indexes for clear down path

### Migration Gotchas

```typescript
// GOTCHA: queryInterface does NOT fire model hooks
// If your beforeCreate hook hashes passwords, manually hash in the migration:
await queryInterface.bulkInsert("users", [
  { email: "admin@example.com", password: await hashPassword("admin123") },
]);

// GOTCHA: ENUM changes require raw SQL on PostgreSQL
await queryInterface.sequelize.query(
  `ALTER TYPE "enum_users_role" ADD VALUE 'editor'`,
);
// sequelize-cli cannot add/remove enum values with queryInterface alone

// GOTCHA: sequelize-cli uses CommonJS --- if your project is ESM,
// you may need a separate tsconfig or .cjs migration files
```

---

## Quick Reference

### Scope API

| Method                                   | What It Does                        |
| ---------------------------------------- | ----------------------------------- |
| `Model.scope("name")`                    | Apply named scope                   |
| `Model.scope("a", "b")`                  | Combine scopes                      |
| `Model.scope({ method: ["name", arg] })` | Function scope with args            |
| `Model.unscoped()`                       | Remove all scopes                   |
| `Model.scope("defaultScope", "other")`   | Re-add default scope after override |

### Hook Execution

| Hook                                     | Fires On                  | Bulk Default                |
| ---------------------------------------- | ------------------------- | --------------------------- |
| `beforeValidate` / `afterValidate`       | create, update (instance) | Only with `individualHooks` |
| `beforeCreate` / `afterCreate`           | create (instance)         | Only with `individualHooks` |
| `beforeUpdate` / `afterUpdate`           | update (instance)         | Only with `individualHooks` |
| `beforeDestroy` / `afterDestroy`         | destroy (instance)        | Only with `individualHooks` |
| `beforeBulkCreate` / `afterBulkCreate`   | bulkCreate                | Always                      |
| `beforeBulkUpdate` / `afterBulkUpdate`   | Model.update (static)     | Always                      |
| `beforeBulkDestroy` / `afterBulkDestroy` | Model.destroy (static)    | Always                      |

### queryInterface Methods

| Method                              | Use When                      |
| ----------------------------------- | ----------------------------- |
| `createTable(name, columns)`        | New table                     |
| `dropTable(name)`                   | Remove table                  |
| `addColumn(table, column, type)`    | New column                    |
| `removeColumn(table, column)`       | Remove column                 |
| `changeColumn(table, column, type)` | Alter column type/constraints |
| `renameColumn(table, old, new)`     | Rename column                 |
| `addIndex(table, fields, opts)`     | New index                     |
| `removeIndex(table, name)`          | Remove index                  |
| `bulkInsert(table, data)`           | Seed data                     |
| `bulkDelete(table, where)`          | Remove data                   |

# Sequelize - Association Examples

> Association types, eager loading, alias patterns, and N+1 prevention. See [SKILL.md](../SKILL.md) for core concepts.

**Prerequisites**: Understand model definition patterns from [core.md](core.md) first.

---

## Association Setup

### Good Example - Complete Bidirectional Setup

```typescript
// models/index.ts - Define all associations in one place AFTER all models are imported
import { User } from "./user";
import { Post } from "./post";
import { Tag } from "./tag";
import { PostTag } from "./post-tag";
import { Profile } from "./profile";
import { Comment } from "./comment";

// One-to-One: User has one Profile
User.hasOne(Profile, { foreignKey: "userId", as: "profile" });
Profile.belongsTo(User, { foreignKey: "userId", as: "user" });

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

// One-to-Many: Post has many Comments
Post.hasMany(Comment, { foreignKey: "postId", as: "comments" });
Comment.belongsTo(Post, { foreignKey: "postId", as: "post" });

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

export { User, Post, Tag, PostTag, Profile, Comment };
```

**Why good:** All associations in one file prevents circular import issues, explicit `foreignKey` on both sides, `as` alias on every association, both directions defined

### Bad Example - Partial Association

```typescript
// BAD: Only one side defined
User.hasMany(Post, { foreignKey: "authorId" });
// Missing: Post.belongsTo(User, ...)
// Now Post has no `getAuthor()` method and can't eager load User from Post
```

**Why bad:** Only `User` gets `getPosts()`, `addPost()` etc. `Post` has no `getAuthor()` accessor, and you can't include `User` when querying `Post`

---

## The Alias Contract

### Good Example - Alias Must Match Everywhere

```typescript
// Definition: as: "posts"
User.hasMany(Post, { foreignKey: "authorId", as: "posts" });

// Query: as: "posts" matches
const user = await User.findOne({
  where: { id: userId },
  include: [{ model: Post, as: "posts" }],
});

// Accessor methods also use the alias
const posts = await user.getPosts();
await user.addPost(newPost);
const count = await user.countPosts();
```

**Why good:** `as: "posts"` is consistent across definition, include, and accessor methods

### Bad Example - Alias Mismatch

```typescript
// Definition uses "posts"
User.hasMany(Post, { foreignKey: "authorId", as: "posts" });

// Query uses different alias - FAILS
const user = await User.findOne({
  include: [{ model: Post, as: "articles" }], // "articles" !== "posts"
  // Error: Post is not associated to User using alias "articles"
});

// Also fails: no alias when one was defined
const user = await User.findOne({
  include: [{ model: Post }], // Missing as: "posts"
  // Error: Post is associated to User using alias "posts"
  // You must specify the alias
});
```

**Why bad:** Once you define `as` in the association, ALL references (includes, accessors) must use the same alias

---

## Eager Loading Patterns

### Good Example - Basic Include

```typescript
// Single include
const user = await User.findByPk(userId, {
  include: [{ model: Profile, as: "profile" }],
});
// user.profile is Profile | null

// Multiple includes
const user = await User.findByPk(userId, {
  include: [
    { model: Profile, as: "profile" },
    { model: Post, as: "posts" },
  ],
});
// user.profile, user.posts both available
```

**Why good:** Single query with JOINs, typed results include association data

### Good Example - Nested Includes

```typescript
// Load post -> author -> profile
const post = await Post.findByPk(postId, {
  include: [
    {
      model: User,
      as: "author",
      include: [{ model: Profile, as: "profile" }],
    },
    {
      model: Comment,
      as: "comments",
      include: [{ model: User, as: "commenter" }],
    },
    { model: Tag, as: "tags" },
  ],
});
// post.author.profile, post.comments[0].commenter, post.tags all available
```

**Why good:** Deep relation loading in single query, each level specifies its alias

---

## Filtering Included Models

### Good Example - Where on Include

```typescript
// Filter included records (still LEFT JOIN by default)
const user = await User.findByPk(userId, {
  include: [
    {
      model: Post,
      as: "posts",
      where: { published: true }, // Only include published posts
      required: false, // LEFT JOIN - user returned even with 0 posts
    },
  ],
});
```

**Why good:** `where` filters the included records, `required: false` keeps it as LEFT JOIN

**Gotcha:** When you add `where` to an include, Sequelize changes the default to `required: true` (INNER JOIN). This means the parent record is excluded if no child matches. Explicitly set `required: false` to preserve LEFT JOIN behavior.

### Good Example - Required Include (INNER JOIN)

```typescript
// Only return users who HAVE at least one published post
const activeAuthors = await User.findAll({
  include: [
    {
      model: Post,
      as: "posts",
      where: { published: true },
      required: true, // INNER JOIN - users without published posts excluded
    },
  ],
});
```

**Why good:** `required: true` acts as existence filter on parent records

---

## Separate Queries (Large Has-Many)

### Good Example - separate: true

```typescript
// When users have many posts, JOIN creates a cartesian product
// separate: true runs 2 queries instead
const users = await User.findAll({
  include: [
    {
      model: Post,
      as: "posts",
      separate: true, // 2 queries instead of JOIN
      order: [["createdAt", "DESC"]], // Order within the separate query
      limit: 10, // Limit per user
    },
  ],
  limit: 50,
});
```

**Why good:** Avoids cartesian product from large JOIN, allows per-user limit and ordering, much faster for large has-many relationships

**When to use:** When a parent has hundreds/thousands of child records. JOIN would return parent_count \* child_count rows.

---

## Many-to-Many Patterns

### Good Example - Through Table Operations

```typescript
// Add tags to post (connect existing records)
await post.addTags([tag1.id, tag2.id]);

// Remove a tag
await post.removeTag(tag1.id);

// Replace all tags
await post.setTags([tag3.id, tag4.id]); // Removes old, adds new

// Get tags
const tags = await post.getTags();

// Check existence
const hasTag = await post.hasTag(tag1.id);
```

**Why good:** Mixin methods handle junction table automatically, `setTags` atomically replaces all associations

### Good Example - Through Table with Extra Fields

```typescript
// Junction model with extra fields
export class PostTag extends Model<
  InferAttributes<PostTag>,
  InferCreationAttributes<PostTag>
> {
  declare postId: ForeignKey<Post["id"]>;
  declare tagId: ForeignKey<Tag["id"]>;
  declare assignedAt: CreationOptional<Date>;
  declare assignedBy: string | null;
}

PostTag.init(
  {
    assignedAt: { type: DataTypes.DATE, defaultValue: DataTypes.NOW },
    assignedBy: { type: DataTypes.STRING, allowNull: true },
  },
  { sequelize, tableName: "post_tags", timestamps: false },
);

// Access junction data in queries
const post = await Post.findByPk(postId, {
  include: [
    {
      model: Tag,
      as: "tags",
      through: { attributes: ["assignedAt", "assignedBy"] },
    },
  ],
});

// Junction data is on tag.PostTag (the through model)
for (const tag of post.tags) {
  console.log(tag.name, tag.PostTag.assignedAt);
}
```

**Why good:** Explicit junction model when join table needs extra fields, `through.attributes` controls which junction fields to fetch

---

## Self-Referencing Associations

### Good Example - Hierarchical Data

```typescript
export class Category extends Model<
  InferAttributes<Category>,
  InferCreationAttributes<Category>
> {
  declare id: CreationOptional<number>;
  declare name: string;
  declare parentId: ForeignKey<Category["id"]> | null;
  declare parent?: NonAttribute<Category>;
  declare children?: NonAttribute<Category[]>;
}

// Self-referencing: a category can have a parent and children
Category.hasMany(Category, { foreignKey: "parentId", as: "children" });
Category.belongsTo(Category, { foreignKey: "parentId", as: "parent" });

// Query tree
const roots = await Category.findAll({
  where: { parentId: null },
  include: [
    {
      model: Category,
      as: "children",
      include: [{ model: Category, as: "children" }], // Grandchildren
    },
  ],
});
```

**Why good:** Self-relation for hierarchies, filter `parentId: null` for roots, nested includes for tree depth

---

## Attributes Control

### Good Example - Select Specific Fields

```typescript
// Only fetch specific columns
const users = await User.findAll({
  attributes: ["id", "name", "email"],
  // Does NOT fetch: role, createdAt, updatedAt, etc.
});

// Exclude specific columns
const users = await User.findAll({
  attributes: { exclude: ["password", "secretToken"] },
});

// Aggregations
const stats = await Post.findAll({
  attributes: [
    "authorId",
    [sequelize.fn("COUNT", sequelize.col("id")), "postCount"],
    [sequelize.fn("MAX", sequelize.col("created_at")), "latestPost"],
  ],
  group: ["authorId"],
});
```

**Why good:** `attributes` limits columns fetched, `exclude` for hiding sensitive fields, `fn`/`col` for aggregations

---

## Quick Reference

| Pattern                                 | SQL Equivalent       | Use When                   |
| --------------------------------------- | -------------------- | -------------------------- |
| `include: [{ model: X, as: "a" }]`      | LEFT JOIN            | Load related data          |
| `include: [{ ..., required: true }]`    | INNER JOIN           | Only parents with children |
| `include: [{ ..., where: {...} }]`      | LEFT JOIN + WHERE    | Filter included records    |
| `include: [{ ..., separate: true }]`    | 2 separate queries   | Large has-many sets        |
| `include: [{ ..., attributes: [...] }]` | SELECT specific cols | Limit payload              |
| `through: { attributes: [...] }`        | Junction table cols  | M:N with extra fields      |

| Association Method                | What It Does             |
| --------------------------------- | ------------------------ |
| `getX()` / `getXs()`              | Lazy load association    |
| `setX(instance)` / `setXs([...])` | Replace association(s)   |
| `addX(instance)` / `addXs([...])` | Add to association       |
| `removeX(instance)`               | Remove from association  |
| `hasX(instance)`                  | Check if associated      |
| `countXs()`                       | Count associated records |
| `createX(data)`                   | Create and associate     |

# Sequelize - Core Examples

> Instance setup, model definitions, TypeScript patterns, and CRUD operations. See [SKILL.md](../SKILL.md) for decision guidance.

**Prerequisites**: None - these are the foundational patterns.

---

## Sequelize Instance Setup

### Good Example - Full Configuration

```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,
  },
  define: {
    underscored: true, // snake_case column names in DB
  },
});
```

**Why good:** Named constants for pool config, conditional logging, `underscored: true` for DB convention, explicit pool sizing

### Good Example - Connection URI

```typescript
export const sequelize = new Sequelize(process.env.DATABASE_URL!, {
  dialect: "postgres",
  logging: false,
  dialectOptions: {
    ssl:
      process.env.NODE_ENV === "production"
        ? { require: true, rejectUnauthorized: false }
        : false,
  },
});
```

**Why good:** URI-based for deployment platforms, SSL for production, logging off in prod

### Good Example - Graceful Shutdown

```typescript
const shutdown = async () => {
  await sequelize.close();
  process.exit(0);
};

process.on("SIGINT", shutdown);
process.on("SIGTERM", shutdown);
```

**Why good:** Drains connection pool, prevents leaked connections on restart

---

## Model Definition with TypeScript (v6)

### Good Example - Complete Model

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

export class User extends Model<
  InferAttributes<User>,
  InferCreationAttributes<User>
> {
  // CreationOptional = not required in create()
  declare id: CreationOptional<number>;
  declare email: string;
  declare name: string | null; // nullable fields are automatically optional in create()
  declare role: CreationOptional<string>;

  // Timestamps - always CreationOptional
  declare createdAt: CreationOptional<Date>;
  declare updatedAt: CreationOptional<Date>;

  // Association fields - NonAttribute to exclude from InferAttributes
  declare posts?: NonAttribute<Post[]>;

  // Association mixin methods
  declare getPosts: HasManyGetAssociationsMixin<Post>;
  declare addPost: HasManyAddAssociationMixin<Post, number>;
  declare createPost: HasManyCreateAssociationMixin<Post, "authorId">;
  declare countPosts: HasManyCountAssociationsMixin;

  // Custom instance methods
  get fullDisplayName(): NonAttribute<string> {
    return `${this.name} (${this.role})`;
  }
}

User.init(
  {
    id: {
      type: DataTypes.INTEGER,
      autoIncrement: true,
      primaryKey: true,
    },
    email: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true,
      validate: { isEmail: true },
    },
    name: {
      type: DataTypes.STRING,
      allowNull: true,
    },
    role: {
      type: DataTypes.ENUM("user", "admin", "moderator"),
      allowNull: false,
      defaultValue: "user",
    },
    createdAt: DataTypes.DATE,
    updatedAt: DataTypes.DATE,
  },
  {
    sequelize,
    tableName: "users", // Explicit - avoids pluralization guessing
    timestamps: true, // createdAt + updatedAt
    underscored: true, // created_at, updated_at in DB
  },
);
```

**Why good:** `declare` on every property, `CreationOptional` for auto-fields, `NonAttribute` for associations and getters, explicit `tableName`, typed mixin methods, inline validation

### Bad Example - Missing declare

```typescript
// BAD: TypeScript emits class fields
export class User extends Model {
  id!: number; // Non-null assertion, no declare
  email!: string; // These override Sequelize's getters
  name!: string;
}
```

**Why bad:** Without `declare`, TS emits JavaScript class field declarations that override Sequelize's internal property descriptors, causing `user.email` to return `undefined` even when the database has data

---

## Model Definition with TypeScript (v7 Alpha)

### Good Example - Decorator-Based (v7)

```typescript
import {
  Model,
  Sequelize,
  DataTypes,
  type InferAttributes,
  type InferCreationAttributes,
  type CreationOptional,
} from "@sequelize/core";
import {
  Attribute,
  PrimaryKey,
  AutoIncrement,
  NotNull,
  Default,
} from "@sequelize/core/decorators-legacy";
import { SqliteDialect } from "@sequelize/sqlite3";

export class User extends Model<
  InferAttributes<User>,
  InferCreationAttributes<User>
> {
  @Attribute(DataTypes.INTEGER)
  @PrimaryKey
  @AutoIncrement
  declare id: CreationOptional<number>;

  @Attribute(DataTypes.STRING)
  @NotNull
  declare email: string;

  @Attribute(DataTypes.STRING)
  declare name: string | null;

  @Attribute(DataTypes.STRING)
  @NotNull
  @Default("user")
  declare role: CreationOptional<string>;
}

// v7: Register models in Sequelize constructor, dialect is a class not a string
const sequelize = new Sequelize({
  dialect: SqliteDialect,
  models: [User],
});
```

**Why good:** Decorators co-locate type and schema info, scoped imports from `@sequelize/core`, models registered in constructor

**When to use:** Only in v7 alpha projects. v6 stable uses `Model.init()`.

---

## ForeignKey Typing

### Good Example - ForeignKey Brand Type

```typescript
import { type ForeignKey } from "sequelize";

export class Post extends Model<
  InferAttributes<Post>,
  InferCreationAttributes<Post>
> {
  declare id: CreationOptional<number>;
  declare title: string;
  declare content: string | null;
  declare published: CreationOptional<boolean>;

  // ForeignKey<T> tells Sequelize this is managed by associations
  declare authorId: ForeignKey<User["id"]>;

  // Association property
  declare author?: NonAttribute<User>;

  declare createdAt: CreationOptional<Date>;
  declare updatedAt: CreationOptional<Date>;
}

Post.init(
  {
    id: { type: DataTypes.INTEGER, autoIncrement: true, primaryKey: true },
    title: { type: DataTypes.STRING, allowNull: false },
    content: { type: DataTypes.TEXT, allowNull: true },
    published: {
      type: DataTypes.BOOLEAN,
      allowNull: false,
      defaultValue: false,
    },
    // authorId does NOT need to be in init() --- it's added by the association
    createdAt: DataTypes.DATE,
    updatedAt: DataTypes.DATE,
  },
  { sequelize, tableName: "posts" },
);
```

**Why good:** `ForeignKey<T>` brands the field so `InferAttributes` knows it's managed by the association, not by `init()`

---

## Read Operations

### Good Example - findByPk and findOne

```typescript
// Find by primary key - returns Model | null
const user = await User.findByPk(userId);
if (!user) throw new Error("User not found");

// Find by unique field
const userByEmail = await User.findOne({
  where: { email: "alice@example.com" },
});

// Throw if not found (rejectOnEmpty)
const user = await User.findByPk(userId, {
  rejectOnEmpty: true, // throws EmptyResultError
});
```

**Why good:** `findByPk` for primary key lookups, `findOne` for unique fields, `rejectOnEmpty` for guaranteed existence

### Good Example - findAll with Filters and Pagination

```typescript
import { Op } from "sequelize";

const DEFAULT_PAGE_SIZE = 20;
const MAX_PAGE_SIZE = 100;

interface PaginationParams {
  page?: number;
  pageSize?: number;
  search?: string;
}

const getUsers = async ({
  page = 1,
  pageSize = DEFAULT_PAGE_SIZE,
  search,
}: PaginationParams) => {
  const limit = Math.min(pageSize, MAX_PAGE_SIZE);
  const offset = (page - 1) * limit;

  const { rows, count } = await User.findAndCountAll({
    where: search
      ? {
          [Op.or]: [
            { name: { [Op.iLike]: `%${search}%` } },
            { email: { [Op.iLike]: `%${search}%` } },
          ],
        }
      : undefined,
    order: [["createdAt", "DESC"]],
    limit,
    offset,
  });

  return {
    data: rows,
    pagination: {
      page,
      pageSize: limit,
      total: count,
      totalPages: Math.ceil(count / limit),
    },
  };
};
```

**Why good:** `findAndCountAll` for paginated lists, `Math.min` caps page size, `Op.iLike` for case-insensitive search, named constants for limits

---

## Create Operations

### Good Example - create and bulkCreate

```typescript
// Single create
const user = await User.create({
  email: "bob@example.com",
  name: "Bob",
  // role and id are CreationOptional --- defaults apply
});

// Bulk create with validation
const users = await User.bulkCreate(
  [
    { email: "user1@example.com", name: "User 1" },
    { email: "user2@example.com", name: "User 2" },
    { email: "user3@example.com", name: "User 3" },
  ],
  {
    validate: true, // Run model validations on each record
    ignoreDuplicates: true, // Skip records that violate unique constraints
  },
);
```

**Why good:** `validate: true` on bulkCreate (off by default), `ignoreDuplicates` for idempotent inserts

### Good Example - findOrCreate

```typescript
const [user, created] = await User.findOrCreate({
  where: { email: "alice@example.com" },
  defaults: { name: "Alice", role: "user" },
});

if (created) {
  // New user was created
} else {
  // Existing user was found
}
```

**Why good:** Atomic find-or-create, `created` boolean indicates which path was taken

---

## Update Operations

### Good Example - Instance and Static Updates

```typescript
// Instance update (fires hooks)
const user = await User.findByPk(userId);
if (!user) throw new Error("User not found");
await user.update({ name: "New Name", role: "admin" });

// Static update (fires bulk hooks only)
const [affectedCount] = await User.update(
  { role: "user" },
  { where: { role: "guest" } },
);

// Increment/decrement
await post.increment("viewCount", { by: 1 });
await account.decrement("balance", { by: amount });
```

**Why good:** Instance `update()` fires per-record hooks, static `update()` for batch, `increment`/`decrement` are atomic

---

## Delete Operations

### Good Example - destroy Patterns

```typescript
// Instance destroy
const user = await User.findByPk(userId);
if (!user) throw new Error("User not found");
await user.destroy(); // Soft delete if paranoid, hard delete otherwise

// Static destroy
const deletedCount = await User.destroy({
  where: { role: "guest", createdAt: { [Op.lt]: cutoffDate } },
});

// Force hard delete on paranoid model
await user.destroy({ force: true });

// Restore soft-deleted record
await user.restore();
```

**Why good:** Instance and static destroy, `force: true` for hard delete escape hatch, `restore()` for undo

---

## Upsert

### Good Example - Create or Update

```typescript
// Upsert - creates if not exists, updates if exists
// Returns [instance, created] but `created` is unreliable on some dialects
const [user] = await User.upsert({
  email: "alice@example.com",
  name: "Alice Updated",
  role: "admin",
});
```

**Why good:** Atomic create-or-update based on primary key or unique constraint

**Gotcha:** The `created` boolean (second element) is only reliable on PostgreSQL. On MySQL/SQLite it may always be `true` or `null`. Don't depend on it for logic.

---

## Quick Reference

| Operation                 | Returns                        | Throws if Not Found         |
| ------------------------- | ------------------------------ | --------------------------- |
| `findByPk(id)`            | `T \| null`                    | No (unless `rejectOnEmpty`) |
| `findOne({ where })`      | `T \| null`                    | No (unless `rejectOnEmpty`) |
| `findAll({ where })`      | `T[]`                          | No (empty array)            |
| `findAndCountAll`         | `{ rows: T[], count: number }` | No                          |
| `findOrCreate`            | `[T, boolean]`                 | No                          |
| `create(data)`            | `T`                            | N/A                         |
| `bulkCreate(data[])`      | `T[]`                          | N/A                         |
| `update(data, { where })` | `[affectedCount]`              | No                          |
| `destroy({ where })`      | `number` (deleted count)       | No                          |
| `upsert(data)`            | `[T, boolean \| null]`         | N/A                         |
| `count({ where })`        | `number`                       | No                          |

# Sequelize - Transaction Examples

> Managed and unmanaged transactions, CLS, isolation levels, and error handling. See [SKILL.md](../SKILL.md) for core concepts.

**Prerequisites**: Understand model definitions and CRUD from [core.md](core.md) first.

---

## Managed Transactions (Recommended)

### Good Example - Auto Commit/Rollback

```typescript
import { sequelize } from "./connection";
import { User } from "./models/user";
import { Profile } from "./models/profile";

// Managed: auto-commits on success, auto-rolls back on throw
const user = await sequelize.transaction(async (t) => {
  const newUser = await User.create(
    { email: "alice@example.com", name: "Alice" },
    { transaction: t },
  );

  await Profile.create(
    { userId: newUser.id, bio: "Developer" },
    { transaction: t },
  );

  return newUser; // Return value is passed through
});
// user is the created User instance
```

**Why good:** Auto-commit/rollback, clean error propagation, return value flows through

### Bad Example - Missing transaction Pass-Through

```typescript
// BAD: Forgetting { transaction: t }
await sequelize.transaction(async (t) => {
  // This runs OUTSIDE the transaction!
  const user = await User.create({ email: "a@b.com" });

  // This is inside the transaction
  await Profile.create({ userId: user.id }, { transaction: t });

  // If this throws, Profile rolls back but User persists!
  throw new Error("Something failed");
});
```

**Why bad:** `User.create` without `{ transaction: t }` runs on a separate connection outside the transaction, won't roll back

---

## Unmanaged Transactions

### Good Example - Manual Commit/Rollback

```typescript
// Unmanaged: you control commit/rollback
const t = await sequelize.transaction();

try {
  const user = await User.create(
    { email: "bob@example.com", name: "Bob" },
    { transaction: t },
  );

  await Profile.create(
    { userId: user.id, bio: "Engineer" },
    { transaction: t },
  );

  await t.commit();
} catch (error) {
  await t.rollback();
  throw error;
}
```

**Why good:** Full control over commit timing, explicit error handling with rollback

**When to use:** When you need to commit/rollback based on external conditions (not just thrown errors), or when integrating with non-Sequelize systems.

### Bad Example - Unmanaged Without Rollback

```typescript
// BAD: No rollback on error
const t = await sequelize.transaction();
const user = await User.create({ email: "a@b.com" }, { transaction: t });
await Profile.create({ userId: user.id }, { transaction: t });
await t.commit();
// If Profile.create throws, transaction is left open!
// Connection is leaked, and DB may deadlock
```

**Why bad:** No try/catch means no rollback on error, transaction stays open holding a connection from the pool

---

## CLS (Continuation Local Storage) - Auto Transaction Passing

### Good Example - CLS in v6

```typescript
import cls from "cls-hooked";
import { Sequelize } from "sequelize";

const namespace = cls.createNamespace("sequelize-transaction");
Sequelize.useCLS(namespace);

// Now all queries inside transaction() automatically use the transaction
// No need to pass { transaction: t }
const user = await sequelize.transaction(async () => {
  // These automatically use the transaction from CLS context
  const newUser = await User.create({ email: "a@b.com", name: "Alice" });
  await Profile.create({ userId: newUser.id, bio: "Dev" });
  return newUser;
});
```

**Why good:** Eliminates `{ transaction: t }` boilerplate, impossible to forget passing transaction, all queries in callback scope automatically join the transaction

**Gotcha (v6):** Requires `cls-hooked` package. Also, CLS only works for queries within the same async context --- if you spawn detached async work (fire-and-forget), those queries won't see the transaction.

### v7 Difference

```typescript
// v7: CLS is enabled by default using Node's AsyncLocalStorage
// No setup needed --- all queries inside transaction() auto-use the transaction
const user = await sequelize.transaction(async () => {
  const newUser = await User.create({ email: "a@b.com", name: "Alice" });
  await Profile.create({ userId: newUser.id, bio: "Dev" });
  return newUser;
});

// v7: Unmanaged transactions use startUnmanagedTransaction()
const t = await sequelize.startUnmanagedTransaction();
```

---

## Isolation Levels

### Good Example - Configuring Isolation

```typescript
import { Transaction } from "sequelize";

const MINIMUM_BALANCE = 0;

const transferFunds = async (fromId: number, toId: number, amount: number) => {
  return await sequelize.transaction(
    { isolationLevel: Transaction.ISOLATION_LEVELS.SERIALIZABLE },
    async (t) => {
      const sender = await Account.findByPk(fromId, {
        transaction: t,
        lock: t.LOCK.UPDATE, // Row-level lock
      });

      if (!sender || sender.balance - amount < MINIMUM_BALANCE) {
        throw new Error("Insufficient funds");
      }

      await sender.decrement("balance", { by: amount, transaction: t });

      const recipient = await Account.findByPk(toId, {
        transaction: t,
        lock: t.LOCK.UPDATE,
      });

      if (!recipient) throw new Error("Recipient not found");

      await recipient.increment("balance", { by: amount, transaction: t });

      return { sender, recipient, amount };
    },
  );
};
```

**Why good:** `SERIALIZABLE` for financial operations, row-level locking prevents concurrent modification, business logic validation inside transaction

### Isolation Level Reference

| Level              | Dirty Read | Non-Repeatable Read | Phantom Read | Use When                   |
| ------------------ | ---------- | ------------------- | ------------ | -------------------------- |
| `READ_UNCOMMITTED` | Yes        | Yes                 | Yes          | Rarely --- analytics only  |
| `READ_COMMITTED`   | No         | Yes                 | Yes          | Default for most databases |
| `REPEATABLE_READ`  | No         | No                  | Yes          | MySQL default              |
| `SERIALIZABLE`     | No         | No                  | No           | Financial operations       |

---

## Transaction Error Handling

### Good Example - Catching Specific Errors

```typescript
import {
  UniqueConstraintError,
  ForeignKeyConstraintError,
  ValidationError,
  TimeoutError,
} from "sequelize";

const createUserWithProfile = async (email: string, name: string) => {
  try {
    return await sequelize.transaction(async (t) => {
      const user = await User.create({ email, name }, { transaction: t });

      await Profile.create({ userId: user.id }, { transaction: t });

      return user;
    });
  } catch (error) {
    if (error instanceof UniqueConstraintError) {
      throw new Error("Email already registered");
    }
    if (error instanceof ForeignKeyConstraintError) {
      throw new Error("Referenced record does not exist");
    }
    if (error instanceof ValidationError) {
      const messages = error.errors.map((e) => e.message).join(", ");
      throw new Error(`Validation failed: ${messages}`);
    }
    if (error instanceof TimeoutError) {
      throw new Error("Transaction timed out --- try again");
    }
    throw error; // Rethrow unknown errors
  }
};
```

**Why good:** Specific Sequelize error types for clean error mapping, validation messages extracted, unknown errors rethrown

---

## afterCommit Hook

### Good Example - Post-Transaction Side Effects

```typescript
await sequelize.transaction(async (t) => {
  const order = await Order.create(
    { userId: user.id, total: amount },
    { transaction: t },
  );

  await OrderItem.bulkCreate(
    items.map((item) => ({ orderId: order.id, ...item })),
    { transaction: t },
  );

  // Runs ONLY after successful commit
  t.afterCommit(async () => {
    // Safe to send notifications, update caches, etc.
    await sendOrderConfirmation(order.id);
  });

  return order;
});
```

**Why good:** Side effects only fire after successful commit, never on rollback

**Gotcha:** `afterCommit` does NOT fire if the transaction rolls back. Don't use it for cleanup that must always run --- use `finally` for that.

---

## Optimistic Concurrency Control

### Good Example - Version-Based Updates

```typescript
// Assuming: Post model has a `version` INTEGER column

const updatePostOptimistic = async (
  postId: number,
  title: string,
  expectedVersion: number,
) => {
  return await sequelize.transaction(async (t) => {
    const [affectedCount] = await Post.update(
      { title, version: expectedVersion + 1 },
      {
        where: { id: postId, version: expectedVersion },
        transaction: t,
      },
    );

    if (affectedCount === 0) {
      throw new Error("Concurrent modification detected --- retry");
    }

    return Post.findByPk(postId, { transaction: t });
  });
};
```

**Why good:** Version field prevents lost updates, `affectedCount === 0` detects concurrent modification, atomic version increment

---

## Quick Reference

| Transaction Type | Use When                  | Boilerplate                               |
| ---------------- | ------------------------- | ----------------------------------------- |
| Managed          | Default choice            | Low --- auto commit/rollback              |
| Unmanaged        | Need manual commit timing | High --- try/catch/rollback               |
| CLS-enabled      | Large codebases           | Lowest --- no `{ transaction: t }` needed |

| Option           | Purpose                                         | Default          |
| ---------------- | ----------------------------------------------- | ---------------- |
| `isolationLevel` | Transaction isolation                           | Database default |
| `type`           | `DEFERRED` / `IMMEDIATE` / `EXCLUSIVE` (SQLite) | Database default |
| `lock`           | Row-level locking                               | None             |
| `lock.of`        | Lock specific table in JOIN                     | N/A              |

| Error Type                  | When It Happens                  |
| --------------------------- | -------------------------------- |
| `UniqueConstraintError`     | Duplicate value on unique column |
| `ForeignKeyConstraintError` | Referenced record missing        |
| `ValidationError`           | Model validation failed          |
| `TimeoutError`              | Transaction or query timed out   |
| `DatabaseError`             | Generic database-level error     |
| `ConnectionError`           | Cannot connect to database       |