Node.js MySQL Tutorial

This tutorial assumes you have Node.js, MySQL, and MySQL Workbench installed

  1. Create a new Node application

Create the package.json and install the necessary dependencies

cd project-folder
npm init
npm i express express-handlebars mysql

2. Create a new MySQL Database

Create a new Schema. I will call mine Practice

3. Set up your server file

Quickly set up your server file and initiate your MySQL Connection in order to make sure the connection is running successfully before we go any further.

const express = require("express");
const mysql = require("mysql");

const app = express();

app.use(express.urlencoded({ extended: false }));
app.use(express.json({ extended: true }));

const mysqlConnection = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "password",
    database: "Practice",
    multipleStatements: true
})

mysqlConnection.connect((err) => {
    if (!err) {
        console.log("Connection Successful");
    } else {
        console.log("Connection Failed");
        console.log(err.message);
    }
})

app.listen(3000);

Run your Node app

node index.js OR nodemon index.js

If your Terminal logs “Connection Successful”, move to Step 4

4. Move MySQL Connection to separate file

This is not required; it’s simply for organization’s sake

At the same level as the server file, make a new file. I’ll call mine connection.js

This will be the contents of the file:

const mysql = require("mysql");

const mysqlConnection = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "josh-mysql",
   database: "Practice",
   multipleStatements: true
});

mysqlConnection.connect((err) => {
   if (!err) {
      console.log("Connection Successful");
   } else {
      console.log(err.message);
   }
});

module.exports = mysqlConnection;

In the server file, import the MySQL connection from the connection.js file:

const mysqlConnection = require("./connection");

5. Create a table in the database

I’m going to open up my Practice Schema and find the Tables. Then, I’m going to right-click on Tables and create a new Table by clicking “Create Table.” Give your table a name and whatever properties you would like. Then, press Apply.

My projects table have properties of id and name

6. Create a route and query the database

In my server file, I’m going to handle GET requests to the root route. When a user requests the root route, I will query the MySQL database for all of the items in our projects table and send back the items in the response.

app.get("/", function(req, res){
   mysqlConnection.query("SELECT * from projects", (err, rows, fields) => {
      if (!err) {
          res.send(rows);
      } else {
         console.log(err);
      }
   })
});

Let’s test out our route. Run your server with node index.js, open up Postman and send a GET request to the root route.

Since we haven’t created any records into our projects table, this request should return an empty array.

That’s it! You’ve set up a basic Node server with a MySQL database.

Errors Connecting to MySQL in Node

ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client

In MySQL Workbench, open a blank Query page and execute these commands (Query > Execute (All or Selection)):

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
flush privileges;

This is what it will look like in Workbench:

ER_ACCESS_DENIED_ERROR: Access denied for user ‘root’@’localhost’ (using password: YES)

In the part of your Node application where you define the connection to the MySQL database, set password to an empty string:

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

After you re-run your server, the error will go away, but you may be left with a new error– the error below:

ER_MUST_CHANGE_PASSWORD_LOGIN: Your password has expired. To log in you must change it using a client that supports expired passwords.

Execute the following query in MYSQL Workbench (change new-password to your own new password):

ALTER USER `root`@`localhost` IDENTIFIED BY 'new-password',
       `root`@`localhost` PASSWORD EXPIRE NEVER;
flush privileges;

Connecting to MongoDB Atlas with Mongoose

In MongoDB Atlas on your Cluster page, click Connect > Connect your application > Copy the connection string

Create config folder. Inside, create default.json. With the config package, we can create global values that we can use throughout our application

In default.json:

{
   "mongoURI": "mongodb+srv://username:<password>@devconnector.cc6yr.mongodb.net/<dbname>?retryWrites=true&w=majority"
}

We use curly braces because this is json formatting

Replace <password> with your password. Replace <dbname> with your database’s name.

We could put our connection logic in the server file, but in order to not clutter it up, we can put it in a separate file within the config folder

We’ll call the file db.js (you can call it whatever you want) and this is what goes inside of it:

const mongoose = require('mongoose');
const config = require('config');
const db = config.get('mongoURI');

