Api Database Typeorm

Database with TypeORM

Quick Guide: Use TypeORM for decorator-based database access with full TypeScript support. Schema defined via entity classes with @Entity, @Column, @PrimaryGeneratedColumn. Use Data Mapper pattern (repositories) over Active Record for non-trivial apps. Never use `synchronize: true` in production - use migrations. Prefer insert()/update() over save() when you know the operation type - save() always executes a SELECT first. Use QueryRunner transactions for full control. Eager relations only work with find* methods, not QueryBuilder.

---

<critical_requirements>

CRITICAL: Before Using This Skill

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

(You MUST NEVER use `synchronize: true` in production - it can drop columns and lose data when entities change)

(You MUST use `insert()`/`update()` instead of `save()` when the operation type is known - `save()` always runs an extra SELECT query)

(You MUST use the provided transaction `manager` parameter or `queryRunner.manager` inside transactions - NEVER use the global entity manager or repository)

(You MUST define relations with explicit `@JoinColumn()` on the owning side of `@OneToOne` and optionally `@ManyToOne`, and `@JoinTable()` on one side of `@ManyToMany`)

</critical_requirements>

---

Auto-detection: typeorm, TypeORM, DataSource, @Entity, @Column, @PrimaryGeneratedColumn, @ManyToOne, @OneToMany, @ManyToMany, createQueryBuilder, getRepository, EntityManager, QueryRunner, migration:generate, migration:run

When to use:

• Decorator-based entity definitions with TypeScript
• Applications requiring both Active Record and Data Mapper patterns
• Complex queries needing QueryBuilder with joins and subqueries
• Projects where class-based ORM feels natural (especially with DI-based frameworks)

When NOT to use:

• Schema-first workflows (consider schema-first ORMs instead)
• Needing fully type-safe queries without runtime decorators (consider lighter ORMs)
• Edge/serverless with minimal cold start (decorator metadata adds weight)
• Projects avoiding reflect-metadata and experimentalDecorators

Key patterns covered:

• DataSource configuration and entity registration
• Entity definitions with decorators and column types
• Relations (OneToOne, OneToMany, ManyToOne, ManyToMany)
• Repository CRUD and QueryBuilder
• Migrations (generate, run, revert)
• Transactions (EntityManager callback, QueryRunner manual)
• save() vs insert()/update() performance

Detailed Resources:

• examples/core.md - DataSource setup, entities, CRUD, repository patterns
• examples/relations.md - All relation types, eager/lazy loading, cascades
• examples/query-builder.md - Joins, subqueries, pagination, raw queries
• examples/migrations.md - Generate, run, revert, CLI configuration
• examples/transactions.md - EntityManager, QueryRunner, isolation levels
• examples/advanced.md - Subscribers, listeners, tree entities, embedded entities
• reference.md - Decision frameworks, anti-patterns, performance, checklists

---

<philosophy>

Philosophy

TypeORM uses TypeScript decorators to define database entities as classes. It supports both the Active Record and Data Mapper patterns, giving teams flexibility in how they structure data access.

Core principles:

1. Decorator-based schema - Entities are classes decorated with @Entity, @Column, etc. 2. Pattern flexibility - Active Record for simplicity, Data Mapper for separation of concerns 3. QueryBuilder power - SQL-like fluent API for complex queries beyond simple find* 4. Migration-driven - Schema changes through versioned migration files, never auto-sync in production

Active Record vs Data Mapper:

• Active Record: Entities extend BaseEntity, call User.find(), user.save() directly. Good for small apps and rapid prototyping.
• Data Mapper: Entities are plain classes, repositories handle persistence (userRepo.find(), userRepo.save()). Better for complex apps, testing, and separation of concerns.

Recommendation: Use Data Mapper for any non-trivial application. Active Record couples domain logic to persistence, making testing and refactoring harder.

</philosophy>

---

<patterns>

Core Patterns

Pattern 1: DataSource Configuration

Configure the DataSource as a singleton. Export it for both the application and migration CLI.

// data-source.ts
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,
  port: Number(process.env.DB_PORT),
  username: process.env.DB_USER,
  password: process.env.DB_PASS,
  database: process.env.DB_NAME,
  entities: [User, Post],
  migrations: ["./src/migrations/*.ts"],
  synchronize: false, // NEVER true in production
  logging: process.env.NODE_ENV === "development",
});

Why good: Single DataSource export used by both app and CLI, synchronize: false prevents data loss, env vars for config

