# TypeORM - Advanced Examples

> Subscribers, listeners, tree entities, embedded entities. See [SKILL.md](../SKILL.md) for core concepts.

**Prerequisites**: Understand entity definitions and DataSource from [core.md](core.md).

---

## Entity Listeners

### Good Example - Lifecycle Hooks on Entity

```typescript
import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
  BeforeInsert,
  BeforeUpdate,
  AfterLoad,
} from "typeorm";
import { createHash } from "crypto";

@Entity("users")
export class User {
  @PrimaryGeneratedColumn("uuid")
  id: string;

  @Column()
  email: string;

  @Column()
  name: string;

  @Column({ nullable: true })
  normalizedEmail: string | null;

  tempFullName: string; // Not a column - computed on load

  @BeforeInsert()
  normalizeEmailOnInsert() {
    this.normalizedEmail = this.email.toLowerCase().trim();
  }

  @BeforeUpdate()
  normalizeEmailOnUpdate() {
    if (this.email) {
      this.normalizedEmail = this.email.toLowerCase().trim();
    }
  }

  @AfterLoad()
  computeFullName() {
    this.tempFullName = this.name; // Compute derived properties on load
  }
}
```

**Why good:** Listeners keep entity logic self-contained, `@BeforeInsert`/`@BeforeUpdate` for data normalization, `@AfterLoad` for computed properties

**Critical caveat:** `@BeforeUpdate` and `@AfterUpdate` only fire when using `save()`, NOT with `update()` or `insert()`. If you use `update()` (recommended for performance), listeners won't trigger.

### Available Listener Decorators

| Decorator           | Fires When                  | Works With            |
| ------------------- | --------------------------- | --------------------- |
| `@BeforeInsert`     | Before entity inserted      | `save()` (new)        |
| `@AfterInsert`      | After entity inserted       | `save()` (new)        |
| `@BeforeUpdate`     | Before entity updated       | `save()` (existing)   |
| `@AfterUpdate`      | After entity updated        | `save()` (existing)   |
| `@BeforeRemove`     | Before entity removed       | `remove()`            |
| `@AfterRemove`      | After entity removed        | `remove()`            |
| `@BeforeSoftRemove` | Before soft delete          | `softRemove()`        |
| `@AfterSoftRemove`  | After soft delete           | `softRemove()`        |
| `@AfterLoad`        | After entity loaded from DB | `find*`, QueryBuilder |

**Important:** Do NOT make database calls inside entity listeners. Use subscribers instead.

---

## Subscribers

### Good Example - Audit Log Subscriber

```typescript
import {
  EventSubscriber,
  EntitySubscriberInterface,
  InsertEvent,
  UpdateEvent,
  RemoveEvent,
} from "typeorm";
import { User } from "../entities/user.entity";

@EventSubscriber()
export class UserSubscriber implements EntitySubscriberInterface<User> {
  // Listen only to User entity events
  listenTo() {
    return User;
  }

  async afterInsert(event: InsertEvent<User>): Promise<void> {
    await event.manager.insert("audit_logs", {
      action: "user_created",
      entityId: event.entity.id,
      data: JSON.stringify({ email: event.entity.email }),
      createdAt: new Date(),
    });
  }

  async afterUpdate(event: UpdateEvent<User>): Promise<void> {
    if (!event.entity) return; // entity may be undefined for bulk updates
    await event.manager.insert("audit_logs", {
      action: "user_updated",
      entityId: event.entity.id,
      data: JSON.stringify(event.updatedColumns.map((c) => c.propertyName)),
      createdAt: new Date(),
    });
  }

  async afterRemove(event: RemoveEvent<User>): Promise<void> {
    if (!event.entityId) return;
    await event.manager.insert("audit_logs", {
      action: "user_deleted",
      entityId: event.entityId,
      createdAt: new Date(),
    });
  }
}
```

**Why good:** Subscribers can make DB calls (unlike listeners), `event.manager` participates in the same transaction, `listenTo()` scopes to specific entity, null checks for bulk operations where entity may be undefined

**Registration:** Add subscriber to DataSource config:

```typescript
export const AppDataSource = new DataSource({
  // ...
  subscribers: [UserSubscriber],
});
```

### Good Example - Global Subscriber (All Entities)

```typescript
@EventSubscriber()
export class TimestampSubscriber implements EntitySubscriberInterface {
  // No listenTo() = listens to ALL entities

  beforeInsert(event: InsertEvent<any>): void {
    // Set createdAt/updatedAt on any entity that has these properties
    if ("createdAt" in event.entity) {
      event.entity.createdAt = new Date();
    }
    if ("updatedAt" in event.entity) {
      event.entity.updatedAt = new Date();
    }
  }
}
```

**Why good:** Global subscribers apply cross-cutting logic to all entities without modifying each entity

---

## Embedded Entities

### Good Example - Reusable Column Groups

```typescript
import { Column } from "typeorm";

// Embeddable - not an @Entity, just a column group
export class Address {
  @Column({ length: 255 })
  street: string;

  @Column({ length: 100 })
  city: string;

  @Column({ length: 10 })
  postalCode: string;

  @Column({ length: 100 })
  country: string;
}

@Entity("companies")
export class Company {
  @PrimaryGeneratedColumn("uuid")
  id: string;

  @Column()
  name: string;

  // Embeds Address columns with prefix
  @Column(() => Address, { prefix: "billing" })
  billingAddress: Address;
  // Creates: billing_street, billing_city, billing_postalCode, billing_country

  @Column(() => Address, { prefix: "shipping" })
  shippingAddress: Address;
  // Creates: shipping_street, shipping_city, shipping_postalCode, shipping_country
}
```

**Why good:** Reusable column groups without extra tables, prefix prevents column name collisions, same `Address` structure used for billing and shipping

**Usage:**

```typescript
const company = new Company();
company.name = "Acme";
company.billingAddress = new Address();
company.billingAddress.street = "123 Main St";
company.billingAddress.city = "Springfield";
// ...
await companyRepo.save(company);
```

---

## Tree Entities

### Good Example - Closure Table (Best for Read and Write)

```typescript
import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
  Tree,
  TreeChildren,
  TreeParent,
} from "typeorm";

@Entity("categories")
@Tree("closure-table") // Stores parent-child in separate closure table
export class Category {
  @PrimaryGeneratedColumn("uuid")
  id: string;

  @Column()
  name: string;

  @TreeChildren()
  children: Category[];

  @TreeParent()
  parent: Category | null;
}
```

**Why good:** Closure table is efficient for both reads and writes, TypeORM manages the closure table automatically

**Usage with TreeRepository:**

```typescript
const categoryRepo = AppDataSource.getTreeRepository(Category);

// Get full tree
const trees = await categoryRepo.findTrees();

// Get ancestors of a node
const ancestors = await categoryRepo.findAncestors(category);

// Get descendants of a node
const descendants = await categoryRepo.findDescendants(category);

// Get roots (no parent)
const roots = await categoryRepo.findRoots();

// Count descendants
const count = await categoryRepo.countDescendants(category);
```

### Tree Strategy Comparison

| Strategy          | Decorator                     | Read Speed       | Write Speed | Multiple Roots |
| ----------------- | ----------------------------- | ---------------- | ----------- | -------------- |
| Adjacency List    | Self-referencing `@ManyToOne` | Slow (recursive) | Fast        | Yes            |
| Closure Table     | `@Tree("closure-table")`      | Fast             | Medium      | Yes            |
| Materialized Path | `@Tree("materialized-path")`  | Fast             | Medium      | Yes            |
| Nested Set        | `@Tree("nested-set")`         | Very Fast        | Slow        | No             |

**Recommendation:** Use Closure Table for general-purpose trees. Use Materialized Path when simplicity matters. Avoid Nested Set unless reads vastly outnumber writes.

---

