How does TypeORM support multiple databases? Including configuration and usage of MySQL, PostgreSQL, MongoDB, etc.

TypeORM supports multiple database types, including MySQL, PostgreSQL, SQLite, SQL Server, Oracle, and MongoDB. This article details how to configure and use multiple databases in TypeORM.

Supported Database Types

Relational Databases

1. 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,
});

1. 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,
});

1. SQLite

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

1. 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,
});

1. 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 Databases

1. MongoDB

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

Multi-Database Configuration

Configuring Multiple DataSources

typescript
import { DataSource } from 'typeorm';

// Primary database (MySQL)
const primaryDataSource = new DataSource({
  type: 'mysql',
  name: 'primary', // Data source name
  host: 'localhost',
  port: 3306,
  username: 'root',
  password: 'password',
  database: 'primary_db',
  entities: [User, Post],
  synchronize: false,
  logging: true,
});

// Secondary database (PostgreSQL)
const secondaryDataSource = new DataSource({
  type: 'postgres',
  name: 'secondary', // Data source name
  host: 'localhost',
  port: 5432,
  username: 'postgres',
  password: 'password',
  database: 'secondary_db',
  entities: [Comment, Like],
  synchronize: false,
  logging: true,
});

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

Initializing Multiple DataSources

typescript
async function initializeDataSources() {
  try {
    // Initialize all data sources
    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;
  }
}

// Usage example
initializeDataSources().then(() => {
  // Application logic
});

Cross-Database Operations

Operating Across Different Databases

typescript
// Read user from MySQL
async function getUserFromPrimary(userId: number) {
  const userRepository = primaryDataSource.getRepository(User);
  return await userRepository.findOne({ where: { id: userId } });
}

// Read comments from PostgreSQL
async function getCommentsFromSecondary(postId: number) {
  const commentRepository = secondaryDataSource.getRepository(Comment);
  return await commentRepository.find({ where: { postId } });
}

// Write to MongoDB cache
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), // Expire in 1 hour
  });
  return await cacheRepository.save(cache);
}

// Combined usage
async function getUserWithCache(userId: number) {
  // Try to read from cache first
  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);
  }

  // Read from primary database
  const user = await getUserFromPrimary(userId);
  
  // Write to cache
  await cacheUserData(userId, user);
  
  return user;
}

Cross-Database Transactions

typescript
// Use distributed transaction pattern
async function transferDataBetweenDatabases(
  userId: number,
  postId: number
) {
  const primaryQueryRunner = primaryDataSource.createQueryRunner();
  const secondaryQueryRunner = secondaryDataSource.createQueryRunner();

  try {
    // Start transactions
    await primaryQueryRunner.connect();
    await primaryQueryRunner.startTransaction();
    
    await secondaryQueryRunner.connect();
    await secondaryQueryRunner.startTransaction();

    // Operations in primary database
    const userRepository = primaryQueryRunner.manager.getRepository(User);
    const user = await userRepository.findOne({ 
      where: { id: userId } 
    });
    
    // Operations in secondary database
    const commentRepository = secondaryQueryRunner.manager.getRepository(Comment);
    const comment = commentRepository.create({
      userId,
      postId,
      content: 'Comment from user',
    });
    await commentRepository.save(comment);

    // Commit transactions
    await primaryQueryRunner.commitTransaction();
    await secondaryQueryRunner.commitTransaction();
  } catch (error) {
    // Rollback transactions
    await primaryQueryRunner.rollbackTransaction();
    await secondaryQueryRunner.rollbackTransaction();
    throw error;
  } finally {
    // Release resources
    await primaryQueryRunner.release();
    await secondaryQueryRunner.release();
  }
}

Database-Specific Configuration

MySQL Specific Configuration

typescript
const mysqlDataSource = new DataSource({
  type: 'mysql',
  host: 'localhost',
  port: 3306,
  username: 'root',
  password: 'password',
  database: 'myapp',
  entities: [User],
  synchronize: false,
  logging: true,
  
  // MySQL specific configuration
  extra: {
    connectionLimit: 10, // Connection pool size
    acquireTimeout: 60000, // Connection acquisition timeout
    timeout: 60000, // Query timeout
    waitForConnections: true, // Wait for available connections
    queueLimit: 0, // Queue limit
    charset: 'utf8mb4', // Character set
    timezone: '+00:00', // Timezone
    ssl: {
      rejectUnauthorized: false
    }
  },
  
  // MySQL driver options
  driverOptions: {
    flags: ['+FOUND_ROWS'],
    namedPlaceholders: true,
  },
});

