# Cannot connect to Postgres database

**URL:** <https://discourse.nodered.org/t/cannot-connect-to-postgres-database/17914>\
**Category:** General\
**Created:** [13 November 2019 21:06 UTC](https://discourse.nodered.org/t/cannot-connect-to-postgres-database/17914 "2019-11-13T21:06:07Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![abesis](https://avatars.discourse-cdn.com/v4/letter/a/b2d939/32.png) [@abesis](https://discourse.nodered.org/u/abesis)\
**Post date:** [13 November 2019 21:06 UTC](https://discourse.nodered.org/t/cannot-connect-to-postgres-database/17914/1 "2019-11-13T21:06:07Z")

</div>

Hi,  
I've been trying to communicate with **Postgres** database that runs locally on a Raspberry pi.  
So far I think that I have tried every node available through the Palette, such as

> node-red-contrib-postgres-variable  
> node-red-contrib-re-postgres  
> node-red-contrib-postgres-multi  
> Postgrestor  
> ..and others

All of them fail with a **TypeError** and cause Node-RED to **restart** , the moment a query is sent to the database. Here is the log:

> Welcome to Node-RED  
> ===================  
> 13 Nov 22:41:55 - [info] Node-RED version: v1.0.2  
> 13 Nov 22:41:55 - [info] Node.js version: v10.17.0  
> 13 Nov 22:41:55 - [info] Linux 4.19.75-v7l+ arm LE  
> 13 Nov 22:41:56 - [info] Loading palette nodes  
> 13 Nov 22:42:03 - [info] Dashboard version 2.17.1 started at /ui  
> 13 Nov 22:42:03 - [info] Settings file : /home/pi/.node-red/settings.js  
> 13 Nov 22:42:03 - [info] Context store : 'default' [module=memory]  
> 13 Nov 22:42:03 - [info] User directory : /home/pi/.node-red  
> 13 Nov 22:42:03 - [info] Server now running at [http://127.0.0.1:1880/](http://127.0.0.1:1880/)  
> 13 Nov 22:42:03 - [info] Active project : ggln25wb  
> 13 Nov 22:42:03 - [info] Flows file : /home/pi/.node-red/projects/ggln25wb/flow.json  
> 13 Nov 22:42:04 - [info] Starting flows  
> 13 Nov 22:42:05 - [info] Started flows  
> 13 Nov 22:42:18 - **[red] Uncaught Exception:**  
> 13 Nov 22:42:18 - **TypeError: Second argument must be a string or a buffer.**  
> at Connection.parseCString (/home/pi/.node-red/node\_modules/pg/lib/connection.js:649:20)  
> at Connection.parseE (/home/pi/.node-red/node\_modules/pg/lib/connection.js:548:30)  
> at Connection.parseMessage (/home/pi/.node-red/node\_modules/pg/lib/connection.js:378:19)  
> at Socket. (/home/pi/.node-red/node\_modules/pg/lib/connection.js:119:22)  
> at Socket.emit (events.js:198:13)  
> at addChunk (\_stream\_readable.js:287:12)  
> at readableAddChunk (\_stream\_readable.js:268:11)  
> at Socket.Readable.push (\_stream\_readable.js:223:10)  
> at TCP.onStreamRead [as onread] (internal/stream\_base\_commons.js:94:17)  
> nodered.service: Main process exited, code=exited, status=1/FAILURE  
> nodered.service: Failed with result 'exit-code'.  
> nodered.service: Service RestartSec=100ms expired, scheduling restart.  
> nodered.service: Scheduled restart job, restart counter is at 1.  
> Stopped Node-RED graphical event wiring tool.  
> Started Node-RED graphical event wiring tool.  
> 13 Nov 22:42:20 - [info]  
> Welcome to Node-RED  
> ===================  
> 13 Nov 22:42:20 - [info] Node-RED version: v1.0.2  
> 13 Nov 22:42:20 - [info] Node.js version: v10.17.0  
> 13 Nov 22:42:20 - [info] Linux 4.19.75-v7l+ arm LE  
> 13 Nov 22:42:22 - [info] Loading palette nodes  
> 13 Nov 22:42:28 - [info] Dashboard version 2.17.1 started at /ui  
> 13 Nov 22:42:29 - [info] Settings file : /home/pi/.node-red/settings.js  
> 13 Nov 22:42:29 - [info] Context store : 'default' [module=memory]  
> 13 Nov 22:42:29 - [info] User directory : /home/pi/.node-red  
> 13 Nov 22:42:29 - [info] Server now running at [http://127.0.0.1:1880/](http://127.0.0.1:1880/)  
> 13 Nov 22:42:29 - [info] Active project : ggln25wb  
> 13 Nov 22:42:29 - [info] Flows file : /home/pi/.node-red/projects/ggln25wb/flow.json  
> 13 Nov 22:42:29 - [info] Starting flows  
> 13 Nov 22:42:30 - [info] Started flows

My flow is a simple one, with just one select statement that should return one row from the database:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/b/bdb13dec960c40420431a44c58f317913df1432a.png)  
Query:

```auto
msg.payload = 'SELECT * FROM table1 limit 1;';
return msg;

```

Connection settings:

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

Postgres node:

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

Am I missing something?  
Using the same connection parameters, I can connect and select records without problems, using Pyhton. Also pgAdmin connects normally.

---

<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:** [13 November 2019 21:55 UTC](https://discourse.nodered.org/t/cannot-connect-to-postgres-database/17914/2 "2019-11-13T21:55:53Z")

</div>

Which node are you using that takes a query in that form in the payload.  
Did you try the example flow in the readme of node-red-contrib-re-postgres?

---

<div class="post-metadata">

**Author:** ![abesis](https://avatars.discourse-cdn.com/v4/letter/a/b2d939/32.png) [@abesis](https://discourse.nodered.org/u/abesis)\
**Post date:** [13 November 2019 22:24 UTC](https://discourse.nodered.org/t/cannot-connect-to-postgres-database/17914/3 "2019-11-13T22:24:14Z")

</div>

Yes, I've tried that too. And tried it again now.  
This is the example from node-red-contrib-re-postgres:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/9/9190efb1042fa838fc5c8a6031a75441d824dc81.png)  
The payload of the format query node is:

```auto
select * from table1 where field2 > $param1

```

which, after removing the optional parameters, eventually becomes a plain SQL query, as the one I used above. Have I misunderstood something here?

By the way, the flow now throws this error, without causing Node-RED to restart:

> [error] [postgres:dcda0807.f56818] TypeError: pg.connect is not a function

Edit: I also now get this warning, that looks relevant:

```auto
DeprecationWarning: PG.connect is deprecated - please see the upgrade guide at https://node-postgres.com/guides/upgrading`

```

---

<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:** [14 November 2019 08:03 UTC](https://discourse.nodered.org/t/cannot-connect-to-postgres-database/17914/4 "2019-11-14T08:03:55Z")

</div>

Export that flow and paste it here please.

---

<div class="post-metadata">

**Author:** ![abesis](https://avatars.discourse-cdn.com/v4/letter/a/b2d939/32.png) [@abesis](https://discourse.nodered.org/u/abesis)\
**Post date:** [14 November 2019 15:58 UTC](https://discourse.nodered.org/t/cannot-connect-to-postgres-database/17914/5 "2019-11-14T15:58:44Z")

</div>

Here is the flow:

```auto
[{"id":"4b9aa0ee.e9305","type":"inject","z":"d27b9737.cad368","name":"trigger","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"00 12 * * *","once":false,"x":320,"y":2740,"wires":[["3f5acb02.90bd14"]]},{"id":"310442ef.1b4b7e","type":"postgres","z":"d27b9737.cad368","postgresdb":"a96a000f.57a5f","name":"","output":true,"perrow":false,"outputs":1,"x":740,"y":2740,"wires":[["e1035bbb.9ec088"]]},{"id":"e1035bbb.9ec088","type":"debug","z":"d27b9737.cad368","name":"query output","active":true,"console":"false","complete":"true","x":930,"y":2740,"wires":[]},{"id":"3f5acb02.90bd14","type":"function","z":"d27b9737.cad368","name":"setup params","func":"msg.payload = 'select * from mytable limit 1;';\nreturn msg;","outputs":1,"noerr":0,"x":540,"y":2740,"wires":[["310442ef.1b4b7e"]]},{"id":"a96a000f.57a5f","type":"postgresdb","z":"","hostname":"localhost","port":"5432","db":"mydb","ssl":false}]

```

And here is the result when I trigger it:

> Welcome to Node-RED  
> ===================  
> 14 Nov 12:00:03 - [info] Node-RED version: v1.0.2  
> 14 Nov 12:00:03 - [info] Node.js version: v10.17.0  
> 14 Nov 12:00:03 - [info] Linux 4.19.75-v7l+ arm LE  
> 14 Nov 12:00:04 - [info] Loading palette nodes  
> 14 Nov 12:00:12 - [info] Dashboard version 2.17.1 started at /ui  
> 14 Nov 12:00:13 - [info] Settings file : /home/pi/.node-red/settings.js  
> 14 Nov 12:00:13 - [info] Context store : 'default' [module=memory]  
> 14 Nov 12:00:13 - [info] User directory : /home/pi/.node-red  
> 14 Nov 12:00:13 - [info] Server now running at [http://127.0.0.1:1880/](http://127.0.0.1:1880/)  
> 14 Nov 12:00:13 - [info] Active project : ggln25\_web  
> 14 Nov 12:00:13 - [info] Flows file : /home/pi/.node-red/projects/ggln25\_web/flow.json  
> 14 Nov 12:00:13 - [info] Starting flows  
> 14 Nov 12:00:14 - [info] Started flows  
> 14 Nov 17:52:10 - [info] Stopping modified flows  
> 14 Nov 17:52:10 - [info] Stopped modified flows  
> 14 Nov 17:52:10 - [info] Starting modified flows  
> 14 Nov 17:52:10 - [info] Started modified flows  
> **(node:18315) DeprecationWarning: PG.connect is deprecated - please see the upgrade guide at [Upgrading – node-postgres](https://node-postgres.com/guides/upgrading)**  
> **14 Nov 17:52:41 - [red] Uncaught Exception:**  
> **14 Nov 17:52:41 - TypeError: Second argument must be a string or a buffer.**  
> at indexOf (/home/pi/.node-red/node\_modules/pg/lib/connection.js:19:21)  
> at Connection.parseCString (/home/pi/.node-red/node\_modules/pg/lib/connection.js:663:13)  
> at Connection.parseE (/home/pi/.node-red/node\_modules/pg/lib/connection.js:562:30)  
> at Connection.parseMessage (/home/pi/.node-red/node\_modules/pg/lib/connection.js:391:17)  
> at Socket. (/home/pi/.node-red/node\_modules/pg/lib/connection.js:129:22)  
> at Socket.emit (events.js:198:13)  
> at addChunk (\_stream\_readable.js:287:12)  
> at readableAddChunk (\_stream\_readable.js:268:11)  
> at Socket.Readable.push (\_stream\_readable.js:223:10)  
> at TCP.onStreamRead [as onread] (internal/stream\_base\_commons.js:94:17)  
> nodered.service: Main process exited, code=exited, status=1/FAILURE  
> nodered.service: Failed with result 'exit-code'.  
> nodered.service: Service RestartSec=100ms expired, scheduling restart.  
> nodered.service: Scheduled restart job, restart counter is at 3.  
> Stopped Node-RED graphical event wiring tool.  
> Started Node-RED graphical event wiring tool.  
> 14 Nov 17:52:44 - [info]

Then Node-RED restarts.

---

<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:** [14 November 2019 21:42 UTC](https://discourse.nodered.org/t/cannot-connect-to-postgres-database/17914/6 "2019-11-14T21:42:06Z")

</div>

> [@Colin](#):
>
> Did you try the example flow in the readme of node-red-contrib-re-postgres

You don't seem to have answered that question, and 'no' is not a valid answer, even if it is true 🙂  
**[Edit]** My guess is that you need to have msg.queryParameters even if there are no properties being specified. But start with the demo flow, if that works then you can modify towards what you need and see at what point it fails.  
There is an issue with the node that needs reporting though. Nothing you send to a node should crash node-red, a node should catch all exceptions inside itself to prevent that.

---

<div class="post-metadata">

**Author:** ![abesis](https://avatars.discourse-cdn.com/v4/letter/a/b2d939/32.png) [@abesis](https://discourse.nodered.org/u/abesis)\
**Post date:** [16 November 2019 14:00 UTC](https://discourse.nodered.org/t/cannot-connect-to-postgres-database/17914/7 "2019-11-16T14:00:05Z")

</div>

> [@Colin](#):
>
> node-red-contrib-re-postgres

I confirm that I have tried the example in the readme without changing anything.  
I still get the same DepricationWarning, TypeError and Node-RED restarts.

---

<div class="post-metadata">

**Author:** ![abesis](https://avatars.discourse-cdn.com/v4/letter/a/b2d939/32.png) [@abesis](https://discourse.nodered.org/u/abesis)\
**Post date:** [16 November 2019 14:02 UTC](https://discourse.nodered.org/t/cannot-connect-to-postgres-database/17914/8 "2019-11-16T14:02:07Z")

</div>

Can anyone with the following configuration confirm that they can work with the db?

```auto
Raspberry Pi 3
psql (11.5 (Raspbian 11.5-1+deb10u1))
Node-RED version: v1.0.2
Node.js version: v10.17.0
Linux 4.19.75-v7l+ arm LE

```

---

<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:** [16 November 2019 15:16 UTC](https://discourse.nodered.org/t/cannot-connect-to-postgres-database/17914/9 "2019-11-16T15:16:55Z")

</div>

In that case submit an issue against the node.