Best way to get sqlite data from Raspberry pi to a remote server

[mesand]

Hi all,

I am writing data from an arduino nano to my raspberry pi.

I have a remote server on linode and I typically communicate with the pi using mqtt. This communication usually consists of turning pumps on and getting sensor data.

Now I am wondering how I can get more data from the pi. I want to be able to get sqlite data from the pi to the server. What would be a good way to do this? Lets say every fix seconds get a row of sqlite data from the pi to the linode server. Would mqtt still be good for this or are there better ways to get sqlite data from the pi to the server?

[Heater]

You could run an MQTT broker on your server. Have your Pi publish the data to it. Have a process on your server subscribe to that data and do whatever it wants with it.

In fact that is the kind of thing MQTT was invented for.

Be sure to use TLS for the MQTT connection for security.

[mesand]

Thank you. Currently I am on port 1883 and not using any ssl. I havent found much information on mqtt security. I'm using the paho library to send and receive data. I didnt think using ssl or tcp was too necessary since I am just sending testing phase sensor data, but I will harden it.

Is it possible to only allow one person/user to publish and subscribe to certain topics on a raspberry pi? I have a flask server and i turn pumps on and receive data to/from the flask app. But I can also publish and subscribe from other machines. How can I allow mqtt communication from only people on the flask app and not other machines?

[Heater]

I have no idea if or how MQTT deals with different users.

I use the NATS messaging system instead of MQTT: https://nats.io/

NATS can be used for pub/sub messaging very much like MQTT. It is very easy to configure users and passwords with NATS and restrict different users to different subjects(topics). It's also very easy to secure NATS as well. The documentation is excellent. Perhaps you would like to take a look at that.