A Personal, Wireless ESP32 Arduino/Raspberry Pi Database

Collect sensor data, send it to a Raspberry Pi, and easily access the data with an external laptop!

The sensor or data does not matter. I used a $60 Geiger counter because I wanted my setup to collect data for very long periods of time, like weeks or months, so I opted for background radiation particles. I believe this project would be just as neat with a $2 ambient light sensor.

The ESP32 Nano is a powerful microcontroller that supports Wi-Fi, making it a great tool for IoT/wireless projects.

If you are unfamiliar with loading external libraries to Arduino, check out their Library Manager in the Arduino IDE. I find it very useful and easy to install from there. 

The Code:

// --------------------------------------------------------------           
// WIFI           
#include <WiFi.h>           
#include <WiFiMulti.h>           
const char* ssid     = "your_wifi_name";           
const char* password = "your_wifi_pwd";           
const uint16_t port = 5000;           
// const char * host = "10.0.0.222";  // computer’s host (will discuss later)           
const char * host = "10.0.0.73";  // raspberry pi host (will discuss later)           
WiFiMulti WiFiMulti;           
// --------------------------------------------------------------           
// specific to my sensor logic        
unsigned long dt = 0;           
unsigned long lastTime = 0;           
unsigned long currentTime = 0;           
// --------------------------------------------------------------           
void setup() {           
	Serial.begin(115200);           
	delay(10);           
	WiFiMulti.addAP(ssid, password);           
	Serial.println();           
	Serial.print("Waiting for WiFi... ");           
	while(WiFiMulti.run() != WL_CONNECTED) {Serial.print(".");delay(500);}           
	Serial.println("WiFi connected");           
	Serial.println("IP address: ");           
	Serial.println(WiFi.localIP());           
	delay(500);           
}           
void loop() {           
	int val = (1023-analogRead(A0));           
	if (val > 200){           
	  // --------------------------------------------------------------           
	  // Should have some kind of trigger logic, even if it’s a delay(1000)           
	  currentTime = millis();           
	  dt = currentTime-lastTime;           
	  lastTime = currentTime;           
	  WiFiClient client;           
	  if (!client.connect(host, port)) {           
	      Serial.println("Connection failed.");           
	      Serial.println("Waiting 5 seconds before retrying...");           
	      delay(5000);           
	      return;}           
	  String PostData="esp32={\"int_10\":";           
	  PostData=PostData+String(val);           
	  PostData=PostData+",\"dtMilli\":" +String(dt);           
	  PostData=PostData+"}";           
	  client.println("POST /echo HTTP/1.1");           
	  client.println("Host:  ESP32");           
	  client.println("User-Agent: Arduino/1.0");           
	  client.println("Connection: close");           
	  client.println("Content-Type: application/x-www-form-urlencoded; charset=UTF-8");           
	  client.print("Content-Length: ");           
	  client.println(PostData.length());           
	  client.println();           
	  client.println(PostData);           
	  Serial.println(PostData);           
	  client.stop();           
	}           
}

Some code comments:

• It is common to collect sensor data using an interrupt, such as attachInterrupt(digitalPinToInterrupt(PIN), function, Condition) . Interrupts should be kept simple, with a global flag (defined outside your void loop() ) set to true; inside the interrupt function. The WiFiClient client; should also be placed outside the interrupt.
• However, I did not use interrupts. Instead, I utilized analogRead(PIN) for sensor data. Initiating WiFiClient client; will stop any analogRead attempts. I found that recording the analog signal first and then initiating WiFiClient client; worked for me. As you can see, the first line inside my void loop() is analogRead(A0) , followed by WiFiClient client;
• client.println(PostData); actually makes the POST request to the Raspberry Pi with the correct syntax. 
• The lines below add custom data (the value from analogRead A0) with the key 'int_10' and the delta time (in milliseconds) between radiation counts with the key 'dtMilli'. This data will be saved into my database! The rest of the code inside the loop simply sends the correctly formatted POST.

String PostData="esp32={\"int_10\":";
PostData=PostData+String(val);
PostData=PostData+",\"dtMilli\":"+String(dt);  

A Raspberry Pi comes with Python and all the libraries you will need!

Get your Raspberry IP address:

