TypeORM Core Knowledge

Deep Knowledge: Use mcp__documentation__fetch_docs with technology: typeorm for comprehensive documentation.

When NOT to Use This Skill

• Prisma Projects: Use prisma skill for Prisma-based applications

• Drizzle Projects: Use drizzle skill for Drizzle ORM

• Python Applications: Use sqlalchemy skill for Python ORMs

• Raw SQL Operations: Use database-query MCP server for direct SQL

• NoSQL Databases: Use mongodb skill for MongoDB (TypeORM MongoDB support is limited)

• Database Design: Consult sql-expert or architect-expert for schema architecture

• Migration Strategy: Engage devops-expert for production deployment planning

Entity Definition

import { Entity, PrimaryGeneratedColumn, Column, CreateDateColumn, ManyToOne, OneToMany } from 'typeorm';

@Entity() export class User { @PrimaryGeneratedColumn() id: number;

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

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

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

@CreateDateColumn() createdAt: Date;

@OneToMany(() => Post, post => post.author) posts: Post[]; }

@Entity() export class Post { @PrimaryGeneratedColumn() id: number;

@Column() title: string;

@Column({ type: 'text', nullable: true }) content: string;

@ManyToOne(() => User, user => user.posts) author: User; }

Repository Operations

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

const userRepository = AppDataSource.getRepository(User);

// Create const user = userRepository.create({ name: 'John', email: 'john@example.com' }); await userRepository.save(user);

// Read const users = await userRepository.find(); const user = await userRepository.findOneBy({ id: 1 }); const userWithPosts = await userRepository.findOne({ where: { id: 1 }, relations: { posts: true }, });

// Update await userRepository.update(1, { name: 'Jane' });

// Delete await userRepository.delete(1);

Query Builder

const users = await userRepository .createQueryBuilder('user') .leftJoinAndSelect('user.posts', 'post') .where('user.isActive = :active', { active: true }) .andWhere('post.published = :published', { published: true }) .orderBy('user.createdAt', 'DESC') .take(10) .getMany();

Data Source Config

import { DataSource } from 'typeorm';

export const AppDataSource = new DataSource({ type: 'postgres', host: 'localhost', port: 5432, username: 'user', password: 'password', database: 'mydb', entities: [User, Post], synchronize: false, migrations: ['src/migrations/*.ts'], });

Anti-Patterns

Anti-Pattern Why It's Bad Better Approach

synchronize: true in production Can drop tables, loses data Always use migrations in production

Using find() without relations N+1 query problem Use relations option or QueryBuilder with joins

Not using transactions for multi-step ops Data inconsistency risk Wrap related operations in transaction()

Hardcoded credentials in DataSource Security vulnerability Use environment variables

No connection pool configuration Connection exhaustion Set extra.max and pool timeouts

Using @Column() without type for large text May truncate data Specify type: 'text' for long content

Lazy loading relations everywhere Performance issues, N+1 queries Use eager loading strategically

Not handling unique constraint errors Poor error messages to users Catch and handle constraint violations

Manual SQL without parameters SQL injection risk Use QueryBuilder with parameters

No indexes on frequently queried columns Slow queries Add @Index() decorators

Quick Troubleshooting

Issue Likely Cause Solution

"Cannot find name 'AppDataSource'" DataSource not initialized Call AppDataSource.initialize() at startup

"relation does not exist" Migration not run Execute pending migrations

"column does not exist" Entity/DB out of sync Generate and run new migration

Type errors on entities Decorator metadata issue Enable emitDecoratorMetadata and experimentalDecorators in tsconfig

"Repository not found" Entity not registered Add entity to DataSource entities array

Slow queries Missing indexes, no joins Add indexes, use leftJoinAndSelect

Connection pool exhausted Too many concurrent queries Increase extra.max pool size

"Cannot query across many-to-many" Missing join table Add explicit join table or use QueryBuilder

Migration generation creates no file No entity changes detected Manually create migration if needed

"ECONNREFUSED" Database not running Start database, verify connection details

Production Readiness

Data Source Configuration

// data-source.ts import { DataSource } from 'typeorm';

