Storing & Graphing IoT Data With PostgREST

Last updated August 27, 2024

Save

Overview
Hardware
Setup PostgreSQL
Method 1: Docker
Method 2: Direct Installation on the Host
Download and Unpack PostgREST
Setting Up Database & Tables
Create Schema
Create Table
Create web_anon User
Running PostgREST
Preparing to POST Data to the API
JWT Token
Python Script to POST Test Data
Drum-roll Please... 🥁
Troubleshooting
Sending Real Sensor Readings From CircuitPython
Graphing The Data

Getting Started

Adafruit Playground is a wonderful and safe place to share your interests with Adafruit's vibrant community of makers and doers. Have a cool project you are working on? Have a bit of code that you think others will find useful? Want to show off your electronics workbench? You have come to the right place.

The goal of Adafruit Playground is to make it as simple as possible to share your work. On the Adafruit Playground users can create Notes. A note is a single-page space where you can document your topic using Adafruit's easy-to-use editor. Notes are like Guides on the Adafruit Learning System but guides are high-fidelity content curated and maintained by Adafuit. Notes are whatever you want them to be. Have fun and be kind.

Click here to learn more about Adafruit Playground and how to get started.

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:

Copy Code

docker run --name iot_db -p 5433:5432 -e POSTGRES_PASSWORD=mysecretpassword -d postgres

docker run --name iot_db -p 5433:5432 -e POSTGRES_PASSWORD=mysecretpassword -d postgres

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.

Copy Code

docker container stop iot_db

docker container stop iot_db

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.

Copy Code

docker container start iot_db

docker container start iot_db

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.

Copy Code

sudo apt install postgresql
sudo apt install nginx

1
2

sudo apt install postgresql
sudo apt install nginx

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:

Download File

Copy Code

tar -xf [the_downloaded_file].tar.gz

# e.g. for me:

tar -xf postgrest-v12.0.2-ubuntu-aarch64.tar.xz

tar -xf [the_downloaded_file].tar.gz

# e.g. for me:

tar -xf postgrest-v12.0.2-ubuntu-aarch64.tar.xz

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:

Copy Code

./postgrest

./postgrest

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.

Copy Code

sudo su postgres
cd ~

1
2

sudo su postgres
cd ~

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

Copy Code

postgres@raspberrypi:~$

postgres@raspberrypi:~$

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

Copy Code

createdb --owner=postgres iot_db

createdb --owner=postgres 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:

Copy Code

psql

psql

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

Copy Code

postgres=#

postgres=#

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.

Download File

Copy Code

Name    |  Owner   | ...   
-----------+----------+-----
 iot_db    | postgres | ... 
 postgres  | postgres | ...
 ...

Name    |  Owner   | ...   
-----------+----------+-----
 iot_db    | postgres | ... 
 postgres  | postgres | ...
 ...

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;

Copy Code

\connect iot_db;

create schema api;

1
2
3

\connect iot_db;

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:

Download File

Copy Code

create table api.datavalues (
    id serial primary key,
    value numeric,
    tag text not null,
    type text not null,
    timestamp timestamptz default current_timestamp
);

create table api.datavalues (
    id serial primary key,
    value numeric,
    tag text not null,
    type text not null,
    timestamp timestamptz default current_timestamp
);

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.)

Download File

Copy Code

\dt api.*

           List of relations
 Schema |    Name    | Type  |  Owner   
--------+------------+-------+----------
 api    | datavalues | table | postgres

\dt api.*

           List of relations
 Schema |    Name    | Type  |  Owner   
--------+------------+-------+----------
 api    | datavalues | table | postgres

Create web_anon User

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

Copy Code

create role web_anon nologin;

grant usage on schema api to web_anon;
grant select on api.datavalues to web_anon;

create role web_anon nologin;

grant usage on schema api to web_anon;
grant select on api.datavalues to web_anon;

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:

Copy Code

CREATE ROLE
GRANT
GRANT

1
2
3

CREATE ROLE
GRANT
GRANT

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.

Copy Code

create role authenticator noinherit login password 'mysecretpassword';
grant web_anon to authenticator;

1
2

