Storing & Graphing IoT Data With PostgREST

Overview

This page aims to illustrate and document how you can use PostgreSQL and PostgREST to store and access data generated by sensors connected to micro-controllers or other IoT devices. Before we dive in, I'll introduce the projects and utilities that we'll be using:

• PostgreSQL - A widely used, open source database system. It's very extensible and has many plugs and other utilities built "on top" of it. Client applications connect to it via a network port and send SQL commands to create, read, update, and delete data.
• PostgREST - A standalone web server that turns your PostgreSQL database directly into a RESTful API. The structural constraints and permissions in the database determine the API endpoints and operations. This web server allows us to create, update and access our data with HTTP requests from micro-controllers and other endpoints.
• NGINX - A widely used web server with reverse proxy, load balancing, and many other capabilities. This project will use it to host static HTML and JS files that can be viewed in a browser. It is good practice to use Apache or NGINX as a reverse proxy "in front" of other web server utilities like PostgREST.
• ApexCharts - A Javascript library for making charts and graphs. We'll use this to graph data that we read out of our database.

Hardware

The end goal of this project is to host the database and web services on a Raspberry Pi SBC. Data will come from temperature / humidity sensor(s) connected to micro-controller(s) running CircuitPython based scripts to collect and POST it to the web services. During testing and development phases some components may be easier to run locally on a PC or other computer. In my case all testing and development was performed on Ubuntu Linux PC. It's possible that they could work on other systems or OS's but won't be directly documented here. If you're having trouble getting it to run on the local PC you can try just working directly on the Raspberry Pi. 

This project is general enough that there are several different pieces of hardware it could run on. In my case the specific hardware in use is:

• Raspberry Pi 3 B+
• Adafruit Feather ESP32-S3 TFT
• BME680 Breakout w/ Stemma

Setup PostgreSQL

Method 1: Docker

The official PostgREST tutorial skips the full PostgreSQL setup opting instead for a quick / easy setup running inside of a Docker container. If you already have or are familiar with Docker then this route is quite straight forward. The following command will create and launch a PSQL container:

Be sure to replace mysecretpassword with a different and more secure password, and keep the password handy because we'll need to use it during the configuration. 

The PostgREST tutorial shows this command running as sudo, but on my system that was not working and I was able to run it successfully without sudo so I recommend trying without first and if you have problems then try with sudo.

You only run the above command once when first creating the container. After it's been created then you can stop and start the container with the following commands.

This command will map the docker containers default PSQL port 5432 from inside the container to the hosts port 5433 outside the container. So on the host PC (or RasPi) any traffic flowing into port 5433 will forwarded into the container where PSQL will "see" the traffic as though it came from port 5432. The specific numbers aren't super important as long as your configurations all match. You could stick with the default port 5432 and forego the mapping, but it could interfere with any other running instances of PSQL on your machine, this mapping allows our new iot_db instance to co-exist alongside any other PSQL instances and not interfere with each other, so long as each one uses a different port on the host machine.

Stopping the Container

This command will stop the container. If you used a different name than iot_db then swap in your container's name.

Starting the Container

This command will start the container. If you used a different name than iot_db then swap in your container's name.

Once the container is running, you're ready to move to the next steps. The container must be running any time that you want to use the database or web services we'll be building on top of it. If nothing is working double check that you remembered to start the container.

Method 2: Direct Installation on the Host

If you don't have, or don't want to use Docker, you can also install PostgreSQL directly onto the host PC or Rasperry Pi. PostgreSQL is distributed conveniently via Linux package managers. On Raspberry Pi or Ubuntu PC it can be installed with this apt command.

We'll use the NGINX webserver to host our HTML and JS files later. NGINX can be installed with apt as well.

When you install PSQL in this manner it will set up everything needed to auto launch itself when the system boots up, so once installed you can move on to the next steps.

Download and Unpack PostgREST

Access the postgrest release page here: https://github.com/PostgREST/postgrest/releases and download the latest version that is labeled for "ubuntu-aarch64". At the time of this writing is it:
postgrest-v12.0.2-ubuntu-aarch64.tar.xz 

Unpack the downloaded archive by running this command:

After unpacking you should left with an executable application named `postgrest`. When the time comes to launch it you do so with a command like this:

Setting Up Database & Tables

The first step is to create a database to store everything for this project inside of. In order to interact with the PostgreSQL server you need to switch users over to the postgres user and change directories to it's home directory.

Once you have switched to postgres user you should see a prompt like this:

To create the database use a command like this. Note that you can choose whatever name you'd for your database. I chose iot_db

Next we need to get into the psql shell. To access it make sure you have have switched user to postgres and then run:

Once you've entered the psql shell you should a prompt like this:

To verify that your database was created successfully in the previous step you can run the list command by typing \l and then pressing enter. It should output a table containing the databases that exist inside of your system. You may need to adjust your terminal or font size to make it easier to understand.

The first column shows names of the databases. They're in alphabetical order so look for the name you selected in the list and verify it exists. If it doesn't go back do the createdb step.

Create Schema

The next several steps are modeled after the ones in the official PostgREST Tutorial 0: Getting It Running The official tutorial uses postgresql inside of a docker container, but the commands used are the same ones we will use directly on the RasPi. 

The next step is to create the schema inside of the database. First connect to the specific database we created. Then run create schema api;

After \connect iot_db; The prompt should change to include the database name e.g. iot_db=#

If it's successfull you will see CREATE SCHEMA printed out. You can validate it by using \dn to print a list of schemas and look for api in the list.

Create Table

I like to stage all of the sql commands used into .sql files locally on my computer and then copy/paste them into the psql shell prompt on the Raspberry Pi as needed. That way I can make tweaks to my configurations and even check them in to version control if desired.

The SQL to create a table is like this:

This SQL code creates a table and specifies 5 columns to go into the table:

• id - an auto incrementing number that serves as the primary key
• value - a number value that will hold our data readings
• tag - an optional text value that can be used to categorize data however you'd like
• type - a text field that declares the type of this data value e.g. temperature, humidity, etc...
• timestamp - a datetime field that automatically sets to the time a reading is saved

Feel free to add or modify these columns as needed if you are storing different types of data, or would like to associate more meta information with it. Refer to the PostgreSQL documentation for a comprehensive list of data types.

You can also change the name of the table if you'd like. In my case I chose datavalues for the name of the table and it's inside of the api schema so the full name is api.datavalues

This SQL code needs to be pasted into the psql shell prompt and then press enter after pasting it. Be mindful of the semi-colon at the end!

If successful it will print the output CREATE TABLE.

To verify you can run \dt api.* to list tables inside of the api schema and look for the one named datavalues (or whatever you chose if you changed the name.)

Create web_anon User

Next we want to create specific user that the database will use when anonymous web requests come in.

If you've used a different name for your schema or table then substitute them into the appropriate places.

These commands can be pasted all at once, or individually one at a time. 

If successful these commands will output:

We also want to create a dedicated role (role and user are synonyms) in the postgresql system to use for connecting to our database instead of using the highly privileged postgres. 

The following SQL commands will create a dedicated role named authenticator and grant it permission to change into the web_anon role.

Change the password to something secure and keep it a secret. Your password can contain special characters, but some characters may require escaping when you go to enter them in. In particular I believe the colon character : would need to be escaped if used.

If successful the commands will print this output:

That's it! 🎉 Our database is ready to go with everything it needs to be a minimal data API.

To exit out of the psql shell type \q and press enter. That will drop you back out to the linux shell prompt under the postgres linux user.

Press Ctrl-D to break out of the postgres linux user and go back to the base user like pi or whatever you setup when you imaged the pi. If in doubt, you can always close the connection and SSH in again to a new session.

Running PostgREST

To run the server you'll need a config file that specifies the location and connection details for the server as well as a few of the roles that PostgREST will use. Here is a copy of the .conf file as it would need to be for the names that I've used. You can update it to your own names as needed.

Save a copy of this file under a name like datavalues_iot.conf (or whatever you want).

To run the server execute the ./postgrest command and pass in the path to the .conf file as an argument. 

Once that is running you should be able to access the PostgREST server by openeing a browser on a PC connected to the same network as the RasPi and navigating to the RasPi's IP and port 3000 e.g.

http://192.168.1.116:3000/

If everything is working successfully you will see a page full of JSON data which contains OpenAPI specification copy of documentation for the API.

You could also navigate to http://192.168.1.116:3000/datavalues (or whatever your IP and table name are) to fetch any datavalues stored in the database. We haven't inserted any data so it will be blank for now.

You can press Ctrl-C in order to exit out of the server and stop it from running.

POSTing Data to the API

By default it's not possible to POST data into our database without authentication. This is a very good practice to use even though it means we need to jump through one more hoop in order to be able to submit data. 

This sections steps are modeled after those found in the official Tutorial 1: The Golden Key from the PostgREST documentation.

I recommend running these commands one at a time.

Run this in psql shell using the database created in the previous sections.

If successful these commands will print these respective outputs:

We need a password to use to secure our tokens. So either think of a nice long secure one, or create one using python with this command.

It will generate and print a secure password.

We need to copy that password and we're going to paste it into a new line in the .conf file.

Use nano or your favorite text editor to add a new line in the .conf file like this: