SKILL.md

TypeORM Development Guidelines

You are an expert in TypeORM, TypeScript, and database design with a focus on the Data Mapper pattern and enterprise application architecture.

Core Principles

TypeORM supports both Active Record and Data Mapper patterns

Uses TypeScript decorators for entity and column definitions

Supports MySQL, PostgreSQL, MariaDB, SQLite, MS SQL Server, Oracle, and more

Works in Node.js, Browser, Ionic, Cordova, React Native, NativeScript, Expo, and Electron

First-class support for database migrations

TypeScript Configuration

Required settings in tsconfig.json:

{

  "compilerOptions": {

    "experimentalDecorators": true,

    "emitDecoratorMetadata": true,

    "strict": true,

    "target": "ES2020",

    "module": "commonjs",

    "moduleResolution": "node"

  }

}

Entity Definition

Basic Entity

import {

  Entity,

  PrimaryGeneratedColumn,

  Column,

  CreateDateColumn,

  UpdateDateColumn,

} from "typeorm";

@Entity("users")

export class User {

  @PrimaryGeneratedColumn()

  id: number;

  @Column({ type: "varchar", length: 255, unique: true })

  email: string;

  @Column({ type: "varchar", length: 255, nullable: true })

  name: string | null;

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

  isActive: boolean;

  @CreateDateColumn()

  createdAt: Date;

  @UpdateDateColumn()

  updatedAt: Date;

}

Primary Key Options

// Auto-increment

@PrimaryGeneratedColumn()

id: number;

// UUID

@PrimaryGeneratedColumn("uuid")

id: string;

// Custom primary key

@PrimaryColumn()

id: string;

// Composite primary key

@Entity()

export class OrderItem {

  @PrimaryColumn()

  orderId: number;

  @PrimaryColumn()

  productId: number;

}

Column Decorators

@Entity()

export class Product {

  @PrimaryGeneratedColumn()

  id: number;

  // String columns

  @Column({ type: "varchar", length: 255 })

  name: string;

  @Column({ type: "text", nullable: true })

  description: string | null;

  // Numeric columns

  @Column({ type: "decimal", precision: 10, scale: 2 })

  price: number;

  @Column({ type: "int", default: 0 })

  stock: number;

  // Boolean

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

  isAvailable: boolean;

  // JSON

  @Column({ type: "jsonb", nullable: true })

  metadata: Record<string, any> | null;

  // Enum

  @Column({

    type: "enum",

    enum: ["active", "inactive", "pending"],

    default: "pending",

  })

  status: "active" | "inactive" | "pending";

  // Timestamps

  @CreateDateColumn()

  createdAt: Date;

  @UpdateDateColumn()

  updatedAt: Date;

  @DeleteDateColumn()

  deletedAt: Date | null; // For soft deletes

  // Version column for optimistic locking

  @VersionColumn()

  version: number;

}

Relationships

One-to-One

@Entity()

export class User {

  @PrimaryGeneratedColumn()

  id: number;

  @OneToOne(() => Profile, (profile) => profile.user, { cascade: true })

  @JoinColumn()

  profile: Profile;

}

@Entity()

export class Profile {

  @PrimaryGeneratedColumn()

  id: number;

  @Column()

  bio: string;

  @OneToOne(() => User, (user) => user.profile)

  user: User;

}

One-to-Many / Many-to-One

@Entity()

export class User {

  @PrimaryGeneratedColumn()

  id: number;

  @Column()

  name: string;

  @OneToMany(() => Post, (post) => post.author)

  posts: Post[];

}

@Entity()

export class Post {

  @PrimaryGeneratedColumn()

  id: number;

  @Column()

  title: string;

  @ManyToOne(() => User, (user) => user.posts, { onDelete: "CASCADE" })

  @JoinColumn({ name: "author_id" })

  author: User;

  @Column()

  authorId: number; // Explicit foreign key column

}

Many-to-Many

@Entity()

export class Post {

  @PrimaryGeneratedColumn()

  id: number;

  @Column()

  title: string;

  @ManyToMany(() => Tag, (tag) => tag.posts)

  @JoinTable({

    name: "post_tags",

    joinColumn: { name: "post_id" },

    inverseJoinColumn: { name: "tag_id" },

  })

  tags: Tag[];

}

@Entity()

export class Tag {

  @PrimaryGeneratedColumn()

  id: number;

  @Column({ unique: true })

  name: string;

  @ManyToMany(() => Post, (post) => post.tags)

  posts: Post[];

}

Repository Pattern

Basic Repository Usage

import { AppDataSource } from "./data-source";