// BAD: synchronize in production
const AppDataSource = new DataSource({
  synchronize: true, // Drops columns, loses data on entity changes
  entities: ["./src/**/*.entity.ts"], // Glob patterns are fragile
});

Why bad: synchronize: true alters schema on startup (can drop columns with data), glob entity paths break with bundlers and are non-deterministic

See examples/core.md for initialization, graceful shutdown, and entity registration patterns.

---

Pattern 2: Entity Definition

Entities are classes with decorators mapping to database tables and columns.

import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
  CreateDateColumn,
  UpdateDateColumn,
  Index,
} from "typeorm";

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

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

  @Column()
  name: string;

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

  @CreateDateColumn()
  createdAt: Date;

  @UpdateDateColumn()
  updatedAt: Date;
}

Why good: Explicit table name avoids casing issues, uuid for distributed-safe IDs, CreateDateColumn/UpdateDateColumn auto-managed by TypeORM, enum column with default

// BAD: Missing explicit table name, no index on frequently queried column
@Entity() // Table name derived from class name - casing varies by database
export class UserProfile {
  @PrimaryGeneratedColumn() // Auto-increment integer - problematic for distributed systems
  id: number;

  @Column()
  userId: string; // No index, no foreign key relation defined
}

Why bad: Derived table names cause casing inconsistency across databases, auto-increment IDs conflict in distributed systems, missing indexes on lookup columns

See examples/core.md for column types, nullable columns, and default values.

---

Pattern 3: Repository CRUD - save() vs insert()/update()

The critical performance distinction: save() always runs a SELECT first. Use insert()/update() when you know the operation.

const userRepo = AppDataSource.getRepository(User);

// CREATING: Use insert() - single INSERT query
await userRepo.insert({
  email: "alice@example.com",
  name: "Alice",
});

// UPDATING: Use update() - single UPDATE query
const ACTIVE_ROLE = "admin";
await userRepo.update({ id: userId }, { role: ACTIVE_ROLE });

// UPSERTING: Use upsert() - INSERT ... ON CONFLICT
await userRepo.upsert(
  { email: "alice@example.com", name: "Alice Updated" },
  ["email"], // conflict columns
);

// save() - only when you need cascade saves or don't know if inserting/updating
const user = userRepo.create({ email: "bob@example.com", name: "Bob" });
await userRepo.save(user); // SELECT + INSERT (2 queries)

Why good: insert()/update() execute single queries, upsert() handles conflicts atomically, save() reserved for when cascades or ambiguous operations are needed

// BAD: Using save() for everything
const user = new User();
user.email = "alice@example.com";
user.name = "Alice";
await userRepo.save(user); // Runs SELECT first, then INSERT - 2 round trips

// BAD: Using save() in a loop
for (const data of users) {
  await userRepo.save(data); // 2N queries instead of 1 bulk insert
}

Why bad: save() always runs SELECT + INSERT/UPDATE (2 round trips), in loops this becomes 2N queries; use insert() for bulk creates

See examples/core.md for find operations, bulk operations, and soft delete patterns.

---

Pattern 4: Relations

Define relations with decorators. The owning side holds the foreign key.

import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
  ManyToOne,
  OneToMany,
  JoinColumn,
} from "typeorm";

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

  @Column()
  title: string;

  // Owning side - holds the foreign key column
  @ManyToOne(() => User, (user) => user.posts, { onDelete: "CASCADE" })
  @JoinColumn({ name: "author_id" }) // Explicit FK column name
  author: User;

  @Column()
  authorId: string; // Expose FK for queries without joining
}

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

  // Inverse side - no FK column here
  @OneToMany(() => Post, (post) => post.author)
  posts: Post[];
}

Why good: Explicit @JoinColumn names the FK column, authorId exposed for direct queries, onDelete: "CASCADE" prevents orphans, inverse side defined for bidirectional navigation

// BAD: Missing JoinColumn, no onDelete, relation typed as required
@ManyToOne(() => User)
author: User; // No explicit FK column name, no cascade delete

Why bad: Auto-generated FK column name may not match conventions, missing onDelete leaves orphaned rows, relation property should be User | undefined since it's not always loaded

See examples/relations.md for all relation types, ManyToMany with JoinTable, and eager/lazy loading.

---

Pattern 5: QueryBuilder

For queries beyond simple find*, use the QueryBuilder's fluent API.

