Welcome to part 2 of my tutorial the previous story finished with me mentioning the npm packages you will need to install, there will be another package you will need to install another for commnunicating between the database and API.
npm install mysqlThis is a node.js driver for “mysql” which basically means you can run queries, create connection pools and do all sorts of other things with this package when connecting to a database. We will be using this to make queries to the database we will make on a Heroku app.
Once you have installed this we will go on and create a Heroku app and then attach a database to this app. You will need to go to this link here and create an account with Heroku if you haven’t already.
I believe when i signed up to Heroku in order for me to have 5 or more apps on there I had to enter billing information but if you dont plan on using heroku that much then i wouldnt worry about this.
Once you have created your account and logged in I’m hoping you will be at your own personal heroku dashboard, if so you should see a button on the top right labelled “New”.
Click on this and then click “create new app”.
Then a new screen will appear when you name your app. Give your app a name of something like “testusersignin”. Then click on “create app” you will now be brought to a new screen which should have various tabs such as “Overview”, “Resources” and “Metrics” and a few more tabs.
Click on the “Resources” tab and you should see a search bar.
Now in the search bar enter “mysql” you should see an option named “ClearDB MYSQL” click on this, a popup should now show, click on the “submit order form” button (make sure it says “free” next to ignite as if people in the future read this article that “free” part may change)
We now have a mySQL database connected to our heroku app. Now that you have been brought back to the dashboard click on the “Settings” tab and click on “reveal config vars” you should see a value with a key. Config vars are one way of keeping sensitive information secure and away from hardcoding sensitive information within an app.
The “clearDB” database string will look something like below
mysql://user:password@hostname/databasename?reconnect=true
The config variable has various pieces of information necessary for you to login to your database my “MySQL workbench”.
The combination of letters and numbers after the “mysql://” and before the “:” is the database username.
The digits after the “:” and before the “@” is the password.
The link that ends with cleardb.com is the host of the database.
The “heroku_” value is the name of the database.
Once you have got that info please open the “MySQL workbench” application you hopefully downloaded and installed earlier.
On the home screen you will see a “+” button, click on this.
A popup should now show with various options such as “hostname”, “username” and “password”.
You will now need to enter the information you have in the config vars into these text boxes and click “Ok”.
The connection name can be anything.
The hostname is the “cleardb.com” (remember to only copy the link from AFTER the “@”.
The username is the group of characters after the “mysql://” but before the “:”
The MySQL password will be the numbers before the “@” but after the “:”.
To store the password you will need to click “Store in Keychain”.
Leave the port the same.
Once you are confident with the information you have entered you can click “Test connection” and it may give a warning about “MySQL version” just click the “Ok” or “Continue” or whatever it is labelled and hopefully you should get a popup saying connection successfully made like below.
I now have access to the sql editor and can start creating tables, stored procedures and functions!
Ok now lets get on with creating a table, our table will be a “Users” table. To create a table click on the arrow to the left of our database name and a few options should dropdown below it including “Tables” and “Views”.
Right click on “Tables” and click on “Create Table” a table wizard should now show. Name the table “users”.
Make sure you have added the below columns. This is all we doing for this tuorial.
Now click on the “Apply” button on the bottom right and this will create the table.
Ok so now we will go back to our npm project we created earlier and this will be used for our API.
Create a Javascript file in the root of your project folder called “index”.
Inside of this file we will require the necessary packages, create an instance of the express web server package so we can perform different types of request such as GET.
const express = require('express');const jwt = require('jsonwebtoken');const mysql = require('mysql');const bcrypt = require('bcrypt');const bodyParser = require('body-parser');const app = express();const PORT = process.env.PORT || 3000;app.use(bodyParser.json());app.use(bodyParser.urlencoded({ extended: false }));app.get('/',function(req,res){res.send({message:'Default route'});});app.listen(PORT,()=>{console.log(`Listening on PORT: ${PORT}`)});
We have also created a variable called “PORT” which will be equal to the “process.env.PORT” environment variable value or 3000.
We need to set this as when publishing to Heroku they set the PORT environment variable for you so it will use the environment variable present else use port 3000.
We now also need to use the “body-parser” package to parse all incoming requests middleware with json that can accessed via req.body.
We have also created a default route to show for our API and added a “listen” which is a method that binds and listens for connections on a specified port.
The final step we have to do to be able to run this project on local is go to the “package.json” file and add a “start” command of “node index.js”. Once this has been added we can run “npm start” in the terminal to run the project.
If you have postman you can now view the default route of the project by entering the url http://localhost:3000/
In the next tutorial we will go through creating the MySQL connection pool to successfully communicate with the database we have made using our API, we will also go over publishing the API to Heroku and using axios in our React Native app to use the newly published API routes to signup to our app ;)
Thank you very much again for reading this article and don’t forget to like/clap the article if you found it useful!
Check out my blog — https://cowboycode.co.uk/
