SQLite on IOTstack, Node-Red can not open sqlite database

[lucibuz]

Hello, I managed to install SQLite using docker-compose on a VM running Ubuntu server 20.04 and IOTstack. Just add the following to compose-override.yml and rebuild stack.

services: sqlite3: container_name: sqlite3 image: nouchka/sqlite3:latest stdin_open: true tty: true volumes: - "./volumes/sqlite3:/root/db/" restart: "unless-stopped"

SQLite database/tables works well, the issue I have is using SQLite with Node-red.

Node-Red can not access the database and I.m getting the following error: Node-Red failed to open /home/iot/IOTstack/volumes/sqlite3/nodered.db

I gave the full r/w permissions to the db file but the error persist. Anybody can help on sorting this issue?

[Paraphraser]

Hi - NodeRed and SQLite (as you've implemented it here) are separate containers. Focus on what a "container" means. It means that NodeRed can see it's own data but can't see SQLite's data, and SQLIte can see its own data but not anything belonging to NodeRed.

More to the point, a path beginning with /home/IOTstack is an external path. Containers only see internal paths. The "volumes" fragment for each container's chunk in docker-compose.yml maps between the two. Using your example above, INSIDE the container, the SQLIte process writes to or reads from one or more databases in /root/db. That's ALL it sees. It is Docker that takes care of the mapping to ./volumes/sqlite3. The SQLIte container isn't even aware of the existence of external paths.

Containers are CONTAINED.

I'm hoping that at some point in the last three paragraphs you had the "ahah" moment and understand why what you were trying was not going to work.

You can easily create SQLIte databases within ./volumes/NodeRed/data and, if you add the SQLIte node for NodeRed, you can read/write those databases from NodeRed flows. That's how I started down the IoT path and, in fact, it's only in the last fortnight that I finally migrated the last of my data stored in SQLite databases to InfluxDB, and only 48 hours ago that I removed the SQLIte node from my Dockerfile for Node-Red (which means it's now a hellofalot faster to build the local NodeRed image).

You probably could come up with some kind of cross-mapping strategy in both container definitions which would enable them to see the same external directory but I'd be cautious. I have never tried telling two chunks in a docker-compose.yml file to use the same external path, so Docker may well object. Even if Docker permits it, I have never done any tests to see whether the NodeRed SQLite node wraps all transactions in begin/commit or handles rollback gracefully in deadlock situations. I have no idea what the SQLIte container does.

How do other databases like InfluxDB handle cross-container sharing? They don't. They can't. They are contained. When a NodeRed flow uses an influxdb out node to "write" to an Influx database, it sends an HTTP message containing the INSERT. When Grafana wants to paint a picture, it sends queries via HTTP and gets answers back the same way.

I wrote my first bit of SQL somewhere around 1990 and, while it was never my main focus, I've lived and breathed it ever since, including teaching it to undergrads. I have always loved the regularity of SAL and the simplicity of SQLIte, particularly the "its all in a single file" and the resulting ability to safely copy those files from place to place. No need to export and reimport just to move a database around. Cool!

But, when it comes to things IoT, I honestly think SQLIte is a bit of a box canyon. InfluxDB is the way to go. Sure, InfluxQL isn't SQL and it's a bit of a pain to deal with but, when it comes to integration with all the other containers you need for IOTstack (or similar), there's really no contest.

[lucibuz]

Thank you for the explanation, it make sense.

Therefore I create a SQLite database within ./volumes/NodeRed/data (named nodered.db) as sugest it, and in the SQLIte node the database path is nodered.db (just the database name as it is located in nodered internal folder). It works now.

The project I'm working on was build around SQLite database, so was easy to replicate it as is. But I'll look into migrating to InfluxDB as suggest it.

Thanks for your help. Lucian

[Paraphraser]

To me, that sounds like you are relying on a relative path inside the container and I don't think that's a good idea. We don't control how the base Node-Red gets built. Just because the runtime working directory is "/data" today doesn't mean it will always be that way. It's the kind of thing that can sneak in one day when you do a docker-compose pull and the first hint you get that something might be wrong is when you wonder why you don't seem to be getting any current data.

So, to go back over the steps…

Starting from this part of docker-compose.yml:

  nodered:
    container_name: nodered
    build: ./services/nodered/.
    …
    volumes:
      - ./volumes/nodered/data:/data
    …

the line we are interested in is:

      - ./volumes/nodered/data:/data

Remove the leading "-" and split about the "colon" and you wind up with:

• ./volumes/nodered/data (the external path)
• /data (the internal path)

The leading "." on the external path implies "~/IOTstack" so:

• ~/IOTstack/volumes/nodered/data (the external path)
• /data (the internal path)

My advice is to create a "Databases" folder at the external path (less risk of collisions with anything else Node-Red might do - remember Node-Red thinks it owns the /data folder):

$ sudo mkdir ~/IOTstack/volumes/nodered/data/Databases

and then move your existing SQLite database into that folder:

$ sudo mv /path/to/my_database.db ~/IOTstack/volumes/nodered/data/Databases

You will need to give that root ownership:

$ sudo chown root:root ~/IOTstack/volumes/nodered/data/Databases/my_database.db

I've just looked at my last SQLite database sitting inside NodeRed. It hasn't been active since Feb 2020 but it still has historical data that I'm yet to migrate to InfluxDB. It has root ownership (as above) and a permission mask of "-rwxr-xr-x". I can't think why it would ever have needed execute permission and I don't remember setting it like that but it's just possible that it turned out to be necessary for some reason I've forgotten. Keep that in mind if you have trouble.

I'd start off with:

$ sudo chmod 644 ~/IOTstack/volumes/nodered/data/Databases/my_database.db

At this point, you have:

• ~/IOTstack/volumes/nodered/data/Databases/my_database.db (the external path)
• /data/Databases/my_database.db (the internal path)

Now:

1. Drag an SQLite node onto the Node-Red canvas and open the node to start configuring it.
2. The "Database" popup should be at "Add new sqlitedb..." but select that if it isn't.
3. Click the pencil icon to the right of the "Database" popup.
4. In the "Database" field, type "/data/Databases/my_database.db". This is the internal path.
5. The "Mode" should be Read-Write-Create.
6. Click "Add" then click "Done".

I recommend leaving the "name" field empty. That way, the node gets "/data/Databases/my_database.db" on the canvas and I find that useful to know.

Another word to the wise. If you have multiple SQLite databases, resist the urge to get the first one working then creating the second one via copy-and-paste of the first one. If you edit the clone, it will also affect the original and you wind up wondering why your first flow has stopped working.

So, that's how I did it, along with the "why" (to the best of my recollection). I guess you'll compare/contrast your own setup and decide what, if anything, needs to change.

[lucibuz]

Do I have to list 2 volumes?

nodered: … volumes: - ./volumes/nodered/data:/data - ./volumes/nodered/data/Databases/my_database.db:/data/Databases/my_database.db …

I did all as instructed and it works, not modifying the docker-compose.yml. Thanks.

[Paraphraser]

No. You only need the - ./volumes/nodered/data:/data. Everything inside the "data" directory (files, folders, as deep as the folder structure goes) is all shared.

[lucibuz]

I see you as a guru of SQLite / NodeRed / IOTstack :-) so I have few more questions.

I've just looked at my last SQLite database sitting inside NodeRed. It hasn't been active since Feb 2020 but it still has historical data.

Form your explanations I'm getting that rebuilding the NodeRed volume will not effect the Databases folder. is that correct?

Another word to the wise. If you have multiple SQLite databases, resist the urge to get the first one working then creating the second one via copy-and-paste of the first one. If you edit the clone, it will also affect the original and you wind up wondering why your first flow has stopped working.

For creating new database my_database2.db do I have to create a file itself and inject the table or is ok just to name the path in the SQLite node "/data/Databases/my_database2.db" and when inject the table the file will be created automatically? Whats the good practice?

If I backup the file my_database.db (copy it to a different location) after restore it, data (stored till backup time) will be present and readable by Nodered?

[Paraphraser]

If you erase ./volumes or ./volumes/nodered then you will indeed lose your databases. But if you mean "will re-running the menu destroy databases?", the answer is no. They will be perfectly safe. If you run backups, they will be backed-up. If you restore a backup, the SQLIte databases will come back as of the time they were backed-up. That's the great thing about SQLIte databases - they are copy safe.

On your second question, I am reasonably sure that I have always created a stub (a table defining the columns needed with one row of dummy data that I delete later). I am also reasonably sure that you need to alter the schema to add new columns before you try to insert data (ie the SQLIte node won't auto-add new columns, the insert will just fail).

Even influx needs at least a "create table table name" but, after that, you can add measurements, series, fields and tags and the Influx node takes care of the details. That can be good and bad. A typo can easily add junk to your database and cleaning up can be tricky. On balance, I'd prefer it if influx insisted that everything was predefined.

Third question. Yes! Copy the file, zip the file, send it on a trip around the moon, do whatever you want to the file, it works.

[lucibuz]

Thank you for your time and patience for explaining all this. I have some questions regarding IOTstack auto-backup and update, I will contact you on discord to not alter this topic. Much appreciated.