# SQLite question

**URL:** <https://discourse.nodered.org/t/sqlite-question/2434>\
**Category:** General\
**Created:** [21 August 2018 00:08 UTC](https://discourse.nodered.org/t/sqlite-question/2434 "2018-08-21T00:08:31Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![zubenubi](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zubenubi/32/44576_2.png) [@zubenubi](https://discourse.nodered.org/u/zubenubi)\
**Post date:** [21 August 2018 00:08 UTC](https://discourse.nodered.org/t/sqlite-question/2434/1 "2018-08-21T00:08:31Z")

</div>

I am using node-red-node-sqlite to connect to a sqlite3 db. I need to load a sqlite extension prior to submitting a select.

When I do this at the command line, it seems to work ok:  
select load\_extension('mod\_spatialite');  
SELECT id, (6371000 \* acos ( cos ( radians(42.48) ) \* cos ( radians ( latitude ) ) \* cos( radians( longitude ) - radians(-70.8) ) + sin ( radians(42.48) ) \* sin( radians( latitude ) ) ) ) AS distance, ssid from log order by distance;

I am getting this from nodered:

Error: SQLITE\_ERROR: not authorized

The error seems to be in response to the ‘select load\_extension() call.

I have tried to use msg.topic, fixed statement and prepared statement.

So Im wondering if:  
-There might be a way to have the select load\_extension automatically execute upon connect? From what I understand sqlite3 can accept a 2nd argument (after the db name) that is a SQL statement.

-Perhaps Im not using the node correctly?

---

<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:** [21 August 2018 07:53 UTC](https://discourse.nodered.org/t/sqlite-question/2434/2 "2018-08-21T07:53:57Z")

</div>

I haven't used load\_extension but I wonder whether it is a path issue. Try specifying the full path and filename of the extension file in the load\_extension call. Try it in the command line first. If you are using Windows then I don't know what you need to do with the backslash characters in the path. You may need someone else to comment on that.

---

<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:** [21 August 2018 09:59 UTC](https://discourse.nodered.org/t/sqlite-question/2434/3 "2018-08-21T09:59:54Z")

</div>

We currently don't expose load\_extension so it would need to be added.  
How we do that needs a bit of thought, and input from you folk. Is it common to add more than one extension ? or is it only one at a time ? Do they tend to be statically loaded ? (ie once at start time) - or need to be added during run time?

---

<div class="post-metadata">

**Author:** ![ghayne](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ghayne/32/39_2.png) [@ghayne](https://discourse.nodered.org/u/ghayne)\
**Post date:** [21 August 2018 10:05 UTC](https://discourse.nodered.org/t/sqlite-question/2434/4 "2018-08-21T10:05:41Z")

</div>

As a work-around, can it be loaded from the command line using an exec node?

---

<div class="post-metadata">

**Author:** ![ghayne](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ghayne/32/39_2.png) [@ghayne](https://discourse.nodered.org/u/ghayne)\
**Post date:** [21 August 2018 10:10 UTC](https://discourse.nodered.org/t/sqlite-question/2434/5 "2018-08-21T10:10:54Z")

</div>

You will have to use :

From the [command-line shell](https://www.sqlite.org/cli.html), extensions can be loaded using the ".load" dot-command. For example:

> .load ./YourCode

---

<div class="post-metadata">

**Author:** ![zubenubi](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zubenubi/32/44576_2.png) [@zubenubi](https://discourse.nodered.org/u/zubenubi)\
**Post date:** [21 August 2018 12:08 UTC](https://discourse.nodered.org/t/sqlite-question/2434/6 "2018-08-21T12:08:50Z")

</div>

I tried the full path on the command line and it seemed to work, so I tried it in node-red-node-sqlite as a fixed statement. This only resulted in the same 'not authorized' error.  
This is on a raspberry pi

---

<div class="post-metadata">

**Author:** ![zubenubi](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zubenubi/32/44576_2.png) [@zubenubi](https://discourse.nodered.org/u/zubenubi)\
**Post date:** [21 August 2018 12:16 UTC](https://discourse.nodered.org/t/sqlite-question/2434/7 "2018-08-21T12:16:08Z")

</div>

The extension I am using is to enable spatial data functions (spatialite). Their docs indicate that loading the extension dynamically seems to be preferred. Here is a link to that doc:  
[https://www.gaia-gis.it/fossil/libspatialite/wiki?name=mod\_spatialite](https://www.gaia-gis.it/fossil/libspatialite/wiki?name=mod_spatialite)

Looking at other comments, I did try to use the .load 'full-path-to-mod\_spatialite' and it seemed to work. So I am considering using a .sqliterc file (which I believe permits the use of any of the dot commands) and will have to test.

I am hoping that node-red-node-sqlite will cause .sqliterc to be used and that a .load call therein will be successful.

Many Thanks for the ideas/discusssion

---

<div class="post-metadata">

**Author:** ![zubenubi](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zubenubi/32/44576_2.png) [@zubenubi](https://discourse.nodered.org/u/zubenubi)\
**Post date:** [21 August 2018 12:17 UTC](https://discourse.nodered.org/t/sqlite-question/2434/8 "2018-08-21T12:17:22Z")

</div>

Yes I have tried this workaround and it works but I would of course prefer to use the node

---

<div class="post-metadata">

**Author:** ![zubenubi](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zubenubi/32/44576_2.png) [@zubenubi](https://discourse.nodered.org/u/zubenubi)\
**Post date:** [21 August 2018 12:44 UTC](https://discourse.nodered.org/t/sqlite-question/2434/9 "2018-08-21T12:44:31Z")

</div>

So, Sorry to report that the node-red-node-sqlite does not appear to use the .sqliterc file.  
I added a .sqliterc file with one line that uses the dot command .load [extension].

When I start sqlite3 command line, the .sqliterc file is read and I am able to execute a select statement that includes functions that are only available if the extension is loaded..

When I submit the same select statement in the node, an error is returned that indicates the extension is not loaded.

So if anyone has any ideas about how I might load an extension in a flow that uses node-red-sqlite-node I am eager to try. Otherwise I guess I will need to use the workaround.

Thanks

---

<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:** [21 August 2018 16:36 UTC](https://discourse.nodered.org/t/sqlite-question/2434/10 "2018-08-21T16:36:24Z")

</div>

> [@Colin](#):
>
> If you are using Windows then I don't know what you need to do with the backslash characters in the path. You may need someone else to comment on that.

Windows generally supports forward slashes. If you use Windows style backslashes, you have to escape them if in a double-quoted string.

---

<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:** [21 August 2018 16:49 UTC](https://discourse.nodered.org/t/sqlite-question/2434/11 "2018-08-21T16:49:44Z")

</div>

At the risk of repeating myslef...

> [@dceejay](#):
>
> We currently don't expose `load_extension` so it would need to be added.  
> How we do that needs a bit of thought, and input from you folk. Is it common to add more than one extension ? or is it only one at a time ? Do they tend to be statically loaded ? (ie once at start time) - or need to be added during run time?

---

<div class="post-metadata">

**Author:** ![zubenubi](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zubenubi/32/44576_2.png) [@zubenubi](https://discourse.nodered.org/u/zubenubi)\
**Post date:** [21 August 2018 17:09 UTC](https://discourse.nodered.org/t/sqlite-question/2434/12 "2018-08-21T17:09:54Z")

</div>

My input is that I am only currently loading a single extension at this time, but I could imagine loading multiple extensions.

I would prefer the dynamic loading option because I know it works and the docs from spatialite seem to prefer that method as well.

Thanks

---

<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:** [21 August 2018 19:00 UTC](https://discourse.nodered.org/t/sqlite-question/2434/13 "2018-08-21T19:00:11Z")

</div>

Ok - so allowing it via a msg property probably makes sense rather than adding to the config.  
If you could try something for me... find the file `sqlite.js` in node\_modules/node-red-node-sqlite and edit it... around line 50 - add the if statement below

```
        node.on("input", function(msg) {
            if (msg.hasOwnProperty("extension")) {
                node.mydbConfig.db.loadExtension(msg.extension, function(err) {
                    if (err) { node.error(err,msg); }
                });
            }

```

Then you can send in a message with msg.extension set to the full filename of the extension.  
Let me know if it helps ...

---

<div class="post-metadata">

**Author:** ![zubenubi](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zubenubi/32/44576_2.png) [@zubenubi](https://discourse.nodered.org/u/zubenubi)\
**Post date:** [21 August 2018 19:45 UTC](https://discourse.nodered.org/t/sqlite-question/2434/14 "2018-08-21T19:45:29Z")

</div>

OK so I added the if statement at around line 50, restarted and seeing this:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/6/616a4127d41399f9debf5cb1e6c7735b0d3df343.png)

I was only supposed to add the 'if' right? I omitted the line starting with node.on.

---

<div class="post-metadata">

**Author:** ![zubenubi](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zubenubi/32/44576_2.png) [@zubenubi](https://discourse.nodered.org/u/zubenubi)\
**Post date:** [21 August 2018 19:48 UTC](https://discourse.nodered.org/t/sqlite-question/2434/15 "2018-08-21T19:48:53Z")

</div>

Tried to reinstall node-red-node-sqlite and got this error log:

0 info it worked if it ends with ok  
1 verbose cli [ '/usr/bin/node',  
1 verbose cli '/usr/bin/npm',  
1 verbose cli 'install',  
1 verbose cli 'node-red-node-sqlite' ]  
2 info using npm@5.6.0  
3 info using node@v8.11.4  
4 verbose npm-session 18d7a0b8eaa6b47e  
5 silly install loadCurrentTree  
6 silly install readLocalPackageData  
7 http fetch GET 200 [https://registry.npmjs.org/node-red-node-sqlite](https://registry.npmjs.org/node-red-node-sqlite) 89ms (from cache)  
8 silly pacote tag manifest for node-red-node-sqlite@latest fetched in 192ms  
9 silly install loadIdealTree  
10 silly install cloneCurrentTreeToIdealTree  
11 silly install loadShrinkwrap  
12 silly install loadAllDepsIntoIdealTree  
13 silly saveTree node-red-node-sqlite@0.3.2  
14 verbose stack Error: Refusing to install package with name "node-red-node-sqlite" under a package  
14 verbose stack also called "node-red-node-sqlite". Did you name your project the same  
14 verbose stack as the dependency you're installing?  
14 verbose stack  
14 verbose stack For more information, see:  
14 verbose stack [https://docs.npmjs.com/cli/install#limitations-of-npms-install-algorithm](https://docs.npmjs.com/cli/install#limitations-of-npms-install-algorithm)  
14 verbose stack at checkSelf (/usr/lib/node\_modules/npm/lib/install/validate-args.js:64:14)  
14 verbose stack at Array. (/usr/lib/node\_modules/npm/node\_modules/slide/lib/bind-actor.js:15:8)  
14 verbose stack at LOOP (/usr/lib/node\_modules/npm/node\_modules/slide/lib/chain.js:15:14)  
14 verbose stack at /usr/lib/node\_modules/npm/node\_modules/slide/lib/chain.js:18:7  
14 verbose stack at hasMinimumFields (/usr/lib/node\_modules/npm/lib/install/validate-args.js:30:12)  
14 verbose stack at Array. (/usr/lib/node\_modules/npm/node\_modules/slide/lib/bind-actor.js:15:8)  
14 verbose stack at LOOP (/usr/lib/node\_modules/npm/node\_modules/slide/lib/chain.js:15:14)  
14 verbose stack at chain (/usr/lib/node\_modules/npm/node\_modules/slide/lib/chain.js:20:5)  
14 verbose stack at /usr/lib/node\_modules/npm/lib/install/validate-args.js:16:5  
14 verbose stack at /usr/lib/node\_modules/npm/node\_modules/slide/lib/async-map.js:52:35  
14 verbose stack at Array.forEach ()  
14 verbose stack at /usr/lib/node\_modules/npm/node\_modules/slide/lib/async-map.js:52:11  
14 verbose stack at Array.forEach ()  
14 verbose stack at asyncMap (/usr/lib/node\_modules/npm/node\_modules/slide/lib/async-map.js:51:8)  
14 verbose stack at module.exports (/usr/lib/node\_modules/npm/lib/install/validate-args.js:15:3)  
14 verbose stack at Array. (/usr/lib/node\_modules/npm/node\_modules/slide/lib/bind-actor.js:15:8)  
15 verbose cwd /home/pi/.signalk/red/node\_modules/node-red-node-sqlite  
16 verbose Linux 4.14.52-v7+  
17 verbose argv "/usr/bin/node" "/usr/bin/npm" "install" "node-red-node-sqlite"  
18 verbose node v8.11.4  
19 verbose npm v5.6.0  
20 error code ENOSELF  
21 error Refusing to install package with name "node-red-node-sqlite" under a package  
21 error also called "node-red-node-sqlite". Did you name your project the same  
21 error as the dependency you're installing?  
...  
22 verbose exit [1, true]

---

<div class="post-metadata">

**Author:** ![zubenubi](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zubenubi/32/44576_2.png) [@zubenubi](https://discourse.nodered.org/u/zubenubi)\
**Post date:** [21 August 2018 20:31 UTC](https://discourse.nodered.org/t/sqlite-question/2434/16 "2018-08-21T20:31:12Z")

</div>

This error resolved after a reboot. Go Figure. I tried a service restart first which led to experiencing the above error. After a reboot my sqlite node is back, so on to testing.

---

<div class="post-metadata">

**Author:** ![zubenubi](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zubenubi/32/44576_2.png) [@zubenubi](https://discourse.nodered.org/u/zubenubi)\
**Post date:** [21 August 2018 20:40 UTC](https://discourse.nodered.org/t/sqlite-question/2434/17 "2018-08-21T20:40:47Z")

</div>

OK, After a few errors on my part I got the flow going again.  
Using a change node, I set msg.extension to the full path of the extension (the same path I use in the .sqliterc file with the .load command).

Then the flow goes to the sqlite node where I am using a fixed sql statement.

New Error:  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/9/98851668e0a28fb13c9e48cc46578a012c3dd1e7.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:** [21 August 2018 21:05 UTC](https://discourse.nodered.org/t/sqlite-question/2434/18 "2018-08-21T21:05:19Z")

</div>

OK - is there anything in the console log also ?  
And are you also sending a query in the same msg ?(hopefully not)...

---

<div class="post-metadata">

**Author:** ![zubenubi](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zubenubi/32/44576_2.png) [@zubenubi](https://discourse.nodered.org/u/zubenubi)\
**Post date:** [21 August 2018 21:14 UTC](https://discourse.nodered.org/t/sqlite-question/2434/19 "2018-08-21T21:14:42Z")

</div>

Same error seen in console log but nothing more.

The query is directly in the sqlite node (as a fixed statement). The change node is setting the msg.extension as shown here.  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/0/021d9b50b7622dffb29759a3fa2b5a1e7ca83329.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:** [21 August 2018 21:57 UTC](https://discourse.nodered.org/t/sqlite-question/2434/20 "2018-08-21T21:57:24Z")

</div>

OK - I have pushed node-red-node-sqlite@0.3.3 - which has a better implementation of the fix that sorts out that timing problem. I don't have a valid extension to try - but at least it now tries to load a file (and for me then fails)... hopefully will work for you with a correct file.  
I did notice it seems to want the full path, and filename - but didn't need the final extension ( On Mac .dylib) but the error message was quite clear.

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