const connectDB = async () => {
    try {
        await mongoose.connect(db, { useNewUrlParser: true });
        console.log("MongoDB connected...");
    } catch(err) {
        console.log(err.message);
        process.exit(1);
    }
}

module.exports = connectDB;

We add { useNewUrlParser: true } to our connect function in order to get rid of this error:

“(node:58034) DeprecationWarning: current URL string parser is deprecated, and will be removed in a future version. To use the new parser, pass option { useNewUrlParser: true } to MongoClient.connect.”

Then, in our server file we can just import and call the connectDB() function:

const express = require("express");
const connectDB = require('./config/db');

const app = express();

connectDB();

app.get('/', (req, res) => res.send('API Running'));

const PORT = process.env.PORT || 5000;

app.listen(PORT, () => console.log(`Server started on port ${PORT}`));

How to use the express-validator package

The express-validator package is an npm package that makes it much easier to validate input fields (for user authentication).

First, at the top of our server file, we need to get access to the check and validationResult methods from express-validator:

const { check, validationResult } = require('express-validator/check');

Then, when you are handling the post route of a form, you can implement validation. First, you add a new parameter to the post method; it should be an empty JS array:

app.post('/', [], (req, res) => {
});

Within the array, run the check function on each field and give it the requirements you want to check for. For example, you can check if the input is an email or has a minimum length:

app.post('/', [
   // username must be an email
   check('username').isEmail(),
   // password must be at least 5 chars long
   check('password').isLength({ min: 5 })
], (req, res) => {
});

Then, in the callback, you create a variable called errors to hold any errors from the validation checks in the above array:

app.post('/', [
   check('username').isEmail(),
   check('password').isLength({ min: 5 })
], (req, res) => {
   // Finds the validation errors in this request and puts them in an array called errors
   const errors = validationResult(req);
});

Then, we check if the errors object holds an errors. If so, we can do stuff with the errors. If not, we can do stuff with the inputs:

app.post('/', [
   check('username').isEmail(),
   check('password').isLength({ min: 5 })
], (req, res) => {
   const errors = validationResult(req);

   if (!errors.isEmpty()) {
      return res.status(400).json({ errors: errors.array() });
   }

   User.create({
      username: req.body.username,
      password: req.body.password
   }).then(user => res.json(user));
});

In the event of an error, we send a 400 Bad Request using res.status(400). This simply means that things didn’t go as planned. Then, we get an array of error messages that correspond with the input validations that failed. The json response object will look something like this:

{
   "errors": [
      {
         "location": "body",
         "params": "username",
         "msg": "Insert custom or default error message here",
      }, 
      {
         "location": "body",
         "params": "password",
         "msg": "Please enter a correct password",
      }
   ]
}

More on the check function

We can pass a second optional parameter into the check function which will serve as a custom error message. If we don’t pass this parameter, it will give some generic error message.

check('name', 'Name is required')

Check if a field is empty

check('name', 'Name is required').not().isEmpty()

Setup React Front-End in Existing Project

Let’s say you’re building with the MERN stack, for example. You have an existing project folder in which you’ve set up your entire backend server with Express and connecting your MongoDB database. Now you want to append a React frontend to the project. How do you setup a new React project within your existing overall project folder?

cd into your existing project folder and run this command:

npx create-react-app [your-folder-name]

This line basically creates a React application within a folder with whatever name you choose (ex. client) within your current project directory. npx is a tool that comes with Node.js that allows us to run create-react-app without having to install it globally on our machine.

To run your React project to make sure everything installed successfully, run these commands:

cd [your-folder-name]
yarn start

Run Frontend and Backend at the Same Time

We can run our frontend React app and backend Node.js server at the same time using the development tool concurrently.

Install concurrently as a development dependency:

npm i -D concurrently

Define the necessary scripts within your package.json:

"scripts": {
    "start": "node server",
    "server": "nodemon server",
    "client": "npm start --prefix client",
    "dev": "concurrently \"npm run server\" \"npm run client\""
},

The “dev” script uses concurrently to run the “server” and “client” scripts at the same time– with one single command.

In the root folder, run the “dev” script:

npm run dev

Installing Dependencies Within the React Project

