Node-red-node-sqlite - .backup command (or otherwise backup an Sqlite Database)

General
1

I can backup an SQLite database using an exec node and the SQLite .backup command:

image
image1458Ă—191 20.8 KB

But trying to do this using the sqlite node gives an error:

image
image1248Ă—191 17.6 KB

I would prefer to use the sqlite node because I have a queue in Node-red to serialise db operations.

Is it possible to use .backup like this?

2

No it is not because the sqlite node deals with SQL queries not SQL commands.

3

Would VACUUM INTO work?

VACUUM (sqlite.org)

4

Yes, that works, thanks @Buckskin

5

Would it be useful for others to rename this thread Backup Sqlite Database or something similar so that others can find it. I had never thought of backing up my databases before ... so thank you for the idea

6

Could you document how you used the vacuum to do the backup so others can benefit from it. Thanks!

7

It's actually very simple, use a change node to set msg.topic
image
Pass that to the node-red-node-sqlite node.

Unlike the commands .backup (which clones the database) and .dump (which exports it to an SQL ASCII file), this is a valid SQL statement in SQLite and is accepted by the node.

It generates a working copy of the database, at minimal size, so you could successfully attach to it with sqlite3 backup.db

It will fail if the INTO file already exists.

I will post an example in Share your flows, tomorrow maybe.
Note, this is my first experience with SQLite, I generally use MySQL so I might be making many mistakes.

2 Likes
8

As @jbudd says it is very simple. I used an inject node to test it. As he also says, the only issue is that an existing file has to be deleted first, so a write file node will also be required set to delete.

I am sure there is probably an easy way to check if the file exists (the write file fails with an error if the file does NOT exist) but the flow below is a quick & easy way to make this work.

[{"id":"f2497bf2a28ff845","type":"inject","z":"aa9a87195d3159a2","name":"Backup \\n HomeAutomation","props":[{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"VACUUM INTO \"/media/usbDrive/HomeAutomation.backup.db\"","x":960,"y":780,"wires":[["97392e943bb49f55","5950684d89bb9964"]]},{"id":"2ada57f15ddab389","type":"sqlite","z":"aa9a87195d3159a2","mydb":"f42c7c9d3f6857fb","sqlquery":"msg.topic","sql":"","name":"Home Automation SQL","x":1440,"y":780,"wires":[["cd25bbb477691c8c"]]},{"id":"cd25bbb477691c8c","type":"debug","z":"aa9a87195d3159a2","name":"SQlite Out","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"true","targetType":"full","statusVal":"payload","statusType":"auto","x":1670,"y":780,"wires":[]},{"id":"5950684d89bb9964","type":"file","z":"aa9a87195d3159a2","name":"Delete SQLite \\n Backup","filename":"/media/usbDrive/HomeAutomation.backup.db","filenameType":"str","appendNewline":true,"createDir":false,"overwriteFile":"delete","encoding":"none","x":1220,"y":860,"wires":[[]]},{"id":"b8368113e717f06a","type":"catch","z":"aa9a87195d3159a2","name":"Catch File Error","scope":["5950684d89bb9964"],"uncaught":false,"x":1220,"y":940,"wires":[["b40e4ff7cafd4abc"]]},{"id":"b40e4ff7cafd4abc","type":"debug","z":"aa9a87195d3159a2","name":"No File","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1420,"y":940,"wires":[]},{"id":"97392e943bb49f55","type":"delay","z":"aa9a87195d3159a2","name":"","pauseType":"delay","timeout":"10","timeoutUnits":"milliseconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"allowrate":false,"outputs":1,"x":1210,"y":780,"wires":[["2ada57f15ddab389"]]},{"id":"f42c7c9d3f6857fb","type":"sqlitedb","db":"/media/usbDrive/HomeAutomation.db","mode":"RWC"}]
1 Like
9

@Buckskin @jbudd
What versions of NR and node.js and what platform is NR running on?
When I run it, I get this error

2/12/2024, 6:10:05 PMnode: 7b13611d76fa2153
VACUUM INTO "pmw/databases/foo2.db" : msg : Object
object
_msgid: "d27b75ef907d05ae"
payload: 1707779405355
topic: "VACUUM INTO "pmw/databases/foo2.db""
error: object
message: "Error: SQLITE_CANTOPEN: unable to open database: pmw/databases/foo2.db"
source: object
stack: "Error: SQLITE_CANTOPEN: unable to open database: pmw/databases/foo2.db"

Running on a RPI running Bullseye
Node-RED v3.02
Node.js v16.20.2
SQLite3 v3.31.1

The foo2.db does not exist so does it have to exist in order to work? Or maybe the versions of NR and node.js are the issue/

10

Try a full pathname?

My file pathname is enclosed in single quotes. Could be relevant.

11

Didn’t help;

2/12/2024, 6:30:28 PMnode: 7b13611d76fa2153
VACUUM INTO 'home/pmw/databases/foo2.db' : msg : Object
object
_msgid: "d2db10363ee64b7e"
payload: 1707780628521
topic: "VACUUM INTO 'home/pmw/databases/foo2.db'"
error: object
message: "Error: SQLITE_CANTOPEN: unable to open database: home/pmw/databases/foo2.db"
source: object
id: "431b21c861943de1"
type: "sqlite"
name: "process SQLite commands"
count: 1
stack: "Error: SQLITE_CANTOPEN: unable to open database: home/pmw/databases/foo2.db"

That’s why I ansked about the platform and versions you use.

12

Try an absolute path (leading slash)

Also ensure the directory exists.

13

That is not a full pathname!
Sorry, I should probably have said absolute pathname.

/home/pmw/databases/foo2.db

I have a raspberry pi zero 2,
OS : Debian GNU/Linux 12 (bookworm)
KERNEL : 6.1.0-rpi8-rpi-v8 aarch64 64bit
Node-red 3.1.5
sqlite3 3.40.1
node.js 18.19.0

foo2.db must not exist for the VACUUM INTO to work.

14

Raspberry Pi - Bullseye
Node-RED - v3.1.5
node.js - 18.19.0
node-red-node-sqlite 1.1.0
SQLite - no idea

My path name is double quotes VACUUM INTO "/media/usbDrive/HomeAutomation.backup.db"

Just change the file name (complete path in SQLite & file write nodes) in the example flow I posted and it should work

15

Head slap, that did it!

I’m claiming that this head cold kept me from trying that early on :roll_eyes:

Thanks for the solution and hopefully it will keep others from spending as much time as I did.

16

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.