TypeORM 如何支持多种数据库?包括 MySQL、PostgreSQL、MongoDB 等的配置和使用 - 面试题

TypeORM 支持多种数据库类型,包括 MySQL、PostgreSQL、SQLite、SQL Server、Oracle 和 MongoDB。本文将详细介绍 TypeORM 如何配置和使用多个数据库。

支持的数据库类型

关系型数据库

MySQL / MariaDB

typescript
import { DataSource } from 'typeorm';

const mysqlDataSource = new DataSource({
  type: 'mysql',
  host: 'localhost',
  port: 3306,
  username: 'root',
  password: 'password',
  database: 'myapp',
  entities: [User, Post],
  synchronize: false,
  logging: true,
});

PostgreSQL

typescript
const postgresDataSource = new DataSource({
  type: 'postgres',
  host: 'localhost',
  port: 5432,
  username: 'postgres',
  password: 'password',
  database: 'myapp',
  entities: [User, Post],
  synchronize: false,
  logging: true,
});

SQLite

typescript
const sqliteDataSource = new DataSource({
  type: 'sqlite',
  database: './myapp.db',
  entities: [User, Post],
  synchronize: false,
  logging: true,
});

SQL Server

typescript
const mssqlDataSource = new DataSource({
  type: 'mssql',
  host: 'localhost',
  port: 1433,
  username: 'sa',
  password: 'password',
  database: 'myapp',
  entities: [User, Post],
  synchronize: false,
  logging: true,
});

Oracle

typescript
const oracleDataSource = new DataSource({
  type: 'oracle',
  host: 'localhost',
  port: 1521,
  username: 'system',
  password: 'password',
  serviceName: 'ORCL',
  entities: [User, Post],
  synchronize: false,
  logging: true,
});

NoSQL 数据库

MongoDB

typescript
const mongoDataSource = new DataSource({
  type: 'mongodb',
  host: 'localhost',
  port: 27017,
  database: 'myapp',
  entities: [User, Post],
  synchronize: false,
  logging: true,
});

多数据库配置

配置多个 DataSource

typescript
import { DataSource } from 'typeorm';

// 主数据库 (MySQL)
const primaryDataSource = new DataSource({
  type: 'mysql',
  name: 'primary', // 数据源名称
  host: 'localhost',
  port: 3306,
  username: 'root',
  password: 'password',
  database: 'primary_db',
  entities: [User, Post],
  synchronize: false,
  logging: true,
});

// 从数据库 (PostgreSQL)
const secondaryDataSource = new DataSource({
  type: 'postgres',
  name: 'secondary', // 数据源名称
  host: 'localhost',
  port: 5432,
  username: 'postgres',
  password: 'password',
  database: 'secondary_db',
  entities: [Comment, Like],
  synchronize: false,
  logging: true,
});

// 缓存数据库 (Redis)
const cacheDataSource = new DataSource({
  type: 'mongodb',
  name: 'cache',
  host: 'localhost',
  port: 27017,
  database: 'cache_db',
  entities: [Cache],
  synchronize: false,
  logging: true,
});

初始化多个数据源

typescript
async function initializeDataSources() {
  try {
    // 初始化所有数据源
    await Promise.all([
      primaryDataSource.initialize(),
      secondaryDataSource.initialize(),
      cacheDataSource.initialize(),
    ]);

    console.log('All data sources initialized successfully');
  } catch (error) {
    console.error('Error initializing data sources:', error);
    throw error;
  }
}

// 使用示例
initializeDataSources().then(() => {
  // 应用程序逻辑
});

跨数据库操作

在不同数据库间操作

typescript
// 从 MySQL 读取用户
async function getUserFromPrimary(userId: number) {
  const userRepository = primaryDataSource.getRepository(User);
  return await userRepository.findOne({ where: { id: userId } });
}

// 从 PostgreSQL 读取评论
async function getCommentsFromSecondary(postId: number) {
  const commentRepository = secondaryDataSource.getRepository(Comment);
  return await commentRepository.find({ where: { postId } });
}

// 写入 MongoDB 缓存
async function cacheUserData(userId: number, data: any) {
  const cacheRepository = cacheDataSource.getRepository(Cache);
  const cache = cacheRepository.create({
    key: `user:${userId}`,
    value: JSON.stringify(data),
    expiresAt: new Date(Date.now() + 3600000), // 1小时后过期
  });
  return await cacheRepository.save(cache);
}

// 组合使用
async function getUserWithCache(userId: number) {
  // 先尝试从缓存读取
  const cacheRepository = cacheDataSource.getRepository(Cache);
  const cached = await cacheRepository.findOne({ 
    where: { key: `user:${userId}` } 
  });

  if (cached && cached.expiresAt > new Date()) {
    return JSON.parse(cached.value);
  }

  // 从主数据库读取
  const user = await getUserFromPrimary(userId);
  
  // 写入缓存
  await cacheUserData(userId, user);
  
  return user;
}

