# Sqlite pragma command

**URL:** <https://discourse.nodered.org/t/sqlite-pragma-command/38439>\
**Category:** General\
**Created:** [3 January 2021 04:39 UTC](https://discourse.nodered.org/t/sqlite-pragma-command/38439 "2021-01-03T04:39:25Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Petr](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/petr/32/6413_2.png) [@Petr](https://discourse.nodered.org/u/Petr)\
**Post date:** [3 January 2021 04:39 UTC](https://discourse.nodered.org/t/sqlite-pragma-command/38439/1 "2021-01-03T04:39:26Z")

</div>

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](https://phiresky.github.io/blog/2020/sqlite-performance-tuning/)

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [3 January 2021 10:09 UTC](https://discourse.nodered.org/t/sqlite-pragma-command/38439/2 "2021-01-03T10:09:17Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Petr](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/petr/32/6413_2.png) [@Petr](https://discourse.nodered.org/u/Petr)\
**Post date:** [3 January 2021 12:45 UTC](https://discourse.nodered.org/t/sqlite-pragma-command/38439/3 "2021-01-03T12:45:46Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [3 January 2021 13:45 UTC](https://discourse.nodered.org/t/sqlite-pragma-command/38439/4 "2021-01-03T13:45:01Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [3 January 2021 13:50 UTC](https://discourse.nodered.org/t/sqlite-pragma-command/38439/5 "2021-01-03T13:50:52Z")

</div>

> [@Petr](#):
>
> If above mentioned is true, there is not a sense to send it to the database with every single query.

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

---

<div class="post-metadata">

**Author:** ![Petr](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/petr/32/6413_2.png) [@Petr](https://discourse.nodered.org/u/Petr)\
**Post date:** [3 January 2021 14:03 UTC](https://discourse.nodered.org/t/sqlite-pragma-command/38439/6 "2021-01-03T14:03:11Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Petr](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/petr/32/6413_2.png) [@Petr](https://discourse.nodered.org/u/Petr)\
**Post date:** [3 January 2021 14:04 UTC](https://discourse.nodered.org/t/sqlite-pragma-command/38439/7 "2021-01-03T14:04:00Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [3 January 2021 16:50 UTC](https://discourse.nodered.org/t/sqlite-pragma-command/38439/8 "2021-01-03T16:50:12Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Petr](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/petr/32/6413_2.png) [@Petr](https://discourse.nodered.org/u/Petr)\
**Post date:** [3 January 2021 17:07 UTC](https://discourse.nodered.org/t/sqlite-pragma-command/38439/9 "2021-01-03T17:07:01Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [3 January 2021 17:19 UTC](https://discourse.nodered.org/t/sqlite-pragma-command/38439/10 "2021-01-03T17:19:23Z")

</div>

> [@Petr](#):
>
> Then the inject node will try to send pragma command(s) over an existing connection.

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?

---

<div class="post-metadata">

**Author:** ![Petr](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/petr/32/6413_2.png) [@Petr](https://discourse.nodered.org/u/Petr)\
**Post date:** [3 January 2021 17:54 UTC](https://discourse.nodered.org/t/sqlite-pragma-command/38439/11 "2021-01-03T17:54:02Z")

</div>

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.

```auto
[{"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"}]

```

---

<div class="post-metadata">

**Author:** ![Petr](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/petr/32/6413_2.png) [@Petr](https://discourse.nodered.org/u/Petr)\
**Post date:** [3 January 2021 18:00 UTC](https://discourse.nodered.org/t/sqlite-pragma-command/38439/12 "2021-01-03T18:00:32Z")

</div>

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

```auto
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.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [3 January 2021 18:35 UTC](https://discourse.nodered.org/t/sqlite-pragma-command/38439/13 "2021-01-03T18:35:19Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Petr](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/petr/32/6413_2.png) [@Petr](https://discourse.nodered.org/u/Petr)\
**Post date:** [3 January 2021 22:48 UTC](https://discourse.nodered.org/t/sqlite-pragma-command/38439/15 "2021-01-03T22:48:58Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [4 January 2021 08:59 UTC](https://discourse.nodered.org/t/sqlite-pragma-command/38439/16 "2021-01-04T08:59:28Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [4 January 2021 09:52 UTC](https://discourse.nodered.org/t/sqlite-pragma-command/38439/17 "2021-01-04T09:52:30Z")

</div>

A delay node should not be necessary should it?

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

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

```

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [4 January 2021 10:18 UTC](https://discourse.nodered.org/t/sqlite-pragma-command/38439/18 "2021-01-04T10:18:29Z")

</div>

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...

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [4 January 2021 10:35 UTC](https://discourse.nodered.org/t/sqlite-pragma-command/38439/19 "2021-01-04T10:35:28Z")

</div>

> [@dceejay](#):
>
> he did try all in one message and that didn't work

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.

> [@dceejay](#):
>
> it needs to be in topic not payload

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

---

<div class="post-metadata">

**Author:** ![Petr](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/petr/32/6413_2.png) [@Petr](https://discourse.nodered.org/u/Petr)\
**Post date:** [4 January 2021 10:56 UTC](https://discourse.nodered.org/t/sqlite-pragma-command/38439/20 "2021-01-04T10:56:28Z")

</div>

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

```auto
[{"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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/f/ef59e6eea8a30e23bde917af50344c6f5bdc9c2b.png)

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [4 January 2021 11:31 UTC](https://discourse.nodered.org/t/sqlite-pragma-command/38439/21 "2021-01-04T11:31:12Z")

</div>

or the underlying sqlite 🙂  
see the notes about execute and execute\_script - [11.13. sqlite3 — DB-API 2.0 interface for SQLite databases — Python 2.7.18 documentation](https://docs.python.org/2/library/sqlite3.html#cursor-objects)

[Next page](https://discourse.nodered.org/t/sqlite-pragma-command/38439.md?page=2)