Hello Gurus of Node-Red!
I downloaded the 13 colonies flow by Kyle into my node-red container and realized that I must create a 13 colonies database and qsos table somewhere. Where should the database be created using sqlite3 externally to node-red such that the database is accessible by the node-red container and also who is the owner of the database (root, I presume?)? I did get 13 colonies to work but I had to dispense with the idea of containers for node-red which I really thought was a great use case for containers. Thoughts? There must be a better way to do this...
73's
Richard -- de WD6FIE
Richard,
I really strongly encourage running node-red under docker. It works really well. While we don't run sqllite it should be very straightforward to do so.
I would suggest running the two services under docker-compose.
From
https://nodered.org/docs/getting-started/docker
Add the SQLLite config
## https://nodered.org/docs/getting-started/docker
################################################################################
# Node-RED Stack or Compose
################################################################################
# docker stack deploy node-red --compose-file docker-compose-node-red.yml
# docker-compose -f docker-compose-node-red.yml -p myNoderedProject up
################################################################################
version: "3.7"
services:
node-red:
image: nodered/node-red:latest
environment:
- TZ=Europe/Amsterdam
ports:
- "1880:1880"
networks:
- node-red-net
volumes:
- node-red-data:/data
sqlite3:
image: nouchka/sqlite3:latest
stdin_open: true
tty: true
volumes:
- sqllite-data:/root/db/
volumes:
node-red-data:
sqllite-data:
networks:
node-red-net:
A flow to test it is here
[{"id":"bef3b82a.3f19c8","type":"sqlite","z":"f6f2187d.f17ca8","mydb":"1ad557d5.253ef","sqlquery":"msg.topic","sql":"","name":"Test sqlllite","x":630,"y":260,"wires":[["1cb0cfe3.496468"]]},{"id":"6b09cf28.a919e","type":"function","z":"f6f2187d.f17ca8","name":"Insert","func":"\nmsg.topic = `INSERT INTO user_table (name, surname) VALUES ($name, $surname)`\nmsg.payload = [\"John\", \"Smith\"]\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":410,"y":260,"wires":[["bef3b82a.3f19c8"]]},{"id":"825bfd4f.562f5","type":"inject","z":"f6f2187d.f17ca8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":160,"y":260,"wires":[["6b09cf28.a919e"]]},{"id":"1cb0cfe3.496468","type":"debug","z":"f6f2187d.f17ca8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":830,"y":260,"wires":[]},{"id":"f5ae6e6f.aae438","type":"function","z":"f6f2187d.f17ca8","name":"Create","func":"msg.topic = \"CREATE TABLE user_table(name STRING, surname STRING)\"\nreturn msg;\n\n//user_table (name, surname)","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":410,"y":180,"wires":[["bef3b82a.3f19c8"]]},{"id":"ac308496.73d31","type":"inject","z":"f6f2187d.f17ca8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":160,"y":180,"wires":[["f5ae6e6f.aae438"]]},{"id":"c549807b.0dcba","type":"function","z":"f6f2187d.f17ca8","name":"Select *","func":"msg.topic = \"SELECT * from user_table\"\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":420,"y":340,"wires":[["bef3b82a.3f19c8"]]},{"id":"2d1daa05.4bdb36","type":"inject","z":"f6f2187d.f17ca8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":180,"y":340,"wires":[["c549807b.0dcba"]]},{"id":"1ad557d5.253ef","type":"sqlitedb","db":"sqlite3","mode":"RWC"}]
2 Likes
Chrisn Au,
Thanks for the reply. Now that I am running Node-Red on the Raspberry Pi without containers, it's a simple change of SD cards to run with containers. I think the biggest hurdle will be installing SQLITE3 (not the one that runs under Node-Red -- that's easy to do) but the actual package that can run in the Node-Red container but natively so that I can create the database and table within the database. I presume that the database file needs to be owned by root and not pi or any other user? Any thoughts on creating the database externally to the container and then just moving the file into the container and changing ownership? I guess I need to try that on the next go around and I'll let you know what I find out.
All the best to you Chrisn -- thanks again
Richard de WD6FIE
Richard,
There is no reason why you could not mount the sql lite db file as a bind mount (effectively mounting the file from the file system to be able to read by NR however I would not do this.
Is there any reason why you would not go down the docker compose path and have SQLlite running under docker as well. There would be some work to migrate the data but that is manageable ?
The docker compose file i sent above should be a good start
I may be missing something
Hello Chrisn .. nice solution
I wanted to ask whether that extra sqlite3 (nouchka/sqlite3:latest) container is really needed.
I did a test with just the node-red service, since sqlite is just a file db and doesnt need a seperate server process .. the db file could be created in /data/db directly in node-red-data volume.
me too i tried, based on your example, to use a bind mount .. i found it easier to copy and backup data instead of the named volume ? ( whatever the docker terminology is )
volumes:
- ./node-red-data:/data
1 Like
Guys, pleased it is working for you. If the bind mount works as a file and using the internal node red SQLite server that is awesome - I assume it needs the same permissions as the node red instance ???
A reason for using a seperate SQLite server would be if other services need to access the data - if not, agreed it may be over kill
In our production node red instances - all deployed in swarm we use volumes as it is easier to instrument the backups and management however the great thing about node red is flexibility
Regardless good to see it is working
1 Like
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
