# AlaSQL working with msg.payload

**URL:** <https://discourse.nodered.org/t/alasql-working-with-msg-payload/28964>\
**Category:** General\
**Created:** [25 June 2020 11:19 UTC](https://discourse.nodered.org/t/alasql-working-with-msg-payload/28964 "2020-06-25T11:19:06Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![mpxd](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mpxd/32/2028_2.png) [@mpxd](https://discourse.nodered.org/u/mpxd)\
**Post date:** [25 June 2020 11:19 UTC](https://discourse.nodered.org/t/alasql-working-with-msg-payload/28964/1 "2020-06-25T11:19:06Z")

</div>

Hello there,

did anyone here already try to inject a `msg.payload` into an [AlaSQL node](https://flows.nodered.org/node/node-red-contrib-alasql) ?

Excerpt from the documentation:

```auto
Refer to input data in msg.payload with $0 in your SQL.
If msg.payload is an array the first value will be $0, the second $1, and so forth.

```

I tried building a SELECT query like:

```sql
SELECT event FROM MyTable
WHERE ts = $0

```

And injected a string `2020.06.25 10:05:30.632` as `msg.payload` and get an empty array as return from my database. But running the query directly gives me the correct result:

```sql
SELECT event FROM MyTable
WHERE ts = "2020.06.25 10:05:30.632"

```

I also tried [node-red-contrib-alasqlfunc](https://flows.nodered.org/node/node-red-contrib-alasqlfunc). But already the README examples result in syntax errors:

```auto
msg.query='select * from abc where Id="'+msg.payload+'"';

```

---

<div class="post-metadata">

**Author:** ![afelix](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/afelix/32/9743_2.png) [@afelix](https://discourse.nodered.org/u/afelix)\
**Post date:** [25 June 2020 12:39 UTC](https://discourse.nodered.org/t/alasql-working-with-msg-payload/28964/2 "2020-06-25T12:39:16Z")

</div>

> [@mpxd](#):
>
> And injected a string `2020.06.25 10:05:30.632` as `msg.payload` and get an empty array as return from my database. But running the query directly gives me the correct result:

I haven't used this node before, but reading that documentation snippet very strict, have you tried injecting an array instead, where the first (and only) item in the array is `2020.06.25 10:05:30.632` as string? Hmmm reading again very closely, that shouldn't make a difference...

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [25 June 2020 12:42 UTC](https://discourse.nodered.org/t/alasql-working-with-msg-payload/28964/3 "2020-06-25T12:42:27Z")

</div>

> [@mpxd](#):
>
> And injected a string `2020.06.25 10:05:30.632` as `msg.payload` and get an empty array as return from my database

Firstly, I assume you realise this doesnt actually talk to a database?

> `alasql` node lets you access javascript objects as if they were a SQL database

Secondly, as your payload is a string, I suspect you need to use single quotes in the querys' `where` clause

Thirdly, my guess is that msg.payload is the DATABASE (not the where clause)

e.g. As a test - try sending this (in a function node) --\> to the alaSQL node then to a debug node...

```javascript
msg.payload = [{id:0,t:"hello"} , {id:1,t:"bye"}];
msg.query = "SELECT id,t FROM ? WHERE t = 'bye'; ";
return msg;

```

**DISCLAIMER**  
I have never used this contrib node - but a quick look at the source code suggests...

- the payload is the object to query
- you can send dynamic alaSQL queries into the node via `msg.query` or `msg.topic` (if the field is left blank)
- there also looks to be a file mode - not sure how that works!

---

<div class="post-metadata">

**Author:** ![afelix](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/afelix/32/9743_2.png) [@afelix](https://discourse.nodered.org/u/afelix)\
**Post date:** [25 June 2020 12:46 UTC](https://discourse.nodered.org/t/alasql-working-with-msg-payload/28964/4 "2020-06-25T12:46:21Z")

</div>

I too just read some code and the issues and I'm spotting a few things. There's 2 open discussions on whether errors in the supplied information/errors in the underlying Ala system should crash Node-RED, or if they should handle those exceptions. The fact that there's even a second discussion needed and not an immediate "hey we handle errors in the node and inform the user rather than taking down Node-RED" does not give me much hope for the rest tbh.

> <https://github.com/AlaSQL/node-red-contrib-alasql/issues/23>
>
> There is no option to handle errors like path to file not exists or bad file format, am I right?

  

> <https://github.com/AlaSQL/node-red-contrib-alasql/issues/15>
>
> Hi, i've a problem with this node, and the google groups comunity suggested that i post the problem here, so i'll...

There's also an issue on a merged PR that deals with `msg.payload`, `msg.topic` and `msg.query`, and what you describe above:

> <https://github.com/AlaSQL/node-red-contrib-alasql/commit/4e9ac45bb5b32e11aefb88eae47e983dc1a05a1a>

A further dive down the code and rest shows that `msg.payload` is indeed, as @Steve-Mcl says, meant to use as data source. It serves as the database that is being queried. The alasql file-in node that is included can be used to read from, for example, and XLSX file, that will be used as datasource instead. The alasql file-out node allows the result to be written back to a file.

This is done through the underlying alasql library, where this node is a wrapper for.

> **[agershun/alasql](https://github.com/agershun/alasql)**
>
> AlaSQL.js - JavaScript SQL database for browser and Node.js. Handles both traditional relational tables and nested JSON data (NoSQL). Export, store, and import data from localStorage, IndexedDB, or...

  
The examples shown here are for the raw `alasql` code, where the first argument passed to the `alasql` function is the SQL query, and the second(/third/fourth/...) argument(s) are the contents of `msg.payload`, which is the data to operate on.

---

<div class="post-metadata">

**Author:** ![mpxd](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mpxd/32/2028_2.png) [@mpxd](https://discourse.nodered.org/u/mpxd)\
**Post date:** [25 June 2020 13:34 UTC](https://discourse.nodered.org/t/alasql-working-with-msg-payload/28964/5 "2020-06-25T13:34:10Z")

</div>

Hello Steve and afelix,

and thank you for your reply!

I am used to working with this node - but so far only with static queries. I have a rough idea what AlaSQL is in the background. Setting this up as a static query does work:

```sql
SELECT event FROM MyDB
WHERE ts = "2020.06.25 10:05:30.632"

```

I also created this "database" **MyDB** :

```sql
CREATE TABLE MyDB (ts TIMESTAMP VARCHAR(80), event VARCHAR(255));

```

And I have Node-RED set up to feed data into it - so the select/where query above does retrieve an event for me.

Injecting the message as an array and using `msg.topic` or `msg.query` (instead of `message.payload`) was a good tip. I come to the same conclusion after re-reading the README. But I cannot get it to work:

```auto
object
_msgid: "255e763c.6ce1ba"
topic: array[1]
0: "2020.06.25 10:05:30.632"
payload: array[1]
0: "2020.06.25 10:05:30.632"
query: array[1]
0: "2020.06.25 10:05:30.632"

```

But the node does not use the message - this still gives me an empty array in return:

```sql
SELECT event FROM MyDB
WHERE ts = $0

```

* * *

This part I don't understand:

```auto
msg.payload = [{id:0,t:"hello"} , {id:1,t:"bye"}];
msg.query = "SELECT id,t FROM ? WHERE t = 'bye'; ";
return msg;

```

You do not have to provide your data inside the query. AlaSQL behaves like an SQL database. Node-RED fills it with data, and I can query against this "database" - just like SQLite. Of course, everything is only in memory - there is nothing persisted. In the end there is no database.

But currently, I am stuck - I think I will try solving this with SQLite instead. Maybe, dynamic queries are just broken at the moment.

---

<div class="post-metadata">

**Author:** ![mpxd](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mpxd/32/2028_2.png) [@mpxd](https://discourse.nodered.org/u/mpxd)\
**Post date:** [25 June 2020 13:36 UTC](https://discourse.nodered.org/t/alasql-working-with-msg-payload/28964/6 "2020-06-25T13:36:59Z")

</div>

> [@afelix](#):
>
> The examples shown here are for the raw `alasql` code, where the first argument passed to the `alasql` function is the SQL query, and the second(/third/fourth/...) argument(s) are the contents of `msg.payload` , which is the data to operate on.

I do have a working example here:

[https://mpolinowski.github.io/node-red-sql-logging-datastreams](https://mpolinowski.github.io/node-red-sql-logging-datastreams)

I have been using variations of this in Node-RED for a while to solve all kinds of issues. But only with static queries.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [25 June 2020 13:48 UTC](https://discourse.nodered.org/t/alasql-working-with-msg-payload/28964/7 "2020-06-25T13:48:14Z")

</div>

> [@mpxd](#):
>
> Injecting the message as an array and using `msg.topic` or `msg.query` (instead of `message.payload` ) was a good tip. I come to the same conclusion after re-reading the README. But I cannot get it to work:
> 
> ```auto
> object
> _msgid: "255e763c.6ce1ba"
> topic: array[1]
> 0: "2020.06.25 10:05:30.632"
> payload: array[1]
> 0: "2020.06.25 10:05:30.632"
> query: array[1]
> 0: "2020.06.25 10:05:30.632"
> 
> ```

topic MUST only be a string. (e.g. `"SELECT * FROM..."`)

> [@Steve-Mcl](#):
>
> you can send dynamic alaSQL queries into the node via `msg.query` or `msg.topic` (if the field is left blank)

* * *

The data to query goes into msg.payload (e.g. the database)

> [@Steve-Mcl](#):
>
> The payload is the object to query

---

<div class="post-metadata">

**Author:** ![mpxd](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mpxd/32/2028_2.png) [@mpxd](https://discourse.nodered.org/u/mpxd)\
**Post date:** [25 June 2020 14:06 UTC](https://discourse.nodered.org/t/alasql-working-with-msg-payload/28964/8 "2020-06-25T14:06:30Z")

</div>

> [@Steve-Mcl](#):
>
> topic MUST only be a string. (e.g. `"SELECT * FROM..."` )

```sql
msg.topic = ["2020.06.25 10:05:30.632"];
msg.payload = "SELECT event FROM MyDB WHERE ts = $0";
return msg;

```

If I inject this into the AlaSQL node, then I would have to leave the node empty ? (this does not work - I just tried)

Currently, I am using a function node to set the payload:

 ![alasql_01](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/0/d0e0d758d4a6b2946b343247b0d5eaf3963e0920.png)

And I have the SQL query inside the AlaSQL node:

 ![alasql_02](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/9/c948770055e97b9fddc0980b62b0a1a02b58bce8.png)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [25 June 2020 14:11 UTC](https://discourse.nodered.org/t/alasql-working-with-msg-payload/28964/9 "2020-06-25T14:11:47Z")

</div>

That is literally nothing like we said.

1st alaSQL queries against arrays of objects e.g.

```auto
msg.payload = [{id:0,t:"hello"} , {id:1,t:"bye"}];

```

2nd the topic must be a string NOT an array e.g...

```auto
msg.topic = "SELECT id,t FROM ? WHERE t = 'bye'; ";
//alternatively - msg.query = "SELECT id,t FROM ? WHERE t = 'bye'; ";

```

So for your example...

```auto
msg.topic = "SELECT event FROM ? WHERE ts = '2020.06.25 10:05:30.632' "; // A STRING!!!!
msg.payload = MyDB; // << you need to set this to the thing to query

```

EDIT...  
Also, for msg.topic or msg.query to work, you must leave the "SQL Query" field blank (as I said in first post).

EDIT2...  
To make it dynamic:

- feed the WHERE value into a function with the payload set as your comparitor
- setup the topic and payload as required
- send it to alaSQL node (wich must not have a query set in its UI field)

function node before alasql

```auto
msg.topic = "SELECT event FROM ? WHERE ts = '" + msg.payload + "'"; 
msg.payload = MyDB; // << you need to set this to the thing to query - wherever that comes from

```

---

<div class="post-metadata">

**Author:** ![mpxd](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mpxd/32/2028_2.png) [@mpxd](https://discourse.nodered.org/u/mpxd)\
**Post date:** [25 June 2020 14:32 UTC](https://discourse.nodered.org/t/alasql-working-with-msg-payload/28964/10 "2020-06-25T14:32:23Z")

</div>

> [@Steve-Mcl](#):
>
> Also, for msg.topic or msg.query to work, you must leave the "SQL Query" field blank (as I said in first post).

I think you cannot do that. If you leave the AlaSQL node empty you get a TypeError. Like I do know that adding the following into the node works:

```sql
SELECT event FROM MyDB
WHERE ts = "2020.06.25 10:05:30.632"

```

But if I inject this `msg.topic = "SELECT event FROM MyDB WHERE ts = '2020.06.25 10:05:30.632' ";` into an empty SQL node, it throws an error:

```auto
"TypeError: Cannot read property 'length' of undefined"

```

What you are describing is the SQLite node - this one receives everything via `msg.topic`.

Hmm let me try solving this with SQLite. I believe that this is going to work there.

---

<div class="post-metadata">

**Author:** ![afelix](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/afelix/32/9743_2.png) [@afelix](https://discourse.nodered.org/u/afelix)\
**Post date:** [25 June 2020 14:39 UTC](https://discourse.nodered.org/t/alasql-working-with-msg-payload/28964/11 "2020-06-25T14:39:49Z")

</div>

@mpxd Mike, back to basics for a moment. What is your data source, what kind of data are you attempting to run this query over, and where is that data located? These nodes expect that your data, in the shape of an array of objects, is located in `msg.payload` that goes into the alasql nodes. Is that the case for your data?

---

<div class="post-metadata">

**Author:** ![mpxd](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mpxd/32/2028_2.png) [@mpxd](https://discourse.nodered.org/u/mpxd)\
**Post date:** [25 June 2020 14:53 UTC](https://discourse.nodered.org/t/alasql-working-with-msg-payload/28964/12 "2020-06-25T14:53:48Z")

</div>

The data source are base64 encoded images from an surveillance camera. they are stored inside the database with an added timestamp.

 ![Untitled](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/2/32182b97889260c9e2311ed5443cd5576d44f45c.png)

I do have an dashboard widget that I am using as an event timeline. It returns the timestamp of an event when you click on it. I now want to find this timestamp and display the corresponding image.

All of this already works, as long as I use static queries. The problem is using the timestamp I receive from the timeline widget instead. So all I have is an AlaSQL node with the SQL Query and a node that outputs a timestamp as an object:

![Untitled](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/1/f16da11c8bc5c219d7f34c32b03b56874a8dc1ac.png)

---

<div class="post-metadata">

**Author:** ![afelix](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/afelix/32/9743_2.png) [@afelix](https://discourse.nodered.org/u/afelix)\
**Post date:** [25 June 2020 15:05 UTC](https://discourse.nodered.org/t/alasql-working-with-msg-payload/28964/13 "2020-06-25T15:05:09Z")

</div>

> [@mpxd](#):
>
> The data source are base64 encoded images from an surveillance camera. they are stored inside the database with an added timestamp.

And where is this located inside Node-RED? I see in your screenshot an array of objects. Is this fed to the alasql node in `msg.payload`?

> [@mpxd](#):
>
> I do have an dashboard widget that I am using as an event timeline. It returns the timestamp of an event when you click on it. I now want to find this timestamp and display the corresponding image.

As an alternative approach, have you looked at JSONata in a change node to solve this? It might be worth migrating the string timestamp in your database back to seconds since epoch, and go through your array of objects with jsonata.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [25 June 2020 15:06 UTC](https://discourse.nodered.org/t/alasql-working-with-msg-payload/28964/14 "2020-06-25T15:06:04Z")

</div>

So, it seems the version on github is not the same as the version on NPM/flows

See my comment on issues...

> <https://github.com/AlaSQL/node-red-contrib-alasql/issues/26#issuecomment-649606928>
>
> Before please undo commit 4e9ac45

---

<div class="post-metadata">

**Author:** ![mpxd](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mpxd/32/2028_2.png) [@mpxd](https://discourse.nodered.org/u/mpxd)\
**Post date:** [25 June 2020 15:13 UTC](https://discourse.nodered.org/t/alasql-working-with-msg-payload/28964/15 "2020-06-25T15:13:48Z")

</div>

> [@afelix](#):
>
> I see in your screenshot an array of objects.

I am using an HTTP GET to get the snapshot, do a base64 encode and then insert this data + timestamp into AlaSQL (all in Node-RED). The screenshot is the output when I query this data.

> [@afelix](#):
>
> It might be worth migrating the string timestamp in your database back to seconds since epoch

I already looked into the Moment.js node for that - I also have an issue that the timestamp inside the database and the timestamp from the widget are not 100% identical. But I postponed trying to think about that... one step at the time 😀

> [@Steve-Mcl](#):
>
> See my comment on issues...

Ok, there is the problem... Thank you!

---

<div class="post-metadata">

**Author:** ![mpxd](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mpxd/32/2028_2.png) [@mpxd](https://discourse.nodered.org/u/mpxd)\
**Post date:** [26 June 2020 13:05 UTC](https://discourse.nodered.org/t/alasql-working-with-msg-payload/28964/16 "2020-06-26T13:05:50Z")

</div>

Hey,

I just wanted to give feedback.

After the AlaSQL update from today everything is working as expected!

I am using a function node to set the `msg.payload` - **important** this has to be an array:

```auto
msg.payload = ["2020.06.26 12:46:25.219"];
return msg;

```

And use the AlaSQL node for the SQL query:

```auto
SELECT event FROM MyDB
WHERE ts = $0

```

And I am getting the object returned from my database - the event that belonged to that timestamp.

 ![Untitled](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/f/cf497e7f6d0df51cd9bfbec6f19d77581bc10837.png)

Thank you again, both of you for your help 🙂

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [26 June 2020 13:11 UTC](https://discourse.nodered.org/t/alasql-working-with-msg-payload/28964/17 "2020-06-26T13:11:02Z")

</div>

@mpxd @afelix

The Dev has fixed [the issue](https://github.com/AlaSQL/node-red-contrib-alasql/issues/26#issuecomment-649606928) I raised and V2.0.1 is now in the flows lib

You can now pass the a dynamic query via `msg.query` or `msg.topic` (but remember to leave the query field on the UI blank for it to work)

EDIT...  
Sorry @mpxd just noticed you mentioned that it had been updated - oops 🙂

---

<div class="post-metadata">

**Author:** ![afelix](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/afelix/32/9743_2.png) [@afelix](https://discourse.nodered.org/u/afelix)\
**Post date:** [26 June 2020 13:12 UTC](https://discourse.nodered.org/t/alasql-working-with-msg-payload/28964/18 "2020-06-26T13:12:03Z")

</div>

It's good to see that it's working, but I don't understand how based on what you show. That's quite interesting 🙂 Great to see that you succeeded with your flow though!

---

<div class="post-metadata">

**Author:** ![mpxd](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mpxd/32/2028_2.png) [@mpxd](https://discourse.nodered.org/u/mpxd)\
**Post date:** [26 June 2020 13:14 UTC](https://discourse.nodered.org/t/alasql-working-with-msg-payload/28964/19 "2020-06-26T13:14:51Z")

</div>

I wanted to upload the result - once I manage to finish it 🙂

I will give you a ping

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [10 July 2020 13:28 UTC](https://discourse.nodered.org/t/alasql-working-with-msg-payload/28964/20 "2020-07-10T13:28:10Z")

</div>

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