## File: README.md [npm-image]: https://img.shields.io/npm/v/mysql2.svg [npm-url]: https://npmjs.com/package/mysql2 [node-version-image]: https://img.shields.io/node/v/mysql2.svg [node-version-url]: https://nodejs.org/en/download [downloads-image]: https://img.shields.io/npm/dm/mysql2.svg [downloads-url]: https://npmjs.com/package/mysql2 [license-url]: https://github.com/sidorares/node-mysql2/blob/master/License [license-image]: https://img.shields.io/npm/l/mysql2.svg?maxAge=2592000 [node-mysql]: https://github.com/mysqljs/mysql [mysqljs]: https://github.com/mysqljs [mysql-native]: https://github.com/sidorares/nodejs-mysql-native [sidorares]: https://github.com/sidorares [TooTallNate]: https://gist.github.com/TooTallNate [starttls.js]: https://gist.github.com/TooTallNate/848444 [node-mariasql]: https://github.com/mscdex/node-mariasql [contributors]: https://github.com/sidorares/node-mysql2/graphs/contributors [contributing]: https://github.com/sidorares/node-mysql2/blob/master/Contributing.md [docs-base]: https://sidorares.github.io/node-mysql2/docs [docs-base-zh-CN]: https://sidorares.github.io/node-mysql2/zh-CN/docs [docs-base-pt-BR]: https://sidorares.github.io/node-mysql2/pt-BR/docs [docs-prepared-statements]: https://sidorares.github.io/node-mysql2/docs/documentation/prepared-statements [docs-mysql-server]: https://sidorares.github.io/node-mysql2/docs/documentation/mysql-server [docs-promise-wrapper]: https://sidorares.github.io/node-mysql2/docs/documentation/promise-wrapper [docs-authentication-switch]: https://sidorares.github.io/node-mysql2/docs/documentation/authentication-switch [docs-streams]: https://sidorares.github.io/node-mysql2/docs/documentation/extras [docs-typescript-docs]: https://sidorares.github.io/node-mysql2/docs/documentation/typescript-examples [docs-qs-pooling]: https://sidorares.github.io/node-mysql2/docs#using-connection-pools [docs-qs-first-query]: https://sidorares.github.io/node-mysql2/docs#first-query [docs-qs-using-prepared-statements]: https://sidorares.github.io/node-mysql2/docs#using-prepared-statements [docs-examples]: https://sidorares.github.io/node-mysql2/docs/examples [docs-faq]: https://sidorares.github.io/node-mysql2/docs/faq [docs-documentation]: https://sidorares.github.io/node-mysql2/docs/documentation [docs-contributing]: https://sidorares.github.io/node-mysql2/docs/contributing/website [coverage]: https://img.shields.io/codecov/c/github/sidorares/node-mysql2 [coverage-url]: https://app.codecov.io/github/sidorares/node-mysql2 [ci-url]: https://github.com/sidorares/node-mysql2/actions/workflows/ci-coverage.yml?query=branch%3Amaster [ci-image]: https://img.shields.io/github/actions/workflow/status/sidorares/node-mysql2/ci-coverage.yml?event=push&style=flat&label=CI&branch=master # MySQL2 [![NPM Version][npm-image]][npm-url] [![NPM Downloads][downloads-image]][downloads-url] [![Node.js Version][node-version-image]][node-version-url] [![GitHub Workflow Status (with event)][ci-image]][ci-url] [![Codecov][coverage]][coverage-url] [![License][license-image]][license-url] [English][docs-base] | [简体中文][docs-base-zh-CN] | [Português (BR)][docs-base-pt-BR] > MySQL client for Node.js with focus on performance. Supports prepared statements, non-utf8 encodings, binary log protocol, compression, ssl [much more][docs-documentation]. **Table of Contents** - [History and Why MySQL2](#history-and-why-mysql2) - [Installation](#installation) - [Documentation](#documentation) - [Acknowledgements](#acknowledgements) - [Contributing](#contributing) ## History and Why MySQL2 MySQL2 project is a continuation of [MySQL-Native][mysql-native]. Protocol parser code was rewritten from scratch and api changed to match popular [Node MySQL][node-mysql]. MySQL2 team is working together with [Node MySQL][node-mysql] team to factor out shared code and move it under [mysqljs][mysqljs] organization. MySQL2 is mostly API compatible with [Node MySQL][node-mysql] and supports majority of features. MySQL2 also offers these additional features: - Faster / Better Performance - [Prepared Statements][docs-prepared-statements] - MySQL Binary Log Protocol - [MySQL Server][docs-mysql-server] - Extended support for Encoding and Collation - [Promise Wrapper][docs-promise-wrapper] - Compression - SSL and [Authentication Switch][docs-authentication-switch] - [Custom Streams][docs-streams] - [Pooling][docs-qs-pooling] ## Installation MySQL2 is free from native bindings and can be installed on Linux, Mac OS or Windows without any issues. ```bash npm install --save mysql2 ``` If you are using TypeScript, you will need to install `@types/node`. ```bash npm install --save-dev @types/node ``` > For TypeScript documentation and examples, see [here][docs-typescript-docs]. ## Documentation - [Quickstart][docs-base] - [First Query][docs-qs-first-query], [Using Prepared Statements][docs-qs-using-prepared-statements], [Using Connection Pools][docs-qs-pooling] and more. - [Documentation][docs-documentation] - [Examples][docs-examples] - [FAQ][docs-faq] ## Acknowledgements - Internal protocol is written by [@sidorares][sidorares] [MySQL-Native][mysql-native]. - Constants, SQL parameters interpolation, Pooling, `ConnectionConfig` class taken from [Node MySQL][node-mysql]. - SSL upgrade code based on [@TooTallNate][TooTallNate] [code][starttls.js]. - Secure connection / compressed connection api flags compatible to [MariaSQL][node-mariasql] client. - [Contributors][contributors]. ## Contributing Want to improve something in **MySQL2**? Please check [Contributing.md][contributing] for detailed instruction on how to get started. To contribute in **MySQL2 Documentation**, please visit the [Website Contributing Guidelines][docs-contributing] for detailed instruction on how to get started. --- ## File: website/docs/contributing/00-index.mdx --- slug: /contributing title: MySQL2 --- import { PageTitle } from '@site/src/components/PageTitle'; # Contributing Want to improve something in **MySQL2**? Please check [Contributing.md](https://github.com/sidorares/node-mysql2/blob/master/Contributing.md) for detailed instruction on how to get started. --- ## File: website/docs/contributing/website.mdx --- title: Documentation Site --- import { FAQ } from '@site/src/components/FAQ'; import { History } from '@site/src/components/History'; import { Stability } from '@site/src/components/Stability'; import { ExternalCodeEmbed } from '@site/src/components/ExternalCodeEmbed'; # Website Contributing Guidelines This website is built using [Docusaurus 3](https://docusaurus.io/), a modern static website generator.
## Environment You will need these tools installed on your system: - [Node.js (18.x or higher)](https://nodejs.org/)
## Development 1. Fork the [MySQL2](https://github.com/sidorares/node-mysql2) repository. 2. Download your forked repository locally. The website's workspace is the "_website_" directory in **node-mysql2** root. 3. Create a new branch from `master` (optional). 4. Run `cd website` to enter the website workspace. 5. Run `npm ci` to install the dependencies from _package-lock.json_. 6. Run `npm start` to starting the local development. > It will start a local development server and opens up a browser window. Most changes are reflected live without having to restart the server. For **Docusaurus** complete documentation, please [see here](https://docusaurus.io/docs). ```bash git clone https://github.com/sidorares/node-mysql2.git git checkout -b website # optional cd /path-to/node-mysql2/website npm ci npm start ``` Documentation is auto-generated from **MDX** files placed in these directories: - _./docs/documentation_ - _./docs/examples_ - _./docs/faq_ :::danger Caution Note that the website has its own _package.json_. Please, do not install dependencies for the website in **node-mysql2** root. :::
## Extras Components Every extra component is thoroughly documented with complete typings descriptions. > [**Docusaurus Markdown Features:**](https://docusaurus.io/docs/markdown-features) The MDX compiler transforms Markdown files to React components, and allows you to use JSX in your Markdown content. This enables you to easily interleave React components within your content, and create delightful learning experiences. ### History The `History` component displays version changes in a table format, listing version numbers alongside their changes. {/* prettier-ignore-start */} ```tsx import { History } from '@site/src/components/History'; ``` {/* prettier-ignore-end */} :::tip You can also utilize React components in the `changes` option. :::
### Stability See the [Stability Badges](/docs/stability-badges) for more details. {/* prettier-ignore-start */} ```tsx import { Stability } from '@site/src/components/Stability'; ``` {/* prettier-ignore-end */} :::tip You can also utilize React components in the `message` option. ::: Available levels: `0`, `1`, `1.1`, `1.2`, `2` and `3`.
### FAQ {/* prettier-ignore-start */} ```tsx import { FAQ } from '@site/src/components/FAQ'; > Some markdown (**MDX**) content. ``` {/* prettier-ignore-end */} :::tip - The **FAQ** component can be utilized in any section or page. - Code blocks are compatible and can be used within the **FAQ** component. ::: > Some markdown (**MDX**) content.
### ExternalCodeEmbed {/* prettier-ignore-start */} ```tsx import { ExternalCodeEmbed } from '@site/src/components/ExternalCodeEmbed'; ``` {/* prettier-ignore-end */}
## Running Tests ```bash npm run test ``` ```bash npm run lintcheck ``` - Included in the **GitHub Actions** workflow. Checks for **MDX** components are missing. } /> ```bash npm run typecheck ``` - Included in the **GitHub Actions** workflow. ```bash npm run clear npm run build ``` - Included in the **GitHub Actions** workflow. ```bash npm run lint ``` - To prevent lint issues, it is recommended to execute this command before creating your commit. - Not included in the **GitHub Actions** workflow. --- ## File: website/docs/documentation/00-index.mdx --- slug: /documentation title: Introduction --- import { PageTitle } from '@site/src/components/PageTitle'; # Documentation [node-mysql]: https://github.com/mysqljs/mysql MySQL2 aims to be a drop in replacement for [Node MySQL][node-mysql]. :::note _If you see any API incompatibilities with [Node MySQL][node-mysql], please report via github issue._ ::: Not only **MySQL2** offers better performance over [Node MySQL][node-mysql], we also support these additional features: - [Prepared Statements](/docs/documentation/prepared-statements) - [Query Attributes](/docs/documentation/query-attributes) - [MariaDB Data Types](/docs/documentation/mariadb-data-types) - [Reset Connection](/docs/documentation/reset-connection) - [Promise Wrapper](/docs/documentation/promise-wrapper) - [Authentication Switch](/docs/documentation/authentication-switch) - [More Features](/docs/documentation/extras) - [MySQL Server](/docs/documentation/mysql-server) - Pooling - [SSL](/docs/documentation/ssl) - [Tracing Channels](/docs/documentation/tracing-channels) - MySQL Compression - Binary Log Protocol Client
## Examples Please check these [examples](/docs/examples) for **MySQL2**.
## Known incompatibilities with [Node MySQL][node-mysql] - `zeroFill` flag is ignored in type conversion. You need to check corresponding field's zeroFill flag and convert to string manually if this is of importance to you. - `DECIMAL` and `NEWDECIMAL` types always returned as `string` unless you pass this config option: ```js { decimalNumbers: true, } ``` :::note This option could lose precision on the number as Javascript Number is a Float! ::: - By default, the `JSON` type is always returned parsed into an object. However, you can modify this behavior by specifying the following configuration: ```js { jsonStrings: true, } ```
## Other Resources - [Wire protocol documentation](https://dev.mysql.com/doc/internals/en/client-server-protocol.html) - [Node MySQL][node-mysql] - Most popular node.js mysql client library - [node-mariasql](https://github.com/mscdex/node-mariasql/) - Bindings to libmariasql. One of the fastest clients - [node-libmysqlclient](https://github.com/Sannis/node-mysql-libmysqlclient) - Bindings to libmysqlclient - [go-mysql](https://github.com/siddontang/go-mysql) - MySQL Go client (prepared statements, binlog protocol, server)
## Benchmarks - https://gist.github.com/sidorares/ffe9ee9c423f763e3b6b - `npm run benchmarks` - [node-mysql-benchmarks](https://github.com/mscdex/node-mysql-benchmarks) - try to run example [benchmarks](https://github.com/sidorares/node-mysql2/tree/master/benchmarks) on your system --- ## File: website/docs/documentation/authentication-switch.mdx # Authentication Switch Request During the connection phase the server may ask the client to switch to a different auth method. If the `authPlugins` connection config option is set, it must be an object where each key is the name of a potential authentication plugin requested by the server, and the corresponding value must be a function that optionally receives the connection config options and returns another function, which in turn, optionally receives the switch request data. The plugin is loaded with a `({user,password,...})` signature, and each call has a `(pluginData)` signature. Each call should make the plugin return any additional authentication data (`Buffer`) that should be sent back to the server, either synchronously or asynchronously using a `Promise`, or should yield an error accordingly. Example: (imaginary `ssh-key-auth` plugin) pseudo code ```js const conn = mysql.createConnection({ user: 'test_user', password: 'test', database: 'test_database', authPlugins: { 'ssh-key-auth': function ({ password }) { return function (pluginData) { return getPrivate(key) .then((key) => { const response = encrypt(key, password, pluginData); // continue handshake by sending response data return response; }) .catch((err) => { // throw error to propagate error to connect/changeUser handlers }); }; }, }, }); ``` There is also a deprecated API where if a `authSwitchHandler` connection config option is set it must be a function that receives switch request data and responds via a callback. In this case, the first invocation always has a `({pluginName, pluginData})` signature, following calls - `({pluginData})`. The client replies with an opaque blob matching the requested plugin via `callback(null, data: Buffer)`. ```js const conn = mysql.createConnection({ user: 'test_user', password: 'test', database: 'test_database', authSwitchHandler: function ({ pluginName, pluginData }, cb) { if (pluginName === 'ssh-key-auth') { getPrivateKey((key) => { const response = encrypt(key, pluginData); // continue handshake by sending response data // respond with error to propagate error to connect/changeUser handlers cb(null, response); }); } else { const err = new Error( `Unknown AuthSwitchRequest plugin name ${pluginName}` ); err.fatal = true; cb(err); } }, }); ``` The initial handshake is always performed using `mysql_native_password` plugin. This will be possible to override in future versions. Note that if the `mysql_native_password` method is requested it will be handled internally according to [Authentication::Native41](https://dev.mysql.com/doc/internals/en/secure-password-authentication.html#packet-Authentication::Native41) and no `authPlugins` function or the `authSwitchHandler` will be invoked. These MAY be called multiple times if the plugin algorithm requires multiple roundtrips of data exchange between client and server. ## Cleartext Authentication The `mysql_clear_password` authentication plugin sends the password to the server in plaintext. This is required by some authentication schemes (e.g. PAM, LDAP, or AWS RDS IAM), but poses a security risk if the connection is not encrypted — a malicious server or man-in-the-middle could capture credentials. For this reason, `mysql_clear_password` is **disabled by default**. If the server requests it and the client has not explicitly opted in, the connection will fail with a `MYSQL_CLEAR_PASSWORD_NOT_ENABLED` error. ### Enabling cleartext authentication Set `enableCleartextPlugin: true` in your connection options: ```js const conn = mysql.createConnection({ host: 'example.com', user: 'my_user', password: 'my_password', ssl: { rejectUnauthorized: true }, enableCleartextPlugin: true, }); ``` :::caution Only enable this over a secure connection (TLS/SSL or a Unix domain socket). Never enable it over an unencrypted network connection. ::: Alternatively, providing a custom `mysql_clear_password` function via the `authPlugins` option implicitly enables cleartext authentication without the `enableCleartextPlugin` flag: ```js const conn = mysql.createConnection({ host: 'example.com', user: 'my_user', ssl: { rejectUnauthorized: true }, authPlugins: { mysql_clear_password: () => () => Buffer.from(password + '\0'), }, }); ``` ## Multi-factor authentication If the user requires multi-factor authentication in the server, the client will receive a `AuthNextFactor` request, which is similar in structure to the regular authentication switch request and contains the name and possible initial data for the additional authentication factor plugin (up to 3). Additional passwords can be provided using the connection config options - `password2` and `password3`. Again, for each authentication factor, multiple roundtrips of data exchange can be required by the plugin algorithm. ```js const conn = mysql.createConnection({ user: 'test_user', password: 'secret1', password2: 'secret2', password3: 'secret3', database: 'test_database', authPlugins: { // password1 === password 'auth-plugin1': function ({ password1 }) { return function (serverPluginData) { return clientPluginData(password1, serverPluginData); }; }, 'auth-plugin2': function ({ password2 }) { return function (serverPluginData) { return clientPluginData(password2, serverPluginData); }; }, 'auth-plugin3': function ({ password3 }) { return function (serverPluginData) { return clientPluginData(password3, serverPluginData); }; }, }, }); ``` --- ## File: website/docs/documentation/connect-on-cloudflare.mdx import { History } from '@site/src/components/History'; # Cloudflare Workers Support for *non-eval* parsers by using{' '} **disableEval** option. , ], }, ]} /> ## Prerequisites - A [Cloudflare](https://dash.cloudflare.com/sign-up) account. - [Wrangler](https://developers.cloudflare.com/workers/wrangler/) installed. - **MySQL2** version `3.13.0` or higher. To learn how to create a **Cloudflare Worker** project, please refer to [**Cloudflare Workers Documentation**](https://developers.cloudflare.com/workers/get-started/guide/). ## Setup ### Wrangler **MySQL2** relies on **Node.js** modules, such as `net`, `events`, `stream`, `tls`, etc. You can enable **Node.js** compatibility for **Cloudflare Workers** by using the `"nodejs_compat"` flag through the `wrangler.jsonc` file: ```json { "compatibility_flags": ["nodejs_compat"] } ``` :::important The minimum compatibility date is `2025-02-24`, for example: ```json { "compatibility_date": "2025-02-24", "compatibility_flags": ["nodejs_compat"] } ``` ::: ### MySQL2 connection **MySQL2** uses a code generation-based parser for performance by default, but since **Cloudflare Workers** don't offer support for evaluations, you can disable it by using the `disableEval` option: ```ts import { createConnection } from 'mysql2/promise'; export default { async fetch(): Promise { const conn = await createConnection({ host: 'localhost', user: 'root', database: 'test', // highlight-start disableEval: true, // highlight-end }); const [results] = await conn.query('SHOW TABLES;'); return new Response(JSON.stringify(results)); }, } satisfies ExportedHandler; ``` :::tip For required **SSL** connections, it is recommended to use the [**Cloudflare Hyperdrive**](https://developers.cloudflare.com/hyperdrive/) connection pool. ::: --- ## File: website/docs/documentation/extras.mdx # Extra Features ## Named placeholders You can use named placeholders for parameters by setting `namedPlaceholders` config value or query/execute time option. Named placeholders are converted to unnamed `?` on the client (mysql protocol does not support named parameters). If you reference parameter multiple times under the same name it is sent to server multiple times. Unnamed placeholders can still be used by providing the values as an array instead of an object. ```js connection.config.namedPlaceholders = true; connection.execute('select :x + :y as z', { x: 1, y: 2 }, (err, rows) => { // statement prepared as "select ? + ? as z" and executed with [1,2] values // rows returned: [ { z: 3 } ] }); connection.execute('select :x + :x as z', { x: 1 }, (err, rows) => { // select ? + ? as z, execute with [1, 1] }); connection.query('select :x + :x as z', { x: 1 }, (err, rows) => { // query select 1 + 1 as z }); // unnamed placeholders are still valid if the values are provided in an array connection.query('select ? + ? as z', [1, 1], (err, rows) => { // query select 1 + 1 as z }); ``` ## Receiving rows as array of columns instead of hash with column name as key: ```js const options = { sql: 'select A,B,C,D from foo', rowsAsArray: true }; connection.query(options, (err, results) => { /* results will be an array of arrays like this now: [[ 'field A value', 'field B value', 'field C value', 'field D value', ], ...] */ }); ``` ## Sending tabular data with 'load infile' and local stream: In addition to sending local fs files you can send any stream using `infileStreamFactory` query option. If set, it has to be a function that return a readable stream. It gets file path from query as a parameter. Note: starting from version 2.0 `infileStreamFactory` is required parameter for `LOAD DATA LOCAL INFILE`. Response from server indicates that it wants access to a local file and no `infileStreamFactory` option is provided the query ends with error. ```js // local file connection.query( 'LOAD DATA LOCAL INFILE "/tmp/data.csv" INTO TABLE test FIELDS TERMINATED BY ? (id, title)', onInserted1 ); // local stream const sql = 'LOAD DATA LOCAL INFILE "mystream" INTO TABLE test FIELDS TERMINATED BY ? (id, title)'; connection.query( { sql: sql, infileStreamFactory: function (path) { return getStream(); }, }, onInserted2 ); ``` The `infileStreamFactory` option may also be set at a connection-level: ```js const fs = require('fs'); const mysql = require('mysql2'); const connection = mysql.createConnection({ user: 'test', database: 'test', infileStreamFactory: (path) => { // Validate file path const validPaths = ['/tmp/data.csv']; if (!validPaths.includes(path)) { throw new Error( `invalid file path: ${path}: expected to be one of ${validPaths.join( ',' )}` ); } return fs.createReadStream(path); }, }); connection.query( 'LOAD DATA LOCAL INFILE "/tmp/data.csv" INTO TABLE test', onInserted ); ``` ## Connecting using custom stream: ```js const net = require('net'); const mysql = require('mysql2'); const shape = require('shaper'); const connection = mysql.createConnection({ user: 'test', database: 'test', stream: net.connect('/tmp/mysql.sock').pipe(shape(10)), // emulate 10 bytes/sec link }); connection.query('SELECT 1+1 as test1', console.log); ``` `stream` also can be a function. In that case function result has to be duplex stream, and it is used for connection transport. This is required if you connect pool using custom transport as new pooled connection needs new stream. Below is an updated SOCKS5 proxy example using the **modern API**, where `stream` returns a stream or a Promise resolving to a stream. ```js const mysql = require('mysql2'); const { SocksClient } = require('socks'); const pool = mysql.createPool({ database: 'test', user: 'foo', password: 'bar', // modern usage: stream returns a Duplex or a Promise that resolves to one stream: async () => { const { socket } = await SocksClient.createConnection({ proxy: { host: 'localhost', port: 1080, type: 5, }, destination: { host: 'remote.host', port: 3306, }, }); return socket; // must be a Duplex stream }, }); ``` **Note:** Older documentation used a callback-style API: ```js stream: function (cb) { cb(null, stream); } ``` This callback style no longer works on current versions of mysql2 and causes: ``` TypeError: cb is not a function ``` Use the modern return-style function shown above. ## In addition to password `createConnection()`, `createPool()` and `changeUser()` accept `passwordSha1` option. This is useful when implementing proxies as plaintext password might be not available. ## Graceful pool connection end ```js const pool = mysql.createPool({ gracefulEnd: true, }); ``` By default, calling end on a pool connection will result in a warning being emitted and the connection being released back to the pool. This is for compatibility with mysqljs/mysql. By providing `gracefulEnd: true` when creating the pool, calling end on a pooled connection will actually close the connection instead of releasing it back to the pool. --- ## File: website/docs/documentation/mysql-server.mdx # MySQL Server API ## Server - `createServer()` - creates server instance - `Server.listen` - listen port / unix socket (same arguments as [net.Server.listen](https://nodejs.org/api/net.html#net_server_listen_port_host_backlog_callback)) ### Events - **connect** - new incoming connection.
## Connection - `serverHandshake({ serverVersion, protocolVersion, connectionId, statusFlags, characterSet, capabilityFlags })` - send server handshake initialisation packet, wait handshake response and start listening for commands - `capabilityFlags` controls which protocol features are advertised. Client flags are masked against these capabilities when parsing the handshake response, so the server only honors mutually-supported features. - `writeOk({ affectedRows: num, insertId: num })` - send [OK packet](https://dev.mysql.com/doc/internals/en/overview.html#packet-OK_Packet) to client - `writeEof(warnings, statusFlags)` - send EOF packet - `writeTextResult(rows, fields)` - write query result to client. Rows and fields are in the same format as in `connection.query` callback. - `writeColumns(fields)` - write fields + EOF packets. - `writeTextRow(row)` - write array (not hash!) of values as result row - **TODO:** binary protocol ### Events Every command packet received by the server will be emitted as a **packet** event with the parameters: - **packet:** Packet - The packet itself - **knownCommand:** boolean - is this command known to the server - **\*commandCode:** number - the parsed command code (first byte) In addition special events are emitted for [commands](https://dev.mysql.com/doc/internals/en/text-protocol.html) received from the client. If no listener is present a fallback behavior will be invoked. - `quit()` - Default: close the connection - `init_db(schemaName: string)` - Default: return OK - `query(sql: string)` - Please attach a listener to this. Default: return HA_ERR_INTERNAL_ERROR - `field_list(table: string, fields: string)` - Default: return ER_WARN_DEPRECATED_SYNTAX - `ping()` - Default: return OK --- ## File: website/docs/documentation/prepared-statements.mdx --- tags: [Prepared Statements, execute] --- # Prepared Statements ## Automatic creation, cached and re-used by connection Similar to `connection.query()`. ```js connection.execute('select 1 + ? + ? as result', [5, 6], (err, rows) => { // rows: [ { result: 12 } ] // internally 'select 1 + ? + ? as result' is prepared first. On subsequent calls cached statement is re-used }); // close cached statement for 'select 1 + ? + ? as result'. noop if not in cache connection.unprepare('select 1 + ? + ? as result'); ``` Note that `connection.execute()` will cache the prepared statement for better performance, remove the cache with `connection.unprepare()` when you're done. ## Manual prepare / execute Manually prepared statements doesn't comes with LRU cache and SHOULD be closed using `statement.close()` instead of `connection.unprepare()`. ```js connection.prepare('select ? + ? as tests', (err, statement) => { // statement.parameters - array of column definitions, length === number of params, here 2 // statement.columns - array of result column definitions. Can be empty if result schema is dynamic / not known // statement.id // statement.query statement.execute([1, 2], (err, rows, columns) => { // -> [ { tests: 3 } ] }); // don't use connection.unprepare(), it won't work! // note that there is no callback here. There is no statement close ack at protocol level. statement.close(); }); ``` Note that you should not use statement after connection reset (`changeUser()`, [`reset()`](/docs/documentation/reset-connection) or disconnect). Statement scope is connection, you need to prepare statement for each new connection in order to use it. ## Configuration `maxPreparedStatements` : We keep the cached statements in a [lru-cache](https://github.com/isaacs/node-lru-cache). Default size is `16000` but you can use this option to override it. Any statements that are dropped from cache will be `closed`. ## Serialization of bind parameters The bind parameter values passed to `execute` are serialized JS -> MySQL as: - `null` -> `NULL` - `number` -> `DOUBLE` - `boolean` -> `TINY` (0 for false, 1 for true) - `object` -> depending on prototype: - `Date` -> `DATETIME` - `JSON` like object - `JSON` - `Buffer` -> `VAR_STRING` - Other -> `VAR_STRING` Passing in `undefined` or a `function` will result in an error. ## Prepared Statements Helper MySQL2 provides `execute` helper which will prepare and query the statement. You can also manually prepare / unprepare statement with `prepare` / `unprepare` methods. ```js connection.execute( 'select ?+1 as qqq, ? as rrr, ? as yyy', [1, null, 3], (err, rows, fields) => { console.log(err, rows, fields); connection.execute( 'select ?+1 as qqq, ? as rrr, ? as yyy', [3, null, 3], (err, rows, fields) => { console.log(err, rows, fields); connection.unprepare('select ?+1 as qqq, ? as rrr, ? as yyy'); connection.execute( 'select ?+1 as qqq, ? as rrr, ? as yyy', [3, null, 3], (err, rows, fields) => { console.log(err, rows, fields); } ); } ); } ); ``` ## Examples For Prepared Statements examples, please see [here](/docs/examples/queries/prepared-statements). ## Parameter types The MySQL type of each bind parameter is inferred from the JavaScript value. See [Typed Parameters](./typed-parameters.mdx) for how to state the type explicitly, and why it matters for large integers, unsigned values, binary data and index lookups. --- ## File: website/docs/documentation/promise-wrapper.mdx # Promise Wrappers In addition to errback interface there is thin wrapper to expose Promise-based api ## Basic Promise ```js const mysql = require('mysql2/promise'); // or require('mysql2').createConnectionPromise mysql .createConnection({/* same parameters as for non-promise createConnection */}) .then((conn) => conn.query('select foo from bar')) .then(([rows, fields]) => console.log(rows[0].foo)); ``` ```js const pool = require('mysql2/promise').createPool({}); // or require('mysql2').createPoolPromise({}) or require('mysql2').createPool({}).promise() pool .getConnection() .then((conn) => { const res = conn.query('select foo from bar'); conn.release(); return res; }) .then((result) => { console.log(result[0][0].foo); }) .catch((err) => { console.log(err); // any of connection time or query time errors from above }); ``` ## ES7 Async Await ```js async function example1() { const mysql = require('mysql2/promise'); const conn = await mysql.createConnection({ database: test }); const [rows, fields] = await conn.execute('select ?+? as sum', [2, 2]); await conn.end(); } async function example2() { const mysql = require('mysql2/promise'); const pool = mysql.createPool({ database: test }); // execute in parallel, next console.log in 3 seconds await Promise.all([ pool.query('select sleep(2)'), pool.query('select sleep(3)'), ]); console.log('3 seconds after'); await pool.end(); } ``` ## Explicit Resource Management (`await using`) With [Explicit Resource Management](https://github.com/tc39/proposal-explicit-resource-management), connections and pools are automatically cleaned up when they go out of scope — no need to manually call `.end()` or `.release()`. ```ts import mysql from 'mysql2/promise'; { await using conn = await mysql.createConnection({ database: test }); const [rows, fields] = await conn.execute('select ?+? as sum', [2, 2]); // conn.end() is called automatically when leaving the scope } ``` ```ts import mysql from 'mysql2/promise'; { await using pool = mysql.createPool({ database: test }); // execute in parallel, next console.log in 3 seconds await Promise.all([ pool.query('select sleep(2)'), pool.query('select sleep(3)'), ]); console.log('3 seconds after'); // pool.end() is called automatically when leaving the scope } ``` ```ts import mysql from 'mysql2/promise'; const pool = mysql.createPool({}); { await using conn = await pool.getConnection(); const res = await conn.query('select foo from bar'); console.log(res[0][0].foo); // conn.release() is called automatically when leaving the scope } ``` :::tip `await using` and `using` leverage [Explicit Resource Management](https://github.com/tc39/proposal-explicit-resource-management) to automatically call `.end()` or `.release()` when the variable goes out of scope, so you never forget to clean up connections. ::: ## With [CO](https://github.com/tj/co) ```js const mysql = require('mysql2'); const co = require('co'); co(function* () { const c = yield mysql.createConnectionPromise({ user: 'root', namedPlaceholders: true, }); const rows = yield c.query('show databases'); console.log(rows); console.log(yield c.execute('select 1+:toAdd as qqq', { toAdd: 10 })); yield c.end(); }); ``` Examples in [/examples/promise-co-await](/docs/examples/promise-wrapper/co-await)