Commercial Energy Monitoring with SQL

Commercial Energy Monitoring with SQL

We’ve added the ability to upload from an IotaWatt to SQL database.

Rationale

TimescaleDB (a superset of SQL) is a good choice for a time series database. Currently IotaWatt and by extension PhiSaver (which uses IotaWatt) supports a few methods:

• influx v1 and v2
• PV Output
• EmonCMS

PhiSaver uses Influx v2 (flux) which is a dedicated time series database. It works well. However, we’ve wanted to add SQL uploading for a while now to:

• Migrate to TimescaleDB as Influx v2 (flux) is not actively developed. This is a pity as flux is a nice language.
• Read directly to end-users on-site databases. Commercial or industrial users are typically more familiar with SQL.

Design

Connecting to SQL database from a embedded device propose a few problems:

• While you can write psql (“PostgreSQL Frontend/Backend Protocol”) directly from a esp8266/esp32 (e.g. library not well supported or used) it’s difficult. Usually we have adaptors/drives/clients to do this on computers.
• We would ideally like to support any SQL database
• We would like to absolutely minimize complexity on the esp8266
• We would like to use HTTP POST request (which fits with existing IotaWatt codebase)

We used PostgREST which provides a HTTP API that the embedded device can POST and GET to. It acts as a HTTP frontend to a Postgresql database. It’s only for Postgresql though. For us, that’s ok since we’ll be using TimescaleDB which requires Postgresql away.

InfluxDB is schemaless, and there are a number of ways to structure the data with tags, etc from the IotaWatt. For SQL, we need a fixed schema which is described in more detail here.

Implementation

Ben Thekkel from PhiSaver implemented a new class for the IotaWatt codebase, which we’ve flashed onto Iotawatt. We’ve provided a pull request to the original repo. You can contact us if you’d like to use it in the meantime.

Key changes are a new postgres_uploader class and UI changes.

JWT tokens are used for authentication and authorisation.

We’ve also depended a supporting docker container and utilities to assist in setting up the data.

Results

In the IotaWatt you can add a new uploader

Further Work

We’re currently testing uploading reliability, heap use, etc. Looking robust and simple so far, but more testers are welcome.

Currently only one metric (V, Amps, etc) is permitted per channel. The database allows many, but some thought is required about how to robustly handle multiple metrics per channel. One option is to configure the database to not overwrite values (i.e. “UPSERT”). This would make the esp8266 code easy to write.

Another method would be be combine all metrics of a sensor into a single POST/INSERT request. This is probably nicer from the database side (i.e. a timestamp/sensor set of values is atomic which is convenient) but will require some complexity on the esp8266 to ‘group’ metrics.