Database - Help / Advice Needed

General
1

Hi all,

I want to start saving my daily Solar Generation stats. So I will obviously start with saving a daily total and the date to some form of database - but what would the best one likely be for this - MySQL ?

After this, I would then maybe like to display a bar chart with all daily values for a Month, or a graph with all months for a Year, or one with all Years shown.

Any advice greatly appreciated

2

InfluxDB + Grafana would be my recommendation. With Node-Red channelling the data to InfluxDB.

3

And would Grafana run inside Node-Red ? What Node do I need to install for this ? I have a Nodered Dashboard with lots of other data and just want to have historical data available in it's own TAB within Node-Red

4

Node-red is just a mediator. Both Grafana and Influx are independent applications.

1 Like
5

So is there anything I can use within Node-Red to display data in the way I hope ? Ideally, to have a dropdown box with the months of the year, to then select and display the monthly data ?

6

With node-red-dashboard you can do this, but grafana is more geared towards advanced charting and can be relatively easy run inside a docker container.

7

In my opinion, a database seems to be a bit of an overkill. You could start with persistent context and migrate to a more powerful system as mentioned above, once/if things getting more complex. It also depends on the system you are running Node-RED on.

We developed some sort of data logger based on Node-RED which collects temperature data from multiple sensors over time (data being stored in persistent context). We used ui_chart for visualization at first and switched to plotly later on. This all runs on a mobile system with limited resources.

1 Like
8

I've got may data saving a daily total for 3 items to an Influxdb, so I'm happy enough with this.

The next stage will be the retrieval of data which should be straight forward, but I assume that I can use this data in some way to display a dynamic bar chart from this data ?

9

3 items with a daily total and a date. That is 1095 data points for a year of data, I concur with @rko that this can be easily done in a context object, no need for a database (then again, anything can act like a database, whether it be a csv, excel, json object).

When it comes to data aggregation (grouping/sum/averaging etc), this is where databases start to shine.

You want some simple charts - keep it in node-red-dashboard and for the data, use/install sqlite, a really powerful standalone database. There is a sqlite node available that installs it directly in within node red. Inserting/retrieval is done using the SQL language, which is relatively simple.

10

It's only a small project as you can see, and so it's a perfect learner, looking at what CAN be done with Node-Red more than anything else I think.

Many thanks for all of the pointers. Node-Red is such a fantastic tool. It's really great for retrieving data from different sources and pooling it together into the one Dashboard

2 Likes
11

Hi,

Node-RED dashboard ( node-red-dashboard) is more than capable of displaying simple graphs. The biggest limitations are: single-user (all users share one web page set) and maybe a few hundred data-points on your graphs for performance reasons.

Both of those limitations indicate when you need 'something more' such as a DB & separate graphing applications like Grafana.

Cheers,

Paul

12

It's not unusual to see people suggesting persistent context storage as a sort of database for small quantities of data with the implication of "upgrading" to a real database later on.

This makes no sense to me.

My Raspberry Pi has Mariadb on it. I only have a few tables with up to 50000 records.
Mariadb is much more powerful than I need: my most complex queries with views, outer joins, calculated fields etc take much, much less than a second and the Pie handles it all with ease.

If I had started with persistent storage I'd have had to write my own code to do any operation on the data.
When the time came to upgrade I would have had to write a humdinger of an INSERT statement to migrate to a db.

Databases are designed to store and process data. They are the right tool for the job and they don't mock you for only having a few records.
Which one you use is a matter of preference.
I use Mariadb because I already know SQL.
Influxdb, MSSQL, SQLite, etc might suit you, your hardware and your data better.

3 Likes
13

had to write my own code to do any operation on the data

This is the key right, if you just have data that you want to display, context works fine, you can store 100.000's of "records" in an array and can be processed extremely fast, even on a Pi - until you want to do any operation or aggregations, those things can become a lot harder quickly without a database.

If you open the capabilities rabbithole of sqlite, there are so many powerful features built-in, it is almost absurd - a fulltext search (FTS) over 10000's of records in a blink of an eye, amazing stuff. It all depends on the needs. A database server in the long term will provide a lot more benefits, without the need of refactoring. Personally I would choose surrealdb over anything else nowadays, regardless what type of data I need to store.

In the end, one needs to learn by doing and that is what OP appears to aim for.

14

Is it still just cloud based?

15

Can run everywhere. I run it in a docker container.

1 Like
16

I need to have another look at it. Thanks!

1 Like
17

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.