## Column with select: false

### Good Example - Sensitive Data Exclusion

```typescript
@Entity("users")
export class User {
  @PrimaryGeneratedColumn("uuid")
  id: string;

  @Column()
  email: string;

  @Column({ select: false }) // Excluded from default SELECTs
  passwordHash: string;

  @Column({ select: false })
  twoFactorSecret: string | null;
}

// Default find - passwordHash NOT included
const user = await userRepo.findOneBy({ id: userId });
// user.passwordHash is undefined

// Explicitly select hidden column when needed
const userWithPassword = await userRepo
  .createQueryBuilder("user")
  .addSelect("user.passwordHash")
  .where("user.id = :id", { id: userId })
  .getOne();
// user.passwordHash is now available
```

**Why good:** Sensitive columns excluded by default, must be explicitly requested, prevents accidental exposure in API responses

---

## Virtual/Computed Columns

### Good Example - Using @VirtualColumn (v0.3.11+)

```typescript
@Entity("users")
export class User {
  @PrimaryGeneratedColumn("uuid")
  id: string;

  @Column()
  firstName: string;

  @Column()
  lastName: string;

  @VirtualColumn({
    query: (alias) =>
      `SELECT COUNT(*) FROM "posts" WHERE "posts"."author_id" = ${alias}.id`,
  })
  postCount: number;
}

// postCount computed by DB on every query
const user = await userRepo.findOneBy({ id: userId });
// user.postCount is a number computed from the subquery
```

**Why good:** DB-computed column, no application-level calculation, always up-to-date, available on standard find queries

---

## Custom Repository (Data Mapper Pattern)

### Good Example - Encapsulated Query Logic

```typescript
// user.repository.ts
import { AppDataSource } from "../data-source";
import { User, UserRole } from "../entities/user.entity";

const DEFAULT_PAGE_SIZE = 20;

export const UserRepository = AppDataSource.getRepository(User).extend({
  findByEmail(email: string) {
    return this.findOneBy({ email });
  },

  findActiveAdmins() {
    return this.find({
      where: { role: UserRole.ADMIN, isActive: true },
      order: { name: "ASC" },
    });
  },

  async findPaginated(page: number, pageSize = DEFAULT_PAGE_SIZE) {
    return this.findAndCount({
      order: { createdAt: "DESC" },
      take: pageSize,
      skip: (page - 1) * pageSize,
    });
  },

  findWithPosts(userId: string) {
    return this.findOne({
      where: { id: userId },
      relations: { posts: true },
    });
  },
});
```

**Why good:** Query logic encapsulated in repository, `extend()` adds custom methods to standard repository, named constants for defaults, reusable across the application

**Usage:**

```typescript
const user = await UserRepository.findByEmail("alice@example.com");
const admins = await UserRepository.findActiveAdmins();
const [users, total] = await UserRepository.findPaginated(1);
```

---

## Quick Reference

| Feature           | Approach              | Use When                                 |
| ----------------- | --------------------- | ---------------------------------------- |
| Entity Listeners  | Decorators on entity  | Simple sync logic (normalize, validate)  |
| Subscribers       | Separate class        | Async logic, DB calls, cross-cutting     |
| Embedded Entities | `@Column(() => Type)` | Reusable column groups without join      |
| Tree Entities     | `@Tree("strategy")`   | Hierarchical data (categories, comments) |
| Custom Repository | `repo.extend({})`     | Encapsulated query logic (Data Mapper)   |
| Virtual Columns   | `@VirtualColumn`      | DB-computed values (counts, aggregates)  |

# TypeORM - Core Examples

> DataSource setup, entity definitions, CRUD operations, and repository patterns. See [SKILL.md](../SKILL.md) for decision guidance.

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

---

## DataSource Initialization

### Good Example - Async Initialization with Graceful Shutdown

```typescript
// main.ts
import "reflect-metadata"; // MUST be first import
import { AppDataSource } from "./data-source";

const bootstrap = async () => {
  try {
    await AppDataSource.initialize();
    console.log("DataSource initialized");

    // Start your server here...
  } catch (error) {
    console.error("DataSource initialization failed:", error);
    process.exit(1);
  }
};

// Graceful shutdown
const shutdown = async () => {
  if (AppDataSource.isInitialized) {
    await AppDataSource.destroy();
    console.log("DataSource destroyed");
  }
  process.exit(0);
};

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

bootstrap();
```

**Why good:** `reflect-metadata` imported first (required for decorators), error handling on init, graceful shutdown prevents connection leaks, `isInitialized` check prevents double-destroy

### Bad Example - No Error Handling, No Shutdown

```typescript
// BAD
import { AppDataSource } from "./data-source";
AppDataSource.initialize(); // Unhandled promise, no shutdown
```

**Why bad:** Unhandled promise rejection crashes silently, leaked connections on process exit

---

## DataSource Configuration Variants

### Good Example - Environment-Aware Config

```typescript
// data-source.ts
import { DataSource } from "typeorm";
import type { DataSourceOptions } from "typeorm";
import { User } from "./entities/user.entity";
import { Post } from "./entities/post.entity";

const BASE_POOL_SIZE = 10;
const PRODUCTION_POOL_SIZE = 25;

const baseOptions: DataSourceOptions = {
  type: "postgres",
  host: process.env.DB_HOST ?? "localhost",
  port: Number(process.env.DB_PORT ?? 5432),
  username: process.env.DB_USER ?? "postgres",
  password: process.env.DB_PASS ?? "postgres",
  database: process.env.DB_NAME ?? "myapp",
  entities: [User, Post],
  migrations: ["./src/migrations/*.ts"],
  synchronize: false,
  logging:
    process.env.NODE_ENV === "development" ? ["query", "error"] : ["error"],
  extra: {
    max:
      process.env.NODE_ENV === "production"
        ? PRODUCTION_POOL_SIZE
        : BASE_POOL_SIZE,
  },
};

export const AppDataSource = new DataSource(baseOptions);
```

**Why good:** Named constants for pool sizes, explicit entity imports (no globs), conditional logging, `synchronize: false` always, pool size tuned per environment

---

## Entity Definitions

### Good Example - Complete Entity with All Common Patterns

```typescript
import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
  CreateDateColumn,
  UpdateDateColumn,
  DeleteDateColumn,
  Index,
  BeforeInsert,
} from "typeorm";
import { randomUUID } from "crypto";

@Entity("users")
export class User {
  @PrimaryGeneratedColumn("uuid")
  id: string;

  @Column({ unique: true, length: 255 })
  email: string;

  @Column({ length: 100 })
  name: string;

  @Column({
    type: "enum",
    enum: ["user", "admin", "moderator"],
    default: "user",
  })
  role: string;

  @Column({ type: "text", nullable: true })
  bio: string | null;

  @Column({ select: false }) // Excluded from default SELECTs
  passwordHash: string;

  @Column({ type: "boolean", default: true })
  isActive: boolean;

  @CreateDateColumn()
  createdAt: Date;

  @UpdateDateColumn()
  updatedAt: Date;

  @DeleteDateColumn() // Enables soft delete
  deletedAt: Date | null;
}
```

**Why good:** `select: false` on sensitive columns, `DeleteDateColumn` for soft delete, nullable types match TypeScript, explicit column types and lengths, `uuid` primary key

### Good Example - Enum as TypeScript Enum

```typescript
export enum UserRole {
  USER = "user",
  ADMIN = "admin",
  MODERATOR = "moderator",
}

@Entity("users")
export class User {
  @PrimaryGeneratedColumn("uuid")
  id: string;

  @Column({ type: "enum", enum: UserRole, default: UserRole.USER })
  role: UserRole;
}
```

**Why good:** TypeScript enum gives type safety and autocomplete, matches DB enum values

---

## Read Operations

### Good Example - Find Variants

