How To Use MySQL With Node.js and Docker

Learn how to implement a MySQL database in your Node.js project

Image by the author.

In this tutorial, you learn how to use a MySQL database in your Node.js project with Docker. You will build a simple application that allows you to collect email addresses from people.

The technologies used in this tutorial are:

• Node.js
• Docker
• Express
• MySQL

Create the Project Structure

The first step is to create the project structure. For the moment, you need:

• The overall folder including all files.
• A .env file.
• A file with the server/database configuration.

Thus, you can create all the files and folders as follows:

take nodejs-mysql
touch index.js
touch .env

Now, the next step is to run MySQL.

Run MySQL With Docker

To avoid inconsistencies and installing new stuff on your machine, you will use the official Docker image for MySQL.

Before going any further, run the following command in your terminal:

docker run -p 3306:3306 --name nodejs-mysql -e MYSQL_ROOT_PASSWORD=pass -e MYSQL_DATABASE=emails_db -d mysql:5.7

This command does the following things:

1. It pulls the image mysql:5.7 from DockerHub and then runs it.
2. It starts the MySQL database on port 3306.
3. It sets the database password to pass.
4. Lastly, it creates a new database called emails_db.

Now you can access your database from the terminal and from your application. If you want to run the container with bash and play directly with MySQL, run the following command:

docker container exec -it nodejs-mysql bash

After that, start the MySQL monitor as follows:

mysql -u root -p

You will be prompted to enter the password. Enter the same password you mentioned in the first command above. In this example, the password is pass.

Now you can run MySQL commands and play around with the database. Also, you are ready to use MySQL in your Node.js application.

Initialize the Project and Install Dependencies

The next step is to initialize your Node.js project and install the required dependencies. You can initialize the project with the following command:

npm init -y

This command initializes your project and auto-completes the package.json file with the default values.

The dependencies you need for this project are mysql, express, express-validator, and dotenv. Install them with the following command:

npm install mysql dotenv express express-validator

You will use the mysql package to manipulate your database. You will also use the express package to create two endpoints:

• Read data.
• Add data to the database.

Lastly, you need the express-validator package to sanitize user input. For instance, you would not want someone to add an SQL query instead of an email address because it could create issues.

You are now ready to build the application!

Build the Application

The first step is to open the index.js file and import the packages. Thus, add the following imports at the top of the file:

const express = require('express');
const mysql = require('mysql');
const { body, validationResult } = require('express-validator');
const app = express();

require('dotenv').config();

The next step is to open the .env file and add the database details such as the username, password, and database name:

DB_USER=admin
DB_PASSWORD=pass
DB_NAME=emails_db

The details above should work for you as well. However, feel free to change them.

The next step is to create the database connection in the application. Go to index.js and add the following code:

const database = mysql.createConnection({
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME
});

Now you can use the database to create tables, add, edit, and remove data, and other operations.

Initialize the Database

You have to initialize the database by creating a new table. You need a table to store people’s emails.

Thus, open the index.js file and add the following route:

Now, when you access the /init route, the application creates a new table called emails only if it does not already exist. The table contains the following fields:

• An id
• The first and last name of the person
• The email address

Once the table is created, you can collect email addresses from people. Let’s see how you can do that!

Add Emails

One of the most important parts of the application is allowing people to subscribe to your services/newsletter. You can do so by writing the following piece of code in index.js:

You can see that this route does quite a lot of stuff, so let’s take it step by step:

1. First of all, you allow people to make a POST request to the /subscribe endpoint.
2. Secondly, you sanitize the input from people by using the methods from the express-validator package. They make sure the fields are not empty, and it also escapes the inputs.
3. Lastly, you check if there are any errors. If there are errors, you return those errors. Otherwise, you subscribe the person to the newsletter/service.

The image above shows an example of submitting empty data. You can see that you get a handful of errors depending on what is wrong with the request.

On the other side, if you make a request with valid data, the subscriber is added to the list. See the example below:

Therefore, the endpoint works correctly. People can subscribe to your email/service.

Get All Emails

Lastly, you need a route to get a list with all the email addresses collected. Thus, add the following code in index.js:

The SELECT * FROM emails SQL query selects all the records from the table. The query returns the following information for each query:

• The id
• The first and last name
• The email address

If you access the URL http://localhost:9898/, you get all the subscribers. Below, you can see an example:

With this route, you can see all the people who subscribed using your application.

Note: It’s not a good idea to have this endpoint available for everyone to see because you are exposing personal information. Thus, one improvement could be to secure this endpoint and only make it available to yourself.

Clean the Application

Before finishing, you could improve the application by reorganising the code. Even though it might be overkill for a small application like this one, it helps when it grows larger.

Let’s start by cleaning the index.js file. Open it and replace everything with the following:

The next step is to create a special place for the database configuration. In the root of the project, create the src folder. You can do it as follows:

take src

This command creates the directory and then opens it. Inside the src folder, create another file named db.js. Open it and add the following code:

In the db.js file, you configure the database and create a connection. After that, you export it so it can be used in other places.

Lastly, you need to create the controllers and routes. Thus, create the following folders and files from the root of the project:

take src/controllers/controllers.js
take src/routes/routes.js

Now, you are ready to create the routes and write the controllers.

Create the routes

Let’s start with the routes. Open the routes.js file and add the following code:

In this file, you import express so you can create the routes. After that, you import the controllers so you can pass the specific controller for each route. Then you import the body function from express-validator and create an Express router.

In the next lines, you define the routes and pass the specific controllers. Lastly, you export the router so you can use it in other places.

Write the controllers

The last step is to write the code for the controllers. Actually, you have the code from the previous steps, but you need to store them as functions.

Take the code that was previously in index.js and add it as follows:

As you can see, the code is the same, but now it’s stored in separate functions. Then, at the end of the file, you export those functions so you can use them in the routes.js file.

You’re finished with the refactoring!

Run the Code

To run the application, go into the root of the application and run the following command:

node index.js

After that, you should be able to add new subscribers and see the existing subscribers!

You can find the full code on this GitHub page.

Conclusion

This is just the beginning of the application, but there are many improvements you could add. Some of the improvements could be:

• Adding the frontend part for this application.
• Protecting the route that shows all the subscribers.
• Adding tests.
• Allowing people to create individual accounts and collect email addresses.

Let me know in the comments if you would like me to build the frontend part as well. If there’s enough interest, I will do it!

> This SQL Bootcamp will take you from a complete beginner to an expert in SQL, database management, and database design.

> Learn Node.js, build advanced large-scale applications from scratch & get hired as a Backend Developer