Cannot connect Node.js app to Postgres db

Questions / Help Build debugging
1

I am trying to get a new app set up on 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.

1 Like
2

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

host: "<myapp>-db.internal"
user: "postgres"
password: "<dbpass>"
database: "<myapp>"
port: 5432
1 Like
3

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.

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.

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

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

5

No change :frowning:

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.

6

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”.

7

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.

8

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

flyctl restart <appname>
9

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?

10

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

11

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?

12

Maybe knex is using development and not production ?

13

You can also do like this yeah

 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] :bell:
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 ?

14

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.

15

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!

[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.

16

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?

17

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…

1 Like
18

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

1 Like
19

I need my heroku-app connect whit db fly.io and heroku app reject de myapp-db.internal like hostname