```typescript
const userRepo = AppDataSource.getRepository(User);

// Find by primary key
const user = await userRepo.findOneBy({ id: userId });
// Returns: User | null

// Find with conditions
const admin = await userRepo.findOne({
  where: { email: "admin@example.com", role: UserRole.ADMIN },
});

// Find or throw
const userOrThrow = await userRepo.findOneByOrFail({ id: userId });
// Throws EntityNotFoundError if not found

// Find many with options
const DEFAULT_PAGE_SIZE = 20;
const users = await userRepo.find({
  where: { isActive: true },
  order: { createdAt: "DESC" },
  take: DEFAULT_PAGE_SIZE,
  skip: 0,
  select: { id: true, name: true, email: true },
});

// Count
const activeCount = await userRepo.countBy({ isActive: true });

// Check existence (v0.3.12+)
const exists = await userRepo.existsBy({ email: "alice@example.com" });
```

**Why good:** `findOneBy` for simple lookups, `findOneByOrFail` when record must exist, `select` reduces payload, named constant for page size

### Good Example - Advanced Filtering with find()

```typescript
import { In, Like, Between, IsNull, Not, LessThan, MoreThan } from "typeorm";

// Multiple conditions (AND)
const users = await userRepo.find({
  where: {
    role: In([UserRole.ADMIN, UserRole.MODERATOR]),
    isActive: true,
    createdAt: MoreThan(new Date("2024-01-01")),
  },
});

// OR conditions (array of where objects)
const users = await userRepo.find({
  where: [{ email: Like("%@company.com") }, { role: UserRole.ADMIN }],
});

// Null checks
const usersWithBio = await userRepo.find({
  where: { bio: Not(IsNull()) },
});

// Range
const recentUsers = await userRepo.find({
  where: {
    createdAt: Between(startDate, endDate),
  },
});
```

**Why good:** TypeORM operators (`In`, `Like`, `Between`, etc.) are type-safe, array of `where` objects for OR conditions

---

## Write Operations

### Good Example - insert() vs save()

```typescript
const userRepo = AppDataSource.getRepository(User);

// PREFERRED: insert() for new records - single INSERT query
const result = await userRepo.insert({
  email: "alice@example.com",
  name: "Alice",
  role: UserRole.USER,
});
// result.identifiers[0].id contains the generated ID
// result.generatedMaps[0] contains generated column values

// PREFERRED: update() for existing records - single UPDATE query
await userRepo.update(
  { id: userId },
  { name: "Alice Updated", role: UserRole.ADMIN },
);

// PREFERRED: upsert() for insert-or-update - single query
await userRepo.upsert(
  { email: "alice@example.com", name: "Alice", role: UserRole.USER },
  ["email"], // Conflict columns (must be unique/PK)
);

// save() - only when you need cascade saves or don't know the operation
const user = userRepo.create({ email: "bob@example.com", name: "Bob" });
const savedUser = await userRepo.save(user);
```

**Why good:** `insert()` and `update()` each run 1 query, `save()` runs 2 (SELECT + INSERT/UPDATE); use `save()` only when cascades or ambiguity require it

### Good Example - Bulk Operations

```typescript
// Bulk insert
await userRepo.insert([
  { email: "user1@example.com", name: "User 1" },
  { email: "user2@example.com", name: "User 2" },
  { email: "user3@example.com", name: "User 3" },
]);

// Bulk update
await userRepo.update({ isActive: false }, { deletedAt: new Date() });

// Bulk delete
await userRepo.delete({ isActive: false });
// Or by IDs:
await userRepo.delete([id1, id2, id3]);
```

**Why good:** Single query for batch operations, `delete` accepts array of IDs

---

## Soft Delete

### Good Example - Using @DeleteDateColumn

```typescript
// Entity must have @DeleteDateColumn (see entity definition above)

const userRepo = AppDataSource.getRepository(User);

// Soft delete - sets deletedAt, doesn't remove row
await userRepo.softDelete({ id: userId });

// Restore - sets deletedAt back to null
await userRepo.restore({ id: userId });

// Find includes soft-deleted
const allUsers = await userRepo.find({ withDeleted: true });

// Find only soft-deleted
const deletedUsers = await userRepo.find({
  where: { deletedAt: Not(IsNull()) },
  withDeleted: true,
});
```

**Why good:** `softDelete`/`restore` are built-in, `withDeleted: true` to include deleted records, `@DeleteDateColumn` works automatically

---

## Pagination

### Good Example - Offset Pagination with Total Count

```typescript
const DEFAULT_PAGE_SIZE = 20;
const MAX_PAGE_SIZE = 100;

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

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

  const [users, total] = await AppDataSource.getRepository(User).findAndCount({
    where: { isActive: true },
    order: { createdAt: "DESC" },
    take,
    skip,
  });

  return {
    data: users,
    pagination: {
      page,
      pageSize: take,
      total,
      totalPages: Math.ceil(total / take),
    },
  };
};
```

**Why good:** `findAndCount` returns data + total in one call, `Math.min` caps page size, named constants for limits

### Good Example - Cursor Pagination

```typescript
const DEFAULT_PAGE_SIZE = 20;

interface CursorParams {
  cursor?: string;
  take?: number;
}

const getPostsCursor = async ({
  cursor,
  take = DEFAULT_PAGE_SIZE,
}: CursorParams) => {
  const qb = AppDataSource.getRepository(Post)
    .createQueryBuilder("post")
    .where("post.published = :pub", { pub: true })
    .orderBy("post.createdAt", "DESC")
    .take(take + 1); // Fetch one extra to detect next page

  if (cursor) {
    qb.andWhere("post.createdAt < :cursor", { cursor });
  }

  const posts = await qb.getMany();
  const hasNextPage = posts.length > take;
  const data = hasNextPage ? posts.slice(0, -1) : posts;

  return {
    data,
    nextCursor: hasNextPage
      ? data[data.length - 1]?.createdAt.toISOString()
      : undefined,
  };
};
```

**Why good:** Cursor-based scales to large datasets, `take + 1` pattern detects next page without extra count query

---

## Quick Reference

| Operation         | Returns         | Throws on Not Found |
| ----------------- | --------------- | ------------------- |
| `findOneBy`       | `T \| null`     | No                  |
| `findOneByOrFail` | `T`             | Yes                 |
| `findOne`         | `T \| null`     | No                  |
| `findOneOrFail`   | `T`             | Yes                 |
| `find`            | `T[]`           | No (empty array)    |
| `findAndCount`    | `[T[], number]` | No                  |
| `insert`          | `InsertResult`  | N/A                 |
| `update`          | `UpdateResult`  | No                  |
| `upsert`          | `InsertResult`  | N/A                 |
| `save`            | `T` or `T[]`    | N/A                 |
| `delete`          | `DeleteResult`  | No                  |
| `softDelete`      | `UpdateResult`  | No                  |
| `restore`         | `UpdateResult`  | No                  |
| `count`/`countBy` | `number`        | No                  |
| `existsBy`        | `boolean`       | No                  |

# TypeORM - Migration Examples

> Generate, run, revert migrations and CLI configuration. See [SKILL.md](../SKILL.md) for core concepts.

**Prerequisites**: Understand DataSource configuration from [core.md](core.md).

---

## DataSource for Migrations

### Good Example - Separate Export for CLI

```typescript
// data-source.ts - Used by BOTH app and migration CLI
import { DataSource } from "typeorm";
import { User } from "./entities/user.entity";
import { Post } from "./entities/post.entity";

export const AppDataSource = new DataSource({
  type: "postgres",
  host: process.env.DB_HOST ?? "localhost",
  port: Number(process.env.DB_PORT ?? 5432),
  username: process.env.DB_USER ?? "postgres",
  password: process.env.DB_PASS ?? "postgres",
  database: process.env.DB_NAME ?? "myapp",
  entities: [User, Post],
  migrations: ["./src/migrations/*.ts"],
  synchronize: false,
  logging: false,
});
```

**Why good:** Single DataSource file shared between app runtime and CLI, explicit entity list (no globs), migrations directory configured

**Key point:** The CLI loads this file directly via `-d` flag. It must export a `DataSource` instance (not a function).

---

## CLI Commands

### Running Migrations

```bash
# Generate migration from entity changes (auto-diff)
npx typeorm-ts-node-esm migration:generate ./src/migrations/AddUserRole -d ./src/data-source.ts

# Create empty migration (for manual SQL)
npx typeorm-ts-node-esm migration:create ./src/migrations/SeedDefaultRoles

# Run all pending migrations
npx typeorm-ts-node-esm migration:run -d ./src/data-source.ts

# Revert the last executed migration
npx typeorm-ts-node-esm migration:revert -d ./src/data-source.ts

# Show migration status
npx typeorm-ts-node-esm migration:show -d ./src/data-source.ts
```

**Key points:**

- `migration:generate` compares entities to DB schema and generates SQL diff
- `migration:revert` reverts only the LAST migration - call repeatedly to revert multiple
- Always use `typeorm-ts-node-esm` for TypeScript projects (or `typeorm-ts-node-commonjs`)
- The `-d` flag is required for run/revert/show/generate (points to DataSource file)

---

## Migration File Structure

### Good Example - Auto-Generated Migration

```typescript
// src/migrations/1710000000000-AddUserRole.ts
import type { MigrationInterface, QueryRunner } from "typeorm";

export class AddUserRole1710000000000 implements MigrationInterface {
  name = "AddUserRole1710000000000";

  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(
      `ALTER TABLE "users" ADD "role" character varying NOT NULL DEFAULT 'user'`,
    );
    await queryRunner.query(
      `CREATE INDEX "IDX_users_role" ON "users" ("role")`,
    );
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`DROP INDEX "IDX_users_role"`);
    await queryRunner.query(`ALTER TABLE "users" DROP COLUMN "role"`);
  }
}
```

**Why good:** `up()` applies changes, `down()` reverts them exactly, index created with the column, timestamp ensures ordering

### Good Example - Manual Migration (Seed Data)

```typescript
// src/migrations/1710000001000-SeedDefaultRoles.ts
import type { MigrationInterface, QueryRunner } from "typeorm";

export class SeedDefaultRoles1710000001000 implements MigrationInterface {
  name = "SeedDefaultRoles1710000001000";

  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
      INSERT INTO "roles" ("id", "name", "description")
      VALUES
        (gen_random_uuid(), 'user', 'Default user role'),
        (gen_random_uuid(), 'admin', 'Administrator role'),
        (gen_random_uuid(), 'moderator', 'Content moderator')
      ON CONFLICT ("name") DO NOTHING
    `);
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
      DELETE FROM "roles" WHERE "name" IN ('user', 'admin', 'moderator')
    `);
  }
}
```

**Why good:** `migration:create` generates empty file for manual SQL like seed data, `ON CONFLICT DO NOTHING` makes it idempotent, `down()` cleanly reverses

---

## Transaction Control in Migrations

### Good Example - Per-Migration Transaction Control

```bash
# Default: all migrations in one transaction
npx typeorm-ts-node-esm migration:run -d ./src/data-source.ts

# Each migration in its own transaction
npx typeorm-ts-node-esm migration:run -d ./src/data-source.ts --transaction each

# No transactions (for DDL that can't run in transactions, e.g., CREATE INDEX CONCURRENTLY)
npx typeorm-ts-node-esm migration:run -d ./src/data-source.ts --transaction none
```

### Good Example - Programmatic Transaction Control

```typescript
// Per-migration transaction override
export class CreateConcurrentIndex1710000002000 implements MigrationInterface {
  // This migration handles its own transaction
  transaction = false as const; // Disable auto-wrapping

  public async up(queryRunner: QueryRunner): Promise<void> {
    // CREATE INDEX CONCURRENTLY cannot run inside a transaction
    await queryRunner.query(
      `CREATE INDEX CONCURRENTLY "IDX_posts_title" ON "posts" ("title")`,
    );
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`DROP INDEX "IDX_posts_title"`);
  }
}
```

**Why good:** `transaction = false` disables transaction wrapping for this specific migration, necessary for PostgreSQL `CONCURRENTLY` operations

---

## Migration Workflow

### Recommended Process

```bash
# 1. Modify entity class(es)

# 2. Generate migration from diff
npx typeorm-ts-node-esm migration:generate ./src/migrations/DescribeChange -d ./src/data-source.ts

# 3. Review the generated SQL - always inspect before running!
cat ./src/migrations/*-DescribeChange.ts

# 4. Run migration on development
npx typeorm-ts-node-esm migration:run -d ./src/data-source.ts

# 5. Test the migration
# ...run tests...

# 6. If something went wrong, revert
npx typeorm-ts-node-esm migration:revert -d ./src/data-source.ts

# 7. Commit migration file with entity changes
```

**Key points:**

- Always review generated SQL before running - TypeORM can generate destructive changes
- Entity renames generate DROP + CREATE (data loss) - use manual migration for renames
- Run migrations in CI/CD pipeline, never use `synchronize: true`

---

## Common Migration Gotchas

- **Column rename = data loss**: `migration:generate` creates DROP + ADD, not ALTER RENAME. Write manual migration for renames.
- **Enum changes**: Adding/removing enum values requires manual SQL. Generated migration may fail on PostgreSQL.
- **Default value changes**: TypeORM generates ALTER for existing rows but doesn't backfill. Consider manual UPDATE in migration.
- **migration:run requires compiled JS** if not using ts-node. Compile TypeScript first or use `typeorm-ts-node-esm`.
- **Migration table**: TypeORM tracks executed migrations in `migrations` table (configurable via `migrationsTableName`). Don't delete rows from it.

---

## package.json Scripts

### Good Example - Convenient Migration Scripts

```json
{
  "scripts": {
    "migration:generate": "typeorm-ts-node-esm migration:generate -d ./src/data-source.ts",
    "migration:run": "typeorm-ts-node-esm migration:run -d ./src/data-source.ts",
    "migration:revert": "typeorm-ts-node-esm migration:revert -d ./src/data-source.ts",
    "migration:show": "typeorm-ts-node-esm migration:show -d ./src/data-source.ts",
    "migration:create": "typeorm-ts-node-esm migration:create"
  }
}
```

**Usage:** `npm run migration:generate -- ./src/migrations/AddUserEmail`

---

## Quick Reference

| Command                     | Purpose                        | Requires `-d` |
| --------------------------- | ------------------------------ | ------------- |
| `migration:generate <path>` | Auto-generate from entity diff | Yes           |
| `migration:create <path>`   | Create empty migration file    | No            |
| `migration:run`             | Execute all pending migrations | Yes           |
| `migration:revert`          | Revert last executed migration | Yes           |
| `migration:show`            | List migrations with status    | Yes           |

| Transaction Flag      | Behavior                                    |
| --------------------- | ------------------------------------------- |
| `--transaction all`   | All migrations in one transaction (default) |
| `--transaction each`  | Each migration in its own transaction       |
| `--transaction none`  | No transaction wrapping                     |
| `transaction = false` | Per-migration override (in class)           |

# TypeORM - QueryBuilder Examples

> Joins, subqueries, pagination, aggregation, and raw queries. See [SKILL.md](../SKILL.md) for core concepts.

**Prerequisites**: Understand entity definitions and repository patterns from [core.md](core.md).

---

## Basic QueryBuilder

### Good Example - Select with Conditions

