MySQL is a Relational Database Management System (RDBMS). Because it allows fast and complex queries to be executed, it is an important element of the tools used by web developers in their work.

On the other hand, we have Node.js, an open-source back-end environment for executing JavaScript code at server level, which has become extremely popular in recent years. Many developers develop their projects in Node.js, using MySQL as their database.

In today’s tutorial, we’ll take a look at how to connect to a MySQL database using a Node.js application. Then we’ll spend a few words on the process of creating MySQL tables, again of course using Node.js.

Table of contents

• Connecting to MySQL using Node.js
• Creating a MySQL table using Node.js

Connecting to MySQL using Node.js

To be able to connect, you need to have created a MySQL database. If you plan to work locally, you can use MySQL Workbench or HeidiSQL. On the NEOSERV server, you can create the database in the cPanel control panel and then access it using phpMyAdmin.

You will also need Node.js and npm to work on your local environment. If you will be working on a server, you can connect to your hosting package using SSH (instructions).

1. The basics: project folder, package.json and mysql module

First, create a folder where you will store your Node.js application and navigate to it.

mkdir project1

cd project1

Create a package.json file that includes the important metadata of the project.

npm init -y

Install the mysql module, if it was not specified for installation in the package.json file.

npm install mysql

2. Establishing a connection

Create a JS file for the application (for example connect.js), open it and import the mysql module.

const mysql = require('mysql');

Connect to the MySQL database using the createConnection() method. In the code below, instead of the vasadomena_user, password and vasadomena_database records, enter the actual details of your database.

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

Now you can call the connect() method on the connection object to connect to the database. The above method accepts a callback function with the err argument, which gives details of the error if it occurs.

connection.connect(function(err) {
if(err){
return console.log('error: ' + err.message);
}
console.log('Connection to MySQL database is established.');
});

So, now you can run the application and check if the connection will be successfully established.

> node connection.js

The connection to the MySQL database is established.

3. Disconnecting

It is recommended that you disconnect from the database after the query has been executed. This is done by calling the end() method on the connection object.

connection.end(function(err) {
if (err) {
return console.log('error:' + err.message);
}
console.log('Connection to the MySQL database is broken.);
});

The end() method makes sure that the remaining queries are always executed before disconnecting from the database. If you decide to disconnect immediately (no callbacks), use the destroy() method.

connection.destroy();

4. Creating a Connection Pool

Suppose you want to create a connection pool with ten connections. In this case, use the code below.

var pool = mysql.createPool({
connectionLimit: 10,
host: 'localhost',
user: 'vasadomena_user',
password: 'password',
database: 'vasadomena_base'
});

If you want to establish a connection from the connection pool, you can use the getConnection() method.

pool.getConnection(function(err, connection) {
// query execution ...
});

When you are done, you can return the connection back to the connection pool, making it accessible to others. Re-order the above command and use the release() method on the connection object.

pool.getConnection(function(err, connection) {
// query execution ...
connnection.release();
});

To terminate the connection and remove it from the pool, use the destroy() method. The new connection would be re-established within the pool should the need arise.

It is good to know that if, for example, you have configured a connection pool with ten connections, but you are only using four connections in parallel, then the pool will only create four connections for you. If you want to terminate all connections within the pool, you can use the end() method and call it on the pool object.

pool.end(function(err) {
if (err) {
return console.log(err.message);
}
// terminate all connections
});

Creating a MySQL table using Node.js

Now let’s take a look at an example of how to create a new table in a MySQL database using the Node.js application. Suppose your database is called vasadomain_database, and now you want to create an informatics table in it.

In this case, you first connect to the database, then use the CREATE TABLE statement to create a new table, and then disconnect from the database.

const mysql = require('mysql');
const connection = mysql.createConnection({
host: 'localhost',
user: 'vasadomena_user',
password: 'password',
database: 'vasadomena_base'
});

connection.connect(function(err) {
if(err){
return console.log('error: ' + err.message);
}
console.log('Connection to MySQL database is established.');
var sql = "CREATE TABLE informatiki (first name VARCHAR(255), last name VARCHAR(255))";
connection.query(sql, function (err, result) {
if (err) throw err;
console.log("Table created.");
});
});

connection.end(function(err) {
if (err) {
return console.log('error:' + err.message);
}
console.log('Connection to the MySQL database is broken.');
});

The only thing left to do is to check if the table was successfully created. You can check this using the phpMyAdmin tool (in the cPanel control panel) or with a simple command in the terminal.

>mysql -u vasadomena_user -p vasadomena_base

Enter password: *********

mysql> show tables;
+-------------------+
| Tables_in_vasadomain_base |
+-------------------+
| Informatics |
+-------------------+
1 row in set (0.06 sec)

If you see a printout similar to the one above, the table has been successfully created.