Running node-red in a docker container with sqlite3

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.

image

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