# Cannot connect Node.js app to Postgres db

**URL:** <https://community.fly.io/t/cannot-connect-node-js-app-to-postgres-db/6631>\
**Category:** Build debugging\
**Tags:** postgres\
**Created:** [August 27, 2022, 2:30pm UTC](https://community.fly.io/t/cannot-connect-node-js-app-to-postgres-db/6631 "2022-08-27T14:30:33Z")\
**Posts on this page:** 19\
**Page:** 1

<div class="post-metadata">

**Author:** ![mhoggard](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/mhoggard/32/38611_2.png) [@mhoggard](https://community.fly.io/u/mhoggard)\
**Post date:** [August 27, 2022, 2:30pm UTC](https://community.fly.io/t/cannot-connect-node-js-app-to-postgres-db/6631/1 "2022-08-27T14:30:33Z")

</div>

I am trying to get a new app set up on [fly.io](http://fly.io). This is my first time working with Fly. I have an app that has been deployed on Heroku that needs a new home. It is a Node.js REST API with a postgres database. It also has a React frontend that needs a new home and would love to have it all in one place, but for now, I’m focused on getting the backend up and running.

I have followed the instructions for `flyctl launch`. It successfully detects my app as Node, prompts to set up the postgres db, it claims to be properly attached, I add my other secrets and deploy. Each time, it successfully deploys the backend but the backend is unable to communicate with the database. If I go to backend root, I get the 200 success response. But if I try to hit any of the query endpoints that talk to postgres, I get `connect ECONNREFUSED 127.0.0.1:5432'`.

I can successfully connect to the postgres db from the CLI with `flyctl postgres connect -a recipes-api-db`. I am able to get into the psql CLI and create all my tables and see that succeed, but nothing seems to enable the connection between the deployed API and the db.

In reading through similar posts, I haven’t really seen much else actionable. Most people who complained of similar issues went through a handful of debugging steps to check the IPs, but ultimately if the problem was resolved they just said “I deleted and started again a few times and it finally worked.” I have deleted both apps and started from scratch four or five times now with no success.

I am using the `fly.toml` that was created automatically. The only change I have made is to add `private_network = true` under experimental as I’ve seen suggested, but that doesn’t seem to make any difference.

I do also notice that the postgres app does not automatically assign any IP addresses. It says I can assign them myself, but I wouldn’t know what values to choose. Is this something I need to do?

I am working from a MacOS computer.

Any help you can provide to guide me through what steps I may have missed would be greatly appreciated!!

Also, as a side note, after the first failed attempt, I saw you had the heroku conversion option. I tried that and after just a few clicks, I successfully had a new backend deployed in Fly that seems to work perfectly. I have already changed my heroku deployed frontend to point to this new Fly backend. However, as far as I can tell, this is still directly connected to heroku, with the postgres db still living on Heroku. And that db will be deleted by Heroku in a few months, so unless I’m misunderstanding the connection, this is not a viable solution for me. Once I get this working the correct way, I will need to delete that app.

---

<div class="post-metadata">

**Author:** ![cliffr](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/cliffr/32/4995_2.png) [@cliffr](https://community.fly.io/u/cliffr)\
**Post date:** [August 27, 2022, 2:47pm UTC](https://community.fly.io/t/cannot-connect-node-js-app-to-postgres-db/6631/2 "2022-08-27T14:47:55Z")

</div>

Hello, are you using this schema for your database connection ?

```auto
host: "<myapp>-db.internal"
user: "postgres"
password: "<dbpass>"
database: "<myapp>"
port: 5432

```

---

<div class="post-metadata">

**Author:** ![mhoggard](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/mhoggard/32/38611_2.png) [@mhoggard](https://community.fly.io/u/mhoggard)\
**Post date:** [August 27, 2022, 2:54pm UTC](https://community.fly.io/t/cannot-connect-node-js-app-to-postgres-db/6631/3 "2022-08-27T14:54:28Z")

</div>

I am honestly not sure. I went through the `flyctl launch` steps (or in some tries, separately postgres create) so it deployed the cluster for me. I don’t know how or where I would go to make changes to the postgres app. But these are the logs that were spit out when the db cluster was created.

```auto
Postgres cluster recipes-api-db created
  Username: postgres
  Password: <password>
  Hostname: recipes-api-db.internal
  Proxy Port: 5432
  PG Port: 5433

Connect to postgres
Any app within the personal organization can connect to postgres using the above credentials and the hostname "recipes-api-db.internal."
For example: postgres://postgres:<password>@recipes-api-db.internal:5432

Postgres cluster recipes-api-db is now attached to recipes-api
The following secret was added to recipes-api:
  DATABASE_URL=postgres://recipes_api:<hash>@top2.nearest.of.recipes-api-db.internal:5432/recipes_api
Postgres cluster recipes-api-db is now attached to recipes-api

```

Meanwhile, I have not changed anything in the Node app. I am using knex to talk to postgres. This is the production section of my knexfile.js.

```javascript
production: {
    client: 'pg',
    connection: process.env.DATABASE_URL,
    ssl: { rejectUnauthorized: false },
    migrations: {
      directory: './db/migrations'
    },
    seeds: {
      directory: './db/seeds/production'
    },
    useNullAsDefault: true
  }

```

---

<div class="post-metadata">

**Author:** ![cliffr](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/cliffr/32/4995_2.png) [@cliffr](https://community.fly.io/u/cliffr)\
**Post date:** [August 27, 2022, 2:56pm UTC](https://community.fly.io/t/cannot-connect-node-js-app-to-postgres-db/6631/4 "2022-08-27T14:56:26Z")

</div>

Try removing this line  
`ssl: { rejectUnauthorized: false },`

Your db infos should be :

Username: postgres  
Password: \< password \>  
Hostname: recipes-api-db.internal  
Database: recipes-api  
Port: 5432

postgres://postgres:\< password \>@recipes-api-db.internal:5432/recipes-api

---

<div class="post-metadata">

**Author:** ![mhoggard](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/mhoggard/32/38611_2.png) [@mhoggard](https://community.fly.io/u/mhoggard)\
**Post date:** [August 27, 2022, 3:01pm UTC](https://community.fly.io/t/cannot-connect-node-js-app-to-postgres-db/6631/5 "2022-08-27T15:01:21Z")

</div>

No change ☹

Also, on heroku, one of my env vars is `PGSSLMODE=no-verify`. I wasn’t sure whether I should include that here too. But I have tried with and without after this change and it didn’t make a difference.

---

<div class="post-metadata">

**Author:** ![cliffr](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/cliffr/32/4995_2.png) [@cliffr](https://community.fly.io/u/cliffr)\
**Post date:** [August 27, 2022, 3:03pm UTC](https://community.fly.io/t/cannot-connect-node-js-app-to-postgres-db/6631/6 "2022-08-27T15:03:33Z")

</div>

I migrated from Heroku yesterday and i also had SSL connection to the database on Heroku, but it’s not working here so i removed it in order to connect to the database.

with `ssl: { rejectUnauthorized: false },` i get “Error: The server does not support SSL connections”.

---

<div class="post-metadata">

**Author:** ![mhoggard](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/mhoggard/32/38611_2.png) [@mhoggard](https://community.fly.io/u/mhoggard)\
**Post date:** [August 27, 2022, 3:04pm UTC](https://community.fly.io/t/cannot-connect-node-js-app-to-postgres-db/6631/7 "2022-08-27T15:04:09Z")

</div>

By the way, since I’m new to this service, if I just make the changes locally and save and run `flyctl deploy` in the project directory, does it pick up those changes? I suppose I should make sure that the change I just made actually did get deployed.

Adding logs to server.js got picked up, but I guess I should make sure that that’s all it takes to deploy a change.

---

<div class="post-metadata">

**Author:** ![cliffr](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/cliffr/32/4995_2.png) [@cliffr](https://community.fly.io/u/cliffr)\
**Post date:** [August 27, 2022, 3:07pm UTC](https://community.fly.io/t/cannot-connect-node-js-app-to-postgres-db/6631/8 "2022-08-27T15:07:40Z")

</div>

Yes it should, or you can restart the app afterwards.

```auto
flyctl restart <appname>

```

---

<div class="post-metadata">

**Author:** ![mhoggard](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/mhoggard/32/38611_2.png) [@mhoggard](https://community.fly.io/u/mhoggard)\
**Post date:** [August 27, 2022, 3:14pm UTC](https://community.fly.io/t/cannot-connect-node-js-app-to-postgres-db/6631/9 "2022-08-27T15:14:58Z")

</div>

Are you suggesting that I unfurl the connection info in my knexfile to separately pass in the username, password, hostname, database and port instead of using the DATABASE\_URL in connection?

---

<div class="post-metadata">

**Author:** ![cliffr](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/cliffr/32/4995_2.png) [@cliffr](https://community.fly.io/u/cliffr)\
**Post date:** [August 27, 2022, 3:16pm UTC](https://community.fly.io/t/cannot-connect-node-js-app-to-postgres-db/6631/10 "2022-08-27T15:16:29Z")

</div>

Try to create a secret  
`flyctl secrets set -a recipes-api DB_URL=postgres://postgres:YOURPASS@recipes-api-db.internal:5432/recipes-api`

and then you’ll use  
`connection: process.env.DB_URL`

---

<div class="post-metadata">

**Author:** ![mhoggard](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/mhoggard/32/38611_2.png) [@mhoggard](https://community.fly.io/u/mhoggard)\
**Post date:** [August 27, 2022, 3:26pm UTC](https://community.fly.io/t/cannot-connect-node-js-app-to-postgres-db/6631/11 "2022-08-27T15:26:28Z")

</div>

Still no change. I added a DB\_URL value in addition to the DATABASE\_URL secret that was already set. And updated the app to point to DB\_URL instead. Added a log of both values so I could ensure they were being pulled in and the changes went through.

The differences between the two urls:

1. The original url generated by fly includes `@top2.nearest.of.recipes-api-db.internal` instead of just `recipes-api-db.internal`
2. The passwords are of different formats

No matter what I do, I still get the same `connect ECONNREFUSED 127.0.0.1:5432'`

Is this implying that it’s not even trying to connect to the db app but somehow back to itself?

---

<div class="post-metadata">

**Author:** ![cliffr](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/cliffr/32/4995_2.png) [@cliffr](https://community.fly.io/u/cliffr)\
**Post date:** [August 27, 2022, 3:33pm UTC](https://community.fly.io/t/cannot-connect-node-js-app-to-postgres-db/6631/12 "2022-08-27T15:33:39Z")

</div>

Maybe knex is using development and not production ?

---

<div class="post-metadata">

**Author:** ![cliffr](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/cliffr/32/4995_2.png) [@cliffr](https://community.fly.io/u/cliffr)\
**Post date:** [August 27, 2022, 3:36pm UTC](https://community.fly.io/t/cannot-connect-node-js-app-to-postgres-db/6631/13 "2022-08-27T15:36:43Z")

</div>

You can also do like this yeah

```auto
 connection: {
    host: process.env.DB_HOST,
    port: process.env.DB_PORT,
    database: process.env.DB_NAME,
    user: process.env.DB_USER,
    password: process.env.DB_PASS
    },

```

[REPLY] 🔔  
I reply here because i have a reply limit for my first day on the forum.

What’s the query ? and did you look if your database was well imported ?

---

<div class="post-metadata">

**Author:** ![mhoggard](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/mhoggard/32/38611_2.png) [@mhoggard](https://community.fly.io/u/mhoggard)\
**Post date:** [August 27, 2022, 3:37pm UTC](https://community.fly.io/t/cannot-connect-node-js-app-to-postgres-db/6631/14 "2022-08-27T15:37:19Z")

</div>

Yep, I think that’s the issue. I just added a log of process.env.NODE\_ENV and it’s coming through as undefined, which falls back to development.

---

<div class="post-metadata">

**Author:** ![mhoggard](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/mhoggard/32/38611_2.png) [@mhoggard](https://community.fly.io/u/mhoggard)\
**Post date:** [August 27, 2022, 3:54pm UTC](https://community.fly.io/t/cannot-connect-node-js-app-to-postgres-db/6631/15 "2022-08-27T15:54:25Z")

</div>

Yes, that was it! I added it to the env section of my TOML and the value is set and I finally got past that error message!

```nohighlight
[env]
  PORT = "8080"
  NODE_ENV = "production"

```

After the first try, I saw what you said you saw, “The server does not support SSL connections”. Then I removed that `PGSSLMODE` secret and got past that error as well.

Unfortunately, I now have a different unexpected error. When I hit one of my queries, I get the following postgres error `error: 'relation "recipes" does not exist',` where recipes is the table that query is based on. But I already created the the tables with their schemas and when I interact with the db in CLI, I see the tables. When the db was first created, I got that error, but then I ran my schema migration to to create all the tables in the CLI and they show up there.

---

<div class="post-metadata">

**Author:** ![mhoggard](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/mhoggard/32/38611_2.png) [@mhoggard](https://community.fly.io/u/mhoggard)\
**Post date:** [August 27, 2022, 4:09pm UTC](https://community.fly.io/t/cannot-connect-node-js-app-to-postgres-db/6631/16 "2022-08-27T16:09:52Z")

</div>

I can successfully enter what I thought was the deployed postgres app via CLI with `flyctl postgres connect -a recipes-api-db`.

I ran my sql to create all the tables here.

Before running that, I got the error for no relations exist. After, when I do `\dt` my tables show up. My queries all work as expected (despite that all my tables are still empty) when run in the CLI.

But in the app, it’s acting like neither the database nor its tables exist.

What am I talked to in the CLI if it’s not the deployed postgres app?

---

<div class="post-metadata">

**Author:** ![mhoggard](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/mhoggard/32/38611_2.png) [@mhoggard](https://community.fly.io/u/mhoggard)\
**Post date:** [August 27, 2022, 4:55pm UTC](https://community.fly.io/t/cannot-connect-node-js-app-to-postgres-db/6631/17 "2022-08-27T16:55:49Z")

</div>

I am finally up and running!!

The first issue was, with all the different things I tried with the database url, I accidentally made a typo in the last one that swapped an underscore for a hyphen in the database name.

The second issue was that, after entering the CLI, I didn’t realize I was automatically connected to the `postgres` database instead of my `recipes_api` database. So that’s where I created all the tables by accident.

Once I created the tables in the right database, the query finally succeeded!

Thanks again for all your help today!

Now, I just have to figure out how to transfer my data out of the heroku db and into this new one…

---

<div class="post-metadata">

**Author:** ![vigneshc5723](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/vigneshc5723/32/14686_2.png) [@vigneshc5723](https://community.fly.io/u/vigneshc5723)\
**Post date:** [August 22, 2023, 11:53pm UTC](https://community.fly.io/t/cannot-connect-node-js-app-to-postgres-db/6631/18 "2023-08-22T23:53:21Z")

</div>

I think I am having a similar issue, what exactly do you mean by: " I didn’t realize I was automatically connected to the `postgres` database instead of my `recipes_api` database".

UPDATE: resolved

I had to connect via: fly pg connect -a myapp-db -d myapp  
Before I was just doing : flyctl pg connect -a

---

<div class="post-metadata">

**Author:** ![kmmarin](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/kmmarin/32/15100_2.png) [@kmmarin](https://community.fly.io/u/kmmarin)\
**Post date:** [September 13, 2023, 7:03am UTC](https://community.fly.io/t/cannot-connect-node-js-app-to-postgres-db/6631/19 "2023-09-13T07:03:15Z")

</div>

I need my heroku-app connect whit db [fly.io](http://fly.io) and heroku app reject de myapp-db.internal like hostname