# Storing my data **URL:** \ **Category:** TTN Network and Services\ **Created:** [June 13, 2016, 6:35pm UTC](https://www.thethingsnetwork.org/forum/t/storing-my-data/2448 "2016-06-13T18:35:14Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![mrme](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/mrme/32/31870_2.png) [@mrme](https://www.thethingsnetwork.org/forum/u/mrme)\ **Post date:** [June 13, 2016, 6:35pm UTC](https://www.thethingsnetwork.org/forum/t/storing-my-data/2448/1 "2016-06-13T18:35:14Z")
Hi All, Been busy but found some time to hop on the new backend. Got the thing running with ttnctl and get my data with a mqtt client (mqttlens and Node-red). Am wondering whats the best / easy way to store my data in a database. First thing i have to figure out is how to convert my base64 data with a easy function in node-red (i keep getting the whole message instead of the data in the payload. Any help with that is welcome;). Next to store it in the cloud or mysql db on my rpi. For the current project i would like to geohash my data and use it in a map solution. I see there are a couple of solutions out there, but not sure which would be the least complicated setup (very limited programming skills). Thanks in advance for some tips and tricks Ewoud ---
**Author:** ![htdvisser](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/htdvisser/32/45312_2.png) [@htdvisser](https://www.thethingsnetwork.org/forum/u/htdvisser)\ **Post date:** [June 14, 2016, 7:16am UTC](https://www.thethingsnetwork.org/forum/t/storing-my-data/2448/2 "2016-06-14T07:16:12Z")
First, you can use the “payload functions” in the [dashboard](https://staging.thethingsnetwork.org/applications/) to convert the binary data from your nodes to JSON. You can use the [node-red-contrib-ttn](https://www.npmjs.com/package/node-red-contrib-ttn) package to easily get your data from TTN into Node-RED. To store your data in a database, Node-RED is probably not the easiest option. We are experimenting with a `collector` feature (implemented in Go) that subscribes to MQTT and stores data in InfluxDB. The basic idea of any integration is roughly the same. You have a long-running process that on one side subscribes to MQTT, and on the other side connects with a database or an IoT platform. When uplink messages arrive, you just insert them in the database or forward them to the IoT platform. I think the easiest option is to use our [Node.JS client](https://www.npmjs.com/package/ttn) to retrieve the data, maybe do some simple processing and then store it in a database with geo support (I personally really like RethinkDB). Good luck! ---
**Author:** ![joris](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/joris/32/31402_2.png) [@joris](https://www.thethingsnetwork.org/forum/u/joris)\ **Post date:** [June 14, 2016, 8:55am UTC](https://www.thethingsnetwork.org/forum/t/storing-my-data/2448/3 "2016-06-14T08:55:51Z")
The node-red-contrib-ttn doesn’t work, at least not for the staging backend. The data is delivered slightlty different. However in node-red you can easily subscribe with mqtt, pass it through the json converter and process the data. My node-red flow looks like this and works perfectly: ![](https://www.thethingsnetwork.org/forum/uploads/default/original/2X/0/04db528697de1bdfdc11a46797f9bafe42f00ff2.png) ---
**Author:** ![Romeo](https://www.thethingsnetwork.org/forum/letter_avatar_proxy/v4/letter/r/e99b99/32.png) [@Romeo](https://www.thethingsnetwork.org/forum/u/Romeo)\ **Post date:** [June 14, 2016, 9:22am UTC](https://www.thethingsnetwork.org/forum/t/storing-my-data/2448/4 "2016-06-14T09:22:11Z")
Can you be a bit more precise on how exactly you data is delivered differently? ---
**Author:** ![joris](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/joris/32/31402_2.png) [@joris](https://www.thethingsnetwork.org/forum/u/joris)\ **Post date:** [June 14, 2016, 11:40am UTC](https://www.thethingsnetwork.org/forum/t/storing-my-data/2448/5 "2016-06-14T11:40:15Z")
Hi Romeo, skip the “slightly different data” but keep the “doesn’t work”. At least for me. I’m getting this error when I try to use it: msg : error TypeError: Super expression must either be null or a function, not object Because the standard node-red tools worked fine, I didn’t dig into it further. ---
**Author:** ![mrme](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/mrme/32/31870_2.png) [@mrme](https://www.thethingsnetwork.org/forum/u/mrme)\ **Post date:** [June 14, 2016, 3:17pm UTC](https://www.thethingsnetwork.org/forum/t/storing-my-data/2448/6 "2016-06-14T15:17:40Z")
Hi joris, Could you paste the node info on your extract function? Thanks Ewoud ---
**Author:** ![joris](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/joris/32/31402_2.png) [@joris](https://www.thethingsnetwork.org/forum/u/joris)\ **Post date:** [June 14, 2016, 3:38pm UTC](https://www.thethingsnetwork.org/forum/t/storing-my-data/2448/7 "2016-06-14T15:38:05Z")
this is the function to create the INSERT query for mysql: ``` var mqtt_topic = msg.topic; var payload = JSON.stringify(msg.payload).replace(/[\\$'"]/g, "\\$&"); var metadata = msg.payload.metadata.shift(); msg.topic = "INSERT INTO ttn SET " + "timestamp = NOW()," + "json_payload='" + payload + "'," + "payload = '" + msg.payload.payload + "'," + "port = '" + msg.payload.port + "'," + "counter = '" + msg.payload.counter + "'," + "dev_eui = '" + msg.payload.dev_eui + "'," + "frequency = '" + metadata.frequency + "'," + "datarate = '" + metadata.datarate + "'," + "codingrate = '" + metadata.codingrate + "'," + "gateway_timestamp = '" + metadata.gateway_timestamp + "'," + "gateway_time = '" + metadata.gateway_time + "'," + "channel = '" + metadata.channel + "'," + "server_time = '" + metadata.server_time + "'," + "rssi = '" + metadata.rssi + "'," + "lsnr = '" + metadata.lsnr + "'," + "rfchain = '" + metadata.rfchain + "'," + "crc = '" + metadata.crc + "'," + "modulation = '" + metadata.modulation + "'," + "gateway_eui = '" + metadata.gateway_eui + "'," + "altitude = '" + metadata.altitude + "'," + "longitude = '" + metadata.longitude + "'," + "latitude = '" + metadata.latitude + "'," + "mqtt_topic = '" + mqtt_topic + "'" + ";"; return msg; ``` The ttn payload from the devise is unaltered stored in the database (base64). This is the database: ``` CREATE TABLE `ttn` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `json_payload` text, `timestamp` datetime DEFAULT NULL, `payload` text, `port` int(11) DEFAULT NULL, `counter` int(11) DEFAULT NULL, `dev_eui` varchar(16) DEFAULT NULL, `frequency` decimal(4,1) DEFAULT NULL, `datarate` varchar(11) DEFAULT NULL, `codingrate` varchar(11) DEFAULT NULL, `gateway_timestamp` int(11) DEFAULT NULL, `gateway_time` varchar(32) DEFAULT NULL, `channel` int(11) DEFAULT NULL, `server_time` varchar(32) DEFAULT NULL, `rssi` int(11) DEFAULT NULL, `lsnr` int(11) DEFAULT NULL, `rfchain` int(11) DEFAULT NULL, `crc` int(11) DEFAULT NULL, `modulation` varchar(11) DEFAULT NULL, `gateway_eui` varchar(32) DEFAULT '', `altitude` int(11) DEFAULT NULL, `longitude` decimal(7,5) DEFAULT NULL, `latitude` decimal(7,5) DEFAULT NULL, `mqtt_topic` varchar(256) DEFAULT NULL, PRIMARY KEY (`id`) ); ``` ---
**Author:** ![Romeo](https://www.thethingsnetwork.org/forum/letter_avatar_proxy/v4/letter/r/e99b99/32.png) [@Romeo](https://www.thethingsnetwork.org/forum/u/Romeo)\ **Post date:** [June 17, 2016, 8:56am UTC](https://www.thethingsnetwork.org/forum/t/storing-my-data/2448/8 "2016-06-17T08:56:58Z")
Can you specify which version of node.js you are using? ---
**Author:** ![joris](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/joris/32/31402_2.png) [@joris](https://www.thethingsnetwork.org/forum/u/joris)\ **Post date:** [June 17, 2016, 9:57am UTC](https://www.thethingsnetwork.org/forum/t/storing-my-data/2448/9 "2016-06-17T09:57:37Z")
Node v0.10.29 Node-red v.0.13.4 Looks like an old nodejs version to me! I tried to update to nodes 4.4.5, but that was not trivial on my debian jessie distribution (rasp.pi), because that was not part of the standard package list. However, after installing I couldn’t run node-red anymore, because it required the Nodejs 0.10. ---
**Author:** ![DannyE](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/dannye/32/42258_2.png) [@DannyE](https://www.thethingsnetwork.org/forum/u/DannyE)\ **Post date:** [January 24, 2018, 4:08pm UTC](https://www.thethingsnetwork.org/forum/t/storing-my-data/2448/10 "2018-01-24T16:08:31Z")
FYI, Longitude goes from -180 to 180 Your: > [@](#): > > `longitude` decimal(7,5) DEFAULT NULL, Will **not** store Longitude DDD,ddddd For SQL DECIMAL; The first number is the total number of digits stored, and the second is the number after the decimal point. So use DECIMAL(8,5) Then you have another problem: TTN uses 7 decimals: ![](https://www.thethingsnetwork.org/forum/uploads/default/original/2X/1/128b38182624704b8009e73c13f55ec1491b6fa1.jpg) To store those you need: - DECIMAL(9,7) for latitude - DECIMAL(10,7) for longitude For really really precise locations (eg. earthquake measurements in Groningen) you want (10,8) and (11,8) References: - [What MySQL data type should be used for Latitude/Longitude with 8 decimal places? - Stack Overflow](https://stackoverflow.com/questions/12504208/what-mysql-data-type-should-be-used-for-latitude-longitude-with-8-decimal-places) - [Decimal degrees - Wikipedia](https://en.wikipedia.org/wiki/Decimal_degrees) ---
**Author:** ![BoRRoZ](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/borroz/32/44234_2.png) [@BoRRoZ](https://www.thethingsnetwork.org/forum/u/BoRRoZ)\ **Post date:** [January 24, 2018, 4:19pm UTC](https://www.thethingsnetwork.org/forum/t/storing-my-data/2448/11 "2018-01-24T16:19:06Z")
why are you kicking 2 year old topics ? _it’s nice off you to help in anyway but he really… after this long time ?_ ---
**Author:** ![DannyE](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/dannye/32/42258_2.png) [@DannyE](https://www.thethingsnetwork.org/forum/u/DannyE)\ **Post date:** [January 24, 2018, 5:06pm UTC](https://www.thethingsnetwork.org/forum/t/storing-my-data/2448/12 "2018-01-24T17:06:08Z")
Because more and more people will (hopefully) search the Forum for knowledge. This topic has incorrect knowledge, which when used, causes (IMHO serious) issues. I don’t look at when posts where posted, I focus on content ---
**Author:** ![BoRRoZ](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/borroz/32/44234_2.png) [@BoRRoZ](https://www.thethingsnetwork.org/forum/u/BoRRoZ)\ **Post date:** [January 24, 2018, 5:13pm UTC](https://www.thethingsnetwork.org/forum/t/storing-my-data/2448/13 "2018-01-24T17:13:37Z")
> [@DannyE](#): > > I don’t look at when posts where posted, I focus on content would be nice if you could write a[LAB story](https://www.thethingsnetwork.org/labs/) on the subject ? ’ I don’t look at when posts where posted ’ but you should ---
**Author:** ![DannyE](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/dannye/32/42258_2.png) [@DannyE](https://www.thethingsnetwork.org/forum/u/DannyE)\ **Post date:** [January 24, 2018, 6:33pm UTC](https://www.thethingsnetwork.org/forum/t/storing-my-data/2448/14 "2018-01-24T18:33:12Z")
> [@BoRRoZ](#): > > would be nice if you could write a LAB story on the subject ? Frankly, I do not belief in the Blog style because incorrect information is forever cast in stone. Give me Wiki style and I’m your man. Further downside of the LAB Stories is that eventually information is hidden/dispersed in a shitload of half useless stories. No one cleans up or enhances old info with new insights. But I am a Internet heretic, after 28 Net years I belief the best thing that happened to mankind was the burning of the Alexandria library. We should do the same with Google content; like everything in life information should automagically decay. > [@BoRRoZ](#): > > ’ I don’t look at when posts where posted ’ > but you should I still would have written the same reply ---
**Author:** ![BoRRoZ](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/borroz/32/44234_2.png) [@BoRRoZ](https://www.thethingsnetwork.org/forum/u/BoRRoZ)\ **Post date:** [January 24, 2018, 8:00pm UTC](https://www.thethingsnetwork.org/forum/t/storing-my-data/2448/15 "2018-01-24T20:00:23Z")
> [@DannyE](#): > > I still would have written the same reply yeah me to 🕶 ---
**Author:** ![Teacher](https://www.thethingsnetwork.org/forum/letter_avatar_proxy/v4/letter/t/c6cbf5/32.png) [@Teacher](https://www.thethingsnetwork.org/forum/u/Teacher)\ **Post date:** [March 20, 2018, 8:23pm UTC](https://www.thethingsnetwork.org/forum/t/storing-my-data/2448/16 "2018-03-20T20:23:14Z")
> [@DannyE](#): > > the best thing that happened to mankind was the burning of the Alexandria library. The greatest library the world had ever seen. I hope this is just hyperbole… ---
**Author:** ![Teacher](https://www.thethingsnetwork.org/forum/letter_avatar_proxy/v4/letter/t/c6cbf5/32.png) [@Teacher](https://www.thethingsnetwork.org/forum/u/Teacher)\ **Post date:** [March 20, 2018, 9:32pm UTC](https://www.thethingsnetwork.org/forum/t/storing-my-data/2448/17 "2018-03-20T21:32:21Z")
> [@htdvisser](#): > > We are experimenting with a collector feature (implemented in Go) that subscribes to MQTT and stores data in InfluxDB. Any progress? I need help to send my TTN data to [https://corlysis.com/](https://corlysis.com/) which expects line protocol (SQL style) because it can’t handle json. The site turns time-based data into beautiful Grafana graphs with ease. I’m an enthusiast but not a programmer. Suggestions would be greatly appreciated. ---
**Author:** ![sundari](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/sundari/32/25844_2.png) [@sundari](https://www.thethingsnetwork.org/forum/u/sundari)\ **Post date:** [March 22, 2018, 6:57am UTC](https://www.thethingsnetwork.org/forum/t/storing-my-data/2448/18 "2018-03-22T06:57:43Z")
Help: Storing Payload size of 21 byte how to insert payload into mysql database. ---
**Author:** ![htdvisser](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/htdvisser/32/45312_2.png) [@htdvisser](https://www.thethingsnetwork.org/forum/u/htdvisser)\ **Post date:** [March 22, 2018, 10:05am UTC](https://www.thethingsnetwork.org/forum/t/storing-my-data/2448/19 "2018-03-22T10:05:46Z")
> [@Teacher](#): > > Any progress? That’s the [Storage Integration](https://www.thethingsnetwork.org/docs/applications/storage/) now. ---
**Author:** ![sundari](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/sundari/32/25844_2.png) [@sundari](https://www.thethingsnetwork.org/forum/u/sundari)\ **Post date:** [April 12, 2018, 9:42am UTC](https://www.thethingsnetwork.org/forum/t/storing-my-data/2448/20 "2018-04-12T09:42:29Z")
I have done that part somehow using integration.Can I implement HTTP integration to my website hosted by apache server. If yes which URL should I give?Help me please i have very less idea about webdevelopment.