Introduction

I have a few Raspberry Pi devices at home and I wanted to be able to collect the data from their temperature sensors on a regular interval and store that data in a Postgres database. Later on, I could use that data for some analytics together with Materialize.

In this tutorial we will use AdonisJS to build a simple API that will collect the data from the sensors and store it in a Postgres database as shown in the following image:

This can be used to collect the temperature data of a large number of Raspberry Pi devices.

Prerequisites

Before you get started, you would need to make sure that you have the following installed:

• Install Node.js
• Install Postgres

What is AdonisJS

AdonisJS is a Node.js framework that is used to create RESTful APIs along with full-stack web applications. It is a great tool to build web applications that can be deployed to any platform. It feels a lot like Laravel, but it is based on Node.js rather than PHP.

If you are not familiar with AdonisJS, no worries, you would still be able to follow along! However, if you want to learn more about AdonisJS make sure to check out this tutorial here:

AdonisJS for Beginners

How to install AdonisJS

To install AdonisJS you would need to run the following command:

npm init adonis-ts-app@latest raspberry-pi-adonisjs-app

Once you run that, you will be asked to select a project structure. You will be able to choose between an API, Web App, and a minimal possible AdonisJS app:

CUSTOMIZE PROJECT
❯ Select the project structure …  Press <ENTER> to select
❯ api   (Tailored for creating a REST API server)
  web   (Traditional web application with server-rendered templates)
  slim  (A smallest possible AdonisJS application)

For this tutorial let's go with the API option! Using your arrow keys select web and hit enter.

After that you will be asked to choose a name for the project, I will leave this as raspberry-pi-adonisjs-app but feel free to choose a different name.

I will then press enter and say yes to the rest of the settings:

❯ Enter the project name · raspberry-pi-adonisjs-app
❯ Setup eslint? (y/N) · y
❯ Configure webpack encore for compiling front-end assets? (y/N) › y

Once that is done, you can switch to the new project directory:

cd raspberry-pi-adonisjs-app

And once in there, start the webserver:

node ace serve --watch

The ace command is very similar to the artisan command in Laravel. It is a command-line interface for running AdonisJS commands. The node ace serve command will start the webserver and watch for changes to your code.

To check all of the ace commands, you can run: node ace.

Installing Lucid

Similar to Laravel Eloquent, AdonisJS provides an ORM. The ORL is called Lucid and we will be using it today.

Lucid comes with an Active Record ORM, Query Builder, Migrations, Seeds, and Factories.

To install Lucid, run the following command:

npm i @adonisjs/lucid

Once done, you would need to do a quick configuration.

Configuring Lucid

In order to configure Lucid, you need to run the following ace command:

node ace configure @adonisjs/lucid

You will be asked to select the database driver that you want to use. Here, make sure to select PostgreSQL!

Next, you will be asked to select where you want to display the configuration instructions. I chose In the terminal, which prints out the necessary environment variables that you have to add to your .envfile.

Make sure to update the DB_DATABASE and DB_USERNAME and DB_PASSWORD variables in your .env file accordingly so that you can connect to your database.

Add a mode and a migration

To add a model and a migration, run the following command:

node ace make:model Sensor -m

This will create a new model and a migration:

CREATE: database/migrations/1639847090390_sensors.ts
CREATE: app/Models/Sensor.ts

Open the migration file and update the file so that it looks like this:

import BaseSchema from '@ioc:Adonis/Lucid/Schema'

export default class Sensors extends BaseSchema {
  protected tableName = 'sensors'

  public async up () {
    this.schema.createTable(this.tableName, (table) => {
      table.increments('id')
      table.string('device')
      table.string('temperature')
      table.string('timestamp')
      /**
       * Uses timestamptz for PostgreSQL and DATETIME2 for MSSQL
       */
      table.timestamp('created_at', { useTz: true })
      table.timestamp('updated_at', { useTz: true })
    })
  }

  public async down () {
    this.schema.dropTable(this.tableName)
  }
}

We basically added 3 extra columns that will store the name of the device, the temperature, and the timestamp when the data was recorded.

To run the migration, run the following command:

node ace migration:run

This will create the sensors table in your database with the columns we specified.

Creating a Controller

