mysql - 如何在 Node js 中承诺一个 mysql 池连接？

Question

我试图了解如何使用节点 js 工具来承诺池连接。我会使用async/await逻辑保持我的代码干净（没有回调地狱，我真的不喜欢特别是交易）。

这是我的配置文件：

const mysql = require('mysql');
const util = require('util');

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

// Ping database to check for common exception errors.
pool.getConnection((err, connection) => {
  if (err) {
    if (err.code === 'PROTOCOL_CONNECTION_LOST') {
      console.error('Database connection was closed.')
    }
    if (err.code === 'ER_CON_COUNT_ERROR') {
      console.error('Database has too many connections.')
    }
    if (err.code === 'ECONNREFUSED') {
      console.error('Database connection was refused.')
    }
  }

  if (connection) connection.release();

  return;
})

// Promisify for Node.js async/await.
pool.query = util.promisify(pool.query)

module.exports = pool;

我正在执行这样的单个简单查询（并且它们工作正常）：

let sql = "SELECT * FROM products WHERE code = ? ;"
let param = [code];
let results = await pool.query(sql, param);

我正在以这种方式开发交易（我认为这是一种完全错误的方法）：

try {
    await pool.query('START TRANSACTION');

    sql = "INSERT INTO test (name) VALUES ( ? ) ;"
    param = ['pippo'];
    results = []
    await pool.query(sql, param);

    await pool.query('COMMIT');
} catch (error) {
    await pool.query('ROLLBACK');
    return next(error)
}

对于我不应该使用的事务pool.query（我认为，每次获取一个新连接并在查询完成时自动释放它）。

在我看来，pool.query事务造成了一个大问题：如果一次只运行一个事务是可以的，但是如果同时运行 2 个（或更多）事务，COMMIT则第二个事务的可能可以COMMIT是第一个事务的所有查询只是因为在第一个之前执行COMMIT。

我认为我应该获得一个新的连接，将连接用于整个事务流程并在最后释放它。所以每个事务流都需要一个自己的连接。

但我不知道如何承诺 apool.getConnection就像我承诺pool.query.

我正在尝试类似的东西：

pool.getConnection = util.promisify(pool.getConnection).bind(pool)
const conn = await pool.getConnection();

let sql = "SELECT * FROM test ;"
let param = [];
let results = await conn.query(sql); // I don't get here the expected rows 

但它不起作用。我不会成为结果中的行，但如果console.log(results)我有这个：

Query {
  _events: [Object: null prototype] {
    error: [Function],
    packet: [Function],
    timeout: [Function],
    end: [Function]
  },
  _eventsCount: 4,
  _maxListeners: undefined,
  _callback: undefined,
  _callSite: Error
      at Protocol._enqueue (C:\Users\rocco\wa\ferramenta\server\node_modules\mysql\lib\protocol\Protocol.js:144:48)
      at PoolConnection.query (C:\Users\rocco\wa\ferramenta\server\node_modules\mysql\lib\Connection.js:198:25)
      at C:\Users\rocco\wa\ferramenta\server\routes\imports.js:304:30
      at processTicksAndRejections (internal/process/task_queues.js:97:5),
  _ended: false,
  _timeout: undefined,
  _timer: Timer { _object: [Circular], _timeout: null },
  sql: 'SELECT * FROM test ; ',
  values: [],
  typeCast: true,
  nestTables: false,
  _resultSet: null,
  _results: [],
  _fields: [],
  _index: 0,
  _loadError: null,
  _connection: PoolConnection {
    _events: [Object: null prototype] {
      end: [Function: _removeFromPool],
      error: [Function]
    },
    _eventsCount: 2,
    _maxListeners: undefined,
    config: ConnectionConfig {
      host: 'localhost',
      port: '3306',
      localAddress: undefined,
      socketPath: undefined,
      user: 'root',
      password: '---myPassword---',
      database: '---nameOfMyDb---',
      connectTimeout: 10000,
      insecureAuth: false,
      supportBigNumbers: false,
      bigNumberStrings: false,
      dateStrings: false,
      debug: undefined,
      trace: true,
      stringifyObjects: false,
      timezone: 'local',
      flags: '',
      queryFormat: undefined,
      pool: [Pool],
      ssl: false,
      localInfile: true,
      multipleStatements: false,
      typeCast: true,
      maxPacketSize: 0,
      charsetNumber: 33,
      clientFlags: 455631,
      protocol41: true
    },
    _socket: Socket {
      connecting: false,
      _hadError: false,
      _parent: null,
      _host: 'localhost',
      _readableState: [ReadableState],
      readable: true,
      _events: [Object: null prototype],
      _eventsCount: 4,
      _maxListeners: undefined,
      _writableState: [WritableState],
      writable: true,
      allowHalfOpen: false,
      _sockname: null,
      _pendingData: null,
      _pendingEncoding: '',
      server: null,
      _server: null,
      timeout: 0,
      [Symbol(asyncId)]: 11,
      [Symbol(kHandle)]: [TCP],
      [Symbol(kSetNoDelay)]: false,
      [Symbol(lastWriteQueueSize)]: 0,
      [Symbol(timeout)]: Timeout {
        _idleTimeout: -1,
        _idlePrev: null,
        _idleNext: null,
        _idleStart: 1284,
        _onTimeout: null,
        _timerArgs: undefined,
        _repeat: null,
        _destroyed: true,
        [Symbol(refed)]: false,
        [Symbol(kHasPrimitive)]: false,
        [Symbol(asyncId)]: 14,
        [Symbol(triggerId)]: 1
      },
      [Symbol(kBuffer)]: null,
      [Symbol(kBufferCb)]: null,
      [Symbol(kBufferGen)]: null,
      [Symbol(kCapture)]: false,
      [Symbol(kBytesRead)]: 0,
      [Symbol(kBytesWritten)]: 0
    },
    _protocol: Protocol {
      _events: [Object: null prototype],
      _eventsCount: 7,
      _maxListeners: undefined,
      readable: true,
      writable: true,
      _config: [ConnectionConfig],
      _connection: [Circular],
      _callback: null,
      _fatalError: null,
      _quitSequence: null,
      _handshake: true,
      _handshaked: true,
      _ended: false,
      _destroyed: false,
      _queue: [Array],
      _handshakeInitializationPacket: [HandshakeInitializationPacket],
      _parser: [Parser],
      [Symbol(kCapture)]: false
    },
    _connectCalled: true,
    state: 'authenticated',
    threadId: 117,
    _pool: Pool {
      _events: [Object: null prototype] {},
      _eventsCount: 0,
      _maxListeners: undefined,
      config: [PoolConfig],
      _acquiringConnections: [],
      _allConnections: [Array],
      _freeConnections: [],
      _connectionQueue: [],
      _closed: false,
      query: [Function],
      getConnection: [Function: bound ],
      [Symbol(kCapture)]: false
    },
    [Symbol(kCapture)]: false
  },
  [Symbol(kCapture)]: false
}

一些想法？谢谢

标签： mysqlnode.jsnode-promisify

Answer 1

这是一个关于我如何解决它的简单示例：

pool.getConnection = util.promisify(pool.getConnection)
let conn = await pool.getConnection();
conn.query = util.promisify(conn.query)

sql = "INSERT INTO test (name) VALUES ( ? ) ; ";
param = ['fakename'];
results = [];
results = await conn.query(sql, param)

conn.release(); // is important remember to release the connection