Multi-database hybrid usage strategy

Multi-Database Hybrid Usage Strategy

In modern application development, a single database often struggles to meet all business scenario requirements. Combining multiple database types to leverage their respective advantages has become an effective way to enhance system performance and flexibility. Koa2, as a lightweight Node.js framework, when paired with appropriate database drivers and middleware, can elegantly achieve multi-database collaboration.

Typical Scenarios for Hybrid Databases

Relational databases like MySQL are suitable for handling structured data and complex transactions, NoSQL databases like MongoDB excel at managing unstructured data and horizontal scaling, while Redis addresses high-frequency read/write and caching needs. In an e-commerce system, user order data is stored in MySQL to ensure transactional consistency, product details use MongoDB for flexible structures, and shopping cart data leverages Redis for fast read/write operations.

// Typical multi-database connection configuration
const mysql = require('mysql2/promise');
const mongoose = require('mongoose');
const redis = require('redis');

// MySQL connection pool
const mysqlPool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  database: 'order_db',
  waitForConnections: true,
  connectionLimit: 10
});

// MongoDB connection
mongoose.connect('mongodb://localhost:27017/product_db', {
  useNewUrlParser: true,
  useUnifiedTopology: true
});

// Redis client
const redisClient = redis.createClient(6379);

Data Synchronization and Consistency Guarantees

In a multi-database environment, data synchronization is a key challenge. Adopting an event-driven architecture with message queues to achieve eventual consistency is a solid solution. After an order is created, an event is published via RabbitMQ, and the product and shopping cart services consume the event to update their respective databases.

// Using RabbitMQ for data synchronization
const amqp = require('amqplib');

async function publishOrderEvent(order) {
  const conn = await amqp.connect('amqp://localhost');
  const channel = await conn.createChannel();
  await channel.assertExchange('order_events', 'fanout', { durable: false });
  
  channel.publish('order_events', '', Buffer.from(JSON.stringify({
    type: 'ORDER_CREATED',
    payload: order
  })));
}

Solutions for Transaction Handling

Cross-database transactions cannot use traditional ACID transactions and require the Saga pattern. Break down large transactions into multiple local transactions, publish events after each transaction completes, and have subsequent transactions subscribe to these events for execution. Use compensating transactions to roll back in case of failure.

// Example of a Saga transaction coordinator
class OrderSaga {
  async createOrder(orderData) {
    try {
      // Step 1: Create order (MySQL)
      const order = await mysqlPool.execute(
        'INSERT INTO orders SET ?', [orderData]);
      
      // Step 2: Deduct inventory (MongoDB)
      await ProductModel.updateOne(
        { _id: orderData.productId },
        { $inc: { stock: -orderData.quantity } }
      );
      
      // Step 3: Clear shopping cart (Redis)
      await redisClient.del(`cart:${orderData.userId}`);
      
      return order;
    } catch (error) {
      // Compensation logic
      await this.compensate(orderData);
      throw error;
    }
  }
}

Query Aggregation and Performance Optimization

In a hybrid database environment, complex queries require aggregating data from multiple sources. The API composition pattern is a common solution, where the service layer queries different databases separately and merges the results. For high-frequency queries, Redis can cache aggregated results.

// Example of aggregating queries from multiple data sources
router.get('/order-details/:id', async (ctx) => {
  const orderId = ctx.params.id;
  
  // Get basic order information from MySQL
  const [order] = await mysqlPool.execute(
    'SELECT * FROM orders WHERE id = ?', [orderId]);
  
  // Get product details from MongoDB
  const product = await ProductModel.findById(order.productId);
  
  // Get user information cache from Redis
  const user = await redisClient.get(`user:${order.userId}`);
  
  ctx.body = {
    ...order,
    product,
    user: JSON.parse(user)
  };
});

Error Handling and Retry Mechanisms

Network partitions and database failures are common in distributed systems. Implementing robust retry and fallback strategies is crucial. Exponential backoff algorithms can prevent cascading failures, while circuit breaker patterns avoid avalanche effects.

// Database operations with retry mechanism
async function withRetry(operation, maxRetries = 3) {
  let attempt = 0;
  while (attempt < maxRetries) {
    try {
      return await operation();
    } catch (error) {
      attempt++;
      if (attempt >= maxRetries) throw error;
      
      const delay = Math.pow(2, attempt) * 100;
      await new Promise(resolve => setTimeout(resolve, delay));
    }
  }
}

// Usage example
router.post('/orders', async (ctx) => {
  await withRetry(async () => {
    const saga = new OrderSaga();
    return await saga.createOrder(ctx.request.body);
  });
});

Monitoring and Operational Considerations

Hybrid database systems require comprehensive monitoring solutions. Prometheus with Grafana can collect performance metrics from various databases, while the ELK stack enables centralized log analysis. Set reasonable connection pool parameters and timeout configurations for each database connection.

// Database health check middleware
async function dbHealthCheck(ctx, next) {
  const checks = [
    mysqlPool.query('SELECT 1').then(() => 'MySQL: OK'),
    mongoose.connection.db.command({ ping: 1 }).then(() => 'MongoDB: OK'),
    redisClient.ping().then(() => 'Redis: OK')
  ];
  
  try {
    const results = await Promise.all(checks);
    ctx.state.dbStatus = results;
    await next();
  } catch (error) {
    ctx.throw(503, `Database unavailable: ${error.message}`);
  }
}

app.use(dbHealthCheck);

Security Best Practices

Different databases have varying security configuration requirements. MySQL requires SSL connections and strict access control, MongoDB should enable authentication and role-based authorization, and Redis must be password-protected. Sensitive data should be encrypted, and connection strings configured via environment variables.

// Example security configuration
// config.js
module.exports = {
  mysql: {
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    ssl: {
      rejectUnauthorized: true,
      ca: fs.readFileSync('./certs/mysql-ca.pem')
    }
  },
  mongo: {
    authSource: 'admin',
    auth: { 
      user: process.env.MONGO_USER,
      password: process.env.MONGO_PASSWORD 
    }
  }
};

Architecture Evolution and Data Migration

As business grows, database strategies may need adjustment. Dual-write mode enables smooth migration: new data is written to both old and new databases simultaneously, background tasks gradually migrate historical data, and read operations are eventually switched to the new database.

// Dual-write mode implementation
class DualWriter {
  constructor(oldDb, newDb) {
    this.oldDb = oldDb;
    this.newDb = newDb;
  }
  
  async create(data) {
    // Write to both databases in parallel
    await Promise.all([
      this.oldDb.create(data),
      this.newDb.create(data)
    ]);
  }
  
  async migrate() {
    // Migrate historical data
    const records = await this.oldDb.findAll();
    for (const record of records) {
      await this.newDb.create(record.toJSON());
    }
  }
}