create role authenticator noinherit login password 'mysecretpassword';
grant web_anon to authenticator;

If successful the commands will print this output:

Copy Code

CREATE ROLE
GRANT ROLE

1
2

CREATE ROLE
GRANT ROLE

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).

Copy Code

db-uri = "postgres://authenticator:YourSecurePasswordHere@localhost:5432/iot_db"
db-schemas = "api"
db-anon-role = "web_anon"

1
2
3

db-uri = "postgres://authenticator:YourSecurePasswordHere@localhost:5432/iot_db"
db-schemas = "api"
db-anon-role = "web_anon"

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

Copy Code

./postgrest datavalues_iot.conf

./postgrest datavalues_iot.conf

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.128: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.128: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.

Preparing to POST 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.

Download File

Copy Code

\connect iot_db;

create role datavalue_user nologin;
grant datavalue_user to authenticator;

grant usage on schema api to datavalue_user;
grant all on api.datavalues to datavalue_user;
grant usage, select on sequence api.datavalues_id_seq to datavalue_user;

\connect iot_db;

create role datavalue_user nologin;
grant datavalue_user to authenticator;

grant usage on schema api to datavalue_user;
grant all on api.datavalues to datavalue_user;
grant usage, select on sequence api.datavalues_id_seq to datavalue_user;

If successful these commands will print these respective outputs:

Download File

Copy Code

CREATE ROLE
GRANT ROLE

GRANT
GRANT
GRANT

CREATE ROLE
GRANT ROLE

GRANT
GRANT
GRANT

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.

Copy Code

import secrets
secrets.token_urlsafe(32)

1
2

import secrets
secrets.token_urlsafe(32)

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:

Copy Code

jwt-secret = "your_secure_password_here"

jwt-secret = "your_secure_password_here"

JWT Token

Next we need to create a JWT Token that will be used to authenticate with the API. We won't go into all of the details about JWT here, but the important things to understand for now are:

JWT allows you store a JSON payload that can contain any data that you want.
JWT Tokens are protected by a secret key. In order to modify the JSON data in the payload, you must have the secret key. If someone tried to "cheat" and make a token without having the real secret key the system would deny access to them.

One easy way to create the JWT Token is https://jwt.io/

To generate the token you need to fill in two fields.

1) The secret password in the bottom blue "verify signature" section. This needs to be the same secret password that you put into the .conf file in the previous step.

2) Set the JSON Payload data to contain a role key that has the value data_value_user. Or if you chose a different username, then substitute it in here.

Copy Code

{
  "role": "datavalue_user"
}

1
2
3

{
  "role": "datavalue_user"
}

As you fill in the fields, the token on the left side of the screen will update automatically. Once you've put in your secret password, and filled in the payload data with the appropriate role key and value then copy the entire token from the left side box. Ensure that you get the whole thing, every character is important!

Python Script to POST Test Data

We should have everything needed to send some test data to the server now. We can use this python script to try it out.

post_data.py

Download File

Copy Code

import json
import requests

jwt_token = 'PASTE_JWT_TOKEN_HERE'

resp = requests.post("http://192.168.1.128:3000/datavalues", headers={
    'Authorization': 'Bearer ' + jwt_token,
    'Content-Type': 'application/json'
}, data=json.dumps({"type": "test", "tag": "", "value": 8.8818}))

print(resp.status_code)

import json
import requests

jwt_token = 'PASTE_JWT_TOKEN_HERE'

resp = requests.post("http://192.168.1.128:3000/datavalues", headers={
    'Authorization': 'Bearer ' + jwt_token,
    'Content-Type': 'application/json'
}, data=json.dumps({"type": "test", "tag": "", "value": 8.8818}))

print(resp.status_code)

Fill in your JWT Token into the TOKEN variable value in place of "PASTE_JWT_TOKEN_HERE".

Change the IP address to the IP of your Raspberry Pi, or you could use localhost:3000 if you run the script on the Pi itself.

I'm doing the test POST from my PC, so I've filled in my Pi's IP address which is 192.168.1.128.

The default port used by PostgREST is 3000, so if you have followed the defaults and setup in this guide then yours will be 3000 too. If you've reconfigured it then be sure to use whichever port you chose in the URL in this script.