```typescript
const DEFAULT_PAGE_SIZE = 20;

const users = await AppDataSource.getRepository(User)
  .createQueryBuilder("user")
  .select(["user.id", "user.name", "user.email"])
  .where("user.isActive = :active", { active: true })
  .andWhere("user.role = :role", { role: "admin" })
  .orderBy("user.createdAt", "DESC")
  .take(DEFAULT_PAGE_SIZE)
  .getMany();
```

**Why good:** Parameterized queries prevent SQL injection, `select` reduces payload, `take` for entity-safe pagination

### Bad Example - String Interpolation

```typescript
// BAD: SQL injection vulnerability
const email = req.body.email;
const user = await userRepo
  .createQueryBuilder("user")
  .where(`user.email = '${email}'`) // INJECTION!
  .getOne();
```

**Why bad:** Attacker can pass `' OR 1=1 --` as email; always use `:paramName` with parameter objects

---

## Joins

### Good Example - Left Join and Select

```typescript
// Load relation data (like find with relations, but with conditions)
const usersWithPosts = await userRepo
  .createQueryBuilder("user")
  .leftJoinAndSelect("user.posts", "post")
  .where("user.isActive = :active", { active: true })
  .getMany();
// Returns User[] with posts array populated

// Conditional join - only load published posts
const usersWithPublishedPosts = await userRepo
  .createQueryBuilder("user")
  .leftJoinAndSelect("user.posts", "post", "post.published = :pub", {
    pub: true,
  })
  .getMany();
// Users without published posts still returned (left join), but posts array empty

// Inner join - only users who HAVE published posts
const activeAuthors = await userRepo
  .createQueryBuilder("user")
  .innerJoinAndSelect("user.posts", "post", "post.published = :pub", {
    pub: true,
  })
  .getMany();
// Only users with at least one published post returned
```

**Why good:** `leftJoinAndSelect` loads relations with optional condition, `innerJoinAndSelect` filters parent by relation existence, third parameter adds JOIN condition

### Good Example - Join Without Select (Filter Only)

```typescript
// Filter by relation without loading it
const usersWithRecentPosts = await userRepo
  .createQueryBuilder("user")
  .innerJoin("user.posts", "post") // Join but don't select post columns
  .where("post.createdAt > :date", { date: new Date("2024-01-01") })
  .getMany();
// Returns User[] without posts populated (smaller payload)
```

**Why good:** `innerJoin` without Select uses the relation for filtering without loading relation data - smaller payload

---

## Pagination with Joins

### Good Example - take/skip (Correct)

```typescript
const DEFAULT_PAGE_SIZE = 20;
const MAX_PAGE_SIZE = 100;

const getPaginatedPosts = async (page: number, pageSize: number) => {
  const take = Math.min(pageSize, MAX_PAGE_SIZE);
  const skip = (page - 1) * take;

  const [posts, total] = await postRepo
    .createQueryBuilder("post")
    .leftJoinAndSelect("post.tags", "tag")
    .leftJoinAndSelect("post.author", "author")
    .where("post.published = :pub", { pub: true })
    .orderBy("post.createdAt", "DESC")
    .take(take) // Paginates on ENTITIES, not rows
    .skip(skip)
    .getManyAndCount();

  return { data: posts, total, page, pageSize: take };
};
```

**Why good:** `take`/`skip` handles pagination correctly with joins (paginates entities, not joined rows), `getManyAndCount` returns total in same query, `Math.min` caps page size

### Bad Example - limit/offset with Joins

```typescript
// BAD: limit/offset counts joined rows, not entities
const posts = await postRepo
  .createQueryBuilder("post")
  .leftJoinAndSelect("post.comments", "comment")
  .limit(10) // If a post has 5 comments, that's 5 rows for 1 post
  .offset(0) // You might get only 2-3 posts instead of 10
  .getMany();
```

**Why bad:** `limit`/`offset` operate on SQL rows, not entities. A post with 5 comments counts as 5 rows, so `limit(10)` might return only 2 posts. Use `take`/`skip` instead.

---

## Subqueries

### Good Example - Subquery in WHERE

```typescript
// Find users whose post count exceeds a threshold
const MINIMUM_POST_COUNT = 5;

const prolificAuthors = await userRepo
  .createQueryBuilder("user")
  .where((qb) => {
    const subQuery = qb
      .subQuery()
      .select("post.authorId")
      .from(Post, "post")
      .groupBy("post.authorId")
      .having("COUNT(post.id) >= :minPosts", { minPosts: MINIMUM_POST_COUNT })
      .getQuery();
    return `user.id IN ${subQuery}`;
  })
  .getMany();
```

**Why good:** Subquery runs in DB (not in JS), named constant for threshold, parameters flow through correctly

### Good Example - Subquery in SELECT

```typescript
// Add computed column via subquery
const usersWithPostCount = await userRepo
  .createQueryBuilder("user")
  .addSelect((qb) => {
    return qb
      .subQuery()
      .select("COUNT(post.id)")
      .from(Post, "post")
      .where("post.authorId = user.id")
      .getQuery();
  }, "postCount")
  .getRawMany();
// Returns raw objects: { user_id, user_name, ..., postCount }
```

**Why good:** Computed column calculated in DB, correlated subquery references outer query, `getRawMany` for non-entity results

---

## Aggregation

### Good Example - Group By with Having

```typescript
// Posts per author with minimum count filter
const MINIMUM_POSTS = 3;

const authorStats = await postRepo
  .createQueryBuilder("post")
  .select("post.authorId", "authorId")
  .addSelect("COUNT(post.id)", "postCount")
  .addSelect("MAX(post.createdAt)", "latestPost")
  .groupBy("post.authorId")
  .having("COUNT(post.id) >= :min", { min: MINIMUM_POSTS })
  .orderBy("postCount", "DESC")
  .getRawMany();
// Returns: [{ authorId: "...", postCount: "5", latestPost: "..." }, ...]
```

**Why good:** Aggregations run in DB, `HAVING` filters after grouping, `getRawMany` for aggregate results (not entity-shaped)

**Note:** `getRawMany` returns raw objects with column aliases, not entity instances. Numeric aggregates may come back as strings depending on the driver.

---

## Raw Queries

### Good Example - Parameterized Raw Query

```typescript
// When QueryBuilder is too limiting
const result = await AppDataSource.query(
  `SELECT u.id, u.name, COUNT(p.id) as "postCount"
   FROM users u
   LEFT JOIN posts p ON p.author_id = u.id AND p.published = $1
   GROUP BY u.id, u.name
   HAVING COUNT(p.id) >= $2
   ORDER BY "postCount" DESC`,
  [true, 5], // Parameterized - safe from injection
);
// Returns raw rows: [{ id, name, postCount }, ...]
```

**Why good:** Parameterized even in raw queries (no string interpolation), useful for complex queries that don't map well to QueryBuilder

### Bad Example - Unparameterized Raw Query

```typescript
// BAD: String interpolation in raw query
const result = await AppDataSource.query(
  `SELECT * FROM users WHERE email = '${email}'`, // SQL INJECTION!
);
```

**Why bad:** Same injection risk as QueryBuilder - always use parameter placeholders (`$1, $2` for PostgreSQL, `?, ?` for MySQL)

---

## Advanced Patterns

### Good Example - Conditional Query Building

```typescript
interface PostFilterParams {
  authorId?: string;
  published?: boolean;
  search?: string;
  tags?: string[];
}

const DEFAULT_PAGE_SIZE = 20;

const filterPosts = async (filters: PostFilterParams, page = 1) => {
  const qb = postRepo
    .createQueryBuilder("post")
    .leftJoinAndSelect("post.author", "author")
    .leftJoinAndSelect("post.tags", "tag");

  if (filters.authorId) {
    qb.andWhere("post.authorId = :authorId", { authorId: filters.authorId });
  }

  if (filters.published !== undefined) {
    qb.andWhere("post.published = :published", {
      published: filters.published,
    });
  }

  if (filters.search) {
    qb.andWhere("post.title ILIKE :search", { search: `%${filters.search}%` });
  }

  if (filters.tags?.length) {
    qb.andWhere("tag.name IN (:...tagNames)", { tagNames: filters.tags });
  }

  return qb
    .orderBy("post.createdAt", "DESC")
    .take(DEFAULT_PAGE_SIZE)
    .skip((page - 1) * DEFAULT_PAGE_SIZE)
    .getManyAndCount();
};
```

