Node js mysql windows

Summary: in this tutorial, you will learn how to connect to the MySQL Server from a Node.js application.

Note that this tutorial targets the MySQL 8.0 and Node.js v20.6.0 or later. Node.js v20.6.0 and newer offers built-in support for .env configuration files.

Installing Node.js driver for MySQL

First, open the Command Prompt on Windows or Terminal on Unix-like systems.

Second, create a directory for storing the Node.js app and use the npm init command to create the package.json file:

npm init --yes

Third, install the MySQL package using the following npm command:

npm install mysql

Creating a sample database

First, connect to the MySQL server:

mysql -h localhost -u root -p

Second, create a new database called todoapp:

CREATE DATABASE todoapp;Code language: SQL (Structured Query Language) (sql)

Creating configuration file .env

First, create a new file called .env in the project directory.

Second, add the MySQL connection’s parameters to the .env file:

DB_HOST=localhost
DB_PORT=3306
DB_USER=root
DB_PASSWORD=
DB_NAME=todoapp

You should replace the DB_HOST, DB_PORT, DB_USER, DB_NAME, and DB_PASSWORD with the actual ones.

First, create a connect.js file in the project’s directory.

Next, import the mysql module in the connect.js file:

let mysql = require('mysql');Code language: JavaScript (javascript)

Then, create a connection to the MySQL server by calling the createConnection() function:

let connection = mysql.createConnection({
  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,
});Code language: JavaScript (javascript)

Note that we access the MySQL parameters from the .env file via the process.env. This feature has been available since Node.js v20.6.0.

After that, call the connect() method on the connection object to connect to the MySQL server:

connection.connect((err) => {
  if (err) return console.error(err.message);

  console.log('Connected to the MySQL server.');
});Code language: JavaScript (javascript)

The connect() method accepts a callback function that has the err argument that provides detailed information if any error occurs.

Here’s the complete connect.js program:

let mysql = require('mysql');

let connection = mysql.createConnection({
  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,
});

connection.connect((err) => {
  if (err) return console.error(err.message);

  console.log('Connected to the MySQL server.');
});
Code language: JavaScript (javascript)

Finally, run the connect.js program that uses MySQL’s parameters from the .env file:

 node --env-file .env connect.jsCode language: JavaScript (javascript)

Output:

Connected to the MySQL server.

To make it more convenient, you can change the start property of the package.json file to the following:

...
  "scripts": {
    "start": " node --env-file .env connect.js"
  },
...Code language: JavaScript (javascript)

Then, use the npm start command to run the connect.js with information from the .env file:

npm start

The output indicates that we have successfully connected to the MySQL server from the Node.js program.

Troubleshooting

If you connect to MySQL 8.0 or later, you are likely getting the following error message:

error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client
Code language: HTTP (http)

In MySQL 8.0, the default authentication plugin is caching_sha2_password, unlike MySQL 5.7, which uses the mysql_native_password plugin, supported by most clients.

Therefore, if you encounter compatibility issues, you must explicitly enable mysql_native_password for a given user using the following command:

ALTER USER 'user' 
IDENTIFIED WITH mysql_native_password BY 'password';Code language: JavaScript (javascript)

Replace the user and password with the ones that you use to connect to MySQL.

Closing database connection

To close a database connection gracefully, you call the end() method on the connection object.

The end() method ensures that all remaining queries will be executed before the database connection is closed.

connection.end((err) => {
  if (err) return console.error(err.message);

  console.log('Close the database connection.');
});
Code language: JavaScript (javascript)

To force the connection to close immediately, you can use the destroy() method. The destroy() method guarantees that no more callbacks or events will be triggered for the connection.

connection.destroy();Code language: JavaScript (javascript)

Note that the destroy() method does not take any callback argument like the end() method.

The following shows the complete connect.js program:

let mysql = require('mysql');

let connection = mysql.createConnection({
  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,
});

connection.connect((err) => {
  if (err) return console.error(err.message);

  console.log('Connected to the MySQL server.');
});

connection.end((err) => {
  if (err) return console.error(err.message);

  console.log('Close the database connection.');
});
Code language: JavaScript (javascript)

Summary

• Connect to a MySQL database from a Node.js application.

Contents:

• Install MySQL node.js driver
• Create and terminate connections, connection options
• Error handling
• SSL options
• Pooling connections, options and events
• Escaping query values, identifiers
• Preparing Queries
• Stored procedures
• Joins
• Transactions
• Timeouts

