Create a Robust API (from the ground up) in Five Minutes with Node-RED

February 10, 2019

Node-RED is a very powerful tool that can make robust workflows/scripts out of visual flowcharts.  Today, we're going to use Node-RED to connect to a Postgres database and provide users with an API endpoint.  The following ready-to-bake recipe will have your API up-and-running within five minutes.  This API will query the database and return the result(s).  Let's get started!


In order to proceed, you must have Node-RED and a Postgres database running.  If you do not, here's how to spin them up with Docker:

Node-RED Container

docker run -d -it -p 8888:1880 -v /lun0/AppData/sandbox/node-red:/data --name="Sanbox-Node-RED" nodered/node-red-docker:v8

PostgreSQL Container

docker run -d -it -p 8889:5432 -v /lun0/AppData/sandbox/db:/var/lib/postgresql/data --name="db" postgres

Some things to keep in mind:

  • Docker port format goes outside:inside (ex. port 1880 is where Node-RED runs and port 8888 is the port that I'm exposing on my host).
  • We're going to map some local folders to our containers so we can have persistent data. For me, I'm using the following path on my local filesystem: /lun0/AppData/sandbox/$container.
  • Permissions for Postgres can be kind of odd whilst using Docker. You can fix that by: chmod -R 777 $postgres_local_path (ex. chmod -R 777 /lun0/AppData/sandbox/db).

If you'd like to run through this tutorial with the exact data that we're using, here is the database: node-red-fast-api-funofficesupplies_db.sql (via GitHub).

Creating the API

First, let's browse to our Node-RED instance: http://docker_host:port (ex. http://docker:8888).

Clean install of Node-RED

At the top right of the page, click the hamburger menu button and then select Manage palette.

Node-RED: Options > Manage palette

In the new window, click on the Install tab and then search for postgres.  Click install for the node-red-contrib-postgres item.

Node-RED: Options > Manage palette > Install > node-red-contrib-postgres

A confirmation dialog box will appear, click Install.  Once the item has installed, you can close the palette.

Node-RED: Options > Manage palette > Install > node-red-contrib-postgres > Confirmation

Drag the http node into your flow.  Double click on the node (once placed) and set the method and URL of your choosing.  For this example, we'll be calling via GET at /api/inventory/all/.

Node-RED: Creating http node (GET)

Next, drag in a template node and double click on it.  Set the template field to your SQL query.  For this API call, we're going to select all items from the database and see how many are in stock.

SELECT "SKU".description AS "Item", 		
	"INVENTORY".is_available AS "Quantity Available" 
    INNER JOIN "SKU" ON ("INVENTORY".sku = "SKU".id);
Node-RED: Creating template node (our SQL query)

Next, we'll drag a Postgres node into our flow.  We'll set the IP, hostname, or FQDN, port, database, username, and password.

Node-RED: Creating postgres node

Once you've clicked Update, be sure to check Receive query output and then click Done.

Node-RED: Creating postgres node

Lastly, add an HTTP Response object.  We're finally ready - go ahead and press the big, red, Deploy button.

Node-RED: Deploy

Now, in your client of choice, call your API.  You should see your results!

API Call Response

Advancing the API

Pretty cool, eh?  Let's say that you're looking for information regarding a particular SKU.  Here's how to pass in data to our API from the client.

First, highlight the first two nodes of our previous flow and copy/paste them below.  Double click on the first node and change the API path and name.

Cloned Flow

Now, double click on the template node labeled Query.  We're going to insert our new query.  Pay close attention to the end of the query where we insert our GET variable's value.

SELECT "SKU".description AS "Item", 
	"INVENTORY".is_available AS "Quantity Available"
    INNER JOIN "SKU" ON ("INVENTORY".sku = "SKU".id)
    WHERE "SKU".id = {{payload.sku}};
Node-RED: Updating template node (our SQL query)

At this point, we're almost done.  Connect your new query's output to the postgres node from the last API call.  No need to waste nodes!  Now, click Deploy.

Sharing nodes from our first API call

Browsing to your API endpoint should now look like this:

API Call Response


And that's it!  A robust API in five minutes using Node-RED!  Besides two SQL queries, no code was written.

Additional Reading

©2024 Tyler Wright