**Why good:** Conditions appended only when filters present, `:...param` spreads arrays into `IN` clause, QueryBuilder is mutable so conditions chain naturally

### Good Example - Locking

```typescript
// Pessimistic lock - prevents concurrent reads/writes
const user = await userRepo
  .createQueryBuilder("user")
  .setLock("pessimistic_write")
  .where("user.id = :id", { id: userId })
  .getOne();
// Row locked until transaction commits/rolls back

// Optimistic lock with version column
@Entity("users")
export class User {
  @PrimaryGeneratedColumn("uuid")
  id: string;

  @VersionColumn()
  version: number;
}

// Throws OptimisticLockVersionMismatchError if version changed
await userRepo
  .createQueryBuilder()
  .update(User)
  .set({ name: "Updated" })
  .where("id = :id AND version = :version", {
    id: userId,
    version: expectedVersion,
  })
  .execute();
```

**Why good:** Pessimistic lock for critical sections, optimistic lock via `@VersionColumn` for low-contention updates

---

## Quick Reference

| Method            | Returns Entities | Use When                        |
| ----------------- | ---------------- | ------------------------------- |
| `getOne`          | Yes              | Single entity by condition      |
| `getMany`         | Yes              | Multiple entities               |
| `getManyAndCount` | Yes + count      | Paginated results               |
| `getRawOne`       | No (raw)         | Single aggregate/computed value |
| `getRawMany`      | No (raw)         | Multiple aggregates             |
| `getCount`        | No (number)      | Count without loading entities  |
| `execute`         | UpdateResult     | INSERT/UPDATE/DELETE via QB     |

# TypeORM - Relations Examples

> All relation types, eager/lazy loading, cascades, and relation gotchas. See [SKILL.md](../SKILL.md) for core concepts.

**Prerequisites**: Understand entity definitions and repository patterns from [core.md](core.md).

---

## One-to-One Relations

### Good Example - Profile Relation

```typescript
import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
  OneToOne,
  JoinColumn,
} from "typeorm";

@Entity("profiles")
export class Profile {
  @PrimaryGeneratedColumn("uuid")
  id: string;

  @Column({ type: "text", nullable: true })
  bio: string | null;

  @Column({ nullable: true })
  avatarUrl: string | null;

  // Owning side - MUST have @JoinColumn
  @OneToOne(() => User, (user) => user.profile, { onDelete: "CASCADE" })
  @JoinColumn({ name: "user_id" })
  user: User;

  @Column()
  userId: string; // Expose FK for direct queries
}

@Entity("users")
export class User {
  @PrimaryGeneratedColumn("uuid")
  id: string;

  // Inverse side - no @JoinColumn
  @OneToOne(() => Profile, (profile) => profile.user)
  profile: Profile;
}
```

**Why good:** `@JoinColumn` on owning side (Profile) with explicit column name, FK exposed as `userId`, `onDelete: CASCADE` cleans up profile when user deleted, inverse side for bidirectional navigation

### Bad Example - Missing JoinColumn

```typescript
// BAD: @JoinColumn missing on @OneToOne
@Entity()
export class Profile {
  @OneToOne(() => User) // No @JoinColumn - TypeORM won't create FK column!
  user: User;
}
```

**Why bad:** `@OneToOne` requires `@JoinColumn` on the owning side - without it, no FK column is created and the relation won't persist

---

## Many-to-One / One-to-Many

### Good Example - Posts Belong to Author

```typescript
@Entity("posts")
export class Post {
  @PrimaryGeneratedColumn("uuid")
  id: string;

  @Column()
  title: string;

  @Column({ type: "text", nullable: true })
  content: string | null;

  @Column({ default: false })
  published: boolean;

  // Many posts belong to one user (owning side - holds FK)
  @ManyToOne(() => User, (user) => user.posts, {
    onDelete: "CASCADE",
    nullable: false,
  })
  @JoinColumn({ name: "author_id" })
  author: User;

  @Column({ name: "author_id" })
  authorId: string; // Direct FK access without join

  @CreateDateColumn()
  createdAt: Date;
}

@Entity("users")
export class User {
  @PrimaryGeneratedColumn("uuid")
  id: string;

  @Column()
  name: string;

  // One user has many posts (inverse side)
  @OneToMany(() => Post, (post) => post.author)
  posts: Post[];
}
```

**Why good:** FK on Many side (Post), `authorId` exposed for queries without joining, `nullable: false` enforces required author, `onDelete: CASCADE` prevents orphaned posts

---

## Many-to-Many

### Good Example - Implicit Join Table

```typescript
@Entity("posts")
export class Post {
  @PrimaryGeneratedColumn("uuid")
  id: string;

  @Column()
  title: string;

  // @JoinTable on owning side (only one side)
  @ManyToMany(() => Tag, (tag) => tag.posts)
  @JoinTable({
    name: "posts_tags", // Explicit join table name
    joinColumn: { name: "post_id", referencedColumnName: "id" },
    inverseJoinColumn: { name: "tag_id", referencedColumnName: "id" },
  })
  tags: Tag[];
}

@Entity("tags")
export class Tag {
  @PrimaryGeneratedColumn("uuid")
  id: string;

  @Column({ unique: true })
  name: string;

  // Inverse side - no @JoinTable
  @ManyToMany(() => Post, (post) => post.tags)
  posts: Post[];
}
```

**Why good:** `@JoinTable` on one side only with explicit names, bidirectional for querying from either side

### Good Example - Explicit Join Entity (Extra Fields on Relation)

```typescript
// When you need extra columns on the many-to-many relationship
@Entity("post_categories")
export class PostCategory {
  @PrimaryGeneratedColumn("uuid")
  id: string;

  @ManyToOne(() => Post, (post) => post.postCategories, { onDelete: "CASCADE" })
  @JoinColumn({ name: "post_id" })
  post: Post;

  @Column()
  postId: string;

  @ManyToOne(() => Category, (category) => category.postCategories, { onDelete: "CASCADE" })
  @JoinColumn({ name: "category_id" })
  category: Category;

  @Column()
  categoryId: string;

  @Column({ default: 0 })
  sortOrder: number; // Extra field on the relationship!

  @CreateDateColumn()
  assignedAt: Date; // Extra field!
}

// Post entity
@OneToMany(() => PostCategory, (pc) => pc.post)
postCategories: PostCategory[];

// Category entity
@OneToMany(() => PostCategory, (pc) => pc.category)
postCategories: PostCategory[];
```

**Why good:** Explicit join entity allows extra columns (`sortOrder`, `assignedAt`), standard ManyToOne/OneToMany patterns, can query the join entity directly

---

## Loading Relations

### Good Example - Explicit Loading with find()

```typescript
const userRepo = AppDataSource.getRepository(User);

// Load specific relations
const userWithPosts = await userRepo.findOne({
  where: { id: userId },
  relations: { posts: true, profile: true },
});

// Nested relation loading
const userWithPostTags = await userRepo.findOne({
  where: { id: userId },
  relations: {
    posts: {
      tags: true, // Load tags on each post
    },
  },
});

// Load relations with field selection
const userSummary = await userRepo.findOne({
  where: { id: userId },
  select: {
    id: true,
    name: true,
    posts: { id: true, title: true },
  },
  relations: { posts: true },
});
```

**Why good:** Explicit relation loading prevents N+1, nested relations loaded in a single query, `select` reduces payload