Feel free to substitute in different values for "type", "tag", and "value" on the last line if you'd like. Since this is only a test the specific values are not important.

Drum-roll Please... 🥁

Start up the PostgREST server if it's not already running. Restart it if you haven't done so since adding the jwt-token to the config file.

On the Raspberry pi:

Copy Code

./postgrest datavalues_iot.conf

./postgrest datavalues_iot.conf

On the PC (or whatever you want to test POST from):

Copy Code

python post_data.py

python post_data.py

If it is successful it will print simply 201 which is the status code returned by the PostgREST server. 201 is the status code that means:

The request succeeded, and a new resource was created as a result.

Quoted from: MDN Status Code 201

You can further verify that it worked by querying the data from the system with a GET request. To do so, open a browser and navigate to a URL like this one. Replace the IP address with your Raspberry Pi's IP.

Copy Code

http://192.168.1.128:3000/datavalues

http://192.168.1.128:3000/datavalues

If everything is working, you'll see a response that contains JSON data with the test values that were hard-coded into the testing script.

Troubleshooting

If you get some value other than 201 it means that some error occurred.

If you get status 401, that means unauthorized. In our case that means it's likely that the JWT token was not copied correctly, or was generated with the wrong secret password or payload data. Or that the commands shown above to grant permissions to the datavalue_user were not completed properly. So go back and re-run those (it won't hurt anything if they get run multiple times) and try to re-create and then copy/paste your JWT token again. Also double check to ensure that the JWT secret value was put into the conf file correctly.

Sending Real Sensor Readings From CircuitPython

With the test POST complete we're now ready to set up a CircuitPython micro-controller to get some real data from a sensor and send it to our server. I will use an Adafruit Funhouse device which has a DPS310 sensor that can read temperature, and barometric pressure. It also has a seperate sensor that can read humidity, but I'll focus on just the DPS310 for my example code. It should be easy to adapt this code to work with different micro-controllers and/or different sensor modules.

funhouse_dps310_post_data_code.py

Download File

Copy Code

import json
import os

import adafruit_connection_manager
import board
import wifi

import adafruit_requests

from adafruit_dps310.basic import DPS310
from adafruit_ticks import ticks_ms, ticks_add, ticks_less

INTERVAL = 20 # seconds
JWT_TOKEN = "PASTE_JWT_TOKEN_HERE"
print(JWT_TOKEN[0])
i2c = board.I2C()

sensor = DPS310(i2c)

# Get WiFi details, ensure these are set up in settings.toml
ssid = os.getenv("CIRCUITPY_WIFI_SSID")
password = os.getenv("CIRCUITPY_WIFI_PASSWORD")

JSON_POST_URL = "http://192.168.1.128:3000/datavalues"

radio = wifi.radio
while not radio.connected:
    radio.connect(ssid, password)

# get the pool and ssl_context from the helpers:
pool = adafruit_connection_manager.get_radio_socketpool(radio)
ssl_context = adafruit_connection_manager.get_radio_ssl_context(radio)
requests = adafruit_requests.Session(pool, ssl_context)
rssi = wifi.radio.ap_info.rssi

print(f"\nConnecting to {ssid}...")

try:
    # Connect to the Wi-Fi network
    wifi.radio.connect(ssid, password)
except OSError as e:
    print(f"OSError: {e}")

headers = {"Content-Type": "application/json", "Authorization": f"Bearer {JWT_TOKEN}"}

deadline = ticks_ms()
deadline = ticks_add(deadline, INTERVAL * 1000)
while True:
    DATA = json.dumps({"type": "temperature", "tag": "funhouse_0", "value": sensor.temperature})
    with requests.post(JSON_POST_URL, data=DATA, headers=headers) as response:
        print(f" Response Status Code: {response.status_code}")

    while ticks_less(ticks_ms(), deadline):
        pass
    deadline = ticks_add(deadline, INTERVAL * 1000)

import json
import os

import adafruit_connection_manager
import board
import wifi

import adafruit_requests

from adafruit_dps310.basic import DPS310
from adafruit_ticks import ticks_ms, ticks_add, ticks_less

