Connection Node-RED to postgreSQL server!

General
1

Hello everyone,

This will be my very first post!
I want to connect Node-RED to a PostgreSQL server and for test reasons just create a table into the database. However, after configuration, I am not able to create a table. It doesn't show anything. The output on the right of the picture is from the first msg.payload. After the PostgreSQL node there is no output.

In the pictures below I will show you the configurations:
Node-RED view:

image
image1575Ă—264 38.4 KB

Inject node:

msg.payload = CREATE TABLE student(intCarrierId INT PRIMARY KEY, intBatchId INT);

Function:

msg.queryParameters = msg.payload;
return msg;

Localhost PostgreSQL Server
Password: 1205

image
image1204Ă—799 39.1 KB

Please, help me to find the solution !
Thanks in advance.

Tim

2

Please tell us which postrgres node you are using as there are a number of them. If you look in the menu in Manage Palette you will see which one you have installed.

3

Thanks for the reply.

I am using node-red-contrib-re-postgres.

4

It is not clear to me from the node description that it is capable of creating tables. Have you tried a simple query to see if that works?
First try adding a Catch node to see if that catches an error from it. Also watch the node-red log to see if there is anything relevant there.

5

I have simplified it to just insert one column.

Inject node:

msg.payload = { "carrier_id": 50 }

Template node:

INSERT INTO public.tbltracking(carrier_id) VALUES ({{{payload}}});

Without any success..

Node-RED View:

image
image1087Ă—483 25.2 KB

6

You have to feed the catch node into a debug node so it displays any errors it catches.
Try a SELECT operation rather than an Insert. Always best to start with the simplest operation.

7

Have you set 'Return message on error' in the config?

8

Okay thank you for your help so far.

Return message on error is on.
Just tryed a simple SELECT statement. Dont get a output whatsoever.
Query is tested in PgAdmin4.

9

What OS are you running on?
Can you start node red in a terminal and run it as far as trying the select operation please. Then copy/paste the log here (not screenshot please). When posting a log use the </> button at the top of the forum entry window and paste it in.

10

Running Windows 10.
64-bit operation system, x64-based processor.

4 Apr 11:09:00 - [info] Loading palette nodes
4 Apr 11:09:02 - [info] Dashboard version 2.28.2 started at /ui
4 Apr 11:09:02 - [warn] ------------------------------------------------------
4 Apr 11:09:02 - [warn] [node-red-contrib-postgres/PostgreSql] Type already registered
4 Apr 11:09:02 - [warn] [node-red-contrib-postgres-multi/PostgreSql] Type already registered
4 Apr 11:09:02 - [warn] [node-red-contrib-postgres-variable/PostgreSql] Type already registered
4 Apr 11:09:02 - [warn] ------------------------------------------------------
4 Apr 11:09:02 - [info] Settings file  : C:\Users\timbo\.node-red\settings.js
4 Apr 11:09:02 - [info] Context store  : 'default' [module=memory]
4 Apr 11:09:02 - [info] User directory : \Users\timbo\.node-red
4 Apr 11:09:02 - [warn] Projects disabled : editorTheme.projects.enabled=false
4 Apr 11:09:02 - [info] Flows file     : \Users\timbo\.node-red\flows_LAPTOP-KD0SFV4F.json
4 Apr 11:09:02 - [info] Server now running at http://127.0.0.1:1880/
4 Apr 11:09:02 - [warn]

---------------------------------------------------------------------
Your flow credentials file is encrypted using a system-generated key.

If the system-generated key is lost for any reason, your credentials
file will not be recoverable, you will have to delete it and re-enter
your credentials.

You should set your own key using the 'credentialSecret' option in
your settings file. Node-RED will then re-encrypt your credentials
file using your chosen key the next time you deploy a change.
---------------------------------------------------------------------

4 Apr 11:09:02 - [info] Starting flows
4 Apr 11:09:02 - [info] Started flows
11

You appear to have multiple postres nodes installed. Remove all of them except re-postrges.

12

Fixed. But the same issue as before..

4 Apr 11:28:06 - [info] Node-RED version: v1.2.9
4 Apr 11:28:06 - [info] Node.js  version: v15.13.0
4 Apr 11:28:06 - [info] Windows_NT 10.0.18363 x64 LE
4 Apr 11:28:07 - [info] Loading palette nodes
4 Apr 11:28:08 - [info] Dashboard version 2.28.2 started at /ui
4 Apr 11:28:08 - [info] Settings file  : C:\Users\timbo\.node-red\settings.js
4 Apr 11:28:08 - [info] Context store  : 'default' [module=memory]
4 Apr 11:28:08 - [info] User directory : \Users\timbo\.node-red
4 Apr 11:28:08 - [warn] Projects disabled : editorTheme.projects.enabled=false
4 Apr 11:28:08 - [info] Flows file     : \Users\timbo\.node-red\flows_LAPTOP-KD0SFV4F.json
4 Apr 11:28:08 - [info] Server now running at http://127.0.0.1:1880/
4 Apr 11:28:08 - [warn]

---------------------------------------------------------------------
Your flow credentials file is encrypted using a system-generated key.

If the system-generated key is lost for any reason, your credentials
file will not be recoverable, you will have to delete it and re-enter
your credentials.

You should set your own key using the 'credentialSecret' option in
your settings file. Node-RED will then re-encrypt your credentials
file using your chosen key the next time you deploy a change.
---------------------------------------------------------------------

4 Apr 11:28:08 - [info] Starting flows
4 Apr 11:28:08 - [info] Started flows

It looks like I am doing every step right, but there is just no connection to the database? Is there a way to check if the configuration is solid?

13

If you configure the db connection incorrectly does it show that it is not connected?

Edit: Also you are using an unsupported version of nodejs. I advise going back to 14 in case that is the issue. After you downgrade nodejs you will need to re-install node-red and go into your .node-red folder and run npm install.

14

No doesn't show. But it is almost impossible that it is wrong.

15

Mmm okay. I will go back to version 14. I will let you know if that was indeed the issue. Thanks for your help so far.

16

You are right, if it is unable to connect there is no error or anything. That is not very clever of it.

17

Unfortunately, no success with version 14.

18

Sorry, I am out of ideas. Perhaps someone with more experience of the node can help.

19

Ok, thanks anyways! I will post again, if I am able to crack this puzzle :wink:

20

@tboekhorst

both node-red-contrib-postgrestor-next and @digitaloak/node-red-contrib-digitaloak-postgresql work on my RPi with node v14.15.1

I prefer node-red-contrib-postgrestor-next