import { User } from "./entities/User";

const userRepository = AppDataSource.getRepository(User);

// Find all

const users = await userRepository.find();

// Find with conditions

const activeUsers = await userRepository.find({

  where: { isActive: true },

});

// Find one

const user = await userRepository.findOne({

  where: { id: 1 },

});

// Find or fail

const user = await userRepository.findOneOrFail({

  where: { id: 1 },

});

// Save

const newUser = userRepository.create({

  email: "user@example.com",

  name: "John Doe",

});

await userRepository.save(newUser);

// Update

await userRepository.update({ id: 1 }, { name: "Jane Doe" });

// Delete

await userRepository.delete({ id: 1 });

// Soft delete (requires @DeleteDateColumn)

await userRepository.softDelete({ id: 1 });

Custom Repository

import { Repository, DataSource } from "typeorm";

import { User } from "./entities/User";

export class UserRepository extends Repository<User> {

  constructor(private dataSource: DataSource) {

    super(User, dataSource.createEntityManager());

  }

  async findByEmail(email: string): Promise<User | null> {

    return this.findOne({ where: { email } });

  }

  async findActiveUsers(): Promise<User[]> {

    return this.find({

      where: { isActive: true },

      order: { createdAt: "DESC" },

    });

  }

  async findWithPosts(userId: number): Promise<User | null> {

    return this.findOne({

      where: { id: userId },

      relations: ["posts"],

    });

  }

}

Query Builder

const users = await userRepository

  .createQueryBuilder("user")

  .leftJoinAndSelect("user.posts", "post")

  .where("user.isActive = :isActive", { isActive: true })

  .andWhere("post.publishedAt IS NOT NULL")

  .orderBy("user.createdAt", "DESC")

  .skip(0)

  .take(10)

  .getMany();

// With raw results

const result = await userRepository

  .createQueryBuilder("user")

  .select("COUNT(*)", "count")

  .where("user.isActive = :isActive", { isActive: true })

  .getRawOne();

// Insert with query builder

await userRepository

  .createQueryBuilder()

  .insert()

  .into(User)

  .values([

    { email: "user1@example.com", name: "User 1" },

    { email: "user2@example.com", name: "User 2" },

  ])

  .execute();

Data Source Configuration

// data-source.ts

import { DataSource } from "typeorm";

import { User } from "./entities/User";

import { Post } from "./entities/Post";

export const AppDataSource = new DataSource({

  type: "postgres",

  host: process.env.DB_HOST || "localhost",

  port: parseInt(process.env.DB_PORT || "5432"),

  username: process.env.DB_USERNAME,

  password: process.env.DB_PASSWORD,

  database: process.env.DB_NAME,

  // Entity configuration

  entities: [User, Post],

  // Or use glob pattern: entities: ["src/entities/**/*.ts"]

  // Migrations

  migrations: ["src/migrations/**/*.ts"],

  // Synchronize - NEVER use in production

  synchronize: false,

  // Logging

  logging: process.env.NODE_ENV === "development",

  // Connection pool

  poolSize: 10,

  // SSL (for production)

  ssl: process.env.NODE_ENV === "production" ? { rejectUnauthorized: false } : false,

});

// Initialize connection

AppDataSource.initialize()

  .then(() => console.log("Data Source initialized"))

  .catch((error) => console.error("Error initializing Data Source:", error));

Migrations

Creating Migrations

# Generate migration from entity changes

npx typeorm migration:generate src/migrations/CreateUsers -d src/data-source.ts

# Create empty migration

npx typeorm migration:create src/migrations/SeedUsers

# Run migrations

npx typeorm migration:run -d src/data-source.ts

# Revert last migration

npx typeorm migration:revert -d src/data-source.ts

Migration File Structure

import { MigrationInterface, QueryRunner, Table, TableIndex } from "typeorm";

export class CreateUsers1234567890 implements MigrationInterface {

  public async up(queryRunner: QueryRunner): Promise<void> {

    await queryRunner.createTable(

      new Table({

        name: "users",

        columns: [

          {

            name: "id",

            type: "int",

            isPrimary: true,

            isGenerated: true,

            generationStrategy: "increment",

          },

          {

            name: "email",

            type: "varchar",

            length: "255",

            isUnique: true,

          },

          {

            name: "name",

            type: "varchar",

            length: "255",

            isNullable: true,

          },

          {

            name: "is_active",

            type: "boolean",

            default: true,

          },

          {

            name: "created_at",

            type: "timestamp",

            default: "CURRENT_TIMESTAMP",

          },

          {

            name: "updated_at",

            type: "timestamp",

            default: "CURRENT_TIMESTAMP",

            onUpdate: "CURRENT_TIMESTAMP",

          },

        ],

      }),

      true

    );

    await queryRunner.createIndex(

      "users",

      new TableIndex({

        name: "IDX_USERS_EMAIL",

        columnNames: ["email"],

      })

    );

  }

