How to Build a Logbook with Node-RED and SQLite: A Technical Adventure

Have you ever wanted to keep a detailed record of your nautical voyages? So did we! And the best part is that we did it by combining modern tools like Node-RED, SQLite, and a Raspberry Pi 4. In this article, we’ll walk you through step-by-step how we created a complete system to manage navigation entries, from hardware to software. Get ready to set sail into the world of development!

1. The Hardware: The Digital Compass

The heart of our project is a Raspberry Pi 4, a small but powerful device that acts as the brain of the system. With its low power consumption and ability to run web applications, it’s perfect for embedded projects like this one. Key Features:

• Operating System: Raspberry Pi OS (Debian-based), lightweight and optimized for the RPi.
• Connectivity: Integrated Wi-Fi and Bluetooth to communicate with other devices.
• Database: We used SQLite3, an embedded database ideal for small or medium-sized projects.

2. The Software: Node-RED to the Rescue

Node-RED is a flow-based visual programming tool. It’s perfect for connecting APIs, databases, and sensors without needing to write much code. Here’s how we used it:

Main Flows:

NMEA Data Reception:

• We connected a GPS receiver via TCP to receive NMEA messages.
• We processed this data to extract key information such as position, speed, and heading.

RESTful API:

We created endpoints to manage logbook entries:

• /api/add-entry: Add new entries.
• /api/edit-entry: Modify existing entries.
• /api/delete-entries: Delete one or more entries.
• /api/entries: List entries with filters and pagination.

Web Interface:

We used HTML, CSS, and JavaScript to create a user-friendly interface where:

• View all entries.
• Filter by type (manual/automatic) and date range.
• Add, edit, or delete entries.

3. Database: SQLite3, Lightweight and Powerful

The database is the anchor of our system. We used SQLite3 to store all logbook entries. Here’s a look at the main table:

CREATE TABLE entradas (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    estado_navegacion TEXT,
    categoria TEXT,
    ocurrio_hace TEXT,
    estado_mar TEXT,
    visibilidad TEXT,
    nubosidad INTEGER,
    mensaje TEXT,
    posicion TEXT,
    velocidad REAL,
    rumbo REAL,
    tipo_entrada TEXT,
    fecha DATETIME DEFAULT CURRENT_TIMESTAMP
);

Advantages of SQLite:

• No separate server required.
• Ideal for small or medium-sized projects.
• Compatible with Node-RED thanks to the node-red-node-sqlite node.

4. File Structure: Everything in Its Place

Here’s a tree view of the file structure we used. It’s simple but effective for keeping the project organized.

/Diario/
├── frontend/
│   ├── index.html          # Main page: list of entries
│   ├── add-entry.html      # Form to add/edit entries
│   ├── edit-entry.html     # Form to edit entries
│   └── css/
│       └── styles.css      # Custom styles
├── static/
│   ├── assets/
│   │   └── bootstrap.min.css # Bootstrap for responsive design
│   └── js/
│       ├── main.js         # General scripts
│       ├── list-entries.js # Logic to list entries
│       └── add-entry.js    # Logic to add/edit entries
└── diario.db               # SQLite database 

5. Key Features

Filters and Pagination

• Filter entries by type (manual/automatic) and date range.
• Use pagination to handle large volumes of data.

Automatic Entries

Thanks to the GPS receiver, we can automatically record data such as position, speed, and heading.

Intuitive Interface

• Clean and responsive design with Bootstrap.
• Disabled or read-only fields clearly differentiated.

6. Final Thoughts

This project is a perfect example of how to combine hardware and software to solve real-world problems. With a Raspberry Pi 4, Node-RED, and SQLite, we created a robust and scalable system that can adapt to different needs. What’s Next?

• Migrate to a more robust database like PostgreSQL if the project grows.
• Add graphs to visualize historical data.
• Integrate additional sensors to measure temperature, humidity, etc.