Creating a local CGM database using a Pi 4 and Node-RED

Drawing

Drawing

I've been using the Dexcom continuous glucose monitors for about 4 years now. The latest sensor, currently the G6 model, attaches to my body and transmits glucose readings every 5 mins. The sensor connects directly with the companion smartphone app via bluetooth and then the data is sends the data to the cloud. With the data in the cloud, Dexcom provide a web based tool called Clarity that allows one to analyse various aspects of their glucose data.

Dexcom app

The Dexcom mobile app

Clarity is useful tool for sure but, it struck me that over the course of four years, I've generated a lot of data. I wanted to start building a local backup of my glucose data to:

  • Have a local backup of all of my glucose readings (Dexcom doesn't offer a download data option and if I change CGM providers I don't want to lose all that useful historic data)
  • Build custom visualisations that are more relevant to my day-to-day tracking of my diabetes goals (I hope to show some of these in later posts)
  • See how my control has fluctuated over the years (Clarity only lets me view or compare 90 days at a time)
  • Combine other data sources so that I can explore how different situations affect my glucose

Another tool for exploring glucose data is Nightscout which I also use extensively. The reports are really useful; the only issue I have is that mongoDB database used in heroku (the cloud hosting platform) has a free tier limit of 512GB which for me gets filled up every few months forcing me to empty the database periodically - another reason for me wanting to creating a local backup.

The Setup

The setup I've built uses a Raspberry Pi 4 and some software called Node-RED which comes pre-installed with Raspian. Node-RED is a low-code environment for events driven applications.

There are plenty of useful resources online that describe Node-RED but, the essential building block is the idea of a flow. A flow in Node-RED is a sequence of nodes that run sequentially and each perform a task. The results of each nodes task is fed into the next connected node till the chain is completed. Since there are many pre-defined nodes for things like connecting to database or making an http request, there is a reduced need to write much code to do a lot of tasks.

For this project, I created a simple flow in Node-RED that runs a few http requests to authenticate with the Dexcom servers, queries my glucose data and finally puts results into a mysql database running on the pi. I leave the pi running 24/7 and schedule the flow to run every few minutes so new glucose data are drip fed as they become available.

Drawing

How flows look in the Node-RED web interface

Eventually, hosting the database externally from the pi is probably a better idea as the flash storage on the pi is not ideal for long term backups. That said, to get going quickly and start exploring the data, this was perfect.

For anyone interested in how I built this setup, the following video details the setup step-by-step and, I've also walked through it in the remainder of this post.


Prerequisites

For anyone looking to follow the setup, the remainder of this post assumes you have a raspberry pi 4: installed with Raspian, booted and connected up to a display. You'll of course need a Dexcom CGM with sharing turned on in the app as we'll be pulling the data from the servers.

Setting up and configuring Node-RED

The Raspian operating system comes with Node-RED pre-installed so there's no installation required here. Node-RED can be started by clicking the Programming menu at the top of the screen followed by Node-RED. This will launch Node-RED inside a console.

Drawing

The console will contain the url that we can access the web interface to Node-RED from. Once I've found the URL in the console, I can navigate to it using a web browser and connect to the web interface.

Drawing

The console showing the Node-RED URL

Starting Node-RED from the menu bar is perfectly fine but, if I accidentally close the console then it will stop. I'd recommend running Node-RED as a service, so that if the pi reboots it'll get auto-started and run in the background.

Run the following in a terminal to setup the service which will auto-start Node-RED after the next reboot:

sudo systemctl enable nodered.service