### Bad Example - N+1 Query Problem

```typescript
// BAD: Loading relations in a loop
const users = await userRepo.find();
for (const user of users) {
  user.posts = await postRepo.findBy({ authorId: user.id }); // N extra queries!
}
```

**Why bad:** 1 query for users + N queries for posts = N+1 queries, use `relations` option or QueryBuilder joins instead

---

## Eager vs Lazy Loading

### Good Example - Eager Relations (Automatic with find\*)

```typescript
@Entity("users")
export class User {
  @PrimaryGeneratedColumn("uuid")
  id: string;

  // Eager: always loaded with find* methods
  @OneToOne(() => Profile, (profile) => profile.user, { eager: true })
  profile: Profile;

  // NOT eager: loaded only when explicitly requested
  @OneToMany(() => Post, (post) => post.author)
  posts: Post[];
}

// Profile is automatically included
const user = await userRepo.findOneBy({ id: userId });
// user.profile is loaded (eager)
// user.posts is undefined (not loaded)

// GOTCHA: eager does NOT work with QueryBuilder!
const user = await userRepo
  .createQueryBuilder("user")
  .where("user.id = :id", { id: userId })
  .getOne();
// user.profile is NOT loaded! Must use leftJoinAndSelect:
const user = await userRepo
  .createQueryBuilder("user")
  .leftJoinAndSelect("user.profile", "profile")
  .where("user.id = :id", { id: userId })
  .getOne();
```

**Why good:** Eager loading is convenient for always-needed relations, but the key gotcha is documented: it only works with `find*`, not QueryBuilder

### Good Example - Lazy Relations (Promise-Based)

```typescript
@Entity("users")
export class User {
  @PrimaryGeneratedColumn("uuid")
  id: string;

  // Lazy: returns Promise, loads on access
  @OneToMany(() => Post, (post) => post.author)
  posts: Promise<Post[]>; // MUST be Promise type

  @OneToOne(() => Profile, (profile) => profile.user)
  profile: Promise<Profile>; // MUST be Promise type
}

// Usage - triggers a query when awaited
const user = await userRepo.findOneBy({ id: userId });
const posts = await user.posts; // Triggers SELECT query here
```

**Why good:** Lazy loading defers queries until needed

**Caveat:** Each `await user.posts` triggers a separate query. If accessed in a loop, this creates N+1 problems. Prefer explicit loading with `relations` for predictable performance.

---

## Cascade Operations

### Good Example - Selective Cascades

```typescript
@Entity("users")
export class User {
  @PrimaryGeneratedColumn("uuid")
  id: string;

  // Cascade only inserts - not updates or removes
  @OneToMany(() => Post, (post) => post.author, {
    cascade: ["insert"], // Only cascade new post creation
  })
  posts: Post[];

  @OneToOne(() => Profile, (profile) => profile.user, {
    cascade: true, // All cascades - use only when intentional
    onDelete: "CASCADE",
  })
  profile: Profile;
}

// Cascade insert: creating user also creates profile
const user = new User();
user.name = "Alice";
user.profile = new Profile();
user.profile.bio = "Developer";
await userRepo.save(user); // Saves user AND profile

// Cascade insert for posts
user.posts = [new Post()];
user.posts[0].title = "First Post";
await userRepo.save(user); // Also inserts the new post
```

**Why good:** `cascade: ["insert"]` is explicit about what cascades, prevents accidental updates/deletes of relations, `cascade: true` only for tightly coupled entities like User-Profile

### Bad Example - Unintended Cascade

```typescript
// BAD: cascade: true saves anything attached to the entity
@OneToMany(() => Post, (post) => post.author, { cascade: true })
posts: Post[];

// Loading user with posts, modifying a post accidentally, then saving
const user = await userRepo.findOne({
  where: { id: userId },
  relations: { posts: true },
});
user.name = "Updated Name";
// Oops - some code modified user.posts[0].title
await userRepo.save(user); // Also updates the post! Unintended side effect
```

**Why bad:** `cascade: true` propagates all operations including updates - any modification to loaded relations gets saved, risking unintended data changes

---

## Relation Queries with find()

### Good Example - Filter by Relation Data

```typescript
// Find users who have published posts
const usersWithPublishedPosts = await userRepo.find({
  where: {
    posts: {
      published: true,
    },
  },
  relations: { posts: true },
});

// Find posts by author role
const adminPosts = await postRepo.find({
  where: {
    author: {
      role: UserRole.ADMIN,
    },
  },
  relations: { author: true },
});
```

**Why good:** Relation filters in `where` generate efficient JOINs, `relations` loads the matched data

---

## Working with Many-to-Many Relations

### Good Example - Add/Remove/Replace

```typescript
const postRepo = AppDataSource.getRepository(Post);
const tagRepo = AppDataSource.getRepository(Tag);

// Add tags to post
const post = await postRepo.findOne({
  where: { id: postId },
  relations: { tags: true },
});

const newTag = await tagRepo.findOneBy({ id: tagId });
post.tags.push(newTag);
await postRepo.save(post); // Updates join table

// Remove a tag
post.tags = post.tags.filter((t) => t.id !== removeTagId);
await postRepo.save(post); // Updates join table

// Replace all tags
const newTags = await tagRepo.findBy({ id: In(newTagIds) });
post.tags = newTags;
await postRepo.save(post); // Replaces all in join table
```

**Why good:** Push to add, filter to remove, assign to replace - TypeORM manages the join table

**Caveat:** This requires loading existing relations first. For large datasets, use QueryBuilder to manipulate the join table directly.

---

## Quick Reference

| Relation Type   | Decorator       | FK Location  | @JoinColumn | @JoinTable |
| --------------- | --------------- | ------------ | ----------- | ---------- |
| One-to-One      | `@OneToOne`     | Owning side  | Required    | No         |
| Many-to-One     | `@ManyToOne`    | Many side    | Optional    | No         |
| One-to-Many     | `@OneToMany`    | Inverse side | No          | No         |
| Many-to-Many    | `@ManyToMany`   | Join table   | No          | Required   |
| Explicit M-to-M | 2x `@ManyToOne` | Join entity  | Yes         | No         |

| Loading Strategy | Pros                          | Cons                                | Use When                |
| ---------------- | ----------------------------- | ----------------------------------- | ----------------------- |
| Explicit         | Predictable, no extra queries | Must specify each time              | Default choice          |
| Eager            | Automatic with find\*         | Not with QueryBuilder, always loads | Tightly coupled 1:1     |
| Lazy             | Loads on demand               | N+1 risk, Promise syntax awkward    | Rarely used in practice |

# TypeORM - Transaction Examples

> EntityManager callback, QueryRunner manual control, and isolation levels. See [SKILL.md](../SKILL.md) for core concepts.

**Prerequisites**: Understand DataSource and repository patterns from [core.md](core.md).

---

## EntityManager Callback (Simple Approach)

### Good Example - Automatic Commit/Rollback

```typescript
// All operations in callback succeed or fail together
await AppDataSource.transaction(async (manager) => {
  const user = manager.create(User, {
    email: "alice@example.com",
    name: "Alice",
  });
  await manager.save(user);

  const post = manager.create(Post, {
    title: "First Post",
    authorId: user.id,
    published: true,
  });
  await manager.save(post);

  // If this throws, both user and post are rolled back
  await manager.insert(AuditLog, {
    action: "user_created",
    entityId: user.id,
  });
});
```

**Why good:** Auto-commits on success, auto-rollbacks on any throw, no manual cleanup needed, concise

### Bad Example - Using Global Manager in Transaction

```typescript
// BAD: Global manager bypasses transaction!
await AppDataSource.transaction(async (manager) => {
  await manager.save(User, userData); // In transaction

  // WRONG: This runs outside the transaction
  await AppDataSource.manager.save(Post, postData);
  // If manager.save(User) above fails, this Post is STILL saved
});
```

