I've been using LiteDB and a C# backend to serve a local web page, where I've been keeping track of the weight of our three cats and two dogs. After migrating the backend to docker, I've been having issues that the database in not being updated correctly. The file is saved on the pi and volume shared to the container, but the changes are not being propagated to the actual file on the pi. Instead of finding out what is wrong and wasting time on that, I'd like to skip a few steps and move on from C#. It is not the best for the pi and was mostly an interesting exercise at the time.

Installing Dotnet is an unnecessary step when setting up the Pi, this is the reason why I ended up with a docker container. For comparison Python comes built-in with the Raspberry Pi OS, so I had a clear favorite when selecting the solution.

This could easily be forked and morphed to suit other needs, to do so you can check out the source code in here: petweb. Note that this project is only suitable for local use and is not concerned with any security issues.

Finding the correct solution

To get some options, I asked ChatGPT what it would propose:

Prompt:
What noSQL db and backend combo do you propose to run on a Raspberry Pi?

My web page is a python flask application

Give several options with pros and cons

Summarized answer:
Running a NoSQL database and a backend on a Raspberry Pi with a Python Flask application can be quite efficient for
lightweight applications, IoT projects, or for learning purposes. Here are a few combinations of NoSQL databases and
how you might use them with Flask, considering the Raspberry Pi's resource constraints:

1. MongoDB with Flask
2. Redis with Flask
3. SQLite with Flask
4. CouchDB with Flask

When choosing a database and backend setup for your Raspberry Pi project, consider the nature of your application,
the complexity of your data, and your future scalability needs. MongoDB and CouchDB are great for more complex
applications with changing schemas, Redis excels in caching and speed, while SQLite can be a simple solution for
smaller projects without the need for NoSQL's schema flexibility.

One of these seemed really nice considering my use case:

SQLite with Flask

Pros:
Lightweight: Doesn't require running a separate database server, which is great for Raspberry Pi.
Ease of Use: Directly integrated into Python through the SQLite module, making it very easy to use with Flask.
Sufficient for Small to Medium Projects: Offers transactional SQL database engine with enough features for many
small to medium projects.

Cons:
Not a NoSQL Database: Lacks the schema flexibility of a NoSQL database.
Scalability: Not ideal for applications with high concurrent write operations.

My page is already running with Flask, so I could bundle the whole thing as a single project and run it as a single process. SQLite is also included in the default Python installation, so there is need to get anything extra.

The Dotnet backend is running as a separate process from the Flask web UI, so it has some extra overhead.

SQLite is not a NoSQL database, but that wasn't a hard requirement as my data needs are easily handled with a relational database.

As I'm a JetBrains boy, I'll be using PyCharm (Community Edition) and Visual Studio Code (for JavaScript) as my editors. I'll be using some snippets, mostly for the Flask page and JavaScript from the original project: PetWeightsWeb.

Getting started

This requires Python3, which I already have installed on both of my machines.

Handling environment variables

I want to be able to configure some variables depending on where the program is running. I'll be developing on my own PC and there will some differences between it and the pi. In the end, this class only contains the database location, but it's nice to have as a backup if there is a need for more configurations later.

I'll be using a config class to handle these by creating a default file, which can be overwritten just by creating a file with the correct name.

The config_example.py looks like this:

class Config:
    DATABASE_PATH = 'petweb.db' # By default, create the database to the current folder

The import logic to load the correct config file, where it first prioritizes the config.py, if it exists looks like this:

try:
        from config.config import Config
    except ImportError:
        from config.config_example import Config

Creating and managing the database

I'll start by creating the database and just getting a feel for the SQLite database, which seems to have a good tutorial on how to do this.

There is no inbuilt version control/migration system in SQLite, so I won't be covering those in here as I have so simple needs that I won't need it. I'll be using the CREATE TABLE IF NOT EXISTS style statements to have the database scripts included in the main code execution. This can be worked around by manually updating the database and matching the creation script in the code.

Because I want to follow the weights on a day-to-day basis, I'll add a constraint to the weights-table, that there can't be multiple rows with the same name and date values:

cursor.execute("""
    CREATE TABLE IF NOT EXISTS weights (
    name TEXT NOT NULL,
    weight REAL NOT NULL,
    date date DEFAULT CURRENT_DATE,
    UNIQUE(name, date))
    """)

