[Node.js] Explanation on how to handle MySQL in Node.js

When implementing backend functions such as API servers in Node.js, you may want to use MySQL. In this article, we will explain how to connect to MySQL from Node.js using the mysql package in Node.js and interact with the database. In the second half of the article, we will implement a simple function using Express.js to pass MySQL data in response to a request from a client in a hands-on way.

How to handle MySQL in Node.js

To connect to MySQL in a Node.js environment, a driver must be installed and used with npm. npm provides a driver called mysql, so use this driver. This driver allows Node.js to programmatically perform CRUD processing of MySQL data. The database to be used must be prepared in advance with MySQL.

The specific usage of the driver will be explained in the Matters section.

Basics of handling MySQL with mysql (npm package)

First of all, the all lower-case mysql refers to mysql, the MySQL driver in the Node.js package, and the mixed-case MySQL refers to the original MySQL that manages the database.

First, let's install mysql with npm so that we can use it. Let's start with a quick installation.

タイトル:terminal

$ npm init

Prepare npm and create package.json.

タイトル:terminal

$ npm install mysql

Install mysql using npm.

Once installed, prepare a js file to operate MySQL and write the following there.

タイトル:main.js

const mysql = require('mysql');
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'sample_db'
});
 
connection.connect();
 
connection.query('SELECT * FROM database', function (error, results, fields) {
  if (error) throw error;
  console.log(results[0]);
});
 
connection.end();

First, import the mysql module.

タイトル:main.js

const mysql = require('mysql');

Then create a connection by specifying the options for connecting to the MySQL database to be used in the createConection method. The database must be created in MySQL beforehand.

タイトル:main.js

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'sample_db'
});

Enter the configuration values for the database you have created in host, user, password, and database. If you want to know more about the configuration values, please refer to the mysql documentation.

Once the connection is made, you can pull data from the database by hitting queries.

タイトル:main.js

connection.query('SELECT * FROM database', function (error, results, fields) {
  if (error) throw error;
  console.log(results[0]);
});

The query method of the connection class is used to execute a query. query method accepts a string query as its first argument. In addition, a callback function can be specified as the second argument to describe processing using the results of query processing. In the callback function, error, results, and fields can be used. error contains information about the error when the query was executed, results contains information about the results after the query was executed, and fields contains information about the result fields of the query that was hit by fillds. In the example, if there is an error in the callback function, this is thrown, and if there is no error, the first row of the result is output to the console.

Finally, the end method disconnects the connection to MySQL.

タイトル:main.js

connection.end();

If you do not write this, the connections will accumulate in MySQL, which will likely lead to errors such as out-of-memory errors.

In addition to the basic mysql usage, mysql allows variables to be used in queries and pooled connections for faster data exchange, which will be discussed in another article.

Real-world examples using Express.js (hands-on)

As a practical example, let's use Express.js to create a function that responds to MySQL data in response to a request from the client side. The image is as follows.

We will not explain the basic usage of Express.js, but Express is a simple framework for building a web server in Node.js. In this case, we will only use the part that starts up the server and handles requests and responses.

The implementation steps can be roughly divided as follows

Installing Express and mysql
Database and table creation in MySQL
Implement data acquisition part in mysql
Implement screen and request/response processing with Express

Let's get started! The code we used this time is also available on code-database's github repository, so if you want to check it out at hand, please refer to it.

Express and mysql installation

First, create a working directory (express_mysql_sample) and install the necessary packages with npm.

タイトル:terminal

$ mkdir express_mysql_sample
$ cd express_mysql_sample
$ npm init
$ npm install express mysql

Database and table creation in MySQL

Then create the database and tables to be used in MySQL.

First, log in to MySQL.

タイトル:teminal

$ mysql -u root -p

Create it by logging in as the root user.

Next, create the database.

タイトル:MySQL Monitor

mysql> create database express_mysql_sample;

A database named express_mysql_sample is created.

Further tables are created and filled with appropriate data.

タイトル:MySQLモニター

mysql> use express_mysql_sample;
Database changed
mysql> create table messages(id int not null primary key auto_increment, message text not null);
Query OK, 0 rows affected (0.03 sec)

mysql> show columns from messages;
+---------+------+------+-----+---------+----------------+
| Field   | Type | Null | Key | Default | Extra          |
+---------+------+------+-----+---------+----------------+
| id      | int  | NO   | PRI | NULL    | auto_increment |
| message | text | NO   |     | NULL    |                |
+---------+------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> insert into messages(message) values('こんにちは、ようこそMySQLへ');
Query OK, 1 row affected (0.00 sec)

