typeorm（typeorm 数据库）

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

Quick Troubleshooting

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

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