NodeJS and MySQL: how to use MySQL on a Node application

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 MySQL.

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.

This MySQL NodeJS guide will show you how to use MySQL on a Node application. Make sure you have NodeJS and MySQL installed on your machine and have a Node project ready to use. 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, a basic MySQL driver for Node.js written in javascript and requiring no compilation. This is the easiest and fastest solution to set up to interact with a MySQL database in 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.

Use the mysql module to use a MySql database in Node

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.

Connect to the MySQL database with the mysql module

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.

Create a MySQL database in Node.js with the missal module

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!");
        });
});

Execute SQL queries on a MySQL database with NodeJS

You can thus execute any type of SQL query:

cont 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 to connect to when we call the createConnection().

Using a MySQL database in NodeJS with Sequelize

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, username, password, and MySQL database name 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);
}

Create a MySQL database in Node.js with Sequelize

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