**Why bad:** `AppDataSource.manager` is the global entity manager - it doesn't participate in the transaction. Only the callback's `manager` parameter is transactional.

---

## EntityManager with Isolation Level

### Good Example - Serializable Transaction

```typescript
// Specify isolation level as first parameter
await AppDataSource.manager.transaction("SERIALIZABLE", async (manager) => {
  const account = await manager.findOneBy(Account, { id: fromAccountId });

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

  await manager.update(
    Account,
    { id: fromAccountId },
    {
      balance: () => `balance - ${amount}`, // Raw SQL for atomic decrement
    },
  );

  await manager.update(
    Account,
    { id: toAccountId },
    {
      balance: () => `balance + ${amount}`,
    },
  );

  await manager.insert(Transfer, {
    fromAccountId,
    toAccountId,
    amount,
    timestamp: new Date(),
  });
});
```

**Why good:** `SERIALIZABLE` prevents concurrent modification of same accounts, atomic balance operations via raw SQL expression, business logic validated inside transaction

---

## QueryRunner (Full Control)

### Good Example - Manual Transaction Lifecycle

```typescript
const MINIMUM_BALANCE = 0;

const transferFunds = async (fromId: string, toId: string, amount: number) => {
  const queryRunner = AppDataSource.createQueryRunner();

  // Establish real database connection
  await queryRunner.connect();

  // Start transaction
  await queryRunner.startTransaction();

  try {
    // All operations use queryRunner.manager
    const sender = await queryRunner.manager.findOneBy(Account, { id: fromId });
    if (!sender || sender.balance - amount < MINIMUM_BALANCE) {
      throw new Error("Insufficient funds");
    }

    await queryRunner.manager.update(
      Account,
      { id: fromId },
      {
        balance: () => `balance - ${amount}`,
      },
    );

    await queryRunner.manager.update(
      Account,
      { id: toId },
      {
        balance: () => `balance + ${amount}`,
      },
    );

    await queryRunner.manager.insert(Transfer, {
      fromAccountId: fromId,
      toAccountId: toId,
      amount,
    });

    // Explicitly commit
    await queryRunner.commitTransaction();

    return { success: true };
  } catch (error) {
    // Explicitly rollback
    await queryRunner.rollbackTransaction();
    throw error;
  } finally {
    // ALWAYS release - returns connection to pool
    await queryRunner.release();
  }
};
```

**Why good:** Explicit commit/rollback control, `finally` block guarantees `release()`, all operations use `queryRunner.manager`, named constant for business rule

### Bad Example - Missing release()

```typescript
// BAD: Connection leaked if exception occurs between connect and try block
const queryRunner = AppDataSource.createQueryRunner();
await queryRunner.connect();
await queryRunner.startTransaction();

try {
  await queryRunner.manager.save(User, userData);
  await queryRunner.commitTransaction();
} catch (error) {
  await queryRunner.rollbackTransaction();
  throw error;
}
// Missing queryRunner.release() - connection never returned to pool!
```

**Why bad:** Without `release()` in `finally`, the connection is permanently leaked. After enough transactions, the pool is exhausted and all queries block indefinitely.

---

## QueryRunner with Raw Queries

### Good Example - Using QueryRunner for Raw SQL in Transaction

```typescript
const queryRunner = AppDataSource.createQueryRunner();
await queryRunner.connect();
await queryRunner.startTransaction();

try {
  // Mix ORM and raw queries in same transaction
  await queryRunner.manager.save(User, userData);

  // Raw query on same transactional connection
  await queryRunner.query(
    `INSERT INTO "audit_logs" ("action", "entity_id", "created_at")
     VALUES ($1, $2, NOW())`,
    ["user_created", userData.id],
  );

  await queryRunner.commitTransaction();
} catch (error) {
  await queryRunner.rollbackTransaction();
  throw error;
} finally {
  await queryRunner.release();
}
```

**Why good:** `queryRunner.query()` runs on the same transactional connection, can mix ORM operations with raw SQL, parameterized query prevents injection

---

## Transaction with Repository

### Good Example - Getting Transactional Repository

```typescript
await AppDataSource.transaction(async (manager) => {
  // Get repository scoped to this transaction
  const userRepo = manager.getRepository(User);
  const postRepo = manager.getRepository(Post);

  const user = await userRepo.findOneBy({ id: userId });
  if (!user) throw new Error("User not found");

  // These operations all use the transactional connection
  await userRepo.update({ id: userId }, { role: "admin" });
  await postRepo.update({ authorId: userId }, { published: true });
});
```

**Why good:** `manager.getRepository()` returns a repository scoped to the transaction - all operations through it participate in the transaction

### Bad Example - Using AppDataSource.getRepository in Transaction

```typescript
// BAD: Global repository, not transactional
await AppDataSource.transaction(async (manager) => {
  const userRepo = AppDataSource.getRepository(User); // WRONG!
  await userRepo.save(userData); // Runs outside transaction
});
```

**Why bad:** `AppDataSource.getRepository()` returns the global repository which operates outside the transaction context

---

## Error Handling in Transactions

### Good Example - Typed Error Handling

```typescript
import { EntityNotFoundError, QueryFailedError } from "typeorm";

const UNIQUE_VIOLATION_CODE = "23505"; // PostgreSQL
const FK_VIOLATION_CODE = "23503"; // PostgreSQL

const createUserWithProfile = async (email: string, name: string) => {
  try {
    return await AppDataSource.transaction(async (manager) => {
      const user = await manager.save(User, { email, name });
      await manager.save(Profile, { userId: user.id, bio: "" });
      return user;
    });
  } catch (error) {
    if (error instanceof QueryFailedError) {
      const driverError = error.driverError as { code?: string };
      if (driverError.code === UNIQUE_VIOLATION_CODE) {
        throw new Error("Email already registered");
      }
      if (driverError.code === FK_VIOLATION_CODE) {
        throw new Error("Referenced record not found");
      }
    }
    if (error instanceof EntityNotFoundError) {
      throw new Error("Required entity not found");
    }
    throw error;
  }
};
```

**Why good:** TypeORM error types for specific handling, PostgreSQL error codes as named constants, rethrows unknown errors

---

## Quick Reference

| Approach                 | Auto Commit/Rollback | Isolation Level                        | Raw SQL Access            |
| ------------------------ | -------------------- | -------------------------------------- | ------------------------- |
| `DataSource.transaction` | Yes                  | Via first param                        | Via `manager.query()`     |
| `manager.transaction`    | Yes                  | Via first param                        | Via `manager.query()`     |
| `QueryRunner`            | No (manual)          | Via `startTransaction("SERIALIZABLE")` | Via `queryRunner.query()` |

| Isolation Level    | Dirty Read | Non-Repeatable Read | Phantom Read | Use When                                |
| ------------------ | ---------- | ------------------- | ------------ | --------------------------------------- |
| `READ UNCOMMITTED` | Yes        | Yes                 | Yes          | Never (debugging only)                  |
| `READ COMMITTED`   | No         | Yes                 | Yes          | Default for most operations             |
| `REPEATABLE READ`  | No         | No                  | Yes          | Reports, consistent reads               |
| `SERIALIZABLE`     | No         | No                  | No           | Financial transactions, critical writes |

| Critical Rule                                                      | Reason                              |
| ------------------------------------------------------------------ | ----------------------------------- |
| Use `manager` param, not `AppDataSource.manager`                   | Global manager bypasses transaction |
| Use `manager.getRepository()`, not `AppDataSource.getRepository()` | Same reason                         |
| `queryRunner.release()` in `finally`                               | Prevents connection pool exhaustion |
| Keep transactions short                                            | Long transactions lock rows/tables  |
| Throw to rollback in callback transactions                         | Any exception triggers rollback     |