npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

@coorpacademy/mysql2

v3.0.0-rc.2

Published

fast mysql driver. Implements core protocol, prepared statements, ssl and compression in native JS

Downloads

35

Readme

Node MySQL 2

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

English | 简体中文

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());

Array results

If you have two columns with the same name, you might want to get results as an array rather than an object to prevent them from clashing. This is a deviation from the Node MySQL library.

For example: select 1 as foo, 2 as foo.

You can enable this setting at either the connection level (applies to all queries), or at the query level (applies only to that specific query).

Connection Option

const con = mysql.createConnection(
  { host: 'localhost', database: 'test', user: 'root', rowsAsArray: true }
);

Query Option

con.query({ sql: 'select 1 as foo, 2 as foo', rowsAsArray: true }, function(err, results, fields) {
  console.log(results) // will be an array of arrays rather than an array of objects
  console.log(fields) // these are unchanged
});

API and Configuration

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

One known incompatibility is that DECIMAL values are returned as strings whereas in Node MySQL they are returned as numbers. This includes the result of SUM() and AVG() functions when applied to INTEGER arguments. This is done deliberately to avoid loss of precision - see https://github.com/sidorares/node-mysql2/issues/935.

If you find any other 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.