PostgreSQL Specific Configuration

typescript
const postgresDataSource = new DataSource({
  type: 'postgres',
  host: 'localhost',
  port: 5432,
  username: 'postgres',
  password: 'password',
  database: 'myapp',
  entities: [User],
  synchronize: false,
  logging: true,
  
  // PostgreSQL specific configuration
  extra: {
    max: 10, // Maximum connections
    idleTimeoutMillis: 30000, // Idle connection timeout
    connectionTimeoutMillis: 2000, // Connection timeout
  },
  
  // PostgreSQL driver options
  driverOptions: {
    application_name: 'MyApp',
    statement_timeout: 10000,
    query_timeout: 10000,
  },
});

MongoDB Specific Configuration

typescript
const mongoDataSource = new DataSource({
  type: 'mongodb',
  host: 'localhost',
  port: 27017,
  database: 'myapp',
  entities: [User],
  synchronize: false,
  logging: true,
  
  // MongoDB specific configuration
  url: 'mongodb://localhost:27017/myapp',
  useNewUrlParser: true,
  useUnifiedTopology: true,
  
  // MongoDB driver options
  driverOptions: {
    maxPoolSize: 10,
    minPoolSize: 2,
    maxIdleTimeMS: 60000,
    serverSelectionTimeoutMS: 5000,
    socketTimeoutMS: 45000,
  },
});

Database Migration Management

Multi-Database Migrations

typescript
// Primary database migration
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');
  }
}

// Secondary database migration
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');
  }
}

Running Specific Database Migrations

typescript
// Run primary database migrations
async function runPrimaryMigrations() {
  await primaryDataSource.runMigrations();
}

// Run secondary database migrations
async function runSecondaryMigrations() {
  await secondaryDataSource.runMigrations();
}

// Run all database migrations
async function runAllMigrations() {
  await Promise.all([
    primaryDataSource.runMigrations(),
    secondaryDataSource.runMigrations(),
    cacheDataSource.runMigrations(),
  ]);
}

Database Switching Strategies

Environment Variable Configuration

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}`);
  }
}

// Usage
const dataSource = createDataSource();
await dataSource.initialize();

Read-Write Separation Configuration

typescript
// Primary database (write operations)
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,
});

// Secondary database (read operations)
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,
});

// Using read-write separation
class UserRepository {
  async findById(id: number) {
    // Read from secondary database
    const repository = readDataSource.getRepository(User);
    return await repository.findOne({ where: { id } });
  }

  async save(user: User) {
    // Write to primary database
    const repository = writeDataSource.getRepository(User);
    return await repository.save(user);
  }

  async findAll() {
    // Read from secondary database
    const repository = readDataSource.getRepository(User);
    return await repository.find();
  }
}

Performance Optimization

Connection Pool Configuration

typescript
// MySQL connection pool optimization
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, // Adjust based on application load
    acquireTimeout: 60000,
    timeout: 60000,
    waitForConnections: true,
    queueLimit: 100,
  },
});

// PostgreSQL connection pool optimization
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, // Maximum connections
    min: 2, // Minimum connections
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 2000,
  },
});

Query Optimization

typescript
// Use appropriate database type
async function getBestDatabaseForOperation(operation: string) {
  switch (operation) {
    case 'read-heavy':
      // Use PostgreSQL for read-heavy operations
      return postgresDataSource;
    
    case 'write-heavy':
      // Use MySQL for write-heavy operations
      return mysqlDataSource;
    
    case 'cache':
      // Use MongoDB for caching
      return mongoDataSource;
    
    case 'analytics':
      // Use PostgreSQL window functions for analytics
      return postgresDataSource;
    
    default:
      return mysqlDataSource;
  }
}

// Usage example
const dataSource = await getBestDatabaseForOperation('read-heavy');
const repository = dataSource.getRepository(User);
const users = await repository.find();

TypeORM's multi-database support provides powerful flexibility, allowing developers to choose the most suitable database type for different needs and use multiple databases simultaneously in the same application.