Pool events

connection :

The pool will emit a connection event when a new connection is made within the pool. If you need to set session variables on the connection before it gets used, you can listen to the connection event.

pool.on('connection', function (connection) {
  connection.query('SET SESSION auto_increment_increment=1')
});

enqueue :

The pool will emit an enqueue event when a callback has been queued to wait for an available connection.

pool.on('enqueue', function () {
  console.log('Waiting for available connection slot');
});

Escaping query values

SQL injection is a technique (like other web attack mechanisms) to attack data driven applications. This attack can bypass a firewall and can affect a fully patched system. The attacker takes the advantage of poorly filtered or not correctly escaped characters embedded in SQL statements into parsing variable data from user input. The attacker inject arbitrary data, most often a database query, into a string that’s eventually executed by the database through a web application (e.g. a login form).

To avoid SQL Injection attacks, you should always escape any user provided data before using it inside a SQL query. You can do so using the connection.escape() or pool.escape() methods :

var userId = 'some user provided value';
var sql    = 'SELECT * FROM users WHERE id = ' + connection.escape(userId);
connection.query(sql, function(err, results) {
  // ...
});

Alternatively, you can use ? characters as placeholders for values you would like to have escaped like this:

connection.query('SELECT * FROM users WHERE id = ?', [userId], function(err, results) {
  // ...
});

Different value types are escaped differently, here is how:

• Numbers are left untouched
• Booleans are converted to true / false strings
• Date objects are converted to ‘YYYY-mm-dd HH:ii:ss’ strings
• Buffers are converted to hex strings, e.g. X’0fa5′
• Strings are safely escaped
• Arrays are turned into list, e.g. [‘a’, ‘b’] turns into ‘a’, ‘b’
• Nested arrays are turned into grouped lists (for bulk inserts), e.g. [[‘a’, ‘b’], [‘c’, ‘d’]] turns into (‘a’, ‘b’), (‘c’, ‘d’)
• Objects are turned into key = ‘val’ pairs. Nested objects are cast to strings.
• undefined / null are converted to NULL
• NaN / Infinity are left as-is. MySQL does not support these, and trying to insert them as values will trigger MySQL errors until they implement support.

Here is an example on INSERT INTO statement :

var post  = {id: 1, title: 'Hello MySQL'};
var query = connection.query('INSERT INTO posts SET ?', post, function(err, result) {
  // Neat!
});
console.log(query.sql); // INSERT INTO posts SET `id` = 1, `title` = 'Hello MySQL'

You can also use the escaping function directly, see the following example :

var query = "SELECT * FROM posts WHERE title=" + mysql.escape("Hello MySQL");

console.log(query); // SELECT * FROM posts WHERE title='Hello MySQL'

Escaping query identifiers

As SQL identifier (database / table / column name) is provided by a user, you should escape it with mysql.escapeId(identifier), connection.escapeId(identifier) or pool.escapeId(identifier) like this :

var sorter = 'date';
var sql    = 'SELECT * FROM posts ORDER BY ' + connection.escapeId(sorter);
connection.query(sql, function(err, results) {
  // ...
});

It also supports adding qualified identifiers. It will escape both parts.

var sorter = 'date';
var sql    = 'SELECT * FROM posts ORDER BY ' + connection.escapeId('posts.' + sorter);
connection.query(sql, function(err, results) {
  // ...
});

Alternatively, you can use ?? characters as placeholders for identifiers you would like to have escaped like this:

var userId = 1;
var columns = ['username', 'email'];
var query = connection.query('SELECT ?? FROM ?? WHERE id = ?', [columns, 'users', userId], function(err, results) {
  // ...
});

console.log(query.sql); // SELECT `username`, `email` FROM `users` WHERE id = 1

Note : The last character sequence is experimental and syntax might change.

When you pass an Object to .escape() or .query(), .escapeId() is used to avoid SQL injection in object keys.

Preparing Queries

MySQL 5.6 provides support for server-side prepared statements. This support takes advantage of the efficient client/server binary protocol available since MySQL 4.1. You can use mysql.format to prepare a query with multiple insertion points, utilizing the proper escaping for ids and values. A simple example of this follows:

var sql = "SELECT * FROM ?? WHERE ?? = ?";
var inserts = ['users', 'id', userId];
sql = mysql.format(sql, inserts);

