Building a Node API with Express and Google Cloud SQL

Note: This is an ongoing series of blog posts detailing how we made an iOS & Android mobile app in 10 days. Feel free to follow me here or on Twitter to get updated when the other articles are published.

  1. Building a Mobile App in 10 Days with React Native
  2. Building a Node API with Express & Google Cloud SQL
  3. Easy React Native Authentication: Auth0 (coming soon)
  4. React Native: Barcode Scanning & Autocomplete (coming soon)
  5. Using AirBnB’s Lottie Animations with React Native (coming soon)
  6. Deploying a React Native App to the App Store & Google Play (coming soon)

Getting Started

In our last blog post, we described how we started building our React Native iOS & Android app. At a certain point, you’re going to want to stop writing dummy data directly in your code base and get some infrastructure in place to store things in a database.

Google Cloud was offering $300 in credit as a free trial, so we decided to build there. We need a couple of things:

  1. A database
  2. A Node.js API

Google SQL

Once you’re set up with a Google Cloud console, setting up a SQL database is pretty easy.

Steps: Hamburger menu in the top left → SQL → Create Instance

Google Cloud Console’s UI is sexier than AWS, but sometimes the lack of information sucks.

Choose your instance type. I’d suggest the development tier (you can upgrade later). Set your root password, etc. Fill out all the things.

Then once you’re up and running you need to do three things:

  1. Create a database
  2. Set up a user that your API can use to connect to the database
  3. Add your IP address to the authorization whitelist

Click on your database instance then using the tabs:

Tabs for days
  • Databases → Create database
  • Users → Create User Account. Set it for “Allow any host(%)” for now.
  • Authorization → Add Network. Use whatip.me to copy / pasta your IPv4

Now that you have an instance, a database, a user with username & password, and you’ve whitelisted your IP address, you need to create a table.

I’m a fan of MySQL Workbench but you can use any database editor you want.

Connect to your database using the user credentials you create earlier and create a books table with at least 3 columns:

  • id
  • bookTitle
  • userId

Create a few rows to populate the database with dummy data. Personally, I used information from Lord of the Rings & Harry Potter.

Express API

Throughout this project we’re basically doing everything in JavaScript, hence why we chose Express. You can build your API any way you want. You could even use a service like Contentful or Firebase if you wanted. We wanted to avoid vendor lock-in and build something we could scale without breaking the bank.

Allie Brosh is the coolest person on the planet.

If you haven’t built an Express API before, don’t worry. It’s pretty simple.

You can follow the directions here but basically:

  • mkdir myApi && cd myApi
  • npm init

Note: Hit enter to skip everything, except change entry point to app.js

  • npm install express --save
  • touch app.js

Boom. You did it! Now the fun begins. Open app.js in the best editor on the market… cough… VS Code… cough.

Blank slate. So beautiful. So exciting.

Let’s start with something simple. We need to get our API server up and running and listening on a certain port.

Save. Back to the terminal.

  • npm install
  • node app.js

You’ll see a message in your terminal and if navigate to http://localhost:3000 in your browser, you’ll see “Hellow World!”. Woot.

Let’s pick up the pace. Back to the terminal, Ctrl + C to stop your Node server. Now you’re going to npm install --save the following packages:

You’ll create the following files :

Pro Tip: Use touch in your terminal. It can be a huge time saver when you’re used to it.

  • database.js
  • .env
  • .gitignore (if you don’t have one already)

Edit your .gitignore and add a single file name at the top, .env

Now edit your .env file to add your secret connection strings. Don’t share these with anyone. The reason we added .env to our .gitignore is so that we don’t accidentally check in our connection strings to version control. That can be bad news.

It should look something like this:

DB_HOST is the Primary IP when looking at the Google SQL Overview. DB_DATABASE is the database name you created earlier, DB_USER is the username and DB_PASS is the password.

Note: If you decide to deploy to Google App Engine (GAE), you’ll need to add a DB_INSTANCE_NAME with the instance name in the Overview tab.

Now, we set up our database.js

We have two connection methods here. On the top, commented out, is the format you’re going to need when deploying to GAE. The bottom format is what you’ll use when connecting while developing locally.

With all that take care of, let’s create our endpoint logic. Here’s what our app.js is going to look like:

Now if we type in node app.js in the terminal, we should see results when going to localhost:3000/books/12345678

Express see’s that we have a GET request hitting our route, runs the query against our Google SQL database and returns the result as JSON. We can now consume this API in our mobile app, our web app, or wherever we want!

As we’ve added more and complexity with querying books, adding books, editing books, etc. our API has gotten a lot bigger. We decided to organize routes in a routes folder and individual files for each /books type of route and a separate controllers folder where we put our MySQL query logic.

The world is your oyster! You can do it however you want. We didn’t cover deploying to GAE, but we still have quite a bit of development to do before we’re ready for that. This should get you started.

The other important thing we added was the ability for the API to receive an Auth0 access token, verify the token’s authenticity, and then decode the JWT and use the userId inside to make sure we’re returning data to the right person. However, that’s content for our next article. Stay tuned.