  public async down(queryRunner: QueryRunner): Promise<void> {

    await queryRunner.dropIndex("users", "IDX_USERS_EMAIL");

    await queryRunner.dropTable("users");

  }

}

Transactions

// Using QueryRunner

const queryRunner = AppDataSource.createQueryRunner();

await queryRunner.connect();

await queryRunner.startTransaction();

try {

  const user = queryRunner.manager.create(User, {

    email: "user@example.com",

    name: "User",

  });

  await queryRunner.manager.save(user);

  const post = queryRunner.manager.create(Post, {

    title: "First Post",

    author: user,

  });

  await queryRunner.manager.save(post);

  await queryRunner.commitTransaction();

} catch (error) {

  await queryRunner.rollbackTransaction();

  throw error;

} finally {

  await queryRunner.release();

}

// Using transaction method

await AppDataSource.transaction(async (manager) => {

  const user = manager.create(User, {

    email: "user@example.com",

    name: "User",

  });

  await manager.save(user);

  const post = manager.create(Post, {

    title: "First Post",

    author: user,

  });

  await manager.save(post);

});

NestJS Integration

// app.module.ts

import { Module } from "@nestjs/common";

import { TypeOrmModule } from "@nestjs/typeorm";

import { User } from "./entities/user.entity";

import { UsersModule } from "./users/users.module";

@Module({

  imports: [

    TypeOrmModule.forRoot({

      type: "postgres",

      host: "localhost",

      port: 5432,

      username: "user",

      password: "password",

      database: "db",

      entities: [User],

      synchronize: false,

    }),

    UsersModule,

  ],

})

export class AppModule {}

// users/users.module.ts

@Module({

  imports: [TypeOrmModule.forFeature([User])],

  providers: [UsersService],

  controllers: [UsersController],

})

export class UsersModule {}

// users/users.service.ts

@Injectable()

export class UsersService {

  constructor(

    @InjectRepository(User)

    private usersRepository: Repository<User>,

  ) {}

  findAll(): Promise<User[]> {

    return this.usersRepository.find();

  }

  findOne(id: number): Promise<User | null> {

    return this.usersRepository.findOneBy({ id });

  }

}

Best Practices

Use Migrations in Production

Never use synchronize: true in production. Always use migrations:

// Development: Use migrations, not sync

synchronize: false,

Eager vs Lazy Loading

// Eager loading - loads relations automatically

@OneToMany(() => Post, (post) => post.author, { eager: true })

posts: Post[];

// Lazy loading - loads relations on access

@OneToMany(() => Post, (post) => post.author)

posts: Promise<Post[]>;

// Explicit loading (recommended)

const user = await userRepository.findOne({

  where: { id: 1 },

  relations: ["posts"],

});

Avoid N+1 Queries

// Bad: N+1 queries

const users = await userRepository.find();

for (const user of users) {

  console.log(user.posts); // Separate query for each user

}

// Good: Eager load relations

const users = await userRepository.find({

  relations: ["posts"],

});

Use Indexes

@Entity()

@Index(["email"])

@Index(["firstName", "lastName"])

export class User {

  @Column()

  @Index()

  email: string;

  @Column()

  firstName: string;

  @Column()

  lastName: string;

}

Cascade Operations

@OneToMany(() => Post, (post) => post.author, {

  cascade: true, // Saves/removes related posts

  onDelete: "CASCADE", // Database-level cascade

})

posts: Post[];

Naming Strategies

For consistent naming between TypeScript and database:

import { DefaultNamingStrategy, NamingStrategyInterface } from "typeorm";

import { snakeCase } from "typeorm/util/StringUtils";

export class SnakeNamingStrategy extends DefaultNamingStrategy implements NamingStrategyInterface {

  tableName(targetName: string, userSpecifiedName: string | undefined): string {

    return userSpecifiedName ? userSpecifiedName : snakeCase(targetName);

  }

  columnName(propertyName: string, customName: string, embeddedPrefixes: string[]): string {

    return snakeCase(embeddedPrefixes.join("_")) + (customName ? customName : snakeCase(propertyName));

  }

}

// Use in data source config

namingStrategy: new SnakeNamingStrategy(),

typeorm