mysql - 如何正确设置 Node js Rest api

Question

我是一名 android 开发人员，正在尝试使用 Node js 制作一个简单的 REST API，所以我对 JS 基本上是新手。我正在设置一个新的 REST API 并想要连接到 mysql 数据库。我试图以这种方式解决这个问题，但出现错误。

还有，要设置多少个连接限制？

const express = require('express');
const db = require('../db');

    const mainNewsRouter = express.Router();

    mainNewsRouter.get('/', async (req, res, next) => {
        try {
          let result = await db.getMainNews();
          console.log(res.json(result));
          res.json(result);
        } catch(e) {
          console.log(e);
        }
    });

    module.exports = mainNewsRouter;

    //DbHandler.js

    var mysql = require('mysql2');
    const url = require('url');
    var SocksConnection = require('socksjs');

    var remote_options = {
        host:'xxx',
        port: 3306
    };

    var proxy = url.parse('http://xxx:xxx@us-east-static-06.quotaguard.com:xxx');
    var auth = proxy.auth;
    var username = auth.split(":")[0];
    var pass = auth.split(":")[1];

    var sock_options = {
    host: proxy.hostname,
    port: 1080,
    user: username,
    pass: pass
    };

    var sockConn = new SocksConnection(remote_options, sock_options);
    var dbConnection = mysql.createPool({
        connectionLimit: 10,
        user: 'xxx',
        database: 'xxx',
        password: 'xxx',
        stream: sockConn
    });


    getMainNews = () => {

      return new Promise((resolve, reject) => {
          dbConnection.query('SELECT ... * from ...;',
         (err, results) => {
            if (err) {
              return reject(err);
            };

    //          sockConn.dispose();
              return resolve(results);
          });
      });

      dbConnection.end();
    };

On first api call I get data from database, but with this error:

Error [ERR_HTTP_HEADERS_SENT]: Cannot set headers after they are sent to the client
    at ServerResponse.setHeader (_http_outgoing.js:470:11)
    at ServerResponse.header (node_modules\express\lib\response.js:771:10)
    at ServerResponse.send (node_modules\express\lib\response.js:170:12)
    at ServerResponse.json (node_modules\express\lib\response.js:267:15)
    at mainNewsRouter.get (server\routes\mainNews.js:10:11)
    at process._tickCallback (internal/process/next_tick.js:68:7)

第二次 API 调用后没有数据，我只得到这个异常。

> Server is running on port: { Error: This socket has been ended by the
> other party
>     at Socket.writeAfterFIN [as write] (net.js:395:12)
>     at SocksConnection._write (node_modules\socksjs\socks.js:72:24)
>     at doWrite (_stream_writable.js:415:12)
>     at writeOrBuffer (_stream_writable.js:399:5)
>     at SocksConnection.Writable.write (_stream_writable.js:299:11)
>     at PoolConnection.write (node_modules\mysql2\lib\connection.js:221:17)
>     at PoolConnection.writePacket(node_modules\mysql2\lib\connection.js:279:12)
>     at ClientHandshake.sendCredentials (node_modules\mysql2\lib\commands\client_handshake.js:63:16)
>     at ClientHandshake.handshakeInit (node_modules\mysql2\lib\commands\client_handshake.js:136:12)
>     at ClientHandshake.execute (node_modules\mysql2\lib\commands\command.js:39:22) code: 'EPIPE',
> fatal: true }

Answer 1

虽然我绝不是专家，但我认为问题之一在于关闭连接。池的整体思想是将连接释放回池中，而不是关闭它。

我已经对连接池进行了测试，并使用了最小:4 最大:12 的池大小，每秒处理 100 个请求，没有遇到与 MySQL 的连接问题。

就我个人而言，我使用 Knex 来管理我的数据库连接，它也管理所有池，解决了很多令人头痛的问题。开销低，我认为值得将那部分代码移植到。连接问题解决后，您就可以解决出现的其他问题。

再说一次，我不是专家，无法准确地确定在上面的代码中将 MySQL 连接释放回池中，但我确实认为这就是为什么您在初次调用后没有获取数据的原因。