With this done, I now have a basic database working and can continue with the UI itself.

Setting up the page

Next step on my migration would be to fetch the original webpage configuration from the PetWeightsWeb repository. I had done some additions on my local branch which weren't present on the original repository. These changes include a separate statistics page, where I'll have graphs that show the changes in the weights.

I'll fetch the html, js and css files and set up the basis Flask structure with templates and static directories in the project root.

I want to make a major change from the original: I want to create the input cells and statistic graphs based on the amount and species of the pets saved to the database. For this I need to have a separate table where I can store the animals and their information. I also want a checkbox whether an animal should be shown on the UI. I'll just use the name column to link between pet_weights- and pets-table.

cursor.execute("""
    CREATE TABLE IF NOT EXISTS pets (
    name TEXT NOT NULL,
    species TEXT NOT NULL,
    birth_day date,
    active BOOLEAN DEFAULT TRUE,
    color TEXT NOT NULL,
    UNIQUE(name),
    UNIQUE(species, color)
    )
    """)

Handling the communication

Now that the database is working and I need get something to the UI, I must integrate the calls to replace the old ones that use the C# backend and JavaScript.

Luckily for me, I can use Flask to create endpoints which return the query results of database queries. This then takes care of the backend of our application and can replace the current calls without much modification.

For an example, we can return a list of JSON objects from an endpoint like this:

@app.route('/get-pets', methods=['GET'])
def get_pets():
    conn = get_db_con()
    items = conn.execute('SELECT * FROM pets WHERE active = true').fetchall()
    conn.close()
    items = [dict(zip(['name', 'species', 'birth-day'], item)) for item in items]
    return jsonify(items)

Which returns this when heading to localhost:8080/get-pets

[
    {"birth-day":null,"name":"Buddy","species":"Dog"},
    {"birth-day":null,"name":"Bobby","species":"Cat"},
    {"birth-day":null,"name":"Baulie","species":"Dog"}
]

Dynamic html creation

Flask uses Jinja2 templating, which enables dynamically generating html elements. For example, we can generate divs that will be used to insert values to the database later by passing a list of pet names to the index.html.

{% for pet in pets %}
<div class="inputContainer">
    <div class="inputRow">
        <label for="{{ pet }}">{{ pet | capitalize }}: </label>
        <input type="number" id="{{ pet }}" name="{{ pet }}" step="0.1" required>
    </div>
    <div class="diff" id="{{ pet }}Diff">0.0</div>
</div>
{% endfor %}

Next I need to modify the JavaScript to use the new endpoints and respect the generated elements. I had to remove some hard coded values and modify them to be dynamic instead.

For example, I had a fixed list of pets, where I created a listener for each of the weight fields that are compared against the Base weight value. I had to modify the list to be fetched from the database, returning each distinct pet from the db and create the listeners from that list instead. These listeners are used to calculate the correct weight for each pet, which is the pet weight minus the base weight.

// Original version
const petNames = ['pet1', 'pet2', 'pet3', 'pet4'];

function initialize() {
    let baseInput = document.getElementById('base');
    petNames.forEach(name => {
        let petInput = document.getElementById(name);
        petInput.addEventListener('input', () => calculateDiff(baseInput, petInput));
    });
}

// Modified version
let petNames = [];

async function initialize() {
    let response = await fetch('/get-pet-names');
    petNames = await response.json();
    let baseInput = document.getElementById('base');

    petNames.forEach(pet => {
        let name = pet.name;
        let petInput = document.getElementById(name);
        petInput.addEventListener('input', () => calculateDiff(baseInput, petInput));
    });
}

The end results works like this:

I created a new end point to Flask which handles the insert to the weights-table:

@app.route('/insert-weights', methods=['POST'])
def insert_weights():
for pet in request.json:
    conn = get_db_con()
    conn.execute('INSERT INTO weights (name, weight) VALUES (?, ?)',
        [pet['name'], pet['weight']])
    print(f"Inserted weight {pet['weight']} for {pet['name']}")
    conn.commit()
    conn.close()

return jsonify({'status': 'ok'})

This is then called by the JavaScript, just like it was done on the original project.

const response = await fetch('insert-weights', {
    method: 'POST',
    headers: {
        'Content-Type': 'application/json'
    },
    body: JSON.stringify(data)
});

Statistics

