MQTT messages store in mysql

[Why Tea]

I am looking for code examples in nodeJS which subscribe to the MQTT broker; and when messages (e.g. periodic voltage and current readings) arrive, they will be stored into a mysql database. Couldn't seem to find anything on the Web. Has anyone written something similar? Any pointers or good links will be much appreciated too. 

IoT devices  pub --> broker  --> data collector --> mysql    (The "data collector" is what I am after)

I'm also thinking about a Web front-end to read the mysql data and present to the user. New IoT devices can also be added from there and inserted into the database.

/WT

              

                    

[Stefano Costa]

MQTTWarn by JP Mens is a Python daemon that has plenty of "actions" plugins, MySQL data store included and may fit your need. Not NodeJS but very easy to be set up:

https://github.com/jpmens/mqttwarn/blob/master/README.md#mysql

Regarding Web frontend, if dealing with MySQL this not really MQTT related, you can go with one of the many Node web frameworks (or Ruby  on Rails Activerecords, etc).

NodeRED is a Node based option for building MQTT to MySQL logic and much more, with a completely different approach if compared to MQTTWarn (graphical programming):

http://nodered.org

--

  Stefano Costa

  R&D, Managing Director

  M. +39 335 6565749

  Skype stefanocosta.bluewind

  Twitter @stefanobluewind

  www.bluewind.it

--
To learn more about MQTT please visit http://mqtt.org
---
You received this message because you are subscribed to the Google Groups "MQTT" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mqtt+uns...@googlegroups.com.
To post to this group, send email to mq...@googlegroups.com.
Visit this group at http://groups.google.com/group/mqtt.
For more options, visit https://groups.google.com/d/optout.

[Darren Clark]

It's also pretty trivial thing to write. I wrote a quick node daemon to write status JSON to MongoDB. I think it was about 15 lines of code. I'll look for that and see if I can send it to you.

-Darren

[Why Tea]

Thanks Stafano for the useful info and the links. Will read through them to see if they fit my needs.  /WT 

[Why Tea]

Thanks Darren. Looking forward to your code :)  /WT

[Paul Fremantle]

Stefano, 

I wasn't aware of JP's mqttwarn. I *love* it. Great job JP!

Paul

--

Paul Fremantle

Part-time PhD student - School of Computing

email: paul.fr...@port.ac.uk, pa...@fremantle.org

twitter: pzfreo / skype: paulfremantle / blog: http://pzf.fremantle.org

Co-Founder, WSO2
Apache Member and Committer
07740 199 729

[Stefano Costa]

Yes definitely a good job. And his Pista backend to OwnTracks family of apps is another well crafted project, check it.
S

[Dominik Obermaier]

There's an blog post which discusses databases + MQTT: [1]. This discusses a centralised DB persistence approach we had great success at higher volume MQTT deployments. The example uses a Java broker, not a NodeJS one, though. The principles remain the same, though.

Best,

Dominik

[1] http://www.hivemq.com/mqtt-sql-database/

[Andy Robinson]

What you are really looking for is a time series database. Yes you can store in any db but a good time series backend will handle a lot of the magic for you. The most exciting one right now is InfluxDB. Go look it up and have fun. Best part is all interaction is over an HTTP API so no external libraries. Also make sure you are following along the 0.9 path. Huge changes so any work in 0.8 will be wasted. Finally don't waste time with the online playground. It's 0.8.

Andy

[Why Tea]

Thanks Andy. InfluxDB seems to be pretty new compared to something more established like Hadoop. I'm totally new to TSDB, so I'd really like to hear your opinion. Thanks.  /WT

[Andy Robinson]

Yes it is quite new but there are some really compelling reasons to look at it.  I mean goodness gracious Docker is only a couple years old and you have some people who think you can't operate a company without it now.  Ok, I'm being a little ridiculous with that statement but I think people who wait for 2 or 3 years will end up using relatively old tech these days.. kinda scary :-)

First, background on me so you have context on my opinions.  I come from 15 years in the the industrial automation world where we have been running essentially time series databases for well over 20 years, going all the way back to the VAX days.  The concepts around storing sensor data in a retrievable form that lets you apply time series concepts is well baked.  What is different today is the technologies available make it dramatically easier and faster.  

I have been learning the MQTT and InfluxDB world over the past couple months so I'm new to it just like anyone else.  The most prominent difference between Influx and something like Hadoop comes down to simplicity.  Influx is literally, and this is no exaggeration, a single executable file and a config file if you choose to tweak and tune a little bit.  That's it.  The API is very straightforward and as long as you are comfortable making Rest-like calls then you will find it very quick to pick up.  InfluxDB actually sits on top of BoltDB which is a pure key-value datastore written in GO.  They have actually changed out the underlying DB a few times through early development looking for better and better solutions.  So, at it's core it's really just a thin API and management layer on top of an ultra modern distributed key-value datastore.  For all we know they may change out the underlying store again if they find something better.  

I encourage you to look up some of the talks the founder, Paul Dix, has given.  It's really interesting to see how they got to where they are.  They actually wrote Influx to underpin a commercial offering but then the offering didn't really gain traction but they had some great tech under it so they open sourced that tech.

I know right now over on the Influx google group there is discussion about how to build something that makes sending MQTT data over even easier than it already is.  One of the guys who helped write MQTT warn is in the middle of the convo to see what he can do to help.  Getting native MQTT support in Influx would be a tiny effort.  The bigger question being tossed around is if you want to start weighing down the otherwise lite weight Influx core with everyone's favorite protocol.  My guess if you will have people write really nice light weight forwarders instead that run like a microservice relaying data over to InFlux.

Hope this helps and it's really cool straddling these two groups and seeing the synergy.  

- Andy

[Stefano Costa]

On 30/03/2015 07:45, Andy Robinson wrote:
> Yes it is quite new but there are some really compelling reasons to look
> at it. I mean goodness gracious Docker is only a couple years old and
> you have some people who think you can't operate a company without it
> now. Ok, I'm being a little ridiculous with that statement but I think
> people who wait for 2 or 3 years will end up using relatively old tech
> these days.. kinda scary :-)
>

I want to thank you Andy for this brief review and comment about
InfluxDB I knew just a few details about before. This is the added value
of this list!
S

[Andy Robinson]

I'm sure you typed "brief" with your sarcasm font :-).  In all seriousness I suspect the same people actively exploring MQTT are the same ones that would play with Influx so it's a natural fit.  Of note, I found Influx by looking at something related to MQTT and it sent me that direction.  One big happy ecosystem.

-andy