基于NodeJS+Express+MySQL+TypeScript的DatabaseController实现咨询

I'm developing a REST API using NodeJS, Express, and MySQL, with the entry point at app.js. I've already initialized the UserController in it like this:

const router: express.Router = express.Router();
new UserController(router);

Here's my UserController code snippet:

import { Request, Response, Router } from 'express';
import UserModel from '../model/user';
class UserController {
  constructor(private router: Router) {
    router.get('/users', async (req: Request, resp: Response) => {
      try {
        // Existing logic here
      } catch (error) {
        // Error handling here
      }
    });
    // Other routes...
  }
}

I'm looking for guidance on how to implement a DatabaseController (a centralized database connection manager) using TypeScript for this setup.

Answer #

Great question! A centralized DatabaseController will help you manage MySQL connections efficiently, avoid duplicate connections, and keep your code clean. Here's a step-by-step implementation tailored to your TypeScript + Express setup:

1. Install required dependencies

First, make sure you have the necessary packages installed. We'll use mysql2 (it has better TypeScript support than the older mysql package) and its type definitions:

npm install mysql2
npm install -D @types/mysql2

2. Create the DatabaseController class

This class will use a connection pool (the recommended approach for MySQL in Node.js) and enforce a singleton pattern to ensure only one pool exists. Create src/controller/database.ts:

import mysql from 'mysql2/promise';
import { Pool, PoolConnection } from 'mysql2/promise';

class DatabaseController {
  private static instance: DatabaseController;
  private pool: Pool;

  // Private constructor to prevent direct instantiation
  private constructor() {
    // Initialize connection pool with your MySQL credentials
    this.pool = mysql.createPool({
      host: process.env.DB_HOST || 'localhost',
      user: process.env.DB_USER || 'root',
      password: process.env.DB_PASSWORD || '',
      database: process.env.DB_NAME || 'your_database_name',
      waitForConnections: true,
      connectionLimit: 10, // Adjust based on your app's needs
      queueLimit: 0
    });

    // Test connection on initialization
    this.testConnection();
  }

  // Singleton method to get the single instance
  public static getInstance(): DatabaseController {
    if (!DatabaseController.instance) {
      DatabaseController.instance = new DatabaseController();
    }
    return DatabaseController.instance;
  }

  // Get a connection from the pool
  public async getConnection(): Promise<PoolConnection> {
    try {
      return await this.pool.getConnection();
    } catch (error) {
      console.error('Failed to retrieve database connection:', error);
      throw error;
    }
  }

  // Release a connection back to the pool
  public releaseConnection(connection: PoolConnection): void {
    connection.release();
  }

  // Helper method to execute queries directly (handles connection cleanup)
  public async executeQuery(query: string, params?: any[]): Promise<any> {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.query(query, params);
      return rows;
    } finally {
      this.releaseConnection(connection);
    }
  }

  // Test if the connection pool is working
  private async testConnection(): Promise<void> {
    try {
      const connection = await this.pool.getConnection();
      console.log('Successfully connected to MySQL database');
      connection.release();
    } catch (error) {
      console.error('Failed to connect to MySQL database:', error);
      process.exit(1); // Exit app if DB connection fails
    }
  }
}

export default DatabaseController;

3. Update UserModel to use DatabaseController

Modify your UserModel to leverage the centralized database controller instead of managing its own connections. For example, in src/model/user.ts:

import DatabaseController from '../controller/database';

class UserModel {
  private db = DatabaseController.getInstance();

  // Example: Get all users from the database
  public async getAllUsers(): Promise<any[]> {
    const query = 'SELECT * FROM users';
    return await this.db.executeQuery(query);
  }

  // Example: Get a single user by ID
  public async getUserById(id: number): Promise<any> {
    const query = 'SELECT * FROM users WHERE id = ?';
    const results = await this.db.executeQuery(query, [id]);
    return results[0]; // Return the first matching user
  }

  // Add more CRUD methods (create, update, delete) as needed...
}

export default new UserModel();

4. Initialize DatabaseController in app.js

Make sure to initialize the database controller when your app starts to set up the connection pool early. Update app.js:

import express from 'express';
import DatabaseController from './controller/database';
import UserController from './controller/user';

const app = express();
const port = process.env.PORT || 3000;

// Initialize database connection pool first
DatabaseController.getInstance();

// Set up routes
const router = express.Router();
new UserController(router);

// Mount the API router
app.use('/api', router);

app.listen(port, () => {
  console.log(`Server running on port ${port}`);
});

Key Benefits of This Setup

• Singleton Pattern: Guarantees only one connection pool exists, preventing unnecessary resource consumption.
• Connection Pooling: Reuses connections instead of creating new ones for each request, boosting performance.
• Centralized Management: All database configuration and logic lives in one place, making it easy to update credentials or add logging.
• Type Safety: Uses TypeScript types for MySQL operations, reducing runtime errors.

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