You install dependencies within a submerged React project the same way you would within an non-submerged React project.

First, make sure you’re inside the React project folder. My folder name is client so:

cd client

Then, install dependencies with npm

npm i react-redux axios ...

Get Ride of Default Git Repository Within React App

Within the React app, a git repository is automatically initialized on the creation of the React app. If you already have a Git repository within your root project, you don’t necessarily need another one for your React project. If you wanted to delete the React repository, you would

  1. Delete the .gitignore and README.md files within the React folder
  2. Delete the Git folder

This is the command to delete the git folder. Make sure you cd into your React folder before running this command or you might delete your root repository:

rm -rf .git

Node.js User Authentication with Bcrypt

In this lesson, we will be allowing users to create accounts, disguising their passwords to prevent hackers from stealing them, saving the accounts in a MongoDB database, and allowing users to log in with their account info.

In your project directory in Terminal, use this command to create a package.json file for your project

npm init -y

Any project that is using Node.js needs a package.json file. The file contains the project’s dependencies, source control information, and specific metadata.

Now install all the packages that our project will need using npm.

npm i express body-parser ejs mongoose bcrypt

I will show you how to set up the app.js file, but you’ll have to design the ejs (or html) and css files on your own.

In general, though, you’ll want to include something like this in your login & signup files:

<form action="/login" method="POST">
   <div class="form-group">
       <label for="email">Email</label>
       <input type="email" class="form-control" name="username">
   </div>
   <div class="form-group">
        <label for="password">Password</label>
        <input type="password" class="form-control" name="password">
   </div>
   <button type="submit" class="btn btn-dark">Login</button>
</form>

You will need a form that has an action parameter that posts to a route. You will take the user’s input using named input tags that can be referenced in the server file.

In your app.js file, require all the packages we installed earlier. Also create a saltRounds variable and set it to 10. We will use it later to salt our passwords.

const express = require("express");
const bodyParser = require("body-parser");
const ejs = require("ejs");
const mongoose = require("mongoose");
const bcrypt = require("bcrypt");
const saltRounds = 10;

More standard setup:

const app = express();

app.use(express.static("public"));
app.set('view engine', 'ejs');
app.use(bodyParser.urlencoded({
  extended: true
}));

mongoose.connect("mongodb://localhost:27017/authtestDB", {useNewUrlParser: true});

Create the User Model in MongoDB:

const userSchema = new mongoose.Schema ({
  email: String,
  password: String
});


const User = new mongoose.model("User", userSchema);

Set up the get routes so users can get to the different pages:

app.get("/", function(req, res){
  res.render("home");
});

app.get("/login", function(req, res){
  res.render("login");
});

app.get("/register", function(req, res){
  res.render("register");
});

Handle users entering their information into the login and register forms:

Posting to the Register Route

As you can see, we are using the bcrypt hash function. The function takes in the password given by the user in the request, the number of salt rounds (defined above), or times we want our password to be salted , and a callback function that can either return an error or the hashed (encrypted) password.

A new user based on our Mongo User Model is created using the username from the request and the encrypted password. Then, that user is saved into the MongoDB. If successful, the server will navigate the user to pages that require authentication.

app.post("/register", function(req, res){
  bcrypt.hash(req.body.password, saltRounds, function(err, hash) {
    const newUser =  new User({
      email: req.body.username,
      password: hash
    });
    newUser.save(function(err){
      if (err) {
        console.log(err);
      } else {
        res.render("secrets");
      }
    });
  });

});
Posting to the Login Route

First, using the findOne function, we see if there are any emails in our database that match the one inputted by the user. This findOne function can return either an error or a found user/ matching user. If a user is found for the inputted email, bcrypt will proceed to compare the inputted password with the password for the email in the database. If it’s another match (result === true), the server will navigate the user to pages that require authentication.

app.post("/login", function(req, res){
  const username = req.body.username;
  const password = req.body.password;

  User.findOne({email: username}, function(err, foundUser){
    if (err) {
      console.log(err);
    } else {
      if (foundUser) {
        bcrypt.compare(password, foundUser.password, function(err, result) {
          if (result === true) {
            res.render("secrets");
          }
        });
      }
    }
  });
});