Following this you then have a valid, escaped query that you can then send to the database safely. This is useful if you are looking to prepare the query before actually sending it to the database. As mysql.format is exposed from SqlString.format you also have the option (but are not required) to pass in stringifyObject and timezone, allowing you provide a custom means of turning objects into strings, as well as a location-specific/timezone-aware Date.

Custom format :

If you prefer to have another type of query escape format, there’s a connection configuration option you can use to define a custom format function. You can access the connection object if you want to use the built-in .escape() or any other connection function.

Here’s an example of how to implement another format:

connection.config.queryFormat = function (query, values) {
  if (!values) return query;
  return query.replace(/\:(\w+)/g, function (txt, key) {
    if (values.hasOwnProperty(key)) {
      return this.escape(values[key]);
    }
    return txt;
  }.bind(this));
};

connection.query("UPDATE posts SET title = :title", { title: "Hello MySQL" });

Getting the id of an inserted row :

If you are inserting a row into a table with an auto increment primary key, you can retrieve the insert id like this:

connection.query('INSERT INTO posts SET ?', {title: 'test'}, function(err, result) {
  if (err) throw err;

  console.log(result.insertId);
});

When dealing with big numbers (above JavaScript Number precision limit), you should consider enabling support BigNumbers option to be able to read the insert id as a string, otherwise it will throw.

This option is also required when fetching big numbers from the database, otherwise you will get values rounded to hundreds or thousands due to the precision limit.

Getting the number of affected rows.

You can get the number of affected rows from an insert, update or delete statement.

«changedRows» differs from «affectedRows» in that it does not count updated rows whose values were not changed.

connection.query('DELETE FROM posts WHERE title = "wrong"', function (err, result) {
  if (err) throw err;

  console.log('deleted ' + result.affectedRows + ' rows');
})

Getting the connection ID

You can get the MySQL connection ID («thread ID») of a given connection using the threadId property.

connection.connect(function(err) {
  if (err) throw err;
  console.log('connected as id ' + connection.threadId);
});

Executing queries in parallel :

The MySQL protocol is sequential, this means that you need multiple connections to execute queries in parallel. You can use a Pool to manage connections, one simple approach is to create one connection per incoming http request.

Streaming query rows

Sometimes you may want to select large quantities of rows and process each of them as they are received. This can be done like this:

var query = connection.query('SELECT * FROM posts');
query
  .on('error', function(err) {
    // Handle error, an 'end' event will be emitted after this as well
  })
  .on('fields', function(fields) {
    // the field packets for the rows to follow
  })
  .on('result', function(row) {
    // Pausing the connnection is useful if your processing involves I/O
    connection.pause();

    processRow(row, function() {
      connection.resume();
    });
  })
  .on('end', function() {
    // all rows have been received
  });

Please note a few things about the example above:

• Usually you will want to receive a certain amount of rows before starting to throttle the connection using pause(). This number will depend on the amount and size of your rows.
• pause() / resume() operate on the underlying socket and parser. You are guaranteed that no more ‘result’ events will fire after calling pause().
• You MUST NOT provide a callback to the query() method when streaming rows.
• The ‘result’ event will fire for both rows as well as OK packets confirming the success of a INSERT/UPDATE query.

Additionally you may be interested to know that it is currently not possible to stream individual row columns, they will always be buffered up entirely. If you have a good use case for streaming large fields to and from MySQL, I’d love to get your thoughts and contributions on this.

Multiple statement queries :

Support for multiple statements is disabled for security reasons (it allows for SQL injection attacks if values are not properly escaped). To use this feature you have to enable it for your connection:

var connection = mysql.createConnection({multipleStatements: true});

Once enabled, you can execute multiple statement queries like any other query:

connection.query('SELECT 1; SELECT 2', function(err, results) {
  if (err) throw err;

  // `results` is an array with one element for every statement in the query:
  console.log(results[0]); // [{1: 1}]
  console.log(results[1]); // [{2: 2}]
});

Additionally you can also stream the results of multiple statement queries:

var query = connection.query('SELECT 1; SELECT 2');

query
  .on('fields', function(fields, index) {
    // the fields for the result rows that follow
  })
  .on('result', function(row, index) {
    // index refers to the statement this result belongs to (starts at 0)
  });

If one of the statements in your query causes an error, the resulting Error object contains a err.index property which tells you which statement caused it. MySQL will also stop executing any remaining statements when an error occurs.

Please note that the interface for streaming multiple statement queries is experimental and I am looking forward to feedback on it.

Stored procedures

You can call stored procedures from your queries as with any other mysql driver. Here is the original procedure code :

