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!