createPool

信息

For queries please see the Simple Queries and Prepared Statements examples.

createPool(connectionUri)

createPool(connectionUri: string)

promise.js

import mysql from 'mysql2/promise';

try {
  const pool = mysql.createPool('mysql://root:password@localhost:3306/test');
  const connection = await pool.getConnection();
  // ... some query

  connection.release();

  // Close the pool
  await pool.end();
} catch (err) {
  console.log(err);
}

注意

Don't forget to release the connection when finished by using:

• pool.releaseConnection(connection)
• connection.release()

callback.js

const mysql = require('mysql2');

const pool = mysql.createPool('mysql://root:password@localhost:3306/test');

pool.getConnection(function (err, connection) {
  if (err instanceof Error) {
    console.log(err);
    return;
  }

  // ... some query

  connection.release();

  // Close the pool
  pool.end();
});

注意

Don't forget to release the connection when finished by using:

• pool.releaseConnection(connection)
• connection.release()

await using

import mysql from 'mysql2/promise';

{
  await using pool = mysql.createPool(
    'mysql://root:password@localhost:3306/test'
  );
  // .release() is called automatically when leaving the scope
  await using connection = await pool.getConnection();

  // ... some query
}

提示

await using and using leverage Explicit Resource Management to automatically call .end() or .release() when the variable goes out of scope, so you never forget to clean up connections.

using

const mysql = require('mysql2');

{
  using pool = mysql.createPool('mysql://root:password@localhost:3306/test');

  pool.getConnection(function (err, _connection) {
    if (err instanceof Error) {
      console.log(err);
      return;
    }

    // .release() is called automatically when leaving the scope
    using connection = _connection;

    // ... some query
  });
}

提示

await using and using leverage Explicit Resource Management to automatically call .end() or .release() when the variable goes out of scope, so you never forget to clean up connections.

---

createPool(config)

createPool(config: PoolOptions)

promise.js

import mysql from 'mysql2/promise';

try {
  const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    database: 'test',
    // port: 3306,
    // password: '',
  });
  const connection = await pool.getConnection();
  // ... some query

  connection.release();

  // Close the pool
  await pool.end();
} catch (err) {
  console.log(err);
}

注意

Don't forget to release the connection when finished by using:

• pool.releaseConnection(connection)
• connection.release()

callback.js

const mysql = require('mysql2');

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  database: 'test',
  // port: 3306,
  // password: '',
});

pool.getConnection(function (err, connection) {
  if (err instanceof Error) {
    console.log(err);
    return;
  }

  // ... some query

  connection.release();

  // Close the pool
  pool.end();
});

注意

Don't forget to release the connection when finished by using:

• pool.releaseConnection(connection)
• connection.release()

await using

import mysql from 'mysql2/promise';

{
  await using pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    database: 'test',
    // port: 3306,
    // password: '',
  });
  // .release() is called automatically when leaving the scope
  await using connection = await pool.getConnection();

  // ... some query
}

提示

await using and using leverage Explicit Resource Management to automatically call .end() or .release() when the variable goes out of scope, so you never forget to clean up connections.

using

const mysql = require('mysql2');

{
  using pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    database: 'test',
    // port: 3306,
    // password: '',
  });

  pool.getConnection(function (err, _connection) {
    if (err instanceof Error) {
      console.log(err);
      return;
    }

    // .release() is called automatically when leaving the scope
    using connection = _connection;

    // ... some query
  });
}

提示

await using and using leverage Explicit Resource Management to automatically call .end() or .release() when the variable goes out of scope, so you never forget to clean up connections.

---

createPool(config) — SHA1

createPool(config: PoolOptions)

promise.js

import mysql from 'mysql2/promise';

try {
  const pool = mysql.createPool({
    // ...
    passwordSha1: Buffer.from(
      '8bb6118f8fd6935ad0876a3be34a717d32708ffd',
      'hex'
    ),
  });
  const connection = await pool.getConnection();
  // ... some query

  connection.release();

  // Close the pool
  await pool.end();
} catch (err) {
  console.log(err);
}

注意

Don't forget to release the connection when finished by using:

• pool.releaseConnection(connection)
• connection.release()

callback.js

const mysql = require('mysql2');

const pool = mysql.createPool({
  // ...
  passwordSha1: Buffer.from('8bb6118f8fd6935ad0876a3be34a717d32708ffd', 'hex'),
});

pool.getConnection(function (err, connection) {
  if (err instanceof Error) {
    console.log(err);
    return;
  }

  // ... some query

  connection.release();

  // Close the pool
  pool.end();
});

注意

Don't forget to release the connection when finished by using:

• pool.releaseConnection(connection)
• connection.release()