DELIMITER $$ 
CREATE PROCEDURE my_procedure_User_Variables() 
BEGIN 
SET @x = 15; 
SET @y = 10; 
SELECT @x, @y, @x-@y; 
END$$ 

Output when it was executed in MySQL :

mysql> CALL my_procedure_User_Variables() ; 
+------+------+-------+ 
| @x   | @y   | @x-@y | 
+------+------+-------+ 
| 15   | 10   | 5     | 
+------+------+-------+ 
1 row in set (0.04 sec) Query OK, 0 rows affected (0.05 sec) 

Now call the stored procedure from node.js :

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : 'datasoft123',
  database : 'hr'
});

connection.connect();

connection.query("CALL my_procedure_User_Variables();", function(err, rows)
{
  if (err) throw err;

  console.log(rows);
});

connection.end();

Output :

[ [ { '@x': 15, '@y': 10, '@x-@y': 5 } ],
  { fieldCount: 0,
    affectedRows: 0,
    insertId: 0,
    serverStatus: 2,
    warningCount: 0,
    message: '',
    protocol41: true,
    changedRows: 0 } ]

Joins

You can call JOINS from your queries as with any other mysql driver. Here is an example :

Write a query to display the department ID, department name and manager first name. See ‘hr’ database.

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : 'datasoft123',
  database : 'hr'
});

connection.connect();

connection.query("SELECT d.department_id, d.department_name, e.manager_id,
 e.first_name FROM departments d INNER JOIN employees e ON 
 (d.manager_id = e.employee_id);", function(err, rows)
{
  if (err) throw err;

  console.log(rows);
});

connection.end();

Output :

[ { department_id: 10,
    department_name: 'Administration',
    manager_id: 101,
    first_name: 'Jennifer' },
  { department_id: 20,
    department_name: 'Marketing',
    manager_id: 100,
    first_name: 'Michael' },
  { department_id: 30,
    department_name: 'Purchasing',
    manager_id: 100,
    first_name: 'Den' },
  { department_id: 40,
    department_name: 'Human Resources',
    manager_id: 101,
    first_name: 'Susan' },
  ------------------------------------
  ------------------------------------

Transactions

MySQL (here we maintain version 5.6) supports local transactions (within a given client session) through statements such as SET autocommit, START TRANSACTION, COMMIT, and ROLLBACK. Here is the syntax of START TRANSACTION, COMMIT, and ROLLBACK :

START TRANSACTION 
   transaction_characteristic [, transaction_characteristic] ...] 
transaction_characteristic: 
WITH CONSISTENT SNAPSHOT 
| READ WRITE 
| READ ONLY 

BEGIN [WORK] 
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] 
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] 
SET autocommit = {0 | 1}

In node.js simple transaction support is available at the connection level :

connection.beginTransaction(function(err) {
  if (err) { throw err; }
  connection.query('INSERT INTO posts SET title=?', title, function(err, result) {
    if (err) { 
      connection.rollback(function() {
        throw err;
      });
    }

    var log = 'Post ' + result.insertId + ' added';

    connection.query('INSERT INTO log SET data=?', log, function(err, result) {
      if (err) { 
        connection.rollback(function() {
          throw err;
        });
      }  
      connection.commit(function(err) {
        if (err) { 
          connection.rollback(function() {
            throw err;
          });
        }
        console.log('success!');
      });
    });
  });
});

Please note that beginTransaction(), commit() and rollback() are simply convenience functions that execute the START TRANSACTION, COMMIT, and ROLLBACK commands respectively. It is important to understand that many commands in MySQL can cause an implicit commit, as described in the MySQL documentation

Timeouts

Every operation takes an optional inactivity timeout option. This allows you to specify appropriate timeouts for operations. It is important to note that these timeouts are not part of the MySQL protocol, and rather timeout operations through the client. This means that when a timeout is reached, the connection it occurred on will be destroyed and no further operations can be performed.

// Kill query after 60s
connection.query({sql: 'SELECT COUNT(*) AS count FROM big_table', timeout: 60000}, function (err, rows) {
  if (err && err.code === 'PROTOCOL_SEQUENCE_TIMEOUT') {
    throw new Error('too long to count table rows!');
  }

  if (err) {
    throw err;
  }

  console.log(rows[0].count + ' rows');
});

Structure of ‘hr’ database :

Картинка с сайта: w3resource.com

Previous:
OS Module
Next:
Node-SQLite3