Node-RED flows can be exported as JSON files and the flows I built for this project can be found at the following github repo. My repo contains two: dexcom-pull-eu.json and dexcom-pull-us.json for those in the EU and US respectively (the Dexcom servers endpoints are different, but the flows are pretty much the same). The other difference is that I made the EU flow output glucose data in mmol/L and the US one in mg/dL by default (it's possible to change this by modifying the 'Clean glucose data' node).

To import the flow into Node-RED you can copy and paste the JSON file to your clipboard and then inside the node-RED web interface, press Ctrl-i to open up the import flow panel and paste your flow right in.

After the flow is imported, you should see the nodes and their connections appearing on the canvas.

Node-RED comes with a base set of nodes installed, however we can also install others as needed. For my particular flow we need to install a couple of other nodes that I've made use of.

Click the 3 horizontal bars in the top right of the Node-RED interface, click manage palette and then navigate to the install tab. The two nodes that need to be installed are node-red-contrib-credentials and node-red-node-mysql. Go ahead and type these into the search, find the packages and install them at which point you should see the dashed lines around the outsides of the mysql and credentials nodes fill in on your canvas.

In addition to the extra nodes, I also made use of an additional nodejs library sql-template-strings. This library was used in one of the nodes that uses some code to create the SQL statements for adding to the database.

I need to install this using npm. To do this, head out of Node-RED and open up a terminal from the Programming menu. Once you have a terminal open, run the following commands:

cd ~/.node-red
npm install sql-template-strings
nano settings.js

The last command will open up a nano text editor that allows you to edit the Node-RED settings file. Once in the settings.js file, scroll down until you see a section like this:

functionGlobalContext: {
    // os:require('os'),
    // jfive:require("johnny-five"),
    // j5board:require("johnny-five").Board({repl:false})
}

modify it to looks as follows:

functionGlobalContext: {
    sqlModule:require('sql-template-strings')
    // os:require('os'),
    // jfive:require("johnny-five"),
    // j5board:require("johnny-five").Board({repl:false})
}

Press Ctrl+x to save and come out of nano. To make the setting updates take effect, we need to restart Node-RED which can be done in the terminal as follows:

sudo service node-red restart

Setting up mysql

Now we're ready to get our database setup. Note: In Raspian, MariaDB is used as a drop-in replacement to MySQL so I installed that instead, but all MySQL commands should still work with MariaDB.

Use the terminal to install and configure the database. The first step is to check for any package updates, by running the following:

sudo apt-get update
sudo apt-get upgrade

The above may take a while depending on how many updates are needed. After the updates have completed, we're ready to install MariaDB/MySQL with the following command:

sudo apt-get install mariadb-server

Once the database is installed the next step is database configuration which involves setting a root password for the database. To start configuration wizard, run:

sudo mysql_secure_installation

The mysql_secure_installation will first ask you to set a root password which has to be input twice. For the remaining questions I did:

  • Press Y to remove anonymous users.
  • Press Y to disallow root login remotely.
  • Press Y to remove test database and user.
  • Press Y to reload privilege tables. (last step)

and we're done. We've now set the root password for connecting to our database.

To be able to login with the password we just set, I had to change the default database plugin from auth_socket to mysql_native_password otherwise I was getting an error when trying to connect to my database. To change this, connect to the MySQL shell using sudo mysql -u root, then run the following with the mysql shell to switch the plugin:

mysql> USE mysql;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit;

In order to make the settings take effect, restart MySQL by running:

$ sudo service mysql restart

Now we can connect to our database by using the following command (you'll be required to enter your database root password):

mysql -u root -p

The above command connects to the mysql database using the command line and allows us to run commands from the prompt directly on the database.

We can use the mysql command line tool to create the database and tables to store the incoming glucose data. First we need to create a database called glucose and then connect to it:

mysql> create database glucose;
mysql> use glucose;

Once connected, I can create a table to store my glucose data:

mysql> create table glucose (
mysql>     date datetime primary key,
mysql>     value float,
mysql>     direction text
mysql> );

The above table has three columns:

  • date, the time the glucose reading was taken
  • value, the glucose reading
  • direction, the direction the glucose is trending e.g. (Flat, FortyFiveUp, FortyFiveDown etc).

Once I've executed this, I can press Ctrl-d to exit from the mysql command line and head back to the node-RED web interface.

Okay, we're nearly up and running. Final 3 small bits of setup to do back in Node-RED. First thing we need to do is add our Dexcom accountId and password into the credentials node so that Node-RED can authenticate with Dexcom. To do this, double click on the node called credentials in your flow and type in your accountId and password.

Drawing

Next, double click on the orange mysql node called diabetes. Similar to the credentials node setup, double click the node, add "root" as the database user and for the password, use the one you setup in the database setup. For the database put "diabetes". Click done.

The final piece of setup involves setting up the flow to run on a regular repeating schedule. To do this, double click on the blue inject node on the very left-hand side of the flow - the name is usually "timestamp". Double click it and ensure the "Inject once after" box is unticked and then in the repeat dropdown choose interval and make the frequency 150 seconds. This will schedule poll the Dexcom api every 2.5 minutes. Click done to close the menu.

We're now ready to deploy our flow so that it actually starts running. To do this click the red deploy button in the top right.

Assuming there are errors upon deploying the flow should be running and will continue to do so till we stop it. If we've configured everything correctly, we should see within a few minutes records starting to appear in the database. Below I show a select statement that shows 10 rows from the glucose table.

MariaDB [(none)]> use diabetes;
Database changed
MariaDB [diabetes]> select * from glucose order by date desc limit 10;
+---------------------+---------+-------------+
| date                | value   | direction   |
+---------------------+---------+-------------+
| 2021-02-28 10:57:54 | 6.61111 | Flat        |
| 2021-02-28 10:52:54 | 6.55556 | Flat        |
| 2021-02-28 10:47:54 | 6.61111 | Flat        |
| 2021-02-28 10:42:55 | 6.77778 | Flat        |
| 2021-02-28 10:37:55 | 6.83333 | Flat        |
| 2021-02-28 10:32:54 |       7 | Flat        |
| 2021-02-28 10:27:54 | 6.94444 | FortyFiveUp |
| 2021-02-28 10:22:54 | 6.61111 | Flat        |
| 2021-02-28 10:17:55 | 6.22222 | Flat        |
| 2021-02-28 10:12:55 | 5.72222 | Flat        |
+---------------------+---------+-------------+
10 rows in set (0.001 sec)

And we're there

Thanks for reading this far! If you're having any problems following the specific setup, do check out the video where I go into a bit more detail on testing the flow as part of the setup.

Otherwise congratulations on creating your local glucose store!