const DEFAULT_PAGE_SIZE = 20;
const MAX_PAGE_SIZE = 100;

const users = await AppDataSource.getRepository(User)
  .createQueryBuilder("user")
  .leftJoinAndSelect("user.posts", "post", "post.published = :pub", {
    pub: true,
  })
  .where("user.role = :role", { role: "admin" })
  .andWhere("user.createdAt > :date", { date: new Date("2024-01-01") })
  .orderBy("user.createdAt", "DESC")
  .take(DEFAULT_PAGE_SIZE)
  .skip(0)
  .getMany();

Why good: Parameterized queries prevent SQL injection, leftJoinAndSelect loads relations in one query, take/skip for pagination (relation-safe unlike limit/offset)

// BAD: String interpolation in where clause
const users = await userRepo
  .createQueryBuilder("user")
  .where(`user.email = '${email}'`) // SQL INJECTION!
  .getMany();

Why bad: String interpolation opens SQL injection vulnerability; always use :paramName with parameter objects

See examples/query-builder.md for subqueries, aggregations, raw queries, and advanced joins.

---

Pattern 6: Migrations

Generate migrations from entity changes, never manually write SQL unless necessary.

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

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

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

Why good: Auto-generated migrations capture exact schema diff, -d flag points to DataSource config, revert undoes one migration at a time

See examples/migrations.md for migration class structure, manual migrations, and transaction control.

---

Pattern 7: Transactions

Two approaches: EntityManager callback (simple) and QueryRunner (full control).

// Approach 1: EntityManager callback - simple, auto-commits/rollbacks
await AppDataSource.transaction(async (manager) => {
  await manager.save(User, userData);
  await manager.save(Post, postData);
  // If any operation throws, entire transaction rolls back
});

// Approach 2: QueryRunner - manual control, reusable connection
const queryRunner = AppDataSource.createQueryRunner();
await queryRunner.connect();
await queryRunner.startTransaction();
try {
  await queryRunner.manager.save(User, userData);
  await queryRunner.manager.save(Post, postData);
  await queryRunner.commitTransaction();
} catch (error) {
  await queryRunner.rollbackTransaction();
  throw error;
} finally {
  await queryRunner.release(); // ALWAYS release
}

Why good: EntityManager callback is concise with auto-rollback, QueryRunner gives explicit commit/rollback control, finally block ensures connection release

// BAD: Using global manager inside transaction
await AppDataSource.transaction(async (manager) => {
  await AppDataSource.manager.save(User, userData); // WRONG: bypasses transaction!
  await manager.save(Post, postData);
});

Why bad: AppDataSource.manager is the global manager, not the transactional one - operations using it run outside the transaction and won't roll back

See examples/transactions.md for isolation levels, QueryRunner patterns, and nested transactions.

</patterns>

---

<red_flags>

RED FLAGS

High Priority Issues:

• synchronize: true in production - alters schema on startup, can drop columns and lose data
• Using save() for all writes - always runs SELECT first, 2x round trips for known inserts/updates
• String interpolation in QueryBuilder .where() - SQL injection vulnerability
• Using global entity manager inside transactions - bypasses transaction context
• Missing queryRunner.release() in finally block - leaks database connections

Medium Priority Issues:

• No indexes on frequently filtered columns - slow queries as data grows
• Missing onDelete cascade on relations - orphaned rows when parent deleted
• Using eager: true on both sides of a relation - TypeORM disallows this, throws error
• Glob patterns for entity paths ("./src/**/*.entity.ts") - breaks with bundlers
• Initializing relation arrays with = [] - causes TypeORM to detach all existing relations on save

Common Mistakes:

• Expecting eager relations to work with QueryBuilder - eager only works with find* methods, use leftJoinAndSelect instead
• Using @BeforeUpdate/@AfterUpdate with update() - listeners only fire with save(), not update()/insert()
• Forgetting reflect-metadata import at app entry point - decorators silently fail
• Using limit()/offset() with joins in QueryBuilder - returns wrong results; use take()/skip() instead
• Not exposing FK column (e.g., authorId) alongside relation - forces a join for simple lookups

Gotchas & Edge Cases:

• save() returns the saved entity but reloads it from DB - the returned object may differ from input
• update() and delete() return UpdateResult/DeleteResult with affected count, not the entity
• findOne({ where: {} }) with empty where returns the first row, not null - always provide conditions
• Enum changes in entity require a migration - database enum types don't auto-update
• @Column({ select: false }) excludes column from default SELECTs - must explicitly select with QueryBuilder
• Lazy relations require Promise<T> type on the property - not intuitive for JS/TS developers
• cascade: true can save unintended nested objects - be explicit with cascade: ["insert"] or cascade: ["update"]
• Transaction isolation varies by database driver - not all levels available on all databases
• QueryRunner must be released even on success - failure to release leaks connections until pool exhaustion

</red_flags>

---

<critical_reminders>

CRITICAL REMINDERS

All code must follow project conventions in CLAUDE.md

(You MUST NEVER use `synchronize: true` in production - it can drop columns and lose data when entities change)

(You MUST use `insert()`/`update()` instead of `save()` when the operation type is known - `save()` always runs an extra SELECT query)

(You MUST use the provided transaction `manager` parameter or `queryRunner.manager` inside transactions - NEVER use the global entity manager or repository)

(You MUST define relations with explicit `@JoinColumn()` on the owning side of `@OneToOne` and optionally `@ManyToOne`, and `@JoinTable()` on one side of `@ManyToMany`)

Failure to follow these rules will cause data loss from schema sync, doubled query counts from unnecessary SELECTs, broken transaction atomicity, and connection pool exhaustion.

</critical_reminders>

TypeORM - Advanced Examples

Subscribers, listeners, tree entities, embedded entities. See SKILL.md for core concepts.

Prerequisites: Understand entity definitions and DataSource from core.md.

---

Entity Listeners

Good Example - Lifecycle Hooks on Entity

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

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

---

Subscribers

Good Example - Audit Log Subscriber

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:

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

Good Example - Global Subscriber (All Entities)

@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

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:

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)

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:

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

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

@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+)

@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

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

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

---

Quick Reference

TypeORM - Core Examples

DataSource setup, entity definitions, CRUD operations, and repository patterns. See SKILL.md for decision guidance.

Prerequisites: None - these are the foundational patterns.

---

DataSource Initialization

Good Example - Async Initialization with Graceful Shutdown

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

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

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

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

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

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

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

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

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

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

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

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

TypeORM - Migration Examples

Generate, run, revert migrations and CLI configuration. See SKILL.md for core concepts.

Prerequisites: Understand DataSource configuration from core.md.

---

DataSource for Migrations

Good Example - Separate Export for CLI

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

# 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

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

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

# 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

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

# 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

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

TypeORM - QueryBuilder Examples

Joins, subqueries, pagination, aggregation, and raw queries. See SKILL.md for core concepts.

Prerequisites: Understand entity definitions and repository patterns from core.md.

---

Basic QueryBuilder

Good Example - Select with Conditions

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

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

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

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

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

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

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

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

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

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

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

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

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

TypeORM - Relations Examples

All relation types, eager/lazy loading, cascades, and relation gotchas. See SKILL.md for core concepts.

Prerequisites: Understand entity definitions and repository patterns from core.md.

---

One-to-One Relations

Good Example - Profile Relation

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

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

@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

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

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

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

// 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\*)

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

@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

@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

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

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

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

TypeORM - Transaction Examples

EntityManager callback, QueryRunner manual control, and isolation levels. See SKILL.md for core concepts.

Prerequisites: Understand DataSource and repository patterns from core.md.

---

EntityManager Callback (Simple Approach)

Good Example - Automatic Commit/Rollback

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

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

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

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

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

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

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

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

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

TypeORM Reference

Decision frameworks, anti-patterns, performance optimization, and checklists for TypeORM.

---

<decision_framework>

Decision Framework

Active Record vs Data Mapper?

What's the project size and complexity?
├─ Small app, rapid prototype, few entities
│   └─ Active Record (entities extend BaseEntity)
├─ Medium-large app, team collaboration
│   └─ Data Mapper (repositories, separation of concerns)
├─ Need to unit test business logic without DB
│   └─ Data Mapper (repositories are injectable/mockable)
└─ DI-based framework (dependency injection)
    └─ Data Mapper (repositories are injectable/mockable)

Which Write Method?

Know the operation type?
├─ Definitely inserting new row(s)
│   ├─ Single row → insert()
│   └─ Multiple rows → insert([...]) (batch)
├─ Definitely updating existing row(s)
│   ├─ By condition → update(criteria, partialEntity)
│   └─ Increment/decrement → update(criteria, { count: () => "count + 1" })
├─ Insert or update (upsert)
│   └─ upsert(entity, conflictColumns)
├─ Don't know if inserting or updating
│   └─ save() (runs SELECT first - acceptable here)
└─ Need cascade saves (nested relations)
    └─ save() (only method that triggers cascades)

