TypeORM Patterns¶

TypeORM bridges TypeScript entities and PostgreSQL tables through decorators and the repository pattern. This chapter uses Warmwind's real domain (User, AgentSession, AgentAction) to teach relation mapping, QueryBuilder for complex queries, expand-contract migrations, transaction management, and the seven most common performance pitfalls -- with every example showing the SQL TypeORM actually emits.

1. Domain Entities -- Warmwind's Core Model¶

1.1 ER Diagram¶

erDiagram
    TENANT ||--o{ USER : "has"
    USER ||--o{ AGENT_SESSION : "starts"
    AGENT_SESSION ||--o{ AGENT_ACTION : "contains"
    AGENT_SESSION }o--|| CONTAINER : "runs in"
    CONTAINER }o--|| TEMPLATE : "created from"
    TENANT ||--o{ CONTAINER : "owns"

    USER {
        uuid id PK
        uuid tenant_id FK
        text email
        text password_hash
        text[] roles
        timestamptz created_at
    }
    AGENT_SESSION {
        uuid id PK
        uuid user_id FK
        uuid container_id FK
        uuid tenant_id FK
        text status
        jsonb config
        timestamptz started_at
        timestamptz ended_at
    }
    AGENT_ACTION {
        uuid id PK
        uuid session_id FK
        text action_type
        jsonb input
        jsonb output
        int duration_ms
        timestamptz created_at
    }

1.2 Entity: User¶

// entities/user.entity.ts
import {
  Entity, PrimaryGeneratedColumn, Column, CreateDateColumn,
  OneToMany, Index, ManyToOne, JoinColumn,
} from 'typeorm';
import { AgentSession } from './agent-session.entity';
import { Tenant } from './tenant.entity';

export enum Role {
  OPERATOR = 'operator',
  ADMIN    = 'admin',
  VIEWER   = 'viewer',
}

@Entity('users')
@Index(['tenantId', 'email'], { unique: true })  // email unique per tenant
export class User {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column({ type: 'uuid', name: 'tenant_id' })
  tenantId: string;

  @ManyToOne(() => Tenant, (t) => t.users, { onDelete: 'CASCADE' })
  @JoinColumn({ name: 'tenant_id' })
  tenant: Tenant;

  @Column({ type: 'text', unique: false })
  email: string;

  @Column({ type: 'text', name: 'password_hash', select: false })
  passwordHash: string;

  @Column({ type: 'text', array: true, default: `{${Role.OPERATOR}}` })
  roles: Role[];

  @CreateDateColumn({ name: 'created_at' })
  createdAt: Date;

  @OneToMany(() => AgentSession, (s) => s.user)
  sessions: AgentSession[];
}

SQL TypeORM generates for the table:

CREATE TABLE "users" (
    "id"            UUID NOT NULL DEFAULT uuid_generate_v4(),
    "tenant_id"     UUID NOT NULL,
    "email"         TEXT NOT NULL,
    "password_hash" TEXT NOT NULL,
    "roles"         TEXT[] NOT NULL DEFAULT '{operator}',
    "created_at"    TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    CONSTRAINT "PK_users" PRIMARY KEY ("id"),
    CONSTRAINT "FK_users_tenant" FOREIGN KEY ("tenant_id")
        REFERENCES "tenants"("id") ON DELETE CASCADE
);
CREATE UNIQUE INDEX "IDX_users_tenant_email" ON "users" ("tenant_id", "email");

1.3 Entity: AgentSession¶

// entities/agent-session.entity.ts
import {
  Entity, PrimaryGeneratedColumn, Column, CreateDateColumn,
  ManyToOne, OneToMany, JoinColumn, Index,
} from 'typeorm';
import { User } from './user.entity';
import { Container } from './container.entity';
import { AgentAction } from './agent-action.entity';

export enum SessionStatus {
  ACTIVE     = 'active',
  COMPLETED  = 'completed',
  FAILED     = 'failed',
  CANCELLED  = 'cancelled',
}

@Entity('agent_sessions')
@Index(['tenantId', 'status'])
@Index(['userId', 'startedAt'])
export class AgentSession {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column({ type: 'uuid', name: 'user_id' })
  userId: string;

  @ManyToOne(() => User, (u) => u.sessions, { onDelete: 'CASCADE' })
  @JoinColumn({ name: 'user_id' })
  user: User;

  @Column({ type: 'uuid', name: 'container_id' })
  containerId: string;

  @ManyToOne(() => Container, (c) => c.sessions, { onDelete: 'SET NULL' })
  @JoinColumn({ name: 'container_id' })
  container: Container;

  @Column({ type: 'uuid', name: 'tenant_id' })
  tenantId: string;

  @Column({ type: 'enum', enum: SessionStatus, default: SessionStatus.ACTIVE })
  status: SessionStatus;

  @Column({ type: 'jsonb', default: {} })
  config: Record<string, unknown>;

  @CreateDateColumn({ name: 'started_at' })
  startedAt: Date;

  @Column({ type: 'timestamptz', name: 'ended_at', nullable: true })
  endedAt: Date | null;

  @OneToMany(() => AgentAction, (a) => a.session)
  actions: AgentAction[];
}

1.4 Entity: AgentAction¶

// entities/agent-action.entity.ts
import {
  Entity, PrimaryGeneratedColumn, Column, CreateDateColumn,
  ManyToOne, JoinColumn, Index,
} from 'typeorm';
import { AgentSession } from './agent-session.entity';

export enum ActionType {
  TOOL_CALL     = 'tool_call',
  LLM_REQUEST   = 'llm_request',
  USER_INPUT    = 'user_input',
  SYSTEM_EVENT  = 'system_event',
}

@Entity('agent_actions')
@Index(['sessionId', 'createdAt'])
export class AgentAction {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column({ type: 'uuid', name: 'session_id' })
  sessionId: string;

  @ManyToOne(() => AgentSession, (s) => s.actions, { onDelete: 'CASCADE' })
  @JoinColumn({ name: 'session_id' })
  session: AgentSession;

  @Column({ type: 'enum', enum: ActionType, name: 'action_type' })
  actionType: ActionType;

  @Column({ type: 'jsonb', default: {} })
  input: Record<string, unknown>;

  @Column({ type: 'jsonb', default: {} })
  output: Record<string, unknown>;

  @Column({ type: 'int', name: 'duration_ms', nullable: true })
  durationMs: number | null;

  @CreateDateColumn({ name: 'created_at' })
  createdAt: Date;
}

Coming from JPA/Slick

JPA parallel: @Entity = @Entity, @PrimaryGeneratedColumn('uuid') = @Id @GeneratedValue(strategy = UUID), @ManyToOne is identical, and @JoinColumn maps directly. The main difference: JPA uses @Table(name=...) while TypeORM uses @Entity('table_name').

Slick parallel: Where Slick uses TableQuery and Rep[T] lifted embedding, TypeORM decorators are closer to JPA annotations -- imperative rather than functional. If you miss Slick's type-safe queries, see the Drizzle comparison at the end of this chapter.

2. Relations -- What SQL TypeORM Actually Emits¶

2.1 OneToMany / ManyToOne¶

// Loading user with sessions
const user = await userRepo.findOne({
  where: { id: userId },
  relations: { sessions: true },
});

Emitted SQL:

SELECT "u"."id", "u"."tenant_id", "u"."email", "u"."roles", "u"."created_at",
       "s"."id" AS "s_id", "s"."user_id", "s"."container_id", "s"."status",
       "s"."started_at", "s"."ended_at"
FROM   "users" "u"
LEFT JOIN "agent_sessions" "s" ON "s"."user_id" = "u"."id"
WHERE  "u"."id" = $1
-- Parameters: ['<userId>']

2.2 Nested Relations¶

// Loading session -> actions -> (no deeper)
const session = await sessionRepo.findOne({
  where: { id: sessionId },
  relations: { actions: true, user: true, container: true },
});

Emitted SQL:

SELECT "s".*, "a".*, "u".*, "c".*
FROM   "agent_sessions" "s"
LEFT JOIN "agent_actions" "a"  ON "a"."session_id" = "s"."id"
LEFT JOIN "users" "u"         ON "u"."id" = "s"."user_id"
LEFT JOIN "containers" "c"    ON "c"."id" = "s"."container_id"
WHERE  "s"."id" = $1

2.3 Relation Summary¶

TypeORM Decorator	FK Location	JPA Equivalent	Slick Equivalent
`@ManyToOne` + `@JoinColumn`	On this entity's table	`@ManyToOne` + `@JoinColumn`	`foreignKey` in `TableQuery`
`@OneToMany` (inverse)	On the other table	`@OneToMany(mappedBy=...)`	No direct equivalent (query)
`@OneToOne` + `@JoinColumn`	On this entity's table	`@OneToOne` + `@JoinColumn`	Manual join
`@ManyToMany` + `@JoinTable`	Junction table	`@ManyToMany` + `@JoinTable`	Manual junction table query

3. QueryBuilder -- Complex Queries¶

3.1 Dynamic Filtering with Pagination¶

async findSessions(
  tenantId: string,
  filters: {
    status?: SessionStatus;
    userId?: string;
    startedAfter?: Date;
    search?: string;  // searches user email
  },
  page = 1,
  limit = 25,
): Promise<[AgentSession[], number]> {
  const qb = this.sessionRepo
    .createQueryBuilder('s')
    .leftJoinAndSelect('s.user', 'u')
    .leftJoinAndSelect('s.container', 'c')
    .where('s.tenantId = :tenantId', { tenantId });

  if (filters.status) {
    qb.andWhere('s.status = :status', { status: filters.status });
  }
  if (filters.userId) {
    qb.andWhere('s.userId = :userId', { userId: filters.userId });
  }
  if (filters.startedAfter) {
    qb.andWhere('s.startedAt > :after', { after: filters.startedAfter });
  }
  if (filters.search) {
    qb.andWhere('u.email ILIKE :search', { search: `%${filters.search}%` });
  }

  return qb
    .orderBy('s.startedAt', 'DESC')
    .skip((page - 1) * limit)
    .take(limit)
    .getManyAndCount();  // Returns [entities, totalCount]
}

Emitted SQL (all filters active):

SELECT "s".*, "u".*, "c".*,
       COUNT(*) OVER() AS "cnt"
FROM   "agent_sessions" "s"
LEFT JOIN "users" "u"       ON "u"."id" = "s"."user_id"
LEFT JOIN "containers" "c"  ON "c"."id" = "s"."container_id"
WHERE  "s"."tenant_id" = $1
  AND  "s"."status" = $2
  AND  "s"."user_id" = $3
  AND  "s"."started_at" > $4
  AND  "u"."email" ILIKE $5
ORDER  BY "s"."started_at" DESC
LIMIT  $6 OFFSET $7

3.2 Aggregation with Raw SQL Fallback¶

// Session statistics per user
async getSessionStats(tenantId: string): Promise<SessionStatsDto[]> {
  return this.sessionRepo
    .createQueryBuilder('s')
    .select('s.userId', 'userId')
    .addSelect('u.email', 'email')
    .addSelect('COUNT(s.id)', 'totalSessions')
    .addSelect('COUNT(s.id) FILTER (WHERE s.status = :active)', 'activeSessions')
    .addSelect('AVG(EXTRACT(EPOCH FROM (s.endedAt - s.startedAt)))', 'avgDurationSec')
    .leftJoin('s.user', 'u')
    .where('s.tenantId = :tenantId', { tenantId })
    .setParameter('active', SessionStatus.ACTIVE)
    .groupBy('s.userId')
    .addGroupBy('u.email')
    .orderBy('"totalSessions"', 'DESC')
    .getRawMany();
}

3.3 Subqueries¶

// Find users whose latest session failed
const subQuery = this.sessionRepo
  .createQueryBuilder('sub')
  .select('MAX(sub.startedAt)')
  .where('sub.userId = u.id');

const usersWithFailedLastSession = await this.userRepo
  .createQueryBuilder('u')
  .innerJoin('u.sessions', 's')
  .where(`s.startedAt = (${subQuery.getQuery()})`)
  .andWhere('s.status = :failed', { failed: SessionStatus.FAILED })
  .setParameters(subQuery.getParameters())
  .getMany();

4. Migrations -- Expand-Contract Pattern¶

4.1 The Four-Phase Model¶

stateDiagram-v2
    [*] --> Expand: Phase 1
    Expand --> Migrate: Phase 2
    Migrate --> Contract: Phase 3
    Contract --> Cleanup: Phase 4
    Cleanup --> [*]

    state Expand {
        [*] --> AddNullableColumn
        AddNullableColumn --> CreateIndexConcurrently
    }
    state Migrate {
        [*] --> BackfillData
        BackfillData --> DualWrite
    }
    state Contract {
        [*] --> DeployNewCode
        DeployNewCode --> ReadNewColumn
    }
    state Cleanup {
        [*] --> DropOldColumn
        DropOldColumn --> AddNotNull
    }

4.2 Real Example: Adding `model_version` to AgentAction¶

Phase 1 -- Expand (safe, additive only):

// migrations/1710000001-ExpandAddModelVersion.ts
export class ExpandAddModelVersion1710000001 implements MigrationInterface {
  async up(qr: QueryRunner): Promise<void> {
    // Nullable column: existing code ignores it
    await qr.query(`
      ALTER TABLE agent_actions
      ADD COLUMN model_version TEXT
    `);

    // CONCURRENTLY: no table lock (cannot run inside a transaction)
    await qr.query(`
      CREATE INDEX CONCURRENTLY idx_actions_model_version
      ON agent_actions (model_version)
      WHERE model_version IS NOT NULL
    `);
  }

  async down(qr: QueryRunner): Promise<void> {
    await qr.query(`DROP INDEX IF EXISTS idx_actions_model_version`);
    await qr.query(`ALTER TABLE agent_actions DROP COLUMN IF EXISTS model_version`);
  }
}

CREATE INDEX CONCURRENTLY and TypeORM migrations

TypeORM wraps migrations in a transaction by default. CONCURRENTLY cannot run inside a transaction. Either set transaction: false in the migration runner options, or use a raw QueryRunner that commits before the concurrent index build.

Phase 2 -- Migrate (backfill existing data):

// migrations/1710000002-MigrateBackfillModelVersion.ts
export class MigrateBackfillModelVersion1710000002 implements MigrationInterface {
  async up(qr: QueryRunner): Promise<void> {
    // Batch backfill to avoid long-running transactions
    let affected = 1;
    while (affected > 0) {
      const result = await qr.query(`
        UPDATE agent_actions
        SET    model_version = output->>'model'
        WHERE  model_version IS NULL
          AND  action_type = 'llm_request'
          AND  id IN (
            SELECT id FROM agent_actions
            WHERE model_version IS NULL AND action_type = 'llm_request'
            LIMIT 5000
            FOR UPDATE SKIP LOCKED
          )
      `);
      affected = result[1]; // rows affected
    }
  }

  async down(qr: QueryRunner): Promise<void> {
    await qr.query(`UPDATE agent_actions SET model_version = NULL`);
  }
}

Phase 3 -- Contract (new code reads the column, deploy completes):

Application code now reads modelVersion from the entity.

Phase 4 -- Cleanup (remove technical debt):

// migrations/1710000003-CleanupModelVersion.ts
export class CleanupModelVersion1710000003 implements MigrationInterface {
  async up(qr: QueryRunner): Promise<void> {
    // Now safe: all running code versions use model_version
    await qr.query(`
      ALTER TABLE agent_actions
      ALTER COLUMN model_version SET NOT NULL,
      ALTER COLUMN model_version SET DEFAULT 'unknown'
    `);
  }

  async down(qr: QueryRunner): Promise<void> {
    await qr.query(`
      ALTER TABLE agent_actions
      ALTER COLUMN model_version DROP NOT NULL,
      ALTER COLUMN model_version DROP DEFAULT
    `);
  }
}

4.3 Migration CLI Commands¶

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

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

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

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

5. Transaction Management¶

5.1 DataSource.transaction (Recommended)¶

async startSession(userId: string, containerId: string, config: object): Promise<AgentSession> {
  return this.dataSource.transaction(async (manager) => {
    // All operations share a single connection and transaction
    const session = manager.create(AgentSession, {
      userId,
      containerId,
      tenantId: /* from JWT context */,
      config,
      status: SessionStatus.ACTIVE,
    });
    await manager.save(session);

    // Log the creation as the first action
    const action = manager.create(AgentAction, {
      sessionId: session.id,
      actionType: ActionType.SYSTEM_EVENT,
      input: { event: 'session_started', config },
      output: {},
    });
    await manager.save(action);

    // Update container status
    await manager.update(Container, containerId, { status: 'BUSY' });

    return session;
  });
}

Emitted SQL:

BEGIN;
INSERT INTO "agent_sessions" (...) VALUES ($1, $2, ...) RETURNING *;
INSERT INTO "agent_actions" (...) VALUES ($1, $2, ...) RETURNING *;
UPDATE "containers" SET "status" = 'BUSY' WHERE "id" = $1;
COMMIT;
-- On any error: ROLLBACK;

5.2 QueryRunner for Fine-Grained Control¶

async endSessionWithRetry(sessionId: string, maxRetries = 3): Promise<void> {
  const queryRunner = this.dataSource.createQueryRunner();
  await queryRunner.connect();

  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    await queryRunner.startTransaction('SERIALIZABLE');
    try {
      const session = await queryRunner.manager.findOneOrFail(AgentSession, {
        where: { id: sessionId },
        lock: { mode: 'pessimistic_write' },
      });

      if (session.status !== SessionStatus.ACTIVE) {
        await queryRunner.rollbackTransaction();
        return; // already ended
      }

      session.status = SessionStatus.COMPLETED;
      session.endedAt = new Date();
      await queryRunner.manager.save(session);

      await queryRunner.manager.update(
        Container,
        session.containerId,
        { status: 'IDLE' },
      );

      await queryRunner.commitTransaction();
      return;
    } catch (err) {
      await queryRunner.rollbackTransaction();
      if (err.code === '40001' && attempt < maxRetries) continue; // serialization failure
      throw err;
    }
  }
  await queryRunner.release();
}

Coming from JPA/Slick

JPA: @Transactional(isolation = SERIALIZABLE) with Spring's @Retryable for serialization retries. TypeORM has no declarative equivalent -- you manage the retry loop manually as shown above.

Slick: db.run(action.transactionally.withTransactionIsolation(Serializable)) wraps the whole DBIO chain. TypeORM's queryRunner is the imperative counterpart.

6. Repository Pattern in NestJS¶

6.1 Custom Repository¶

// repositories/agent-session.repository.ts
@Injectable()
export class AgentSessionRepository {
  constructor(
    @InjectRepository(AgentSession)
    private readonly repo: Repository<AgentSession>,
  ) {}

  findActiveByTenant(tenantId: string): Promise<AgentSession[]> {
    return this.repo.find({
      where: { tenantId, status: SessionStatus.ACTIVE },
      relations: { user: true, container: true },
      order: { startedAt: 'DESC' },
    });
  }

  findByIdWithActions(id: string): Promise<AgentSession | null> {
    return this.repo.findOne({
      where: { id },
      relations: { actions: true, user: true },
      order: { actions: { createdAt: 'ASC' } },
    });
  }

  async countByStatus(tenantId: string): Promise<Record<SessionStatus, number>> {
    const rows = await this.repo
      .createQueryBuilder('s')
      .select('s.status', 'status')
      .addSelect('COUNT(*)', 'count')
      .where('s.tenantId = :tenantId', { tenantId })
      .groupBy('s.status')
      .getRawMany<{ status: SessionStatus; count: string }>();

    return Object.fromEntries(
      rows.map((r) => [r.status, parseInt(r.count, 10)]),
    ) as Record<SessionStatus, number>;
  }
}

6.2 Module Registration¶

@Module({
  imports: [
    TypeOrmModule.forFeature([AgentSession, AgentAction, User, Container]),
  ],
  providers: [AgentSessionRepository, AgentSessionService],
  exports: [AgentSessionRepository],
})
export class AgentSessionModule {}

7. Seven Common Performance Pitfalls¶

Pitfall 1: N+1 Queries¶

// BAD: loads sessions, then fires one query per session for actions
const sessions = await sessionRepo.find({ where: { tenantId } });
for (const s of sessions) {
  console.log(s.actions); // lazy triggers SELECT for each session
}

// GOOD: single JOIN
const sessions = await sessionRepo.find({
  where: { tenantId },
  relations: { actions: true },
});

Emitted SQL (bad): N+1 queries. Emitted SQL (good): 1 query with LEFT JOIN.

Pitfall 2: Loading Entire Relations When You Need a Count¶

// BAD: loads ALL actions into memory just to count them
const session = await sessionRepo.findOne({
  where: { id },
  relations: { actions: true },
});
const count = session.actions.length;

// GOOD: count in the database
const count = await actionRepo.count({ where: { sessionId: id } });

Pitfall 3: Missing `select` -- Fetching All Columns¶

// BAD: loads JSONB payload columns (potentially 10KB+ each)
const sessions = await sessionRepo.find({ where: { tenantId } });

// GOOD: select only what you need
const sessions = await sessionRepo.find({
  where: { tenantId },
  select: { id: true, status: true, startedAt: true, userId: true },
});

Pitfall 4: `synchronize: true` in Production¶

// data-source.ts
export const dataSource = new DataSource({
  // ...
  synchronize: process.env.NODE_ENV === 'development',  // NEVER true in prod
  migrationsRun: true,  // run migrations on startup in prod
});

synchronize: true compares entities to the database and applies DDL without migrations -- it can drop columns, lose data, and cause downtime.

Pitfall 5: Implicit Cascade Saves¶

// BAD: cascade: true on User -> sessions saves ALL sessions on user.save()
@OneToMany(() => AgentSession, (s) => s.user, { cascade: true })
sessions: AgentSession[];

// The user save unexpectedly UPDATEs every session:
await userRepo.save(user); // fires UPDATE for each session in the array

// GOOD: explicitly save sessions when needed, no cascade
@OneToMany(() => AgentSession, (s) => s.user)
sessions: AgentSession[];

Pitfall 6: Not Using `CONCURRENTLY` for Index Creation¶

-- BAD: locks the entire table for writes during index build
CREATE INDEX idx_actions_type ON agent_actions (action_type);

-- GOOD: non-blocking (slower build, no lock)
CREATE INDEX CONCURRENTLY idx_actions_type ON agent_actions (action_type);

Pitfall 7: `getMany()` vs `getRawMany()` for Aggregations¶

// BAD: getMany() tries to map raw aggregate results into entities and fails silently
const stats = await qb
  .select('s.status').addSelect('COUNT(*)', 'count')
  .groupBy('s.status')
  .getMany(); // returns entities with most fields undefined

// GOOD: getRawMany() returns plain objects with your aliases
const stats = await qb
  .select('s.status', 'status').addSelect('COUNT(*)', 'count')
  .groupBy('s.status')
  .getRawMany(); // [{ status: 'active', count: '42' }, ...]

8. Drizzle ORM Comparison¶

Aspect	TypeORM	Drizzle
Paradigm	Active Record / Data Mapper with decorators	Functional, SQL-like TypeScript DSL
Type Safety	Runtime class validation; query results typed but loosely	Full compile-time inference from schema definition
Schema Definition	Decorator classes (`@Entity`, `@Column`)	Plain TS objects (`pgTable`, `text`, `uuid`)
Migrations	Auto-generate from entity diff	Auto-generate from schema diff (`drizzle-kit`)
Query Builder	Method chaining (`createQueryBuilder`)	SQL-like syntax (`db.select().from().where()`)
Raw SQL	`.query()` on repo or queryRunner	`sql\`SELECT ...`` tagged template
JPA Similarity	High (decorators, repository, entity manager)	Low (closer to Slick/jOOQ philosophy)
N+1 Protection	Manual (`relations`, `leftJoinAndSelect`)	Explicit by design (no lazy loading)
Bundle Size	~2.4 MB	~50 KB
Maturity	2016, large ecosystem	2022, growing rapidly

Coming from JPA/Slick

If TypeORM's decorator model feels familiar from JPA, Drizzle will feel closer to Slick or jOOQ -- schema-as-code, queries that look like SQL, compile-time type inference. For a Scala/Rust developer accustomed to type-driven design, Drizzle's approach may feel more natural.

Drizzle equivalent of the AgentAction entity:

// drizzle/schema.ts
import { pgTable, uuid, text, jsonb, integer, timestamp, pgEnum } from 'drizzle-orm/pg-core';

export const actionTypeEnum = pgEnum('action_type', [
  'tool_call', 'llm_request', 'user_input', 'system_event',
]);

export const agentActions = pgTable('agent_actions', {
  id:          uuid('id').primaryKey().defaultRandom(),
  sessionId:   uuid('session_id').notNull().references(() => agentSessions.id),
  actionType:  actionTypeEnum('action_type').notNull(),
  input:       jsonb('input').default({}).notNull(),
  output:      jsonb('output').default({}).notNull(),
  durationMs:  integer('duration_ms'),
  createdAt:   timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
});

Drizzle query:

const slowActions = await db
  .select({
    id: agentActions.id,
    tool: sql`${agentActions.input}->>'tool'`.as('tool'),
    durationMs: agentActions.durationMs,
  })
  .from(agentActions)
  .where(
    and(
      eq(agentActions.sessionId, sessionId),
      gt(agentActions.durationMs, 5000),
    ),
  )
  .orderBy(desc(agentActions.durationMs));

Glossary¶

Glossary

Entity: TypeScript class decorated with @Entity() mapping to a database table. Each instance represents a single row. Equivalent to JPA's @Entity or Slick's TableQuery row type.
Repository: Abstraction providing CRUD operations for a specific entity via @InjectRepository(). In NestJS, repositories are injectable services wrapping TypeORM's Repository<T>.
Query Builder: Fluent API for constructing SQL queries programmatically: repo.createQueryBuilder('alias'). Outputs parameterized SQL -- never concatenates user input.
Migration: Versioned TypeScript file with up() and down() methods applying or reverting schema changes. Stored in the migrations table for tracking.
Expand-Contract: Multi-phase migration strategy separating additive (expand) from destructive (contract) DDL changes. Guarantees zero downtime by ensuring old and new code versions coexist during the transition.
Eager Loading: Automatically loading related entities on every query via eager: true. Convenient but almost always wasteful -- prefer explicit relations in find().
N+1 Problem: Querying N parent entities and then issuing one additional query per parent to load a relation. Fix by using relations or leftJoinAndSelect.
Active Record vs Data Mapper: TypeORM supports both. Active Record: entity instances have .save(), .remove(). Data Mapper: repositories handle persistence, entities are plain objects. Warmwind uses Data Mapper for testability.
SELECT ... FOR UPDATE SKIP LOCKED: Row-level locking that skips already-locked rows. Used in batch backfills and job queues to allow concurrent workers without contention.
CREATE INDEX CONCURRENTLY: PostgreSQL-specific DDL that builds an index without holding an exclusive lock on the table. Slower build time but does not block writes -- mandatory for production migrations.

TypeORM Patterns¶

1. Domain Entities -- Warmwind's Core Model¶

1.1 ER Diagram¶

1.2 Entity: User¶

1.3 Entity: AgentSession¶

1.4 Entity: AgentAction¶

2. Relations -- What SQL TypeORM Actually Emits¶

2.1 OneToMany / ManyToOne¶

2.2 Nested Relations¶

2.3 Relation Summary¶

3. QueryBuilder -- Complex Queries¶

3.1 Dynamic Filtering with Pagination¶

3.2 Aggregation with Raw SQL Fallback¶

3.3 Subqueries¶

4. Migrations -- Expand-Contract Pattern¶

4.1 The Four-Phase Model¶

4.2 Real Example: Adding model_version to AgentAction¶

4.3 Migration CLI Commands¶

5. Transaction Management¶

5.1 DataSource.transaction (Recommended)¶

5.2 QueryRunner for Fine-Grained Control¶

6. Repository Pattern in NestJS¶

6.1 Custom Repository¶

6.2 Module Registration¶

7. Seven Common Performance Pitfalls¶

Pitfall 1: N+1 Queries¶

Pitfall 2: Loading Entire Relations When You Need a Count¶

Pitfall 3: Missing select -- Fetching All Columns¶

Pitfall 4: synchronize: true in Production¶

Pitfall 5: Implicit Cascade Saves¶

Pitfall 6: Not Using CONCURRENTLY for Index Creation¶

Pitfall 7: getMany() vs getRawMany() for Aggregations¶

8. Drizzle ORM Comparison¶

Glossary¶

4.2 Real Example: Adding `model_version` to AgentAction¶

Pitfall 3: Missing `select` -- Fetching All Columns¶

Pitfall 4: `synchronize: true` in Production¶

Pitfall 6: Not Using `CONCURRENTLY` for Index Creation¶

Pitfall 7: `getMany()` vs `getRawMany()` for Aggregations¶