Multi-database connection management

Multi-Database Connection Management

Mongoose, as the most popular MongoDB ODM in Node.js, provides a flexible multi-database connection mechanism. In real-world projects, it is often necessary to operate multiple database instances simultaneously, such as in scenarios like master-slave separation, sharded clusters, or cross-business database access.

Basic Connection Configuration

Creating a single database connection is the foundational scenario. Mongoose achieves this through mongoose.connect():

const mongoose = require('mongoose');
const mainDB = mongoose.createConnection('mongodb://localhost:27017/main');

mainDB.on('connected', () => {
  console.log('Main DB connected');
});

mainDB.on('error', (err) => {
  console.error('Main DB connection error:', err);
});

For multiple database connections, independent connection instances need to be created. Each connection maintains its own connection pool, model definitions, and transaction states:

const userDB = mongoose.createConnection('mongodb://localhost:27017/users');
const productDB = mongoose.createConnection('mongodb://localhost:27017/products');

Connection Pool Optimization

Mongoose defaults to a connection pool size of 5. High-concurrency scenarios require adjustments:

const options = {
  poolSize: 10, // Connection pool size
  bufferMaxEntries: 0, // Disable buffering
  connectTimeoutMS: 30000,
  socketTimeoutMS: 45000
};

const analyticsDB = mongoose.createConnection(
  'mongodb://cluster1.example.com:27017/analytics',
  options
);

Different business scenarios recommend differentiated configurations:

• High-frequency queries: Increase poolSize
• Batch writes: Maintain a smaller poolSize but increase timeout
• Reporting databases: Set readPreference=secondaryPreferred

Model Binding Mechanism

In a multi-database environment, models must be bound to specific connections:

// Wrong approach - Model will bind to the default mongoose instance
// const User = mongoose.model('User', userSchema);

// Correct approach
const User = userDB.model('User', userSchema);
const Product = productDB.model('Product', productSchema);

Dynamic model registration solution:

function createModel(conn, modelName, schema) {
  if (conn.models[modelName]) {
    return conn.models[modelName];
  }
  return conn.model(modelName, schema);
}

const Order = createModel(orderDB, 'Order', orderSchema);

Cross-Database Transactions

MongoDB 4.2+ supports cross-database transactions but requires special handling:

const session = await mainDB.startSession();
session.startTransaction();

try {
  const user = await User.create([{ name: 'Alice' }], { session });
  await Product.updateOne(
    { sku: '123' },
    { $inc: { stock: -1 } },
    { session: session }
  );
  await session.commitTransaction();
} catch (error) {
  await session.abortTransaction();
  throw error;
} finally {
  session.endSession();
}

Connection State Monitoring

Production environments require real-time monitoring of connection states:

function monitorConnections() {
  const connections = [mainDB, userDB, productDB];
  
  connections.forEach(conn => {
    console.log(`${conn.name} state:`, conn.readyState);
    // 0: disconnected
    // 1: connected
    // 2: connecting
    // 3: disconnecting
  });
}

setInterval(monitorConnections, 60000);

Fault Handling Strategies

Reconnection mechanism during network fluctuations:

function setupReconnect(conn, url) {
  conn.on('disconnected', () => {
    console.log(`Disconnected from ${conn.name}`);
    setTimeout(() => conn.openUri(url), 5000);
  });
}

setupReconnect(userDB, 'mongodb://localhost:27017/users');

Multi-Tenancy Architecture Implementation

Common multi-tenant solutions in SaaS applications:

class TenantManager {
  constructor() {
    this.tenantConnections = new Map();
  }

  getTenantDB(tenantId) {
    if (this.tenantConnections.has(tenantId)) {
      return this.tenantConnections.get(tenantId);
    }

    const conn = mongoose.createConnection(
      `mongodb://cluster.example.com/${tenantId}`,
      { maxPoolSize: 5 }
    );
    
    this.tenantConnections.set(tenantId, conn);
    return conn;
  }
}

Performance Optimization Techniques

1. Connection warm-up:

async function warmupConnection(conn) {
  await conn.collection('test').findOne({});
}

2. Read-write separation configuration:

const readReplica = mongoose.createConnection('mongodb://replica1.example.com,replica2.example.com/db', {
  readPreference: 'secondary',
  replicaSet: 'rs0'
});

3. Connection lifecycle management:

process.on('SIGINT', async () => {
  await Promise.all([
    mainDB.close(),
    userDB.close(false), // forceClose=false
    productDB.close(true) // forceClose=true
  ]);
  process.exit(0);
});

Testing Environment Strategies

Isolation solutions in automated testing:

beforeEach(async () => {
  testDB = mongoose.createConnection(
    'mongodb://localhost:27017/test',
    { poolSize: 1 }
  );
  await testDB.dropDatabase();
});

afterEach(async () => {
  await testDB.close();
});

Configuration Best Practices

Recommended production environment configuration template:

const productionConfig = {
  autoIndex: false, // Disable auto-indexing in production
  bufferCommands: false, // Disable buffering
  connectTimeoutMS: 30000,
  socketTimeoutMS: 45000,
  family: 4, // Force IPv4
  keepAlive: true,
  keepAliveInitialDelay: 300000,
  maxPoolSize: 10,
  minPoolSize: 2,
  retryReads: true,
  retryWrites: true,
  serverSelectionTimeoutMS: 5000,
  heartbeatFrequencyMS: 10000
};

const prodDB = mongoose.createConnection(
  'mongodb+srv://prod.example.com/main',
  productionConfig
);

Advanced Connection Patterns

1. Dynamic routing connections:

function routeConnection(user) {
  return user.region === 'EU' ? 
    euDB : 
    asiaDB;
}

2. Connection proxy pattern:

class DBProxy {
  constructor() {
    this.primary = primaryDB;
    this.secondary = secondaryDB;
  }

  query(options) {
    return options.readOnly ? 
      this.secondary : 
      this.primary;
  }
}

3. Hybrid cloud connection solutions:

const hybridDB = mongoose.createConnection([
  'mongodb://on-premise.example.com:27017/db',
  'mongodb://cloud-cluster.example.com:27017/db'
], {
  replicaSet: 'hybridSet',
  readPreference: 'nearest'
});