Which Read Method?

What data do you need?
├─ Single record by primary key or unique field
│   ├─ May not exist → findOne({ where: { id } })
│   └─ Must exist (throw if missing) → findOneOrFail({ where: { id } })
├─ Multiple records
│   ├─ Simple filters → find({ where, order, take, skip })
│   └─ Complex joins/subqueries → createQueryBuilder()
├─ Count only
│   └─ count({ where })
├─ Check existence
│   └─ exists({ where }) or existsBy({ field })
└─ Aggregate (SUM, AVG, etc.)
    └─ createQueryBuilder().select("SUM(...)").getRawOne()

QueryBuilder vs find\*?

What's the query complexity?
├─ Simple CRUD with filters
│   └─ find/findOne (cleaner, fully typed)
├─ Need joins with conditions
│   ├─ Eager relations (unconditional) → find with relations option
│   └─ Conditional joins → createQueryBuilder with leftJoinAndSelect
├─ Need subqueries
│   └─ createQueryBuilder with .subQuery()
├─ Need aggregations (GROUP BY, HAVING)
│   └─ createQueryBuilder with .groupBy().having()
├─ Need raw SQL fragments
│   └─ createQueryBuilder with .addSelect(() => subQuery)
└─ Need pagination with joins
    └─ createQueryBuilder with .take()/.skip() (NOT limit/offset)

Which Transaction Approach?

What level of control do you need?
├─ Simple: all operations succeed or all fail
│   └─ DataSource.transaction(async (manager) => { ... })
├─ Need specific isolation level
│   └─ DataSource.manager.transaction("SERIALIZABLE", async (manager) => { ... })
├─ Need manual commit/rollback control
│   └─ QueryRunner (connect, startTransaction, commit/rollback, release)
├─ Need to reuse connection across operations
│   └─ QueryRunner (single connection instance)
└─ Nested transactions / savepoints
    └─ QueryRunner with createQueryRunner() per level

</decision_framework>

---

<performance>

Performance Optimization

Indexing Strategy

Add indexes for columns used in WHERE, ORDER BY, and JOIN conditions:

@Entity("posts")
@Index(["authorId"]) // Single column - FK lookups
@Index(["authorId", "published"]) // Composite - common query pattern
@Index(["createdAt"]) // Sort by date
@Index(["title"], { fulltext: true }) // Full-text search (MySQL/PostgreSQL)
export class Post {
  @PrimaryGeneratedColumn("uuid")
  id: string;

  @Column()
  @Index({ unique: true }) // Inline unique index
  slug: string;

  @Column()
  authorId: string;

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

  @CreateDateColumn()
  createdAt: Date;
}

Avoid save() for Known Operations

// WRONG: save() for bulk inserts (2N queries)
for (const item of items) {
  await repo.save(item);
}

// CORRECT: insert() for bulk (1 query)
await repo.insert(items);

// WRONG: save() for updating one field (SELECT + UPDATE)
const user = await repo.findOneBy({ id: userId });
user.name = "New Name";
await repo.save(user);

// CORRECT: update() directly (1 query)
await repo.update({ id: userId }, { name: "New Name" });

Use select to Reduce Payload

// WRONG: Loading all columns when you need two
const users = await userRepo.find();

// CORRECT: Select only needed columns
const users = await userRepo.find({
  select: { id: true, name: true },
});

take/skip vs limit/offset in QueryBuilder

// WRONG: limit/offset with joins returns wrong count
const posts = await postRepo
  .createQueryBuilder("post")
  .leftJoinAndSelect("post.comments", "comment")
  .limit(10) // Limits total ROWS including joined rows
  .offset(0)
  .getMany();

// CORRECT: take/skip works on entities, not rows
const posts = await postRepo
  .createQueryBuilder("post")
  .leftJoinAndSelect("post.comments", "comment")
  .take(10) // Returns 10 posts (regardless of comment count)
  .skip(0)
  .getMany();

Batch Operations

// WRONG: Individual operations in a loop
for (const id of deleteIds) {
  await repo.delete(id);
}

// CORRECT: Batch delete
await repo.delete(deleteIds);