Next, we will create a controller. This is where we will add the functionality that will allow us to store the Raspberry Pi data in our Postgres database.

Again we will be using the ace command to create a new controller:

node ace make:controller SensorsController

This will create a controller file at:

app/Controllers/Http/SensorsController.ts

Next, let's create the routes that we would need!

Adding our methods

As we are going to use this API to store the data from our Raspberry Pi devices, we will add just a single method to our controller.

With your favorite text editor, open the SensorsController.ts file and add the following method:

import Route from '@ioc:Adonis/Core/Route'
import Database from '@ioc:Adonis/Lucid/Database'

// import { HttpContextContract } from '@ioc:Adonis/Core/HttpContext'

export default class SensorsController {
    public async store ({ request }) {

        let name = 'raspberry-1';
        if (request.qs().name != null) {
            name = request.qs().name;
        }

        let timestamp = '2021-11-21 19:52:49';
        if (request.qs().timestamp != null) {
            timestamp = request.qs().timestamp;
        }

        let temperature = '41.1';
        if (request.qs().temperature != null) {
            temperature = request.qs().temperature;
        }

        console.log(name, timestamp, temperature)

        await Database
        .insertQuery()
        .table('sensors')
        .insert({ device: name, timestamp: timestamp, temperature: temperature})

        return {
            message: 'Successfully added sensor data'
        }
    }
})

There are a few things to note here:

• The import statement is importing the Route and Database from the @ioc:Adonis/Core/Route and @ioc:Adonis/Lucid/Database packages.
• The await keyword is used to wait for the database query to finish.
• The request.qs() method is used to get the query string parameters from the request. That way we will be able to get the name, timestamp, and temperature sent by the Raspberry Pi devices.

Creating the AdonisJS routes

Your routes file is stored at start/routes.ts. In there we can specify our application URLs and map them to different controllers and methods!

We do not yet have the methods ready, but we know that we would need the following routes:

• GET /temperature: This route will store the data sent by the Raspberry Pi devices.

Open your routes file at start/routes.ts and update it so that it has the following content:

import Route from '@ioc:Adonis/Core/Route'

Route.get('/temperature', 'SensorsController.store')

Adding authentication

For the sake of this tutorial, I would not be implementing a full-blown authentication as the API would be running locally on my network and would not have any sensitive data.

However if you want to take this one step further, you can follow the steps from the documentation here on how to implement this:

AdonisJS Authentication Docs

Adding cron jobs to the Raspberry Pi devices

Now that we have our controller and routes, we can add a cron job to the Raspberry Pi devices which will send the data to the API and store it in our database.

Let's create a small bash script which we will run every minute:

#!/bin/bash

# AdonisJS API URL - Make sure to change this to your API URL
api_url="http://localhost:3333/temperature"

# Specify the name of the Raspberry Pi device:
name="raspberry-1"

if [[ -z ${NAME} ]] ; then
    name="raspberry"
fi

# Get the temperature from the Raspberry Pi device:
function temperature(){
    temperature=$(/opt/vc/bin/vcgencmd measure_temp | tr -d temp=\'C)
    echo ${temperature}
}

# Get the current time
function timestamp(){
    time=$(date +%s)
    echo ${time}
}

echo ${name},$(timestamp),$(temperature)

curl -X GET "${api_url}?name=${name}-${i}&timestamp=$(timestamp)&temperature=$(temperature)"

Make sure to change the URL to your AdonisJS API. If you are running this on the same Raspbery Pi, you can leave it as localhost, if not you could use the IP of the device that you are running the API on.

Save the script as temperature.sh and make it executable:

chmod +x temperature.sh

Then edit your crontab:

sudo crontab -e

Add the following line to your crontab:

* * * * * /home/pi/temperature.sh

This will run the script every minute and send the data to the API.

Conclusion

You can find the code for this tutorial here:

AdonisJS API - Raspberry Pi Temperature

As the second part of this tutorial, we will use Materialize to run streaming SQL queries on the data collected by the API. We are going to build the following setup:

If you want to learn more about AdonisJS I could suggest checking out this tutorial here:

• Building a real-time web application with Materialize and AdonisJS

То learn more about Materialize make sure to check out this tutorial here:

Learn Materialize by running streaming SQL on your nginx logs

Hope that this helps!