INTERVAL = 20 # seconds
JWT_TOKEN = "PASTE_JWT_TOKEN_HERE"
print(JWT_TOKEN[0])
i2c = board.I2C()

sensor = DPS310(i2c)

# Get WiFi details, ensure these are set up in settings.toml
ssid = os.getenv("CIRCUITPY_WIFI_SSID")
password = os.getenv("CIRCUITPY_WIFI_PASSWORD")

JSON_POST_URL = "http://192.168.1.128:3000/datavalues"

radio = wifi.radio
while not radio.connected:
    radio.connect(ssid, password)

# get the pool and ssl_context from the helpers:
pool = adafruit_connection_manager.get_radio_socketpool(radio)
ssl_context = adafruit_connection_manager.get_radio_ssl_context(radio)
requests = adafruit_requests.Session(pool, ssl_context)
rssi = wifi.radio.ap_info.rssi

print(f"\nConnecting to {ssid}...")

try:
    # Connect to the Wi-Fi network
    wifi.radio.connect(ssid, password)
except OSError as e:
    print(f"OSError: {e}")

headers = {"Content-Type": "application/json", "Authorization": f"Bearer {JWT_TOKEN}"}

deadline = ticks_ms()
deadline = ticks_add(deadline, INTERVAL * 1000)
while True:
    DATA = json.dumps({"type": "temperature", "tag": "funhouse_0", "value": sensor.temperature})
    with requests.post(JSON_POST_URL, data=DATA, headers=headers) as response:
        print(f" Response Status Code: {response.status_code}")

    while ticks_less(ticks_ms(), deadline):
        pass
    deadline = ticks_add(deadline, INTERVAL * 1000)

This code uses adafruit_connection_manager to manage the sockets and adafruit_requests to send data to the PostgREST server. It assumes that you've put your WIFI details in the settings.toml file.

The adafruit_ticks module is used wait for a predefined interval length. I've used 20 seconds, but you can change the value to whatever makes sense for your sensor and environment.

Once per interval it takes a reading from the sensor and puts it into a JSON data payload. The payload is sent to the server with requests.post(). In order for the server to handle our requests we must authenticate by including the Authorization header with a value of "Bearer " followed by our JWT token.

Graphing The Data

Now we have a database and API to access it, as well as a microcontroller script that POSTs data into the system. We can fetch raw data out of the database with the API, but it'd be nice if we had a way to visualize the data.

We can do this with a basic HTML web page and a Javascript library named Apex Charts to show graphs of our data. I'll show you a basic example with a line chart.

I'll use Nginx to serve the page. If you haven't already then install it with:

Copy Code

sudo apt install nginx

sudo apt install nginx

Now we need to make an nginx config file to serve the page for us. Use whatever text editor that you'd like to create a file and put these contents within it.

Copy Code

sudo nano /etc/nginx/sites-available/iot_server_frontend

sudo nano /etc/nginx/sites-available/iot_server_frontend

/etc/nginx/sites-available/iot_server_frontend

Download File

Copy Code

server {
	listen 80 default_server;
	listen [::]:80 default_server;

	root /var/www/html;

	index index.html index.htm index.nginx-debian.html;

	server_name _;

	location / {
		# First attempt to serve request as file, then
		# as directory, then fall back to displaying a 404.
		try_files $uri $uri/ =404;
	}
}

server {
	listen 80 default_server;
	listen [::]:80 default_server;

	root /var/www/html;

	index index.html index.htm index.nginx-debian.html;

	server_name _;

	location / {
		# First attempt to serve request as file, then
		# as directory, then fall back to displaying a 404.
		try_files $uri $uri/ =404;
	}
}

This is a very basic nginx config that will serve static HTML files from a specified directory /var/www/html/.

So now we just need to paste a copy of our html file into there and it'll get served for us. We could put it directly into here, but it can be nice to make a sub-directory in order to organize multiple different web pages.

Copy Code

sudo mkdir /var/www/html/iot/
sudo mkdir /var/www/html/iot/js/

1
2

sudo mkdir /var/www/html/iot/
sudo mkdir /var/www/html/iot/js/

Put this file into /var/www/html/iot/.

index.html

Download File

