Cannot connect to postgreSQL database

Support
,
1

Hi All,

I am having trouble with this website: https://todos-back.netlify.app/

I am trying to deploy a node/express backend on Netlify. I have a PostgreSQL database hosted on my local machine.

When I test the app in my local environment (i.e. on localhost using npm start run) I am able to load all routes and access data as expected. However when I try to access it via the live website I get this error:

{"error":{"errno":-111,"code":"ECONNREFUSED","syscall":"connect","address":"127.0.0.1","port":5432}}

This error was produced by visiting the URL https://todos-back.netlify.app/api/todos

If I visit URLs that point to routes that do not require connection to the PostgreSQL database, they work fine - i.e https://todos-back.netlify.app/api.

I am using the node-postgres package to manage connections to my database.

Here are the postgres database details (with user and password redacted for security purposes):

DB_USER={myusername}

DB_PW ={mypassword}

DB_HOST=ā€˜localhostā€™

DB_PORT=ā€˜5432ā€™

DB_DATABASE=ā€˜todosā€™

Here is the information on the postgres connection coming from pgAdmin 4
image

Thanks in advance for any help!

2

@YSquid Is your postgres database running only on your local machine?

If so, that will be your problem.

The code on Netlify isnā€™t going to be able to connect your local database using an address of 127.0.0.1 (which is localhost).

You can see in the error that itā€™s trying to connect to 127.0.0.1ā€¦

{
  "error": {
    "errno": -111,
    "code": "ECONNREFUSED",
    "syscall": "connect",
    "address": "127.0.0.1",
    "port": 5432
  }
}
3

Thanks for your reply.

Yes, the Postgres database is on my local machine. So your answer makes sense to me

Is there any way to allow the app to access the database on my local machine? Or would I need to host the database on a server somewhere? For example, Iā€™ve built a database on ElephantSQL before, would having my node-postgres client connect to that be better?

This is my first time deploying a backend so some of the concepts are a little challenging for me - any insight greatly appreciated!

4

You would need to set the connection details to the IP address of your computer and ensure the connection is allowed through any firewalls that might be in place.

5

Ok I think that makes sense. Just to ensure Iā€™m getting it:

In my configuration for node-postgres I would change the host to my IP address so like:

DB_HOST={mypublicIPaddress}

And then I would configure my firewall to allow outside connections through port 5432.

6

@YSquid Thatā€™s my understanding yes.

7

I tried this method, but unfortunately, it did not work.

It seems like I may be better off hosting the database somewhere else so I donā€™t have to allow connections to my own machine anyway.

Thanks for your input.

8

Thatā€™s correct.

Hosting it yourself is fine for development, but itā€™s not something that Iā€™d do for production anyway, and Iā€™d assumed that youā€™d run into connection issues.

Best of luck getting it sorted out.

9

Ok. I am looking into using Render for hosting the backend then, as it seems like Netlify is more suited to the front end anyway.

Thanks a lot, this was a good learning experience for me.

10

@YSquid Youā€™re very welcome.

If you just need a Postgres database another option would be Supabase.

11

Ok, Iā€™ll mark that as another option.

I guess this leads to another question - say I wanted to keep my backend app hosted on Netlify. I could keep my app basically the same, but instead of pointing to localhost in my node-postgres configuration, I would point to the supabase address instead?

12

@YSquid You would connect to Supabase with the details provided once you sign up to their service, and following the documentation that they have.

1 Like
13

Fantastic. I will start there (in the morning I need a break from my computer screen).

Seriously appreciate all the help, Nathan!

14

I have deployed an PostgreSQL Database on Superbase but there I get also the problem with Connecting to the database.

My NodeJS Script:
// db.js
const postgres = require(ā€˜postgresā€™);
const dotenv = require(ā€˜dotenvā€™);

dotenv.config({ path: ā€˜secure.envā€™ });

let { PGHOST, PGDATABASE, PGUSER, PGPASSWORD, ENDPOINT_ID } = process.env;
PGPASSWORD = decodeURIComponent(PGPASSWORD);

const sql = postgres({
host: PGHOST,
database: PGDATABASE,
username: PGUSER,
password: PGPASSWORD,
port: 5432,
ssl: ā€˜requireā€™,
connection: {
options: project=${ENDPOINT_ID},
},
});

async function getPgVersion() {
const result = await sqlselect version();
console.log(result);
}

getPgVersion();

module.exports = { sql };
Error Message: Sep 10, 02:54:47 PM: 20fdd5cb ERROR Unhandled Promise Rejection {ā€œerrorTypeā€:ā€œRuntime.UnhandledPromiseRejectionā€,ā€œerrorMessageā€:ā€œError: connect ECONNREFUSED 127.0.0.1:5432ā€,ā€œreasonā€:{ā€œerrorTypeā€:ā€œErrorā€,ā€œerrorMessageā€:ā€œconnect ECONNREFUSED 127.0.0.1:5432ā€,ā€œcodeā€:ā€œECONNREFUSEDā€,ā€œerrnoā€:-111,ā€œsyscallā€:ā€œconnectā€,ā€œaddressā€:ā€œ127.0.0.1ā€,ā€œportā€:5432,ā€œstackā€:[ā€œError: connect ECONNREFUSED 127.0.0.1:5432ā€," at TCPConnectWrap.afterConnect [as oncomplete] (node:net:1555:16)ā€œ,ā€ at cachedError (/var/t

15

@baumann Double check your connection details and environment variables (if you havenā€™t already).

16

No I dont have. the Database dont allow connections from server, but from Localhost is ok. If you know how to connect netƶify with Superbase (you mentioned it earlier of this thread) I get a timeout issue from Database server

17

@baumann Iā€™m not sure what you mean, or why youā€™re encountering an issue.

It seems most likely to me that something just isnā€™t correct with your configuration.

Hence you should confirm your various environment variables and that the script does have access to them.

Iā€™m not going to provide instructions, (Note: I donā€™t work for Netlify), but as I said previously:

Find and follow appropriate documentation or tutorials.