Deploying/Hosting Node.js app on Heroku with MySQL database

Heroku is one of the most popular cloud platform as a service (PaaS). We can use it to deploy and manage our application in simple & convenient way. This tutorial guides you through the steps of deploying Node.js app on Heroku with ClearDB and MySQL database. The deployment will be done using Git and Heroku CLI.

Related Posts:
Build Node.js Rest APIs with Express & MySQL
Node.js Rest APIs example with Express, Sequelize & MySQL
Dockerize Node.js Express and MySQL example – Docker Compose


Create a Node.js app for deployment

In previous tutorial, we’ve built a Restful CRUD API with Node.js, Express and MySQL.

The routes for handling all CRUD operations looks like this:

Methods Urls Actions
GET /customers get all Customers
GET /customers/42 get Customer with id=42
POST /customers add new Customer
PUT /customers/42 update Customer with id=42
DELETE /customers/42 remove Customer with id=42
DELETE /customers remove all Customers

Our project structure’s like:

nodejs-rest-api-express-mysql-project-structure

You can clone the sample app directly from Github.
We’re gonna deploy the app using Git and Heroku CLI.

Download and Install Heroku

Heroku CLI is a command line application that we can use to create, deploy and manage Heroku apps.
Now go to Heroku Dev Center, follow the instructions to download & install Heroku CLI.

Log in to Heroku

First we need a Heroku account and it can be created from the Signup Page.
After having an account, open command prompt, run the command:

$ heroku login

It will prompt us to enter our Heroku account (email & password) in a browser window. Then we will receive:

heroku: Press any key to open up the browser to login or q to exit:
Opening browser to https://cli-auth.heroku.com/auth/browser/xxx
Logging in... done
Logged in as [email protected]

Setup Git and Create a Heroku app

Point the command prompt to our project root directory, then create Git repository:

$ cd nodejs-express-mysql
$ git init
$ git add .
$ git commit -m "initial commit"

Create Heroku app with command: heroku create [app-name].

$ heroku create bezkoder-nodejs-mysql
Creating ⬢ bezkoder-nodejs-mysql... done
https://bezkoder-nodejs-mysql.herokuapp.com/ | https://git.heroku.com/bezkoder-nodejs-mysql.git

Now we have initialized a new Heroku app, we also create a remote repository at Heroku:
https://git.heroku.com/bezkoder-nodejs-mysql.git

If we don’t pass a name, just run the command: heroku create, Heroku will generate a random name:

$ heroku create
Creating app... done, ⬢ tranquil-woodland-06325
https://tranquil-woodland-06325.herokuapp.com/ | https://git.heroku.com/tranquil-woodland-06325.git

And we need to rename the app using heroku apps:rename.

$ heroku apps:rename --app tranquil-woodland-06325 bezkoder-nodejs-mysql

You can check the result of this step with git remote -v:

$ git remote -v
heroku  https://git.heroku.com/bezkoder-nodejs-mysql.git (fetch)
heroku  https://git.heroku.com/bezkoder-nodejs-mysql.git (push)

Deploy the app to Heroku

We can easily deploy our Node.js app to Heroku by pushing the code to the remote repository that we created at the previous step. Heroku will automatically detects that this is a Node.js app and builds it accordingly.

$ git push heroku master
Enumerating objects: 16, done.
Counting objects: 100% (16/16), done.
Delta compression using up to 4 threads
Compressing objects: 100% (13/13), done.
Writing objects: 100% (16/16), 3.41 KiB | 290.00 KiB/s, done.
Total 16 (delta 0), reused 0 (delta 0)
remote: Compressing source files... done.
remote: Building source:
remote:
remote: -----> Node.js app detected
remote:
remote: -----> Creating runtime environment
remote:
remote:        NPM_CONFIG_LOGLEVEL=error
remote:        NODE_ENV=production
remote:        NODE_MODULES_CACHE=true
remote:        NODE_VERBOSE=false
remote:
remote: -----> Installing binaries
remote:        engines.node (package.json):  unspecified
remote:        engines.npm (package.json):   unspecified (use default)
remote:
remote:        Resolving node version 10.x...
remote:        Downloading and installing node 10.16.3...
remote:        Using default npm version: 6.9.0
remote:
remote: -----> Installing dependencies
remote:        Installing node modules (package.json)
remote:        added 59 packages from 48 contributors and audited 171 packages in 2.351s
remote:        found 0 vulnerabilities
remote:
remote:
remote: -----> Build
remote:
remote: -----> Pruning devDependencies
remote:        audited 171 packages in 1s
remote:        found 0 vulnerabilities
remote:
remote:
remote: -----> Caching build
remote:        - node_modules
remote:
remote: -----> Build succeeded!
remote: -----> Discovering process types
remote:        Procfile declares types     -> (none)
remote:        Default types for buildpack -> web
remote:
remote: -----> Compressing...
remote:        Done: 20M
remote: -----> Launching...
remote:        Released v3
remote:        https://bezkoder-nodejs-mysql.herokuapp.com/ deployed to Heroku
remote:
remote: Verifying deploy... done.
To https://git.heroku.com/bezkoder-nodejs-mysql.git
 * [new branch]      master -> master

