Have ideas to improve npm?Join in the discussion! »

    mysql2
    TypeScript icon, indicating that this package has built-in type declarations

    2.2.5 • Public • Published

    Node MySQL 2

    Greenkeeper badge NPM Version NPM Downloads Node.js Version Linux Build Windows Build License

    MySQL client for Node.js with focus on performance. Supports prepared statements, non-utf8 encodings, binary log protocol, compression, ssl much more

    Table of contents

    History and Why MySQL2

    MySQL2 project is a continuation of MySQL-Native. Protocol parser code was rewritten from scratch and api changed to match popular mysqljs/mysql. MySQL2 team is working together with mysqljs/mysql team to factor out shared code and move it under mysqljs organisation.

    MySQL2 is mostly API compatible with mysqljs and supports majority of features. MySQL2 also offers these additional features

    Installation

    MySQL2 is free from native bindings and can be installed on Linux, Mac OS or Windows without any issues.

    npm install --save mysql2

    First Query

    // get the client
    const mysql = require('mysql2');
     
    // create the connection to database
    const connection = mysql.createConnection({
      host: 'localhost',
      user: 'root',
      database: 'test'
    });
     
    // simple query
    connection.query(
      'SELECT * FROM `table` WHERE `name` = "Page" AND `age` > 45',
      function(err, results, fields) {
        console.log(results); // results contains rows returned by server
        console.log(fields); // fields contains extra meta data about results, if available
      }
    );
     
    // with placeholder
    connection.query(
      'SELECT * FROM `table` WHERE `name` = ? AND `age` > ?',
      ['Page', 45],
      function(err, results) {
        console.log(results);
      }
    );

    Using Prepared Statements

    With MySQL2 you also get the prepared statements. With prepared statements MySQL doesn't have to prepare plan for same query everytime, this results in better performance. If you don't know why they are important, please check these discussions

    MySQL provides execute helper which will prepare and query the statement. You can also manually prepare / unprepare statement with prepare / unprepare methods.

    // get the client
    const mysql = require('mysql2');
     
    // create the connection to database
    const connection = mysql.createConnection({
      host: 'localhost',
      user: 'root',
      database: 'test'
    });
     
    // execute will internally call prepare and query
    connection.execute(
      'SELECT * FROM `table` WHERE `name` = ? AND `age` > ?',
      ['Rick C-137', 53],
      function(err, results, fields) {
        console.log(results); // results contains rows returned by server
        console.log(fields); // fields contains extra meta data about results, if available
     
        // If you execute same statement again, it will be picked from a LRU cache
        // which will save query preparation time and give better performance
      }
    );

    Using connection pools

    Connection pools help reduce the time spent connecting to the MySQL server by reusing a previous connection, leaving them open instead of closing when you are done with them.

    This improves the latency of queries as you avoid all of the overhead that comes with establishing a new connection.

    // get the client
    const mysql = require('mysql2');
     
    // Create the connection pool. The pool-specific settings are the defaults
    const pool = mysql.createPool({
      host: 'localhost',
      user: 'root',
      database: 'test',
      waitForConnections: true,
      connectionLimit: 10,
      queueLimit: 0
    });

    The pool does not create all connections upfront but creates them on demand until the connection limit is reached.

    You can use the pool in the same way as connections (using pool.query() and pool.execute()):

    // For pool initialization, see above
    pool.query("SELECT field FROM atable", function(err, rows, fields) {
       // Connection is automatically released when query resolves
    })

    Alternatively, there is also the possibility of manually acquiring a connection from the pool and returning it later:

    // For pool initialization, see above
    pool.getConnection(function(err, conn) {
       // Do something with the connection
       conn.query(/* ... */);
       // Don't forget to release the connection when finished!
       pool.releaseConnection(conn);
    })

    Using Promise Wrapper

    MySQL2 also support Promise API. Which works very well with ES7 async await.

    async function main() {
      // get the client
      const mysql = require('mysql2/promise');
      // create the connection
      const connection = await mysql.createConnection({host:'localhost', user: 'root', database: 'test'});
      // query database
      const [rows, fields] = await connection.execute('SELECT * FROM `table` WHERE `name` = ? AND `age` > ?', ['Morty', 14]);
    }

    MySQL2 use default Promise object available in scope. But you can choose which Promise implementation you want to use

    // get the client
    const mysql = require('mysql2/promise');
     
    // get the promise implementation, we will use bluebird
    const bluebird = require('bluebird');
     
    // create the connection, specify bluebird as Promise
    const connection = await mysql.createConnection({host:'localhost', user: 'root', database: 'test', Promise: bluebird});
     
    // query database
    const [rows, fields] = await connection.execute('SELECT * FROM `table` WHERE `name` = ? AND `age` > ?', ['Morty', 14]);

    MySQL2 also exposes a .promise() function on Pools, so you can create a promise/non-promise connections from the same pool

    async function main() {
      // get the client
      const mysql = require('mysql2');
      // create the pool
      const pool = mysql.createPool({host:'localhost', user: 'root', database: 'test'});
      // now get a Promise wrapped instance of that pool
      const promisePool = pool.promise();
      // query database using promises
      const [rows,fields] = await promisePool.query("SELECT 1");

    MySQL2 exposes a .promise() function on Connections, to "upgrade" an existing non-promise connection to use promise

    // get the client
    const mysql = require('mysql2');
    // create the connection
    const con = mysql.createConnection(
      {host:'localhost', user: 'root', database: 'test'}
    );
    con.promise().query("SELECT 1")
      .then( ([rows,fields]) => {
        console.log(rows);
      })
      .catch(console.log)
      .then( () => con.end());

    API and Configuration

    MySQL2 is mostly API compatible with Node MySQL. You should check their API documentation to see all available API options.

    If you find any incompatibility with Node MySQL, Please report via Issue tracker. We will fix reported incompatibility on priority basis.

    Documentation

    You can find more detailed documentation here. You should also check various code examples to understand advanced concepts.

    Acknowledgements

    • Internal protocol is written by @sidorares MySQL-Native
    • Constants, SQL parameters interpolation, Pooling, ConnectionConfig class taken from node-mysql
    • SSL upgrade code based on @TooTallNate code
    • Secure connection / compressed connection api flags compatible to MariaSQL client.
    • Contributors

    Contributing

    Want to improve something in node-mysql2. Please check Contributing.md for detailed instruction on how to get started.

    Install

    npm i mysql2

    DownloadsWeekly Downloads

    621,492

    Version

    2.2.5

    License

    MIT

    Unpacked Size

    461 kB

    Total Files

    91

    Last publish

    Collaborators

    • avatar
    • avatar
    • avatar