How to Deploy Node.JS, Express, EJS, MySQL website on Heroku & ClearDB

By Raddy in NodeJs · 15th July 2021

In this article, we are going to create and deploy a very simple Node.Js, Express Application using EJS as our View Engine and ClearDB for our MySQL Database. Everything that we are going to cover here is using the FREE services as long as you are working on a personal project and you don’t need a huge database. You can always scale up if you wish to.

Please note that Heroku no longer offers a free tier.

1) Create Heroku Verified Account

The first thing that you need is a Verified Heroku Account. Unfortunately, in order to verify your account, you will need to put in your Credit/Debit card details so you can add add-ons like ClearDB to your Account.

Once you have created a Heroku account and you have it verified log in to the dashboard.

2) Create New Heroku App

Create a new Application by clicking the “New” button on the top right corner and then selecting “New App“.

Give your app a name and choose the region that is closest to you or the one where most of your users will come from. Once you are done, click the “Create App” button.

3) Install ClearDB MySQL

To Install ClearDB Navigate to the “Resources” Tab on the main menu. Scroll down to Add-ons and use the search bar to look for “ClearDB“. Submit the form and you should have ClearDB MySQL installed.

4) Get ClearDB MySQL Credentials

To find your database credentials click on the “Settings” tab and then scroll down until you see the “Config Vars” section. Find the “Reveal Config Vars” button and then click on it. This will give you everything that you need to connect to your database. Copy and paste the code somewhere on a text file so we can use it later in this tutorial.

The config vars should look something like this:

mysql://b54966f5205365:9cbaa662@eu-cdbr-west-01.cleardb.com/heroku_b1791a6475f6a54?reconnect=true

We need to break this down and get the following details:

5) Connect ClearDB to MySQL Workbench

Download MySQL Workbench if you haven’t yet and open up the application. To create a new connection, click on the small plus icon shown in the screenshot below:

This will open up the Setup New Connection window and now we need to enter the credentials that we took from Heroku earlier in Step 4. Note that you can leave the Port to 3306.

Once you enter the connection details make sure that you click “Test Connection” to ensure that everything is working well. If you get the Connection Successful message, click “OK” and this will save the MySQL Connection for us.

If the connection didn’t work, make sure that you go back to Step 4 and double-check the credentials that you entered.

6) Create User Test table

In order to test this project, we will need at least one table. Let’s create a new “user” table where we can store our user data. To do this click on our newly created connection to enter the MySQL Workbench Dashboard.

Now we need to find our database and create a new table. Find the “Schemas” Tab in the left sidebar and select it. You should be able to see your SCHEMAS there. Double-click on your database and then create a new table by clicking the “new table” icon just under the Query menu shown in the image below:

This is where we can create our new “user” table. To make it simple, I am only going to add an id, name and a job column name just like the image above.

Alternatively, you can do the query manually if you wish to:

CREATE TABLE `heroku_4eb7a8ff5383ffb`.`user` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  `job` VARCHAR(45) NULL,
  PRIMARY KEY (`id`));

7) Create a user record

To create a new record, expand your “Tables” in the left sidebar and hover over the table “user” which should show you a tiny grid icon on the right side. Click on it and that should open your user table and you should be able to see the columns and rows grid just like in the image below:

Enter at least one record. I have mine set to id: 1, name: Rad and job: Web Developer. Once you are done make sure that you apply the changes.

Our database is now ready and we can create a super simple application to test it out before we deploy it to Heroku.

1) Creating Node.js, Express, EJS Application

The first thing that you need to do is to create a new project folder. Once you do that make sure that you open your Terminal. Command-Line or Powershell and CD to your project folder.

npm init

This will initialise a new project for you and it’s going to ask you a few questions about your project. The most important one is to give your package a name and then you can just keep pressing enter until the installation is over.

You can skip all questions by adding the “-y” flag like in the example below:

npm init -y

If the “-y” flag doesn’t work for you, you probably have spaces in your project folder name or maybe even special characters. You can try without spaces and special characters.