But currently our Node.js app doesn’t connect with any MySQL database. We need to come to next step to make it work.

Configure MySQL Database for Node.js on Heroku app

Setup ClearDB

Heroku provides PostgreSQL as default database engine for our application. In this tutorial, we’re gonna work with MySQL database, so service provider called ClearDB will be used.

To get started, install ClearDB add-on to our application with command:

$ heroku addons:create cleardb:ignite
Creating cleardb:ignite on ⬢ bezkoder-nodejs-mysql... free
Created cleardb-cubed-17749 as CLEARDB_DATABASE_URL
Use heroku addons:docs cleardb to view documentation

Configure MySQL connection

After installing the Add-ons, we can get our database URL by running the command:

$ heroku config | grep CLEARDB_DATABASE_URL

If you get the error on Window Os:
'grep' is not recognized as an internal or external command, operable program or batch file.
You can use this command instead:

$ heroku config | findstr CLEARDB_DATABASE_URL
CLEARDB_DATABASE_URL: mysql://b7e2437887xxxa:[email protected]/heroku_7643ec736354xxx?reconnect=true

Copy the value of the CLEARDB_DATABASE_URL config variable and use it in the following command:

$ heroku config:set DATABASE_URL='mysql://b7e2437887xxxa:[email protected]/heroku_7643ec736354xxx?reconnect=true'
Setting DATABASE_URL and restarting ⬢ bezkoder-nodejs-mysql... done, v6
DATABASE_URL: 'mysql://b7e2437887xxxa:[email protected]/heroku_7643ec736354xxx?reconnect=true'

Config Node.js to connect ClearDB MySQL on Heroku

In the previous step, we get DATABASE_URL that contains:

  • b7e2437887xxxa: username
  • 0200xxx6: password
  • us-cdbr-iron-east-02.cleardb.net: host
  • heroku_7643ec736354xxx: database

Now we’re gonna use these parameters to config our database connection in Node.js app.
Open db.config.js file, change the configuration like this:

module.exports = {
  HOST: "us-cdbr-iron-east-02.cleardb.net",
  USER: "b7e2437887xxxa",
  PASSWORD: "0200xxx6",
  DB: "heroku_7643ec736354xxx"
};

Don’t forget to push the updated code to Heroku remote repository.

$ git add .
$ git commit
$ git push heroku master

Create MySQL table on ClearDB

Before testing our app on Heroku, we need to create MySQL table named customers on ClearDB. Using the connect parameters above, open another command prompt, run the command:

mysql --host=us-cdbr-iron-east-02.cleardb.net --user=b7e2437887xxxa --password=0200xxx6 --reconnect heroku_7643ec736354xxx

Create the table with SQL script like this:

