MySQL如何结合Node.js构建REST API MySQL+Node.js实现高效API开发的步骤

mkdir my-api
cd my-api
npm init -y
npm install express mysql2 dotenv
# 开发环境建议安装nodemon
npm install --save-dev nodemon

config/db.js

// config/db.js
const mysql = require('mysql2/promise');
require('dotenv').config();

const pool = mysql.createPool({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

// 测试连接
pool.getConnection()
    .then(connection => {
        console.log('数据库连接池已成功建立');
        connection.release(); // 释放连接
    })
    .catch(err => {
        console.error('数据库连接失败:', err.message);
        process.exit(1); // 退出应用
    });

module.exports = pool;

DB_HOST=localhost
DB_USER=root
DB_PASSWORD=your_password
DB_NAME=your_database_name
PORT=3000

app.js

server.js

// app.js
const express = require('express');
const app = express();
require('dotenv').config(); // 确保环境变量加载

// 引入数据库连接
const db = require('./config/db');

// 中间件
app.use(express.json()); // 解析JSON格式的请求体

// 简单的根路由
app.get('/', (req, res) => {
    res.send('API works!');
});

// 引入并使用路由 (稍后创建)
const userRoutes = require('./routes/userRoutes');
app.use('/api/users', userRoutes); // 例如，所有用户相关的API都以/api/users开头

// 错误处理中间件 (放在所有路由之后)
app.use((err, req, res, next) => {
    console.error(err.stack);
    res.status(500).send('Something broke!');
});

const PORT = process.env.PORT || 3000;
app.listen(PORT, () => {
    console.log(`服务器运行在 http://localhost:${PORT}`);
});

routes/userRoutes.js

// routes/userRoutes.js
const express = require('express');
const router = express.Router();
const userController = require('../controllers/userController');

router.get('/', userController.getAllUsers);
router.get('/:id', userController.getUserById);
router.post('/', userController.createUser);
router.put('/:id', userController.updateUser);
router.delete('/:id', userController.deleteUser);

module.exports = router;

controllers/userController.js

// controllers/userController.js
const db = require('../config/db');

// 获取所有用户
exports.getAllUsers = async (req, res, next) => {
    try {
        const [rows] = await db.query('SELECT * FROM users');
        res.json(rows);
    } catch (err) {
        next(err); // 传递错误给错误处理中间件
    }
};

// 根据ID获取用户
exports.getUserById = async (req, res, next) => {
    const { id } = req.params;
    try {
        const [rows] = await db.query('SELECT * FROM users WHERE id = ?', [id]);
        if (rows.length === 0) {
            return res.status(404).send('User not found');
        }
        res.json(rows[0]);
    } catch (err) {
        next(err);
    }
};

// 创建新用户
exports.createUser = async (req, res, next) => {
    const { name, email } = req.body;
    if (!name || !email) {
        return res.status(400).send('Name and email are required');
    }
    try {
        const [result] = await db.query('INSERT INTO users (name, email) VALUES (?, ?)', [name, email]);
        res.status(201).json({ id: result.insertId, name, email });
    } catch (err) {
        next(err);
    }
};

// 更新用户
exports.updateUser = async (req, res, next) => {
    const { id } = req.params;
    const { name, email } = req.body;
    if (!name && !email) {
        return res.status(400).send('At least one field (name or email) is required for update');
    }
    try {
        let query = 'UPDATE users SET ';
        const params = [];
        if (name) {
            query += 'name = ?, ';
            params.push(name);
        }
        if (email) {
            query += 'email = ?, ';
            params.push(email);
        }
        query = query.slice(0, -2); // 移除最后的逗号和空格
        query += ' WHERE id = ?';
        params.push(id);

        const [result] = await db.query(query, params);
        if (result.affectedRows === 0) {
            return res.status(404).send('User not found or no changes made');
        }
        res.status(200).send('User updated successfully');
    } catch (err) {
        next(err);
    }
};

// 删除用户
exports.deleteUser = async (req, res, next) => {
    const { id } = req.params;
    try {
        const [result] = await db.query('DELETE FROM users WHERE id = ?', [id]);
        if (result.affectedRows === 0) {
            return res.status(404).send('User not found');
        }
        res.status(204).send(); // 204 No Content for successful deletion
    } catch (err) {
        next(err);
    }
};

package.json

"scripts": {
    "start": "node app.js",
    "dev": "nodemon app.js"
}

npm run dev

await db.query(...)

db.query

db.query('SELECT * FROM users WHERE id = ?', [id])

async

try...catch

next(err)

Joi

express-validator