基于子域名的多客户端数据库连接（Node.js+Express）实现指引

Hey there! Let's walk through exactly how to build this subdomain-based multi-tenant database setup in your Node.js + Express app. I've broken this down into actionable steps with code examples to make it straightforward:

1. First: Set Up Local Subdomain Resolution #

Since you're using *.localhost URLs locally, you need to tell your OS to recognize these subdomains. Edit your system's hosts file:

• Windows: C:\Windows\System32\drivers\etc\hosts
• Mac/Linux: /etc/hosts

Add these lines at the bottom:

127.0.0.1 shreyas.localhost ajay.localhost vijay.localhost

Save the file (you might need admin rights for this). Now your machine will route these subdomains to your local server.

2. Create Database Configuration #

Make a config file to map each subdomain to its database credentials. Let's call it db.config.js:

module.exports = {
  shreyas: {
    uri: 'mongodb://localhost:27017/shreyas_db',
    // For SQL: host: 'localhost', user: 'root', password: 'your_password', database: 'shreyas_db'
  },
  ajay: {
    uri: 'mongodb://localhost:27017/ajay_db',
  },
  vijay: {
    uri: 'mongodb://localhost:27017/vijay_db',
  }
};

Adjust the credentials to match your database type (MongoDB, MySQL, PostgreSQL, etc.).

3. Build a Subdomain-to-Database Middleware #

This middleware will extract the subdomain from the request, match it to the correct database, and attach the connection to the request object so your routes can use it.

First, let's create a database manager (we'll use Mongoose for MongoDB here; adjust if you're using SQL):
db.manager.js

const mongoose = require('mongoose');
const dbConfig = require('./db.config');

// Cache connections to avoid re-creating them on every request
const connections = {};

async function getDbConnection(subdomain) {
  if (!dbConfig[subdomain]) {
    throw new Error(`No database configured for subdomain: ${subdomain}`);
  }

  // Reuse existing connection if it exists
  if (connections[subdomain]) {
    return connections[subdomain];
  }

  // Create new connection
  const connection = await mongoose.createConnection(dbConfig[subdomain].uri);
  connections[subdomain] = connection;
  return connection;
}

module.exports = getDbConnection;

Now the Express middleware:
subdomainDb.middleware.js

const getDbConnection = require('./db.manager');

async function subdomainDbMiddleware(req, res, next) {
  try {
    // Extract subdomain: handle cases where host includes port (e.g., shreyas.localhost:3001)
    const host = req.headers.host.split(':')[0];
    const subdomain = host.split('.')[0];

    // Get the matching database connection
    const dbConnection = await getDbConnection(subdomain);
    // Attach connection to request for routes to use
    req.db = dbConnection;

    next();
  } catch (err) {
    res.status(400).json({ error: err.message });
  }
}

module.exports = subdomainDbMiddleware;

4. Integrate Middleware into Your Express App #

In your main app.js (or server.js):

const express = require('express');
const subdomainDbMiddleware = require('./subdomainDb.middleware');
const app = express();

// Apply the middleware to all routes (or specific routes if needed)
app.use(subdomainDbMiddleware);

// Example route: Fetch data from the tenant's database
app.get('/api/data', async (req, res) => {
  try {
    // Use the attached database connection to define a model (or use pre-defined models)
    const User = req.db.model('User', new mongoose.Schema({ name: String }));
    const users = await User.find();
    res.json({ users, tenant: req.headers.host.split('.')[0] });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

app.listen(3001, () => {
  console.log('Server running on http://localhost:3001');
});

5. Test It Out #

1. Make sure your databases (shreyas_db, ajay_db, vijay_db) are created and running.
2. Start your Express server.
3. Visit each subdomain URL:
   • http://shreyas.localhost:3001/api/data → connects to shreyas_db
   • http://ajay.localhost:3001/api/data → connects to ajay_db
   • http://vijay.localhost:3001/api/data → connects to vijay_db

Bonus Tips #

• Handle Invalid Subdomains: In the middleware, add logic to redirect or return an error if the subdomain isn't in your config.
• SQL Databases: If using MySQL/PostgreSQL, use connection pooling instead of caching individual connections. For example, with mysql2, create a pool for each tenant and reuse it.
• Case Insensitivity: Convert the subdomain to lowercase (subdomain.toLowerCase()) to avoid issues with capitalization.
• Security: Ensure your database credentials are stored securely (use environment variables instead of hardcoding them in the config file).

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