NodeJS and MySQL Connection

MySQL is one of the most popular relational database management systems in the world.

Unlike NoSQL solutions such as MongoDB, MySQL allows you to manage relational databases based on SQL ( Structured Query Language ).

Each row of a table in a relational database can be uniquely identified via its primary key and can refer to the row from another table via a foreign key. These relationships make it possible, among other things, to perform joins that allow the same query to access information located in different tables of the database.

Beyond this ability to manage complex relationships, MySQL offers optimal performance, advanced security features, and all the advantages of an open-source database. These characteristics make MySQL a solid solution and a safe choice for Node.js applications that need relational databases.

In this article, I will show you how to establish a NodeJS and MySQL connection and how to use MySQL on a Node application. Ensure you have NodeJS and MySQL installed on your machine and have a Node project ready. Feel free to refer to our other Node.js guides.

Connect a MySQL database to a Node.js app

Several NodeJS libraries allow you to establish a connection with a MySQL database and execute queries. Among them, the two most popular are:

• MySQL; Which is a basic MySQL driver for Node.js written in javascript and requires no compilation. This is the easiest and fastest solution if you intend to interact with a MySQL database using Node.
• Sequelize; is the most popular library for using SQL-based database management systems with Node.js. It supports MySQL but also Postgres, Microsoft SQL, MariaDB… This powerful ORM (Object-Relational Mapping) allows, among other things, the use of promises and the customization of error messages for each field.

Using Nodejs MySQL module to interact with MySql database

Start by installing the MySQL module in your Node.js project folder with npm install:

npm install mysql

In your Node.js project’s entry point file, initialize the module in a variable with require() :

const mysql = require('mysql');

Your NodeJS app can now connect to your MySQL database.

Now, in order to connect to your MySQL database, enter the host, user, and password specified when installing MySQL on your system:

const db = mysql.createConnection({
        host: "localhost",
        user: "user_name",
        password: "user_password"
});

Finally, use the connect function to connect your MySQL database.

An exception will be thrown if an error occurs:

db.connect(function(err) {
        if(err) throw err;
        console.log("connected to your MySQL database");
});

Your user is now connected to the MySQL database and can run queries on it.

Equally, to create a MySQL database, simply execute a CREATE DATABASE with the query() in your app code:

const mysql = require('mysql');

const db = mysql.createConnection({
   host: "localhost",
   user: "user_name",
   password: "user_password"
 });

db.connect(function(err) {
    if(err) throw err;
    console.log("connected to the database MySQL!");
    db.query("CREATE DATABASE mydb", function(err, result) {
        if(err) throw err;
        console.log("database created!");
        });
});

We will execute our SQL query as follows:

const mysql = require('mysql');

const con = mysql.createConnection({
   host: "localhost",
   user: "user_name",
   password: "user_password"
   database : "mydb"
 }); 
con.connect(function(err) {
   if (err) throw err;
   console.log("Connected to the database MySQL!");
   con.query("SELECT student.id as 'student_id', student.name as 'student_name', student.course_id, course.name as 'course_name', course.date as 'course_date' FROM students JOIN cours on students.course_id = course.id", function (err, result) {
       if (err) throw err;
       console.log(result);
     });
 });

Note that this time we have specified the name of the MySQL database in order to connect to it when we call the createConnection().

Using a MySQL database in NodeJS with Sequelize

In order to use Sequelize to interact with your MySQL database in Node, you must first install the mysql2 driver. It is a separate driver from the MySQL module, less popular than the latter, but which offers some additional features.

Install mysql2 with npm install:

npm install mysql2

In your app code, initialize Sequelize with require() :

const {Sequelize} = require('sequelize');

In a new Sequelize object, provide the username, password, and MySQL database name in order to establish the connection :

const sequelize = new Sequelize("database_name", "user_name", "user_password", {
      dialect: "mysql",
      host: "localhost"
}); 

Finally, you can verify that the connection is well established with authenticate(), though it is not necessary to execute queries on your database:

try{
        sequelize.authenticate();
        console.log("Connected to the database MySQL!");
} catch(error) {
        console.log("impossible to connect: the error is: ", error);
}

Creating a MySQL database in Node.js using Sequelize

In order to create a MySQL database in Node with Sequelize, simply add a CREATE DATABASE in a query() to your code:

const sequelize = new Sequelize("database_name", "user_name", "user_password", {
      dialect: "mysql",
      host: "localhost"
});

try{
        sequelize.authenticate();
        console.log("Connected to the database MySQL!");
        sequelize.query("CREATE DATABASE 'mydb';").then(([resulta, metadata]) => {
                console.log("database created!);
        });
} catch(error) {
        console.log("impossible to connect: the error is: ", error);
}

Execute SQL queries on a MySQL database with Sequelize

With the query() method, you can execute any SQL query on your database, such as the join we used previously:

const sequelize = new Sequelize("mydb", "user_name", "user_password", {
     dialect: "mysql",
     host: "localhost"
});

try {
     sequelize.authenticate();
     console.log("connected to the database MySQL!");
     sequelize.query("SELECT students.id as 'students_id', 
                      students.name as 'students_name', 
                      students.course_id, course.name as 'course_name', 
                      course.date as 'course_date' 
            FROM students JOIN course on students.course_id = course.id)
            .then(([results, metadata]) => {
                      console.log(results);
            });
} catch(error) {
     console.error('Impossible to connect, the error is: ', error);
}