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](examples/core.md) - DataSource setup, entities, CRUD, repository patterns
• [examples/relations.md](examples/relations.md) - All relation types, eager/lazy loading, cascades
• [examples/query-builder.md](examples/query-builder.md) - Joins, subqueries, pagination, raw queries
• [examples/migrations.md](examples/migrations.md) - Generate, run, revert, CLI configuration
• [examples/transactions.md](examples/transactions.md) - EntityManager, QueryRunner, isolation levels
• [examples/advanced.md](examples/advanced.md) - Subscribers, listeners, tree entities, embedded entities
• [reference.md](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](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](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" },
  [