Sqlite pragma command

Is there a way to run pragma commands when the node-red-node-sqlite is creating a connection to the database?

The purpose to do this is a performance tuning

I believe you can send a PRAGMA command to the sqlite db in msg.topic.

It looks to me connection is persistent from the moment the sqlite node is configured/connected to the database file (see .

root@husova:/root/.node-red/sqlite# fuser water.sqlite3
/root/.node-red/sqlite/water.sqlite3: 8086
root@husova:/root/.node-red/sqlite#

If above mentioned is true, there is not a sense to send it to the database with every single query.
Or is there a way how to organize it better?

Which settings are you wanting to change? As that article says, some are permanent changes so you might try doing them once and seeing if they stick.

Other than that, you will need to contact the author. I think that @dceejay did the last update to that node.

Who said you need to do that? Send the pragma command just once.

Yes, some settings are permanent, but author recommends to "run all of these each time you connect to the database"

I just want to understand how to control sqlite database behaviour.

In general, what is the best practice to achieve sqlite node sends all settings once when the node-red starts?

Any idea how (which nodes) to achieve such a goal?

You can trigger it with an inject node set to fire once on startup.

Hmmm.. I was thinking the sqlite connection is made when the flow starts (when the DEPLOY button is pushed).
Then the inject node will try to send pragma command(s) over an existing connection.
Am I missing something?

Not sure what you mean by that. You can't send a PRAGMA command to a database you haven't connected to yet. Or am I missing something?

I see. I was thinking it is something like to configure the database connection when establishing the connection.

My mistake.
Finally following code is doing the trick.
Thanks for your help.

[{"id":"75a6e887.af59","type":"inject","z":"7aab9528.6045dc","name":"","props":[{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":true,"onceDelay":"0,1","topic":"pragma journal_mode = WAL;","x":630,"y":720,"wires":[["75d0bc75.b6e894"]]},{"id":"75d0bc75.b6e894","type":"sqlite","z":"7aab9528.6045dc","mydb":"ddcbf2ef.e7b3c8","sqlquery":"msg.topic","sql":"pragma journal_mode = WAL;\npragma synchronous = normal;\npragma temp_store = memory;\npragma mmap_size = 30000000000;","name":"","x":980,"y":780,"wires":[["67cc3bde.9ca4c4"]]},{"id":"67cc3bde.9ca4c4","type":"debug","z":"7aab9528.6045dc","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1250,"y":780,"wires":[]},{"id":"6bdb62e1.12432c","type":"inject","z":"7aab9528.6045dc","name":"","props":[{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":true,"onceDelay":"0.4","topic":" pragma mmap_size = 30000000000;","x":650,"y":840,"wires":[["75d0bc75.b6e894"]]},{"id":"310204a4.613d6c","type":"inject","z":"7aab9528.6045dc","name":"","props":[{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":true,"onceDelay":"0.2","topic":"pragma synchronous = normal;","x":630,"y":760,"wires":[["75d0bc75.b6e894"]]},{"id":"35fce801.363a","type":"inject","z":"7aab9528.6045dc","name":"","props":[{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":true,"onceDelay":"0.3","topic":"pragma temp_store = memory;","x":630,"y":800,"wires":[["75d0bc75.b6e894"]]},{"id":"ddcbf2ef.e7b3c8","type":"sqlitedb","db":"/root/.node-red/sqlite/water.sqlite3","mode":"RWC"}]

Strange thing is, when I was trying to put the whole bunch of commands

pragma journal_mode = WAL;
pragma synchronous = normal;
pragma temp_store = memory;
pragma mmap_size = 30000000000;

in to the injection node topic, or as a "Fixed Statement" it to the sqlite node, only the first command was executed.

I believe the node will only cope with one statement at a time, in which case you will have to send them as a sequence of messages.

Can you suggest how to achieve it? Or my strategy is good enough (I am using 4 injection nodes to send 4 separate messages)?

That's as good as any. You could create them all in one message as multiple lines - then use a split node to split them into separate messages - then a delay node in rate limit node to separate them time wise - then a change node to move the payloads to the topic... - but that is even less obvious... so yes - I'd stick to what you have - simple and readable.

A delay node should not be necessary should it?

If not then you could use a function node containing something like

return [[
  {topic: "pragma journal_mode = WAL;"},
  {topic: "pragma synchronous = normal;"},
  {topic: "pragma temp_store = memory;"},
  {topic: "pragma mmap_size = 30000000000;"},
]]

it may have been the delays that let it work as he did try all in one message and that didn't work so... well - and it needs to be in topic not payload - but maybe yes...

I think that is because the node only copes with a single sql statement at a time. Similarly you cannot put multiple db write statements in one message.

Yes of course, thanks. I will correct my previous post.

1 Like

I am confirming Colin's solution works flawlessly.
Example here:

[{"id":"fa4cccfa.e78fa","type":"inject","z":"7aab9528.6045dc","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":770,"y":560,"wires":[["283268e9.7f86f8"]]},{"id":"283268e9.7f86f8","type":"function","z":"7aab9528.6045dc","name":"","func":"return [[\n\n  {topic: \"pragma journal_mode = WAL;\"},\n\n  {topic: \"pragma synchronous = normal;\"},\n\n  {topic: \"pragma temp_store = memory;\"},\n\n  {topic: \"pragma mmap_size = 30000000000;\"},\n\n]]\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":980,"y":560,"wires":[["75d0bc75.b6e894"]]},{"id":"67cc3bde.9ca4c4","type":"debug","z":"7aab9528.6045dc","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1250,"y":780,"wires":[]},{"id":"75d0bc75.b6e894","type":"sqlite","z":"7aab9528.6045dc","mydb":"ddcbf2ef.e7b3c8","sqlquery":"msg.topic","sql":"pragma journal_mode = WAL;\npragma synchronous = normal;\npragma temp_store = memory;\npragma mmap_size = 30000000000;","name":"","x":980,"y":780,"wires":[["67cc3bde.9ca4c4"]]},{"id":"ddcbf2ef.e7b3c8","type":"sqlitedb","db":"/root/.node-red/sqlite/water.sqlite3","mode":"RWC"}]

It is a pity, the same does not work directly from the sqlite node usign SQL Query Fixed Statement
(see below - this configuration is not working). It looks like a kind of bug in the sqlite node.
image

or the underlying sqlite :slight_smile:
see the notes about execute and execute_script - 11.13. sqlite3 — DB-API 2.0 interface for SQLite databases — Python 2.7.18 documentation