Mine is 10.0.0.73. Your IP address may be different — just change the address accordingly. Place your Raspberry Pi’s IP address into your Arduino code: const char * host = "10.0.0.73"; . You may notice I have a commented-out line // computer’s host (will find later) . Many of the steps below can be initially executed on a computer if you want to test it before uploading to a Pi. Just make sure to use the correct IP address. 

Getting the Python code on your Pi:

My Current project file/folder organization is as follows (yours may be different). My Pi has a folder named _dev with two Python files inside a folder named geiger_data_base/.

_dev/

_____| geiger_data_base/

__________| web_ex_0.py

__________| create_db.py

create_db.py code:

import sqlite3            
connection = sqlite3.connect("/media/mars91/5049-B94F/geiger_counter.db")            
cursor = connection.cursor()            
cursor.execute("CREATE TABLE geiger_counter (time FLOAT, int_10 INT, dt INT)")            
cursor.close()            
connection.close()            
print("created /media/mars91/5049-B94F/geiger_counter.db")

I used the sqlite3 library, named my database geiger_counter.db, and created three columns in my database.

• time, a float: the UTC time the Raspberry Pi logged data
• Int_10, an integer: the A0 data recorded by the ESP32
• dt, an integer: the millisecond time between radiation counts 

I used the absolute path, /media/mars91/5049-B94F/, for my USB. SSH into your Raspberry Pi and type the following command to list the USB's path:

df-h

web_ex_0.py code:

There is a lot in this code, but it's really not that complicated. The Raspberry Pi will now host a site at http://10.0.0.73:5000/, which should be accessible if you are on the Wi-Fi.

from flask import Flask, request, jsonify            
import json            
import sqlite3            
from datetime import datetime, timedelta, timezone            
import time            
app = Flask(__name__)            
time.sleep(30)  
## ----------------------------------------------------------------------------- 
## check your USB path                
connection = sqlite3.connect("/media/mars91/5049-B94F/geiger_counter.db", check_same_thread=False)       
cursor = connection.cursor()            
@app.route("/")            
def index():            
	  return "<p>Hello World, From Pi!</p>"            
@app.route('/echo', methods=['GET', 'POST'])            
def echo():            
	  if request.method == 'POST':            
	      dataIncoming = json.loads(request.values['esp32'])            
	      tNow = datetime.utcnow().timestamp()            
	      cursor.execute("""INSERT INTO geiger_counter(time, int_10, dt) VALUES (?,?,?);""",(tNow, dataIncoming['int_10'], dataIncoming['dtMilli']))            
	      connection.commit()            
	      return jsonify('success'), 200            
@app.route('/dataGet', methods=['GET', 'POST'])            
def dataGet():            
	  rParams = dict(request.values)            
	  if rParams.get('ymdhms', None):            
	      print(rParams)            
	      timeParams = [int(i) for i in rParams['ymdhms'].split('-')]            
	      year, month, day, hour, minute, second = timeParams            
	      timeDelta = int(rParams['deltaT'])            
	      startTime = datetime(year, month, day, hour, minute, second, tzinfo=timezone.utc)            
	      endTime = startTime + timedelta(seconds=timeDelta)            
	      startTime = startTime.timestamp()            
	      endTime = endTime.timestamp()            
	      rows = cursor.execute("SELECT dt, int_10 FROM geiger_counter WHERE time > ? AND time < ?",(startTime, endTime),).fetchall()            
	      dt = []            
	      int_10_list = []            
	      for i in range(len(rows)):            
	          int_10_list.append(rows[i][1])            
	          dt.append(rows[i][0])            
	      return jsonify({'int_10': int_10_list, 'dts': dt}), 200            
	  utc0db = cursor.execute("SELECT dt FROM geiger_counter").fetchmany(1)[0][0]            
	  return jsonify({'int_10': [], 'dts': [],            
	                  'dataStarts': datetime.utcfromtimestamp(utc0db).strftime('%Y, %m, %d, %H, %M, %S')}), 200            
if __name__ == "__main__":            
	  app.run(host='0.0.0.0', port=5000)

