如何实现同一REST API连接多台不同主机上的数据库？

Hey there! Your approach to creating multiple connection pools for different databases is totally on the right track—let's figure out why it wasn't working and fix it up.

First, Let's Fix the Core Issues #

Looking at your code, there are two key things that might have caused your setup to fail:

1. Missing quotes around string values: If user1, host1.server.com, etc., aren't pre-defined variables, they need to be wrapped in quotes (otherwise Node.js will throw an "undefined variable" error).
2. Export/import mismatch: Your export structure requires a specific way to reference the pools in other files, which might have tripped you up.

Working Implementation Option 1: Export Individual Pools #

This is the most straightforward approach—export each pool directly so you can import them cleanly elsewhere:

const mysql = require("mysql");

// Pool for first database/server
const pool1 = mysql.createPool({
  connectionLimit: 10,
  user: 'user1', // Add quotes for string values
  password: '123456',
  database: 'database1',
  host: 'host1.server.com',
  port: 3306,
});

// Pool for second database/server
const pool2 = mysql.createPool({
  connectionLimit: 10,
  user: 'user2',
  password: '123456',
  database: 'database2',
  host: 'host2.server.com',
  port: 3306,
});

// Export each pool separately
exports.pool1 = pool1;
exports.pool2 = pool2;

How to Use This in Your API Routes/Controllers

Import the pools and use them to query the respective databases:

// Import the pools from your config file
const { pool1, pool2 } = require('./path-to-your-config-file.js');

// Query first database
pool1.query('SELECT * FROM your_table_in_db1', (err, results) => {
  if (err) throw err;
  console.log('Data from Database 1:', results);
});

// Query second database
pool2.query('SELECT * FROM your_table_in_db2', (err, results) => {
  if (err) throw err;
  console.log('Data from Database 2:', results);
});

Working Implementation Option 2: Export a Pool Object #

If you prefer to keep all pools in a single exported object (like your original attempt), you just need to adjust how you import and reference them:

const mysql = require("mysql");

const pool1 = mysql.createPool({
  connectionLimit: 10,
  user: 'user1',
  password: '123456',
  database: 'database1',
  host: 'host1.server.com',
  port: 3306,
});

const pool2 = mysql.createPool({
  connectionLimit: 10,
  user: 'user2',
  password: '123456',
  database: 'database2',
  host: 'host2.server.com',
  port: 3306,
});

// Export as a single object
exports.pools = { pool1, pool2 };

How to Use This

const dbConnections = require('./path-to-your-config-file.js');

// Access pool1 from the exported object
dbConnections.pools.pool1.query('SELECT * FROM table1', (err, results) => {
  // Handle results/errors
});

// Or destructure for cleaner code
const { pool1, pool2 } = dbConnections.pools;
pool2.query('SELECT * FROM table2', (err, results) => {
  // Handle results/errors
});

Bonus: Clean Up Your Code with Config Objects #

For better maintainability, you can separate your database configurations from the pool creation:

const mysql = require("mysql");

// Centralize all database configs
const dbConfigs = {
  db1: {
    connectionLimit: 10,
    user: 'user1',
    password: '123456',
    database: 'database1',
    host: 'host1.server.com',
    port: 3306,
  },
  db2: {
    connectionLimit: 10,
    user: 'user2',
    password: '123456',
    database: 'database2',
    host: 'host2.server.com',
    port: 3306,
  }
};

// Create pools from configs
const pools = {
  pool1: mysql.createPool(dbConfigs.db1),
  pool2: mysql.createPool(dbConfigs.db2)
};

// Export all pools
module.exports = pools;

This makes it easier to update credentials or add new databases later!

内容的提问来源于stack exchange，提问作者Nebenzahl