await using

import mysql from 'mysql2/promise';

{
  await using pool = mysql.createPool({
    // ...
    passwordSha1: Buffer.from(
      '8bb6118f8fd6935ad0876a3be34a717d32708ffd',
      'hex'
    ),
  });
  // .release() is called automatically when leaving the scope
  await using connection = await pool.getConnection();

  // ... some query
}

提示

await using and using leverage Explicit Resource Management to automatically call .end() or .release() when the variable goes out of scope, so you never forget to clean up connections.

using

const mysql = require('mysql2');

{
  using pool = mysql.createPool({
    // ...
    passwordSha1: Buffer.from(
      '8bb6118f8fd6935ad0876a3be34a717d32708ffd',
      'hex'
    ),
  });

  pool.getConnection(function (err, _connection) {
    if (err instanceof Error) {
      console.log(err);
      return;
    }

    // .release() is called automatically when leaving the scope
    using connection = _connection;

    // ... some query
  });
}

提示

await using and using leverage Explicit Resource Management to automatically call .end() or .release() when the variable goes out of scope, so you never forget to clean up connections.

---

createPool(config) — SSL

createPool(config: PoolOptions)

promise.js

import mysql from 'mysql2/promise';

try {
  const pool = mysql.createPool({
    // ...
    ssl: {
      // key: fs.readFileSync('./certs/client-key.pem'),
      // cert: fs.readFileSync('./certs/client-cert.pem')
      ca: fs.readFileSync('./certs/ca-cert.pem'),
    },
  });
  const connection = await pool.getConnection();
  // ... some query

  connection.release();

  // Close the pool
  await pool.end();
} catch (err) {
  console.log(err);
}

注意

Don't forget to release the connection when finished by using:

• pool.releaseConnection(connection)
• connection.release()

callback.js

const mysql = require('mysql2');

const pool = mysql.createPool({
  // ...
  ssl: {
    // key: fs.readFileSync('./certs/client-key.pem'),
    // cert: fs.readFileSync('./certs/client-cert.pem')
    ca: fs.readFileSync('./certs/ca-cert.pem'),
  },
});

pool.getConnection(function (err, connection) {
  if (err instanceof Error) {
    console.log(err);
    return;
  }

  // ... some query

  connection.release();

  // Close the pool
  pool.end();
});

注意

Don't forget to release the connection when finished by using:

• pool.releaseConnection(connection)
• connection.release()

await using

import mysql from 'mysql2/promise';

{
  await using pool = mysql.createPool({
    // ...
    ssl: {
      // key: fs.readFileSync('./certs/client-key.pem'),
      // cert: fs.readFileSync('./certs/client-cert.pem')
      ca: fs.readFileSync('./certs/ca-cert.pem'),
    },
  });
  // .release() is called automatically when leaving the scope
  await using connection = await pool.getConnection();

  // ... some query
}

提示

await using and using leverage Explicit Resource Management to automatically call .end() or .release() when the variable goes out of scope, so you never forget to clean up connections.

using

const mysql = require('mysql2');

{
  using pool = mysql.createPool({
    // ...
    ssl: {
      // key: fs.readFileSync('./certs/client-key.pem'),
      // cert: fs.readFileSync('./certs/client-cert.pem')
      ca: fs.readFileSync('./certs/ca-cert.pem'),
    },
  });

  pool.getConnection(function (err, _connection) {
    if (err instanceof Error) {
      console.log(err);
      return;
    }

    // .release() is called automatically when leaving the scope
    using connection = _connection;

    // ... some query
  });
}

提示

await using and using leverage Explicit Resource Management to automatically call .end() or .release() when the variable goes out of scope, so you never forget to clean up connections.

certs/ca-cert.pem

• See ssl/certs.

---

createPool(config) — RDS SSL

createPool(config: PoolOptions)

You can use Amazon RDS string as value to ssl property to connect to Amazon RDS MySQL over SSL.

In that case https://s3.amazonaws.com/rds-downloads/mysql-ssl-ca-cert.pem CA cert is used:

npm install --save aws-ssl-profiles

promise.js

import mysql from 'mysql2/promise';
import awsCaBundle from 'aws-ssl-profiles';

try {
  const pool = mysql.createPool({
    // ...
    host: 'db.id.ap-southeast-2.rds.amazonaws.com',
    ssl: awsCaBundle,
  });
  const connection = await pool.getConnection();
  // ... some query

  connection.release();

  // Close the pool
  await pool.end();
} catch (err) {
  console.log(err);
}

信息

For detailed instructions, please follow the AWS SSL Profiles documentation.

注意

Don't forget to release the connection when finished by using:

• pool.releaseConnection(connection)
• connection.release()