At this point, you should see a file called package.json in your project folder.

2) Dependencies Installation

There are only three dependencies that we need for this project. We need Express, Ejs and MySQL. Express is going to help us create a server, Ejs is going to be our view engine and MySQL will help us connect to our database.

[x] express
[x] ejs
[x] mysql

Open the Command Line / Terminal / Powershell and install the following dependencies:

npm install express ejs mysql

Your packages.json file should look similar to this:

{
  "name": "node-heroku-cleardb",
  "version": "1.0.0",
  "description": "",
  "main": "app.js", // update this line
  "scripts": {
    // remove test line if you wish
    "start": "node app.js" // add this line
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "dependencies": {
    "ejs": "^3.1.6",
    "express": "^4.17.1",
    "mysql": "^2.18.1"
  }
}

Note that the version of the dependencies might change in the future.

3) Project Structure

Our project is going to be simple.

📂 node_modules
📂 views
 📂 pages
  🌍 index.ejs
🌍 app.js - create this file
📜 package-lock.json
📜 package-json
📜 .gitignore

4) Create App.js

Let’s start by creating our application file.

const express = require('express')
const app = express()
const mysql = require('mysql')

const port = process.env.PORT || 3000

// Connection Details
const connection = mysql.createConnection({
  host: 'eu-cdbr-west-01.cleardb.com',
  user: 'b864459d35e3fd',
  password: '6a880cda',
  database: 'heroku_4eb7a8ff5383ffb'
})

// View engine
app.set('view engine', 'ejs')

// Render Home Page
app.get('/', function (req, res) {

  connection.query('SELECT * FROM user WHERE id = "1"', (error, rows) => {
    if (error) throw error;

    if (!error) {
      console.log(rows)
      res.render('pages/index', { rows })
    }

  })

})

app.listen(port)
console.log(`Server is listening on port ${port}`);

Index.ejs

This is going to be our home page where we can render some of the values from the database. As you can see we are selecting the first object of the rows and then selecting the name and the job so we can display them.

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Document</title>
</head>
<body>
  <h1>Hello <%- rows[0].name %> </h1>
  Job Title: <%- rows[0].job %>
</body>
</html>

.gitignore

You don’t have to do this step, but it’s nice to have. We don’t need to upload the “node_modules” folder to Heroku when we deploy. Heroku is going to take care of that.

Create .gitignore file and inside you can simply put the folder that you want to ignore:

node_modules/

Run Application Locally

To test out the application locally all you need to do is the following command:

node app.js

This should run our application and now if everything is working well, you should be able to visit http://localhost:3000/ and see your website.

1) Deploy to Heroku

Deploying our application should be fairly straightforward at this point. You will need to download and install the Heroku CLI before we start.

Steps From Heroku:

If you haven’t already, log in to your Heroku account and follow the prompts to create a new SSH public key.

$ heroku login

Clone the repository

Use Git to clone nodejs-cleardb’s source code to your local machine.

$ heroku git:clone -a nodejs-cleardb
$ cd nodejs-cleardb

Deploy your changes

Make some changes to the code you just cloned and deploy them to Heroku using Git.

$ git add .
$ git commit -am "make it better"
$ git push heroku master

That’s it. You should be able to visit our app now by clicking on the “Open app” button in the top right corner of the Heroku Dashboard.

Tips are never expected but are deeply appreciated if you are able. It helps me create more content like this.

Thank you for reading this article. Please consider subscribing to my YouTube Channel.

Credit:

• heroku.com
• Photo by Cookie the Pom on Unsplash

More Resources:

• Self Host Umami on Heroku – Step by Step Guide
• Build Simple REST API With NodeJs, ExpressJs And MongoDB
• Show selected option value from Array & MySQL DB using PHP
• WordPress Admin “Submit for Review”
• Best Alternatives To Heroku For hosting a NodeJS app for FREE

Author

Hi! I’m Raddy. I am a web developer, often making YouTube Videos, Blog Posts and side projects. Enquiries: [email protected]

Previous Post in Category

Next Post in Category