Make a NodeJS API with mySQL
Today we'll be making an API to serve JSON data using NodeJS, Express, and MySQL. The example API we'll be building will serve cannabis strain data using the open source Kushy dataset. If you need example data, you can download it from the Github and import the SQL files to your DB.
I'll walk you through the process of installing dependencies, the structure of our app, and how to build it from scratch - step by step.
This guide assumes you have basic knowledge of mySQL (and ideally a database - either on your local server, LAMP, remote URL, anywhere), and that you've at least installed Node on your computer. and used NPM before.
Installing the dependencies
In order to create the API, we need to use a few different libraries to make it happen. This is where things get opinionated.
We're going to use Express as our primary framework. If you're not jiving with this, you might want to try Koa or hapi (and another tutorial, cause we're jumping on the Express train).
Production
npm install express express-rate-limit cors helmet mysql --save
We'll be installing the following packages:
- express - MVC for creating Node sites
- express-rate-limit - Allows for rate limiting of API
- cors - Cors will allow you to serve the API remotely
- helmet - Secures your Express app with HTTP headers
- mysql - Connects and interacts with MySQL through Node.
Development
npm install --save-dev nodemon
Nodemon is used for hot reloading the server in development. Whenever we make a change in the code and save, if nodemon is running, it'll restart the Node server with the new code.
App Structure
Our app is structured as MVC, or Model View Controller. The model is the connection to the MySQL db. The view are the routes we connect to and display JSON data (like yoursite.com/api/users/). The controller are the functions that get data from the model and feed it to the view.
Development Server
Our 'development server' is Node. Isn't that convenient?
Let's setup your package.json. Under the scripts section, we'll set the start script to run our server.js file and set the port to 4200 (PORT=4200 node server.js). We'll also set the dev script to run nodemon, which will allow hot reloading in development. It should look like this:
Now you can run the server using npm start in Terminal/Command Line. That'll spin up the Node server and run Express. Or you can enable hot reloading for development using Nodemon by running npm run dev.
Hello World
Lets set up the server to a working state. We'll create a file called server.js in your project root, include Express and it's router, and print out a 'Hello World' style statement to website. You can find a version of this tutorial on the Express site. My version is modified to use routes, instead of a direct printout:
Model
Now let's connect to our MySQL database and start pulling information to feed into Express. Create file in your models folder called dbconnection.js:
We define our variables that we want to query for, and execute the SQL statement using the query function. This returns a callback function, which has a object with results and another for any error reporting.
This MySQL package follows the same kind of principles you see from PDO in PHP, you use prepared SQL statements. That's when you never directly insert your variables into a SQL statement, you use placeholders like ?? and ? to represent spots you want escaped variables like `table_name or'value'`. You can find more query examples here.
Now that we have a way to grab data, let's setup our routes to request it.
Routes
Routes tell the server what to show when you access certain parts of the site. For example, if you wanted to have an 'About' page located at http://yoursite.com/about/, you'd have to setup a route for /about/. The route would tell the server, when someone requests /about/, we give them a response (like HTML).
We'll be creating a route file that shows the user welcome text when they come to the site (like the Hello World example). And we'll create another route for accessing strain data.
Let's do it!
The Route
Create a new file in your routes folder called index.js and add the following code:
We create a function that accepts the Express app variable. Inside the function, we import our controllers, which will actually display the data. Then we use app to assign the routes.
For the site index, we go with app.use(), which pumps out HTML. For the strains, we use app.route to send data to any GET POST request.
Now the server is programmed to use getAllItems() from the strain controller when we visit http://yoursite.com:4200/strains/. If we visit there now, we'll get an error. So let's give it something to show.
Controllers + GET Data
We need a controller that pulls data from the model (MySQL DB), converts it to JSON, and returns it to the route. You could just create a single function that queries the DB. But we'll create a Class, that way we can have multiple functions (for different routes in the future). Type or copy paste the following:
The gist breaks down each step. We basically do a SQL query and we print results or error. We use res.json() to send the JSON to Express.
POST/UPDATE/DELETE?
We've handled the GET part of the POST request, but what about sending data or deleting? It's simple with the Express framework:
We assign different functions to the get, put, and delete routes. In the put and delete functions, we use req.params.rowId to pull up the :rowID, and do a SQL statement to delete them. And ideally -- authenticate the request somehow (password, OAuth, something).
Production
You can run this server in production, but you'll notice one major flaw: once it crashes, it's down, and the API won't work until you manually restart the server command line-style. We solve this problem by using a process manager.
We can either use a server to deploy our app like Heroku (which is a cloud-based host with built-in process manager), or we install our own process manager on the production server. We'll cover Heroku another time.
I use PM2, but there are other options. I'll be covering PM2 here. There's a quick start guide on the PM2 site, you'll find more details and useful commands there.
This requires SSH access to your production server. If you don't know what that is, I'd contact your host and see if it's available.
Upload your project
Upload the node project to your server. You can copy the node_modules folder, but it's recommended your run npm install in your project folder. The local node_modules folder will contain dev dependencies the production server might not need.
Installing PM2
You have to install PM2 globally, as we'll be using across the entire server.
npm install pm2 -g
Quick Start
We can quickly spin up our app right away using the following command in the project folder:
pm2 start app.js
But let's create a configuration file to pass variables to the app, like PORT=4200 so our app knows it should run on that port. It's basically like the package.json script earlier, but PM2 uses a different structure.
Create your ecosystem file
Create a file called ecosystem.json in your project root (where server.js is):
Run the server!
You're good to go! Run this script in your project folder:
pm2 start ecosystem.json --env production
Check sever status!
How do we know it's running? Did it crash? Where's my console.log?! -- No worries! PM2 stores it's data in logs, which we can access with the following shell scripts:
pm2 show kushy-api- Show server info and stats.pm2 logs kushy-api --lines 50- Show last 50 lines of server logs
Change kushy-api to the app name you specified in your ecosystem file.
Conclusion
You can make any kind of API with this. As much as I have space in my heart for PHP, once you understand Node and Express, it's insanely easy to make an API. It feels more natural than using libraries like Slim in PHP.
If you have any questions, feel free to hit us up Twitter.
Hope that helps,
Oscar
Keep Reading
