Add support for write mode on sqlite database?

Core Development Feature Requests
1

Apparently the node red sqlite node does not have the ability to mark a database as write-able? If I attempt to execute say a DROP TABLE query via the node red sqlite node, it fails. Per sqlite this is default behavior unless the database is marked write-able. So request a feature be added to the sqlite node for NR to enable this feature.

Details...

Dropping a table in SQLite requires write access to the database file. If a database is opened in read-only mode, you cannot execute a DROP TABLE statement.
To drop a table, you must open the SQLite database in a writable mode. This is the default behavior when connecting to a database unless specifically specified otherwise (e.g., using mode=ro in the URI or SQLITE_OPEN_READONLY flag).
Example of dropping a table (requires writable connection):
Code

DROP TABLE IF EXISTS my_table;
If you are currently connected in read-only mode and need to drop a table:
Close the read-only connection.
Open a new connection to the database in a writable mode.
In Python with sqlite3:
Python

        import sqlite3
        conn = sqlite3.connect('your_database.db') # Default is writable
        cursor = conn.cursor()
        cursor.execute("DROP TABLE IF EXISTS my_table;")
        conn.commit()
        conn.close()
Using the sqlite3 command-line tool.
Code

        sqlite3 your_database.db
        DROP TABLE IF EXISTS my_table;
        .quit
Execute the DROP TABLE statement and Commit the changes and close the connection.
Important Note: If the database file itself has file system permissions set to read-only, even opening it with a writable flag in SQLite will result in an "attempt to write a readonly database" error. In such cases, you would need to adjust the file system permissions of the database file to allow write access before connecting.
2

That says that default mode is writable. To make it read only you have to specify mode=ro (read only). If it were not writable you would not be able to write any records to it.

Edit: what version of node-red-node-sqlite are you using? Check in Manage Palette.

3

Using node-red-node-sqlite 1.1.1 looking at the NR palette information.

4

That is the latest version so it should be ok. I am able to drop tables without issues.

Are you able to insert records into the db?

If you are then please add a Debug node set to Output Complete Message showing what is coming from the db node when you try to DROP it and show us what that says.

5

Sure... I should be able to test this tomorrow.

6

Sorry, the holidays.. slowed me down. I have one additional question, the documentation states that default mode for sqlite3 is persistent. That hold true for NR, correct?

Via NR attempting to create table when database does not exist, this apparently is done by node by default? As persistent? But NR Sqlite Node returns error:

Create table no existing database file...

msg : Object
object
_msgid: "104640bcee9e953d"
payload: 1767930212853
error: object
message: "Error: SQLITE_READONLY: attempt to write a readonly database"
source: object
code: "SQLITE_READONLY"
stack: "Error: SQLITE_READONLY: attempt to write a readonly database"

CLI... Works...
# sqlite3 '/tmp/Solar Array Monitor.sqlite'

If create a table via NR, for existing database file, no errors, no exceptions, the node seem to complete but the payload (array) is empty? Should there not be some indicator of table creation?

Create table existing database... CLI works...
# sqlite3 '/tmp/Solar Array Monitor.sqlite'

CREATE TABLE IF NOT EXISTS 'Solar Array Monitor' (Id INTEGER PRIMARY KEY AUTOINCREMENT, Device TEXT, Port NUMERIC, Result NUMERIC, Seconds REAL);
.tables
Solar Array Monitor
.quit

Now when I try to delete table via NR node (created outside of NR just to try)... No errors no exceptions, but table remains.

msg : Object
object
_msgid: "aa2f1a5412619c71"
payload: array[0]

# sqlite3 '/tmp/Solar Array Monitor.sqlite'

SQLite version 3.46.1 2024-08-13 09:16:08
Enter ".help" for usage hints.
sqlite> .tables
Solar Array Monitor

As for delete of existing table created out side of NR... Node fails to delete table. No errors no exceptions but table remains.

However CLI works... running the same command as the node...

DROP TABLE IF EXISTS 'Solar Array Monitor';

Table is destroyed.

So still at a lost of what is going on. I validated that database file is owned by the id that NR is running under of course.

# ls -l Solar\ Array\ Monitor.sqlite

-rw-r--r-- 1 nodered nodered 12288 Jan  8 19:49 'Solar Array Monitor.sqlite'
7

Now this is interesting... even though I was explicitly setting the file permissions, and looked correct, it was not until I opened an ssh session and explicitly created the sqlite3 file from scratch, only then did the NR based create table and drop table flows work. Unlike before.

Both files were -rw--r--r--, both had ownership 'nodered:nodered' which is the user that NR is running under... odd.

The fact that the sqlite3 node still returns payload[0] as null bugs me, would prefer, like say Microsoft SQL server, notes row added or removed as table is added or removed... but maybe sqlite3 internals are different?

8

Tip: don't use spaces in table names, use underscores, camelCase etc, thank me later.

The fact that the sqlite3 node still returns payload[0] as null bugs me, would prefer, like say Microsoft SQL server, notes row added or removed as table is added or removed... but maybe sqlite3 internals are different?

I think so, I too find the response a bit 'misleading' - but then again, you only will do this once (hopefully).

9

Well SQLite isn't a database server. So it has no running change log as far as I know? Actual SQL servers maintain their transaction logs so are able to be more responsive to change. Best to switch to MariaDB or Postgres to get these kinds of features.