基于Node.js与Sequelize的多租户应用数据库连接方案咨询

Great question! Your plan to use sessions and middleware for dynamic database switching is exactly the right direction for this isolated multi-tenant setup. Let’s walk through a concrete implementation tailored to Node.js, Sequelize (MySQL), and Passport.js:

First, you’ll need a main database to store customer-specific database credentials and identifiers. Let’s define a Customer model in Sequelize for this:

// models/Customer.js (main database connection)
const { DataTypes } = require('sequelize');
const mainSequelize = require('../config/main-db'); // Your main DB connection

const Customer = mainSequelize.define('Customer', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  name: {
    type: DataTypes.STRING,
    allowNull: false
  },
  dbHost: {
    type: DataTypes.STRING,
    allowNull: false
  },
  dbUser: {
    type: DataTypes.STRING,
    allowNull: false
  },
  dbPassword: {
    type: DataTypes.STRING,
    allowNull: false
  },
  dbName: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: true
  }
});

module.exports = Customer;

Once a user logs in via Passport, you need to link them to their associated customer record and store the customerId in the session. Adjust your Passport strategy’s serializeUser and authentication callback:

// passport-config.js
const passport = require('passport');
const LocalStrategy = require('passport-local').Strategy;
const User = require('./models/User'); // Your user model in main DB
const Customer = require('./models/Customer');

passport.use(new LocalStrategy(async (username, password, done) => {
  try {
    const user = await User.findOne({ where: { username } });
    if (!user || !await user.validatePassword(password)) {
      return done(null, false, { message: 'Invalid credentials' });
    }
    // Fetch the customer associated with this user (adjust based on your schema)
    const customer = await Customer.findByPk(user.customerId);
    if (!customer) {
      return done(null, false, { message: 'No customer linked to user' });
    }
    // Store both user and customer ID in session (only non-sensitive data!)
    return done(null, { userId: user.id, customerId: customer.id });
  } catch (err) {
    return done(err);
  }
}));

passport.serializeUser((userData, done) => {
  done(null, userData); // Stores { userId, customerId } in session
});

passport.deserializeUser(async (userData, done) => {
  // Optional: Fetch fresh user/customer data on each request if needed
  done(null, userData);
});

Create a middleware that runs on every authenticated request, fetches the customer’s database credentials from the main DB, and creates/reuses a Sequelize instance for that customer. Use a cache to avoid re-establishing connections on every request:

// middleware/dynamic-db.js
const Customer = require('../models/Customer');
const { Sequelize } = require('sequelize');

// Cache for active Sequelize instances (key: customerId)
const sequelizeCache = {};

const dynamicDbMiddleware = async (req, res, next) => {
  // Skip middleware for unauthenticated routes (e.g., login)
  if (!req.isAuthenticated() || !req.user.customerId) {
    return next();
  }

  const customerId = req.user.customerId;
  const cacheKey = `customer_${customerId}`;

  try {
    // Check if we already have a cached connection
    if (!sequelizeCache[cacheKey]) {
      // Fetch customer DB credentials from main DB
      const customer = await Customer.findByPk(customerId);
      if (!customer) {
        return res.status(403).json({ message: 'Customer not found' });
      }

      // Initialize new Sequelize instance for the customer's DB
      const customerSequelize = new Sequelize(
        customer.dbName,
        customer.dbUser,
        customer.dbPassword,
        {
          host: customer.dbHost,
          dialect: 'mysql',
          pool: {
            max: 5, // Adjust based on your DB capacity
            min: 0,
            idle: 10000
          }
        }
      );

      // Verify the connection
      await customerSequelize.authenticate();
      console.log(`Connected to customer ${customerId}'s database`);

      // Cache the instance
      sequelizeCache[cacheKey] = customerSequelize;
    }

    // Attach the customer's Sequelize instance to the request object
    req.customerSequelize = sequelizeCache[cacheKey];
    next();
  } catch (err) {
    console.error('Failed to connect to customer database:', err);
    // Clean up invalid cache entry
    if (sequelizeCache[cacheKey]) {
      delete sequelizeCache[cacheKey];
    }
    return res.status(500).json({ message: 'Database connection failed' });
  }
};

module.exports = dynamicDbMiddleware;

Then register this middleware in your Express app after Passport and session middleware:

// app.js
const express = require('express');
const session = require('express-session');
const passport = require('passport');
const dynamicDbMiddleware = require('./middleware/dynamic-db');

const app = express();

// Session configuration (use Redis in production!)
app.use(session({
  secret: 'your-secret-key',
  resave: false,
  saveUninitialized: false,
  cookie: { secure: process.env.NODE_ENV === 'production' }
}));

app.use(passport.initialize());
app.use(passport.session());

// Apply dynamic DB middleware after authentication
app.use(dynamicDbMiddleware);

Now you can use req.customerSequelize to interact with the customer’s database. Create reusable model factories instead of using a global Sequelize instance:

// models/customer/User.js (model factory)
const { DataTypes } = require('sequelize');

const createUserModel = (sequelize) => {
  return sequelize.define('User', {
    name: DataTypes.STRING,
    email: DataTypes.STRING,
    // Add other customer-specific fields
  });
};

module.exports = createUserModel;

In your route handler:

// routes/customer.js
const express = require('express');
const createUserModel = require('../models/customer/User');

const router = express.Router();

router.get('/users', async (req, res) => {
  try {
    const User = createUserModel(req.customerSequelize);
    // Optional: Sync model if needed (use cautiously in production)
    await User.sync({ alter: false });
    const users = await User.findAll();
    res.json(users);
  } catch (err) {
    res.status(500).json({ message: 'Failed to fetch users', error: err.message });
  }
});

module.exports = router;

• Session Security: Use express-session with a Redis store (instead of in-memory) for scalability and security. Enable secure, HttpOnly cookies.
• Connection Pool Management: Tune the pool settings in Sequelize to avoid exhausting database connections across tenants.
• Cache Invalidation: If a customer’s database credentials change, delete their entry from sequelizeCache so the next request creates a new connection.
• Error Handling: Add robust error handling in the middleware to catch connection failures and return meaningful responses.
• Authorization: Ensure users can only access their assigned customer’s database—validate the customerId in the session against the user’s actual customer association on every request.

内容的提问来源于stack exchange，提问作者Ermanno Palmizio Salieri