mysql> CREATE TABLE IF NOT EXISTS `customers` (
  id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  email varchar(255) NOT NULL,
  name varchar(255) NOT NULL,
  active BOOLEAN DEFAULT false
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Check the table:

mysql> DESCRIBE customers;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| id     | int(11)      | NO   | PRI | NULL    | auto_increment |
| email  | varchar(255) | NO   |     | NULL    |                |
| name   | varchar(255) | NO   |     | NULL    |                |
| active | tinyint(1)   | YES  |     | 0       |                |
+--------+--------------+------+-----+---------+----------------+

Test the Heroku app on local

To test our app in local machine, run the command:

$ heroku local
9:50:46 AM web.1 |  > [email protected] start D:\Projects\NodeJs\nodejs-express-mysql
9:50:46 AM web.1 |  > node server.js
9:50:47 AM web.1 |  Server is running on port 5000.
9:50:48 AM web.1 |  Successfully connected to the database.

Now you can make CRUD operations with our Rest Apis using the url: http://localhost:5000/customers.

But for https://bezkoder-nodejs-mysql.herokuapp.com/customers, we’ve not done yet and will get some errors.

Test the Node.js Rest Apis on Heroku

Run the command: heroku run node server.js.

Currently, if you make any request to https://bezkoder-nodejs-mysql.herokuapp.com/customers, you will see:

deploy-nodejs-app-mysql-heroku-application-error

Run the command heroku logs --tail, there are 2 errors that occur frequently:

Error code=H10 desc=”App crashed”

heroku[router]: at=error code=H10 desc="App crashed" method=GET path=/... host=...

Error R10: failed to bind to $PORT

Error R10 (Boot timeout) -> Web process failed to bind to $PORT within 60 seconds of launch

This is because our Node.js app is configured to bind to port 3000, but Heroku uses the $PORT environment variable, and it is dynamic.

To fix this issue, just change our code like this:

const PORT = process.env.PORT || 3000;
app.listen(PORT, () => {
  console.log(`Server is running on port ${PORT}.`);
});

Error Connection lost: The server closed the connection

We may also get this database connection error. This is because the MySQL server on ClearDB closed the connection. You can detect the disconnect event, then recreate the connection.

Another way to fix this is to use connection pooling. Open db.js and change its code to new code like this:

const mysql = require("mysql");
const dbConfig = require("../config/db.config.js");

var connection = mysql.createPool({
  host: dbConfig.HOST,
  user: dbConfig.USER,
  password: dbConfig.PASSWORD,
  database: dbConfig.DB
});

module.exports = connection;
Note: After fixing the issues, you should update code to Heroku remote repository.

$ git add .
$ git commit
$ git push heroku master

Now we can make CRUD operations to Rest Apis on Heroku server with command above: heroku run node server.js.

Or restart the server with: heroku restart.

Conclusion

Today we’ve learned how to deploy Node.js app on Heroku, we also know way to config ClearDB add-on to work with MySQL Database, then we test the Heroku app on local and Heroku server with fixing Error H10, R10, database connection lost.

There are many things to do, but hope you can cover them all.
Happy learning! See you again.

Further Reading

Security: Node.js – JWT Authentication & Authorization example

Node.js & MySQL Associations:
One-to-Many Relationship example
Many-to-Many Relationship example

Source code

You can find the complete source code for this example on Github.

More Practice: Dockerize Node.js Express and MySQL example – Docker Compose

30 thoughts to “Deploying/Hosting Node.js app on Heroku with MySQL database”

  1. Hello from Cameroon! Excellent work. You are saving lives dude. keep on and God bless you!

  2. Excellent post, thank you so much, it works very well, btw, the section of errors was so helpful.

  3. hello, your tutos are great by the way 🙂 i am trying to deploy an app tht i created with VueJs for front and node js et SQL for the back…i have no idea how to do tht plz help

  4. Followed all the steps in the tutorial, running heroku local returns an error

    ENOENT: no such file or directory, open ‘Procfile’

  5. hi, im deploying for the very first time and my question is regarding scripts which you mentioned in “Create MySQL table on ClearDB” section.. so where do i have to write those script??
    for previous steps i have been using terminal but in this step it gives this error
    “mysql : The term ‘mysql’ is not recognized as the name of a cmdlet, function, script file, or operable program.”

  6. Hi! I have my doubts in where to write the SQL script for creating tables and how does this work with models and controllers for a web app?

  7. Wonderful work! This is the kind of Node.js Deployment that are meant to be shared around the internet.
    Thank you so much!

  8. Howdy! Would you mind if I share your blog with
    my twitter group? There’s a lot of people that I think would really appreciate your content.
    Please let me know. Many thanks

  9. You really could have mentioned at the start that this only works with a verified Heroku account, for which you need to provide your credit card information to Heroku.

  10. Thank you very much ,
    Many problems solved by following your tutorials .
    I appreciate your efforts,
    God bless you.

  11. May I know how to insert array in MySQL?

    ‘INSERT INTO customers (name, number, sales) VALUES ?

    doesn’t work.

    “ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘NULL’ at line 1”

    1. Hi, you should use:

      INSERT INTO customers (name, number, sales) VALUES (name_A, number_A, sales_A), (name_B, number_B, sales_B)
  12. Hey, I really liked the way you did it. Just one thing how can we import .sql file?

  13. Greetings! I’ve been waiting for the way to deploy MySQL on Heroku. Now I can do it. Thank you so much.

  14. Thank you so much for these steps.
    Just did all the steps as it is and boom it’s done.

Comments are closed to reduce spam. If you have any question, please send me an email.