跨数据库事务

typescript
// 使用分布式事务模式
async function transferDataBetweenDatabases(
  userId: number,
  postId: number
) {
  const primaryQueryRunner = primaryDataSource.createQueryRunner();
  const secondaryQueryRunner = secondaryDataSource.createQueryRunner();

  try {
    // 开始事务
    await primaryQueryRunner.connect();
    await primaryQueryRunner.startTransaction();
    
    await secondaryQueryRunner.connect();
    await secondaryQueryRunner.startTransaction();

    // 在主数据库操作
    const userRepository = primaryQueryRunner.manager.getRepository(User);
    const user = await userRepository.findOne({ 
      where: { id: userId } 
    });
    
    // 在从数据库操作
    const commentRepository = secondaryQueryRunner.manager.getRepository(Comment);
    const comment = commentRepository.create({
      userId,
      postId,
      content: 'Comment from user',
    });
    await commentRepository.save(comment);

    // 提交事务
    await primaryQueryRunner.commitTransaction();
    await secondaryQueryRunner.commitTransaction();
  } catch (error) {
    // 回滚事务
    await primaryQueryRunner.rollbackTransaction();
    await secondaryQueryRunner.rollbackTransaction();
    throw error;
  } finally {
    // 释放资源
    await primaryQueryRunner.release();
    await secondaryQueryRunner.release();
  }
}

数据库特定配置

MySQL 特定配置

typescript
const mysqlDataSource = new DataSource({
  type: 'mysql',
  host: 'localhost',
  port: 3306,
  username: 'root',
  password: 'password',
  database: 'myapp',
  entities: [User],
  synchronize: false,
  logging: true,
  
  // MySQL 特定配置
  extra: {
    connectionLimit: 10, // 连接池大小
    acquireTimeout: 60000, // 获取连接超时时间
    timeout: 60000, // 查询超时时间
    waitForConnections: true, // 等待可用连接
    queueLimit: 0, // 队列限制
    charset: 'utf8mb4', // 字符集
    timezone: '+00:00', // 时区
    ssl: {
      rejectUnauthorized: false
    }
  },
  
  // MySQL 驱动选项
  driverOptions: {
    flags: ['+FOUND_ROWS'],
    namedPlaceholders: true,
  },
});

PostgreSQL 特定配置

typescript
const postgresDataSource = new DataSource({
  type: 'postgres',
  host: 'localhost',
  port: 5432,
  username: 'postgres',
  password: 'password',
  database: 'myapp',
  entities: [User],
  synchronize: false,
  logging: true,
  
  // PostgreSQL 特定配置
  extra: {
    max: 10, // 最大连接数
    idleTimeoutMillis: 30000, // 空闲连接超时
    connectionTimeoutMillis: 2000, // 连接超时
  },
  
  // PostgreSQL 驱动选项
  driverOptions: {
    application_name: 'MyApp',
    statement_timeout: 10000,
    query_timeout: 10000,
  },
});

MongoDB 特定配置

typescript
const mongoDataSource = new DataSource({
  type: 'mongodb',
  host: 'localhost',
  port: 27017,
  database: 'myapp',
  entities: [User],
  synchronize: false,
  logging: true,
  
  // MongoDB 特定配置
  url: 'mongodb://localhost:27017/myapp',
  useNewUrlParser: true,
  useUnifiedTopology: true,
  
  // MongoDB 驱动选项
  driverOptions: {
    maxPoolSize: 10,
    minPoolSize: 2,
    maxIdleTimeMS: 60000,
    serverSelectionTimeoutMS: 5000,
    socketTimeoutMS: 45000,
  },
});

数据库迁移管理

多数据库迁移

typescript
// 主数据库迁移
export class CreateUserTable1234567890123 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.createTable(
      new Table({
        name: 'user',
        columns: [
          {
            name: 'id',
            type: 'int',
            isPrimary: true,
            isGenerated: true,
            generationStrategy: 'increment',
          },
          {
            name: 'name',
            type: 'varchar',
          },
        ],
      }),
      true
    );
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.dropTable('user');
  }
}

// 从数据库迁移
export class CreateCommentTable1234567890124 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.createTable(
      new Table({
        name: 'comment',
        columns: [
          {
            name: 'id',
            type: 'int',
            isPrimary: true,
            isGenerated: true,
            generationStrategy: 'increment',
          },
          {
            name: 'content',
            type: 'text',
          },
        ],
      }),
      true
    );
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.dropTable('comment');
  }
}

运行特定数据库的迁移

typescript
// 运行主数据库迁移
async function runPrimaryMigrations() {
  await primaryDataSource.runMigrations();
}