For the statistics page, I'll need to create separate charts for each species so the weights aren't too spread out. I'll also have to think how I would control what time range is shown. There is no need to fetch everything from the database every time and there is no static solution to show just enough information for mobile.

Previously, I had hard coded each animal to a graph, but now with pets-table I can just fetch the data from there and assign them to the correct species graph. The thing is that I still more data and need a new table for it. This table will contain the species specific graph configurations. I'll also want to add configurable colors to the pets-table.

After implementing the chart creation for each species and basic colors etc. it looks like this:

I also added dynamic graph creation to the statistics.html page using the Jinja2 for loop:

{% for species in species %}
    <canvas id="{{ species }}_graph"></canvas>
{% endfor %}

And similarly passing the species to the template:

@app.route('/statistics')
    def statistics():
    conn = get_db_con()
    species = conn.execute('SELECT distinct species FROM pets WHERE active = true').fetchall()
    species = [item[0] for item in species]
    return render_template('statistics.html', species=species)

Configuration

SQLite has a Command Line Shell, which works similarly to actual SQL, so making manual changes to the database is really easy.

This means that the nicest way to have configurations is to have them in the database. For example having species specific min/max values for the graphs.

Database location/name (optional)

This is done in the config-directory either by default in config_example.py or overwritten in config.py.

The default creates the database petweb.db to the current directory. Overwriting allows us to specify either a relative or absolute path.

A basic setup for the database

What is necessary for having the application working as expected? Modify as needed.

You need to have the sqlite installed to use this. Alternatively you could run these with the application, as shown in the setup_database.py - setup_test_data-function. Just remember to only run them once.

Species

This is required to dynamically create the graphs to the statistics page.

INSERT INTO SPECIES (species, min, max) VALUES ('dog', 20, 35);
INSERT INTO SPECIES (species, min, max) VALUES ('cat', 0, 5);

Pets

Here we can tell what pets we have, their species and color on the statistics page. (optionally also birthdate) We can also exclude pets from showing in the UI by setting the active to false.

INSERT INTO PETS (name, species, active, color) VALUES ('bobby', 'dog', true, 'blue');
INSERT INTO PETS (name, species, active, color) VALUES ('billy', 'cat', true, 'hsl(30, 100%, 40%)');

Custom configs

A key-value table called configs. Supports base_name and graph_range configurations. base_name just gives a name to the base value column and weights entry. graph_range tells in months what time range is shown in the statistics page. Both of these values are optional and have default values. (base and 3)

INSERT INTO CONFIGS (name, value) VALUES ('base_name', 'me');
INSERT INTO CONFIGS (name, value) VALUES ('graph_range', '6');

The basic functionality

Here we have the basic site found from localhost:8080 with the above configs inserted:

And here's statistics page after submitting the values:

Conclusion

I had an easy time migrating the project and feel like it went to a better direction. I didn't really hit any big road blocks and felt like I was constantly progressing with a good pace. Using console.log commands and Firefox debugging helped me solve the JavaScript issues, whereas PyCharm debugging was used when necessary with the backend. For clarity this whole thing took a single weekend to migrate and while there are no tangible changes on the UI, I have a better base to build from.

I'm really happy with the tools that I chose. They were easy and simple to use. I feel I could easily throw together a site if necessary. Only for personal use though, and through LAN.

Creating new and replacing old endpoint was really simple using Flask and SQLite. Somehow it felt funny to link endpoints straight to SQL queries, but it does make sense with this small of a scope.

I like how little set up the project requires, you just need the database and initial rows, and you're good to go. The database is also nice and easy to move, making creating backups nice and simple. SQLite database makes management easier and feels more like home for me because I get to use SQL, which is a huge plus.

With the database handling most of the configuration it feels like implementing new features and options should be pretty easy. I'd like to revision some of the code that I've written, especially the JavaScript side of things as I'm not an expert with it.

Maybe I'll do some improvements later and continue with this. For example having a page dedicated to managing and adding pets would be nice. Or completely having the database configurations accessible through the UI.

And just a reminder to always backup your data, especially if running a Raspberry Pi with an SD card. `rsync has been my choice for now and it is a good option for Linux and Windows (if using Window Subsystem for Linux).

Thanks for reading!

References

• Petweb GitHub repository
• PyCharm Homepage
• PetWeightsWeb repository
• Python.org
• SQLite package tutorial
• SQLite Command Line Shell