How to build a Node.js API using Postgres, Lambda and API Gateway

I’ve found it really hard to find good examples on the internet on how to build an API using Node.js and Postgres on Lambda and API Gateway so I’ve decided I’ll make my first blog here on Medium about it.

I hope you enjoy it!

So first of all you will need Node and npm. Next of all open up the terminal if you are on a mac and go to the place where you do all your Dev work and create a new directory. In there, using your favourite text editor of choice (I’m currently using Atom for this sort of thing), create a new file called packages.json and paste in the following:

{
 “name”: “node_postgres”,
 “version”: “1.0.0”,
 “description”: “node postgres api”,
 “main”: “index.js”,
 “scripts”: {
 “test”: “echo \”Error: no test specified\” && exit 1",
 “deploy”: “ — zip-file fileb://Lambda-Deployment.zip”,
 “predeploy”: “zip -r Lambda-Deployment.zip * -x *.zip *.log”
 },
 “keywords”: [
 “postgres”
 ],
 “author”: “Peter Hanssens”,
 “license”: “ISC”,
 “dependencies”: {
 “pg”: “^6.1.2”
 }
}

Then create a file called index.js and paste the following content (remembering to update your connection string and the select statement):

console.log(‘PostgreSQL GET Function’);
var pg = require(“pg”);
exports.handler = function(event, context) {
var conn = “postgres://username:password@hostname/databasename”;
var client = new pg.Client(conn);
client.connect();
//var id = event.id;
console.log(‘Connected to PostgreSQL database’);
var query = client.query(“SELECT column from table;”);
query.on(“row”, function (row, result) {
 result.addRow(row);
});
query.on(“end”, function (result) {
 var jsonString = JSON.stringify(result.rows);
 var jsonObj = JSON.parse(jsonString);
 console.log(jsonString);
 client.end();
 context.succeed(jsonObj);
});
};

Once you’ve updated your connection string and select statement, hop back into the terminal and run the following commands:

npm install
npm run predeploy

Now that you’ve done that, jump into the AWS console, click on Lambda and then click on “create a Lambda function”.

Then click on create a blank function:

Then click next (no need to configure the trigger):

Then give your function a name. On top of that you will have to option to upload from a zip file which we created earlier running the predeploy command:

Once that’s done, leave the index.handler as is and chose either an existing role or a new role depending on whether you have created a lambda function before:

Click next and create the function.

Now it’s time to hop over to API Gateway and create an API, giving it a name and a description:

Then you will need to go to “Actions” and “create a method” — select “GET” from the dropdown and hit the tick. Once there, select the region you made the lambda function in and in the box that appears begin typing it’s name and it will appear from a menu:

Once that’s done, enable CORS and then deploy the API:

And that’s it, you should now have an http endpoint which you can use to integrate into you website or wherever else you’d like to use it.

Hope you found this useful — please feel free to leave me feedback on anything that I’ve posted here.

Thanks,
Peter