Make a NodeJS API with mySQL
Tags: node, api, mysql, javascript
Published: 2018-03-19 19:09:23

---
title: Make a NodeJS API with mySQL
published: true
tags: nodejs, api, mysql, javascript
canonical_url: http://stayregular.net/blog/make-a-nodejs-api-with-mysql
cover_image: http://stayregular.net/content/2-blog/20180306-make-a-nodejs-api-with-mysql/nodejs-mysql-api%400.4x.jpg
---

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](https://github.com/kushyapp/cannabis-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](https://www.apachefriends.org/index.html), remote URL, anywhere), and that you've at least [installed Node on your computer.](https://nodejs.org/en/download/package-manager/) 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](http://expressjs.com)** as our primary framework. If you're not jiving with this, you might want to try [Koa or hapi](http://nodeframework.com/index.html#mvc) _(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](https://github.com/helmetjs/helmet)** - Secures your Express app with HTTP headers
- **[mysql](https://github.com/mysqljs)** - Connects and interacts with MySQL through Node.

### Development

`npm install --save-dev nodemon`

[Nodemon](https://github.com/remy/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:

<script src="https://gist.github.com/stayregular/85f56a199d1080dc689a05d2ec5f46dc.js"></script>

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](https://expressjs.com/en/starter/hello-world.html). My version is modified to use **routes** , instead of a direct printout:

<script src="https://gist.github.com/stayregular/f4942b0bcf98252a65300c83a261c7b3.js"></script>
## 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`:

<script src="https://gist.github.com/stayregular/4cde72889f1405ef1e1db1f16435b43a.js"></script>

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](http://php.net/manual/en/ref.pdo-mysql.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.](https://github.com/mysqljs/mysql#preparing-queries)

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/](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:

<script src="https://gist.github.com/stayregular/29450a6614134ea013704ff387735813.js"></script>

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/](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:

<script src="https://gist.github.com/stayregular/f72a153f4dbd67e4826b81656ddda66c.js"></script>

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:

<script src="https://gist.github.com/stayregular/7208540db6fdb3e0bffb67d5eb64df37.js"></script>

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](heroku.com/) (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](http://pm2.keymetrics.io/), but there are [other options](https://expressjs.com/en/advanced/pm.html). I'll be covering PM2 here. There's [a quick start guide on the PM2 site](http://pm2.keymetrics.io/docs/usage/quick-start/), 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):

<script src="https://gist.github.com/stayregular/f70e26385f7baaf63ec9506750d7834c.js"></script>
### 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](https://www.slimframework.com/) in PHP.

If you have any questions, feel free to [hit us up Twitter](http://twitter.com/stayregular420/).

Hope that helps,  
Oscar

* * *

**Keep Reading**

- [Express](http://expressjs.com)
- [How to access RowDataPacket mysql-node.js](https://stackoverflow.com/questions/38133084/how-to-access-rowdatapacket-mysql-node-js)