mysql> select * from messages;
+----+----------------------------------------+
| id | message                                |
+----+----------------------------------------+
|  1 | こんにちは、ようこそMySQLへ            　  |
+----+----------------------------------------+
1 row in set (0.00 sec)

We have created a table called messages and put one piece of data in it. We will call this data later.

Implement the data acquisition part in mysql

Next, create a server.js that describes the back-end processing, and write the process of accessing the database with mysql and retrieving data in server.js.

タイトル:server.js

const mysql = require('mysql');

const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '***********', //この部分は秘密です
    database: 'express_mysql_sample'
});
connection.connect();
connection.query('SELECT * FROM messages', function (error, results, fields) {
    if (error) throw error;
    console.log(results[0]);
    connection.end();
});

Let's run it in this state.

タイトル:ターミナル

$ node server.js

If a result like this is output, there is no problem.

Screen and request/response processing implementation in Express

Finally, start the server with Express and write the process to respond to the request with the output results described earlier.

タイトル:server.js

const express = require('express'); //追記
const app = express(); //追記
const mysql = require('mysql');

app.get('/', (req, res) => res.sendFile(__dirname + '/index.html')); //追記

//一部追記
app.get('/api/hello', (req, res) => {
    const connection = mysql.createConnection({
        host: 'localhost',
        user: 'root',
        password: '*********', //この部分は秘密です
        database: 'express_mysql_sample'
    });
    connection.connect();
    connection.query('SELECT * FROM messages', function (error, results, fields) {
        if (error) throw error;
        console.log(results[0]);
        res.send(results[0].message); //追記
        connection.end();
    });
});

//追記
app.listen(3000, function () {
    console.log('Example app listening on port 3000!'); 
});

First, import express and create an application (app).

タイトル:server.js

const express = require('express');
const app = express();

Then, let's serve index.html in the root directory.

タイトル:server.js

app.get('/', (req, res) => res.sendFile(__dirname + '/index.html'));

The __dirname is the directory where the server.js file is located. index.html should look something like the following

タイトル:index.html

<!DOCTYPE html>
<html lang='en'>
<head>
    <meta charset='UTF-8'>
    <meta name='viewport' content='width=device-width, initial-scale=1.0'>
    <title>express_mysql_sample</title>
</head>
<body>
    <button id="submit_btn">こんにちは</button>
</body>
</html>

<script>
    document.getElementById('submit_btn').addEventListener('click', function(){
        xhr = new XMLHttpRequest();
        xhr.open('GET', '/api/hello');
        xhr.onreadystatechange = function () {
            if (xhr.readyState === 4) {
                if (xhr.status === 200) {
                    alert(xhr.response);
                } else {
                    alert('データが正常に送れませんでした');
                }
            }
        }
        xhr.send();
    });
</script>

Install the button

タイトル:index.html

<button id="submit_btn">こんにちは</button>

In the script tag, a /api/hello request is sent in response to a button click event.

タイトル:index.html(scriptタグ内)

document.getElementById('submit_btn').addEventListener('click', function(){
    xhr = new XMLHttpRequest();
    xhr.open('GET', '/api/hello');
    xhr.onreadystatechange = function () {
        if (xhr.readyState === 4) {
            if (xhr.status === 200) {
                alert(xhr.response);
            } else {
                alert('データが正常に送れませんでした');
            }
        }
    }
    xhr.send();
});

The request is sent with XMLHttpRequest. If the response is returned successfully, use alert to display its contents.

Return to server.js again, and in the app.get method, write the process to respond to the /api/hello request with the output from the previous step.

タイトル:server.js

app.get('/api/hello', (req, res) => {
    const connection = mysql.createConnection({
        host: 'localhost',
        user: 'root',
        password: '*********',
        database: 'express_mysql_sample'
    });
    connection.connect();
    connection.query('SELECT * FROM messages', function (error, results, fields) {
        if (error) throw error;
        console.log(results[0]);
        res.send(results[0].message);
        connection.end();
    });
});

We will return only the MESSAGE portion of the query results.

タイトル:server.js

res.send(results[0].message);

Finally, set up a local server on port 3000.

タイトル:server.js

app.listen(3000, function () {
    console.log('Example app listening on port 3000!'); 
});

Now it's complete! Let's check the operation one last time.

Start a local server.

タイトル:ターミナル

$ node server.js

Access http://localhost:3000/ and check success if the following functions have been implemented.

この記事のまとめ

In this article, we introduced how to connect to MySQL with Node.js. We also introduced an example of using Express to return MySQL data in response to a request from the front desk. Finally, I will summarize the main points of this article.

Node.js can connect to MySQL using a package called mysql
mysql allows you to actually hit MySQL queries

Let's all develop in Node.js using mysql!