// CORRECT: Batch update with QueryBuilder
await repo
  .createQueryBuilder()
  .update(Post)
  .set({ published: true })
  .where("authorId = :authorId", { authorId })
  .andWhere("status = :status", { status: "reviewed" })
  .execute();

Connection Management

// Pool configuration in DataSource options
const AppDataSource = new DataSource({
  type: "postgres",
  extra: {
    max: 20, // Max connections in pool
    idleTimeoutMillis: 10000,
    connectionTimeoutMillis: 3000,
  },
});

// Graceful shutdown
process.on("SIGTERM", async () => {
  await AppDataSource.destroy();
  process.exit(0);
});

</performance>

---

<anti_patterns>

Anti-Patterns to Avoid

Using synchronize in Production

// ANTI-PATTERN
const AppDataSource = new DataSource({
  synchronize: true, // NEVER in production
});

Why it's wrong: synchronize compares entities to DB schema and alters tables on every startup. Renaming a column creates a new column and drops the old one - losing all data in that column.

What to do instead: Use migrations: migration:generate to create, migration:run to apply.

---

save() for Everything

// ANTI-PATTERN: Always using save()
const newUser = repo.create({ email, name });
await repo.save(newUser); // SELECT + INSERT = 2 queries

const existingUser = await repo.findOneBy({ id });
existingUser.name = "Updated";
await repo.save(existingUser); // SELECT + UPDATE = 2 queries

Why it's wrong: save() runs a SELECT before every INSERT or UPDATE to determine which to execute. For known operations, this doubles query count. The SELECT includes a subquery that is slow on large tables.

What to do instead: Use insert() for creates, update() for updates, upsert() for upsert.

---

Global Manager in Transactions

// ANTI-PATTERN
await AppDataSource.transaction(async (manager) => {
  // WRONG: using global repository/manager
  await AppDataSource.getRepository(User).save(userData);
  // WRONG: using global manager
  await AppDataSource.manager.save(Post, postData);
  // Only this is correct:
  await manager.save(Post, otherData);
});

Why it's wrong: Global manager/repositories execute outside the transaction. If manager.save(Post) fails, the User save won't roll back.

What to do instead: All operations inside the callback must use the manager parameter (or queryRunner.manager for QueryRunner transactions).

---

Eager Loading on Both Sides

// ANTI-PATTERN
@Entity()
export class User {
  @OneToMany(() => Post, (post) => post.author, { eager: true })
  posts: Post[];
}

@Entity()
export class Post {
  @ManyToOne(() => User, (user) => user.posts, { eager: true }) // ERROR
  author: User;
}

Why it's wrong: TypeORM forbids eager: true on both sides of a relation - it would cause infinite recursion. This throws an error at runtime.

What to do instead: Set eager: true on only one side, or omit it entirely and load relations explicitly.

---

Initializing Relation Arrays

// ANTI-PATTERN
@Entity()
export class Question {
  @ManyToMany(() => Category)
  @JoinTable()
  categories: Category[] = []; // Initializing to empty array
}

Why it's wrong: When you save() a Question loaded from the DB, TypeORM sees the empty array and detaches all existing categories. The initialization overwrites the loaded relation data.

What to do instead: Don't initialize relation properties. Let TypeORM manage them.

---

Missing QueryRunner Release

// ANTI-PATTERN
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 leaked!

Why it's wrong: Without release() in a finally block, the connection is never returned to the pool. After enough leaked connections, the pool is exhausted and all queries block.

What to do instead: Always call queryRunner.release() in a finally block.

</anti_patterns>

---

Quick Reference Tables

Column Type Mapping

Relation Decorator Rules

find\* Options

Migration Commands

---

Checklists

Before Deploying

• [ ] synchronize: false in production DataSource
• [ ] All entity changes captured in migrations
• [ ] Indexes on frequently filtered/sorted columns
• [ ] onDelete cascade configured on child relations
• [ ] Connection pool limits configured for environment
• [ ] Graceful shutdown calls AppDataSource.destroy()
• [ ] reflect-metadata imported at application entry point

Code Review Checklist

• [ ] insert()/update() used instead of save() where operation is known
• [ ] No string interpolation in QueryBuilder .where() - parameterized queries only
• [ ] take()/skip() used instead of limit()/offset() with joins
• [ ] QueryRunner always released in finally block
• [ ] Transaction callback uses provided manager, not global one
• [ ] Relation properties NOT initialized with = []
• [ ] FK columns exposed alongside relation properties for simple lookups
• [ ] Named constants for pagination limits and timeouts