Getting Started with Google Cloud Functions — Write Rest API to communicate with PostgreSQL

Hi there! This blog covers writing basic API to fetch the user data from the database and send back the response in JSON. Since you already know basics about google cloud functions I will focus only on writing API with database call and also how to check your database from your local machine.

if you are not familiar with google cloud apis I have a blog on it to write some of the basic API here I strongly recommend to go through it once.

Technical Specification

First thing first, you should have a good understanding of javascript and some basic SQL commands (to check data in the database).

We are going to use Node JS to write our API and Postgresql as a database server. So I am assuming you already have You should have npm installed on your local machine and should also have Postgresql installed and have a project created in your google cloud platform console, NodeJS Emulator installed and set up in your local machine.

if you don’t have the project created or setup you can follow this

Before you Begin

• Create a GCP project and a PostgreSQL instance
• If you are using an existing project, make sure your GCP user is an owner of your project or has a Cloud SQL role other than Cloud SQL Viewer. Otherwise, you should set up the proxy using the instructions outlined in Connecting using the Cloud SQL Proxy

Note:- You can always go back to the google cloud console to check your database tables but if you are not the owner of the project then you can connect it from your local database server via Cloud SQL proxy.

In this blog, we are not going to access the database through GCP console but using psql client installed in your local via Cloud SQL proxy. Before we jump into coding let’s quickly cover some of the essential yet basic steps.

PSQL Client Connection

1. You need to login to your service account and then need to enable the Cloud SQL API here
2. You can download the Cloud SQL proxy according to your OS here. I am using the mac 64-bit version, which you can download from this link by pasting it into your terminal.

You can download and keep it at any specific location you wantcurl -o cloudsqlproxy #Now we need to give executable permissionschmod +x cloudsqlproxy

3. If you don’t have a service account created or need to select one for . the project you can click here.
4. Let’s start the proxy now,
   Copy your instance connection name from the Instance details page.
   For example: myproject:us-central1:myinstance

# Start the terminal (Where you have kept downloaded proxy)and paste this and replace instance connection name with your instance name./cloud_sql_proxy -instances=<INSTANCE_CONNECTION_NAME>=tcp:5432  

5. Since we have started the proxy, we are all set to create our first table where we will keep the information about users. I will name the table “User”. So let’s start our Postgres server in another terminal.

Note:- Please keep the proxy server running and open PostgreSQL in another terminal.

6. Now, just open the terminal and paste this.

# replace port number (the port number you have given to postgres at the time of installation of PostgreSQL server.) and replace dbname with database name given while creating PostgreSQL Instance in GCP Console.psql "host=127.0.0.1 port=5433 sslmode=disable dbname=<DB_NAME> user=<USER_NAME>"# for example  
psql "host=127.0.0.1 port=5433 sslmode=disable dbname= postgres user= postgres"  

7. We are now all set to create the “User” Table.

CREATE TABLE IF NOT EXISTS public.user (id SERIAL NOT NULL,name character varying(300),email character varying(300),PRIMARY KEY(id));

Now we have the database connection and table created it’s time for code.

Writing Database API

Before we start, Let’s quickly install Postgres support library to establish the connection between our function and PostgresSQL Instance.

npm install pg  

So we have the dependency install so let’s write the API.

const pg = require("pg");  
var pgConfig = {user: <dbUser>,   // replace it with your user name password: <dbPassword>, // replace it with your user passworddatabase: <DATABASE-NAME>, // replace it with your db namehost: <HOST-NAME>       // use host while deploying the function//PORT: 5432,           //use port number while deploying in local};  
var pgPool;if (!pgPool) {pgPool = new pg.Pool(pgConfig);}//database function to query tablelet Db_updateUser = (userData) => {return pgPool.query("INSERT INTO userconsent(email,name,created_date) " +" values($1,$2,now())",[userData.email,userData.name ]);};  
//Helper function to send error as response  
let sendError = (res,errMsg) =&gt; {  
         res.status(500).json({error: errMsg});  
}  
//get the data of user from databaseexports.getAllUsers = async (req, res) => {try {  
let userResponse = await Db_getAllUsers();if(!!userResponse){  
res.status(200).send(JSON.stringify(userResponse));  
}  
    else {  
         sendError(res,'No data available');  
         }  
}   
catch (error) 
         {  
         console.log("error" + error);  
         sendError(res,'Internal Server Error! '+error.message);  
         }  
}//database function to query table 'select with * is not preferable //though'let Db_getAllUsers = () => {return pgPool.query("SELECT * from user");};  

Note:- Hostname is available under your Postgres Instance in your GCP console. Please note here, since we are using cloud proxy, user PORT number only when you connect from local while deploying function remove PORT

The Above code contains four functions in which two helper functions have a prefix (Db_) are for querying the database, sendError() is a helper function to send error and getAllUsers() is our API to retrieve all user data in the database.

There is an API missing to push data into the database which you can create using helper function Db_updateUser same like Db_getAllUsers has done. if you want to receive data in body or param or deploying setup you can also follow this blog for help. If you still face any trouble you can comment below for help.

So without further delay let’s deploy our API.

Deploy the Database API

As we have Node.js emulator installed, let’s run the emulator and deploy the function.

functions starts  

Note:- Always deploy functions from the directory where index.js and package.json file resides. and after running the command you will also see a table of deployed functions in local.

functions deploy getAllUsers --trigger-http  

Once you test it in your local and see everything working fine. just deploy it with public URL.

 gcloud functions deploy getAllUsers --runtime nodejs8 --trigger-http

Just copy your URL and start hit it from postman or browser.

Hurray! We have now REST API which communicates with a database, now you can just make your way to add a feature to the API and create some crazy project. so go ahead and give a try.