Testing

try {
  const [res] = await connection.query('SHOW `status` LIKE "Ssl_cipher"');
  await pool.end();

  console.log(res);
} catch (err) {
  console.log(err);
}

callback.js

const mysql = require('mysql2');
const awsCaBundle = require('aws-ssl-profiles');

const pool = mysql.createPool({
  // ...
  host: 'db.id.ap-southeast-2.rds.amazonaws.com',
  ssl: awsCaBundle,
});

pool.getConnection(function (err, connection) {
  if (err instanceof Error) {
    console.log(err);
    return;
  }

  // ... some query

  connection.release();

  // Close the pool
  pool.end();
});

信息

For detailed instructions, please follow the AWS SSL Profiles documentation.

注意

Don't forget to release the connection when finished by using:

• pool.releaseConnection(connection)
• connection.release()

Testing

connectionquery('SHOW `status` LIKE "Ssl_cipher"', function (err, res) {
  pool.end();

  if (err instanceof Error) {
    console.log(err);
    return;
  }

  console.log(res);
});

await using

import mysql from 'mysql2/promise';
import awsCaBundle from 'aws-ssl-profiles';

{
  await using pool = mysql.createPool({
    // ...
    host: 'db.id.ap-southeast-2.rds.amazonaws.com',
    ssl: awsCaBundle,
  });
  // .release() is called automatically when leaving the scope
  await using connection = await pool.getConnection();

  // ... some query
}

信息

For detailed instructions, please follow the AWS SSL Profiles documentation.

提示

await using and using leverage Explicit Resource Management to automatically call .end() or .release() when the variable goes out of scope, so you never forget to clean up connections.

using

const mysql = require('mysql2');
const awsCaBundle = require('aws-ssl-profiles');

{
  using pool = mysql.createPool({
    // ...
    host: 'db.id.ap-southeast-2.rds.amazonaws.com',
    ssl: awsCaBundle,
  });

  pool.getConnection(function (err, _connection) {
    if (err instanceof Error) {
      console.log(err);
      return;
    }

    // .release() is called automatically when leaving the scope
    using connection = _connection;

    // ... some query
  });
}

信息

For detailed instructions, please follow the AWS SSL Profiles documentation.

提示

await using and using leverage Explicit Resource Management to automatically call .end() or .release() when the variable goes out of scope, so you never forget to clean up connections.

---

createPool(config) — Socks

createPool(config: PoolOptions)

A.js

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

const socksProxy = new SocksConnection({ port: 3306 });
const pool = mysql.createPool({
  stream: socksProxy,
});

// Close the pool
pool.end();

B.js

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

const pool = mysql.createPool({
  debug: 1,
  stream: function () {
    return new SocksConnection({ port: 3306 });
  },
});

// Close the pool
pool.end();

Testing

pool.execute('SELECT SLEEP(1.1) AS `www`', (err, rows, fields) => {
  if (err instanceof Error) {
    console.log(err);
    return;
  }

  console.log(rows, fields);
});

pool.execute('SELECT SLEEP(1) AS `qqq`', (err, rows, fields) => {
  if (err instanceof Error) {
    console.log(err);
    return;
  }

  console.log(rows, fields);
});

pool.execute('SELECT SLEEP(1) AS `qqq`', (err, rows, fields) => {
  if (err instanceof Error) {
    console.log(err);
    return;
  }

  console.log(rows, fields);
});

---

Escaping in a Pool

pool.escape(value) · pool.escapeId(identifier) · pool.format(sql, values)

A Pool exposes the same escaping helpers as a single Connection, so there is no need to acquire a connection just to escape a value or an identifier.

import mysql from 'mysql2';

const pool = mysql.createPool('mysql://root:password@localhost:3306/test');

pool.escape(1); // 1
pool.escapeId('users'); // `users`
pool.format('SELECT * FROM ?? WHERE `id` = ?', ['users', 1]);

---

Promise Wrapper in a Pool

connection.promise()

Calling .promise() on a pooled connection returns a promise-based pooled connection, not a promise-based Pool. It keeps release(), so the connection still returns to the pool when you are done with it.

import mysql from 'mysql2';

const pool = mysql.createPool('mysql://root:password@localhost:3306/test');

pool.getConnection(async (err, connection) => {
  if (err instanceof Error) {
    console.log(err);
    return;
  }

  const promiseConnection = connection.promise();
  const [rows, fields] = await promiseConnection.query('SELECT 1');

  console.log(rows, fields);
  promiseConnection.release();
});

---

Glossary

PoolOptions

PoolOptions extends all options from ConnectionOptions:

ConnectionOptions Specification

PoolOptions Specification