Copy Code

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>Title</title>
</head>
<body>

<div id="chart_container"></div>

<script src="https://cdn.jsdelivr.net/npm/apexcharts"></script>
<script type="text/javascript" src="js/app.js"></script>
</body>
</html>

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>Title</title>
</head>
<body>

<div id="chart_container"></div>

<script src="https://cdn.jsdelivr.net/npm/apexcharts"></script>
<script type="text/javascript" src="js/app.js"></script>
</body>
</html>

Put this into /var/www/html/iot/js/

app.js

Download File

Copy Code

let SERVER_URL_PREFIX = "http://192.168.1.128:3000/";
async function fetchData() {
    const response = await fetch(SERVER_URL_PREFIX + "rpc/data_latest?type=eq.temperature&tag=eq.funhouse_0&num=1&unit=hour");
    const temperature_data = await response.json();
    return temperature_data;
}

let times = [];
let temperature_values = [];

const $chartContainer = document.querySelector("#chart_container");
fetchData().then(function (temperature_data) {
    console.log("inside then() temperature_data: " + temperature_data)
    for (let i = 0; i < temperature_data.length; i++) {
        let curTemperatureData = temperature_data[i];
        console.log(curTemperatureData);
        temperature_values.push(curTemperatureData.value);
        times.push(curTemperatureData.timestamp);
    }

    var options = {
        series: [{
            name: "Temperature",
            data: temperature_values
        }],
        chart: {
            type: 'area',
            height: 350,
            zoom: {
                enabled: false
            }
        },
        dataLabels: {
            enabled: false
        },
        stroke: {
            curve: 'straight'
        },

        title: {
            text: 'Temperature Readings Over Time',
            align: 'left'
        },
        subtitle: {
            text: 'Temperature C',
            align: 'left'
        },
        labels: times,
        xaxis: {
            type: 'datetime',
        },
        yaxis: {
            opposite: false
        },
        legend: {
            horizontalAlign: 'left'
        }
    };

    var chart = new ApexCharts($chartContainer, options);
    chart.render();
});

let SERVER_URL_PREFIX = "http://192.168.1.128:3000/";
async function fetchData() {
    const response = await fetch(SERVER_URL_PREFIX + "rpc/data_latest?type=eq.temperature&tag=eq.funhouse_0&num=1&unit=hour");
    const temperature_data = await response.json();
    return temperature_data;
}

let times = [];
let temperature_values = [];

const $chartContainer = document.querySelector("#chart_container");
fetchData().then(function (temperature_data) {
    console.log("inside then() temperature_data: " + temperature_data)
    for (let i = 0; i < temperature_data.length; i++) {
        let curTemperatureData = temperature_data[i];
        console.log(curTemperatureData);
        temperature_values.push(curTemperatureData.value);
        times.push(curTemperatureData.timestamp);
    }

    var options = {
        series: [{
            name: "Temperature",
            data: temperature_values
        }],
        chart: {
            type: 'area',
            height: 350,
            zoom: {
                enabled: false
            }
        },
        dataLabels: {
            enabled: false
        },
        stroke: {
            curve: 'straight'
        },

        title: {
            text: 'Temperature Readings Over Time',
            align: 'left'
        },
        subtitle: {
            text: 'Temperature C',
            align: 'left'
        },
        labels: times,
        xaxis: {
            type: 'datetime',
        },
        yaxis: {
            opposite: false
        },
        legend: {
            horizontalAlign: 'left'
        }
    };

    var chart = new ApexCharts($chartContainer, options);
    chart.render();
});

Update the SERVER_URL_PREFIX with the IP address of your pi and the port that postgrest is running on.

The filesystem should look like this:

Copy Code

/var/www/html/iot/index.html
/var/www/html/iot/js/app.js

1
2

/var/www/html/iot/index.html
/var/www/html/iot/js/app.js

Restart NGINX if you haven't already so that our configuration will take effect.

Copy Code

sudo nginx -s reload

sudo nginx -s reload

Now with postgrest running, the micro-controller POSTing data, and nginx hosting our front end page we're ready to load the page and see our graph!

This page (Storing & Graphing IoT Data With PostgREST) was last updated on August 27, 2024.