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.
- Building a Mobile App in 10 Days with React Native
- Building a Node API with Express & Google Cloud SQL
- Easy React Native Authentication: Auth0 (coming soon)
- React Native: Barcode Scanning & Autocomplete (coming soon)
- Using AirBnB’s Lottie Animations with React Native (coming soon)
- Deploying a React Native App to the App Store & Google Play (coming soon)
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:
- A database
- A Node.js API
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
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:
- Create a database
- Set up a user that your API can use to connect to the database
- Add your IP address to the authorization whitelist
Click on your database instance then using the tabs:
- 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:
Create a few rows to populate the database with dummy data. Personally, I used information from Lord of the Rings & Harry Potter.
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
Note: Hit enter to skip everything, except change entry point to
npm install express --save
Boom. You did it! Now the fun begins. Open app.js in the best editor on the market… cough… VS Code… cough.
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.
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.
- .gitignore (if you don’t have one already)
.gitignore and add a single file name at the top,
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
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
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.