• We are writing to a sqlite3 database.
• @app.route('/echo', methods=['GET', 'POST']) creates the POST link for our ESP32. This route is always hit when the sensor sends data. Inside this function, you'll see data being inserted into the database. You can see my hard-coded values dataIncoming['int_10'] and dataIncoming['dtMilli']. They were defined in the ESP32 code; yours may be different.
• @app.route('/dataGet', methods=['GET', 'POST']) is a function (or decorator) that allows an external laptop to easily access the Pi’s data using a GET request.
• I can send the time window for the data I want. For example, 3 hours of data from 2024/4/14 starting at 14:00:00 can be handled inside the '/dataGet' function. Time windows are declared from tNow = datetime.utcnow().timestamp() in the @app.route('/echo', methods=['GET', 'POST']) . Every time data is saved, so is the UTC timestamp so when I retrieve data I specify UTC start < data < UTC end.
• When we want to retrieve the data, we can access it via Python code below with "http://10.0.0.73:5000/dataGet".

This is almost directly copied from https://www.instructables.com/Raspberry-Pi-Launch-Python-script-on-startup/. I will try to give a brief overview, but follow the link above for a better tutorial. I did this on a mac, but another OS should be similar.

• ssh into your PI
• Navigate to the project's folder by using cd commands, and create a file named launcher.sh by running nano launcher.sh. Then, edit the file with your specific path and the Python file you want to start on startup. 

cd /
cd /home/mars91/_dev/geiger_data_base
sudo python web_ex_0.py
cd /     

• Press Ctrl + x, y, and Enter to save the edited file.
• Make the launcher script executable using this command:

chmod 755 launcher.sh

• and test it with

sh launcher.sh

• Go back to your home directory using cd (twice for me) and create a directory for future troubleshooting logs:

mkdir logs

• In home directory, add the following line to your crontab. Crontab is a background process that allows you to execute scripts at specific times.

sudo crontab -e

• and enter

@reboot sh /home/mars91/_dev/geiger_data_base/launcher.sh >/home/mars91/logs/cronlog 2>&1

• Remember to change paths according to your setup.
• Check out https://www.instructables.com/Raspberry-Pi-Launch-Python-script-on-startup/ for a better tutorial 

final project file organization:

_dev/

_____| geiger_data_base/

__________| web_ex_0.py

__________| create_db.py

__________| launcher.sh 

First, you don't need a dedicated (always-on) laptop to handle incoming data; you can use a Raspberry Pi instead. Second, you can customize it without needing a hosting database service like AWS/Azure/Google. Third, by having the ESP32 POST to the Raspberry Pi's micro website, the two pieces of hardware don't really need each other. The ESP32 can POST all day long to an IP address regardless of whether that IP address even exists. The same goes for the Raspberry Pi microsite listening for requests. This architecture takes advantage of HTTP POST and REQUEST, making it a database that should be hard to break.

From my external laptop, connected to my Wi-Fi, I can simply retrieve the data with this python code.

import requests       
year, month, day, hour, minute, second = 2024, 4, 15, 15, 0, 0      
deltaSeconds = 60 * 60 * 48      
timeStr = f"{year}-{month}-{day}-{hour + 4}-{minute}-{second}"      
payload = {'ymdhms': timeStr, 'deltaT': int(deltaSeconds)}      
page = requests.get("http://10.0.0.73:5000/dataGet", params=payload).json()  # raspberry pi     
dts = np.array(page['dts']) * .001

I can also get the data if I'm on my Wi-Fi with http://10.0.0.73:5000/dataGet?ymdhms=2024-4-15-19-0-0&deltaT=86400

requests.get("http://10.0.0.73:5000/dataGet", params=payload).json() is instructing Python to access the Raspberry Pi's http://10.0.0.73:5000/dataGet endpoint and retrieve data. In the code, I'm requesting two days' worth of data starting from the specified date and time: year, month, day, hour, minute, second = 2024, 4, 15, 15, 0, 0. Current UTC time is four hours ahead of me, which is why I need to add 4 to the hour in the time string. 

I received over 46 hours of data and obtained over 58,000 data entries in fractions of a second from my PI database! This data pertains to my background radiation.

In the plots,

Right: A histogram of the delta times between radiation counts.

Middle: A time plot of counts per 5 minutes. I placed a piece of uranium glass by my sensor for a couple of hours, which is why the plot jumps to ~200 counts per 5 minutes. When I removed it, the count went back down to ambient radiation levels.

Left: Thousands of custom, truly random integers 0<>15 These can be generated from my data since background radiation and radioactive decay are truly random. 

I also made a real-time radiation plot using a bit of JavaScript and https://plotly.com/javascript/. I didn't include instructions for these features, as I believe this post is long enough. 

Thanks and I hope this can help you build a custom database!