export const AppDataSource = new DataSource({ type: 'postgres', url: process.env.DATABASE_URL, // SECURITY: Use proper CA certificate in production instead of disabling verification // ssl: { rejectUnauthorized: false } is INSECURE - vulnerable to MITM attacks ssl: process.env.NODE_ENV === 'production' ? { ca: process.env.DB_CA_CERT } : false,

// Connection pool extra: { max: 20, idleTimeoutMillis: 30000, connectionTimeoutMillis: 10000, },

entities: ['dist/entities//*.js'], migrations: ['dist/migrations//.js'], subscribers: ['dist/subscribers/**/.js'],

// Never use in production synchronize: false,

// Logging logging: process.env.NODE_ENV === 'development' ? ['query', 'error'] : ['error'], logger: 'advanced-console',

// Cache cache: { type: 'ioredis', options: { host: process.env.REDIS_HOST, port: parseInt(process.env.REDIS_PORT || '6379'), }, duration: 30000, // 30 seconds }, });

// Initialize AppDataSource.initialize() .then(() => console.log('Database connected')) .catch((err) => console.error('Database connection error:', err));

Transaction Management

import { EntityManager } from 'typeorm';

async function transferFunds( manager: EntityManager, fromId: number, toId: number, amount: number ) { return await manager.transaction(async (transactionalManager) => { const from = await transactionalManager .createQueryBuilder(Account, 'account') .setLock('pessimistic_write') .where('account.id = :id', { id: fromId }) .getOne();

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

await transactionalManager
  .createQueryBuilder()
  .update(Account)
  .set({ balance: () => `balance - ${amount}` })
  .where('id = :id', { id: fromId })
  .execute();

await transactionalManager
  .createQueryBuilder()
  .update(Account)
  .set({ balance: () => `balance + ${amount}` })
  .where('id = :id', { id: toId })
  .execute();

}); }

Query Optimization

// Pagination async function getUsers(page: number, limit: number) { const [users, total] = await userRepository.findAndCount({ skip: (page - 1) * limit, take: limit, order: { createdAt: 'DESC' }, });

return { data: users, meta: { total, page, lastPage: Math.ceil(total / limit), }, }; }

// Select specific columns const users = await userRepository .createQueryBuilder('user') .select(['user.id', 'user.name', 'user.email']) .where('user.isActive = :active', { active: true }) .getMany();

// Batch operations await userRepository .createQueryBuilder() .insert() .into(User) .values(usersToCreate) .orIgnore() // Skip duplicates .execute();

Soft Deletes

@Entity() @DeleteDateColumn() export class User { @PrimaryGeneratedColumn() id: number;

@Column() name: string;

@DeleteDateColumn() deletedAt?: Date; }

// Soft delete await userRepository.softDelete(1);

// Include soft deleted const allUsers = await userRepository .createQueryBuilder('user') .withDeleted() .getMany();

// Restore await userRepository.restore(1);

Migration Best Practices

// migrations/1234567890-CreateUsersTable.ts import { MigrationInterface, QueryRunner } from 'typeorm';

export class CreateUsersTable1234567890 implements MigrationInterface { public async up(queryRunner: QueryRunner): Promise<void> { await queryRunner.query( CREATE TABLE "users" ( "id" SERIAL PRIMARY KEY, "email" VARCHAR(255) UNIQUE NOT NULL, "name" VARCHAR(100) NOT NULL, "created_at" TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ); await queryRunner.query( CREATE INDEX "idx_users_email" ON "users" ("email") ); }

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

// CLI commands // npm run typeorm migration:generate -- -n CreateUsersTable // npm run typeorm migration:run // npm run typeorm migration:revert

Testing

// test-utils.ts import { DataSource } from 'typeorm';

export const TestDataSource = new DataSource({ type: 'postgres', url: process.env.TEST_DATABASE_URL, entities: ['src/entities/**/*.ts'], synchronize: true, dropSchema: true, });

// tests/user.test.ts describe('UserRepository', () => { beforeAll(async () => { await TestDataSource.initialize(); });

afterAll(async () => { await TestDataSource.destroy(); });

beforeEach(async () => { await TestDataSource.synchronize(true); });

it('should create user', async () => { const repo = TestDataSource.getRepository(User); const user = await repo.save({ name: 'Test', email: 'test@example.com' }); expect(user.id).toBeDefined(); }); });

Monitoring Metrics

Metric Target

Query time (p99) < 100ms

Connection pool usage < 80%

Slow query count 0

Migration success 100%

Checklist

• Connection pooling configured

• SSL in production

• synchronize: false in production

• Query caching with Redis

• Transaction management

• Soft deletes where appropriate

• Pagination for list queries

• Migration versioning

• Logging only errors in production

• Test database isolation

Reference Documentation

• Relations

• Migrations