// 运行从数据库迁移
async function runSecondaryMigrations() {
  await secondaryDataSource.runMigrations();
}

// 运行所有数据库迁移
async function runAllMigrations() {
  await Promise.all([
    primaryDataSource.runMigrations(),
    secondaryDataSource.runMigrations(),
    cacheDataSource.runMigrations(),
  ]);
}

数据库切换策略

环境变量配置

typescript
// config/database.ts
import { DataSource } from 'typeorm';

export function createDataSource(): DataSource {
  const dbType = process.env.DB_TYPE || 'mysql';
  
  const commonConfig = {
    entities: [User, Post],
    synchronize: false,
    logging: true,
  };

  switch (dbType) {
    case 'mysql':
      return new DataSource({
        type: 'mysql',
        host: process.env.MYSQL_HOST || 'localhost',
        port: parseInt(process.env.MYSQL_PORT || '3306'),
        username: process.env.MYSQL_USER || 'root',
        password: process.env.MYSQL_PASSWORD || 'password',
        database: process.env.MYSQL_DATABASE || 'myapp',
        ...commonConfig,
      });
    
    case 'postgres':
      return new DataSource({
        type: 'postgres',
        host: process.env.POSTGRES_HOST || 'localhost',
        port: parseInt(process.env.POSTGRES_PORT || '5432'),
        username: process.env.POSTGRES_USER || 'postgres',
        password: process.env.POSTGRES_PASSWORD || 'password',
        database: process.env.POSTGRES_DATABASE || 'myapp',
        ...commonConfig,
      });
    
    case 'sqlite':
      return new DataSource({
        type: 'sqlite',
        database: process.env.SQLITE_DATABASE || './myapp.db',
        ...commonConfig,
      });
    
    default:
      throw new Error(`Unsupported database type: ${dbType}`);
  }
}

// 使用
const dataSource = createDataSource();
await dataSource.initialize();

读写分离配置

typescript
// 主数据库(写操作)
const writeDataSource = new DataSource({
  type: 'mysql',
  name: 'write',
  host: 'write-db.example.com',
  port: 3306,
  username: 'root',
  password: 'password',
  database: 'myapp',
  entities: [User, Post],
  synchronize: false,
  logging: true,
});

// 从数据库(读操作)
const readDataSource = new DataSource({
  type: 'mysql',
  name: 'read',
  host: 'read-db.example.com',
  port: 3306,
  username: 'root',
  password: 'password',
  database: 'myapp',
  entities: [User, Post],
  synchronize: false,
  logging: true,
});

// 使用读写分离
class UserRepository {
  async findById(id: number) {
    // 从从数据库读取
    const repository = readDataSource.getRepository(User);
    return await repository.findOne({ where: { id } });
  }

  async save(user: User) {
    // 写入主数据库
    const repository = writeDataSource.getRepository(User);
    return await repository.save(user);
  }

  async findAll() {
    // 从从数据库读取
    const repository = readDataSource.getRepository(User);
    return await repository.find();
  }
}

性能优化

连接池配置

typescript
// MySQL 连接池优化
const mysqlDataSource = new DataSource({
  type: 'mysql',
  host: 'localhost',
  port: 3306,
  username: 'root',
  password: 'password',
  database: 'myapp',
  entities: [User],
  synchronize: false,
  logging: true,
  
  extra: {
    connectionLimit: 20, // 根据应用负载调整
    acquireTimeout: 60000,
    timeout: 60000,
    waitForConnections: true,
    queueLimit: 100,
  },
});

// PostgreSQL 连接池优化
const postgresDataSource = new DataSource({
  type: 'postgres',
  host: 'localhost',
  port: 5432,
  username: 'postgres',
  password: 'password',
  database: 'myapp',
  entities: [User],
  synchronize: false,
  logging: true,
  
  extra: {
    max: 20, // 最大连接数
    min: 2, // 最小连接数
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 2000,
  },
});

查询优化

typescript
// 使用合适的数据库类型
async function getBestDatabaseForOperation(operation: string) {
  switch (operation) {
    case 'read-heavy':
      // 使用 PostgreSQL 适合读密集型操作
      return postgresDataSource;
    
    case 'write-heavy':
      // 使用 MySQL 适合写密集型操作
      return mysqlDataSource;
    
    case 'cache':
      // 使用 MongoDB 适合缓存
      return mongoDataSource;
    
    case 'analytics':
      // 使用 PostgreSQL 的窗口函数适合分析
      return postgresDataSource;
    
    default:
      return mysqlDataSource;
  }
}

// 使用示例
const dataSource = await getBestDatabaseForOperation('read-heavy');
const repository = dataSource.getRepository(User);
const users = await repository.find();

TypeORM 的多数据库支持提供了强大的灵活性,让开发者可以根据不同的需求选择最合适的数据库类型,并在同一个应用中同时使用多个数据库。