Connection refused between Fly Express app and Fly Postgres

JavaScript
1

Hello,

I’ve been having a lot of trouble getting my Express app to successfully query the Postgres database. I would really appreciate any help.

I looked at the database logs and this is what happens when it’s queried:

2024-03-10T19:24:15.185 app[17816201f92558] ewr [info] Error: connect ECONNREFUSED 127.0.0.1:5432
2024-03-10T19:24:15.185 app[17816201f92558] ewr [info] at TCPConnectWrap.afterConnect [as oncomplete] (node:net:1602:16) {
2024-03-10T19:24:15.185 app[17816201f92558] ewr [info] errno: -111,
2024-03-10T19:24:15.185 app[17816201f92558] ewr [info] code: 'ECONNREFUSED',
2024-03-10T19:24:15.185 app[17816201f92558] ewr [info] syscall: 'connect',
2024-03-10T19:24:15.185 app[17816201f92558] ewr [info] address: '127.0.0.1',
2024-03-10T19:24:15.185 app[17816201f92558] ewr [info] port: 5432
2024-03-10T19:24:15.185 app[17816201f92558] ewr [info] }

This error happens when I go to https://express-backend.fly.dev/test in my browser. Here is the query:

const getUsers = (request, response) => {
    let cn = "postgres://postgres:<connection string>@<db app name>.internal:5432/template1"
    const pool = new pg.Pool({
      cn,
    });
    pool.query("SELECT * FROM users ORDER BY id ASC", (error, results) => {
        if (error) {
          console.log(error)
        }
        response.status(200).json(results);
    });
};

app.get("/test", db.getUsers);

And here is my .toml file:

app = 'express-backend'
primary_region = 'ewr'

[build]

[http_service]
  internal_port = 8080
  force_https = true
  auto_stop_machines = true
  auto_start_machines = true
  min_machines_running = 0
  processes = ['app']

[[vm]]
  memory = '1gb'
  cpu_kind = 'shared'
  cpus = 1

I also expose port 8080 in the docker file and my express app is listening as follows:

app.listen(8080, '::',() => {
  console.log('Example app listening on port 8080');
});

Does anyone have any ideas as to why this isn’t working? I am also unable to query the database if I’m running the express app locally, but I get a different error that says

error: database "user" does not exist
    at Parser.parseErrorMessage (/Users/user/dev/react-portfolio-site/express-backend/node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (/Users/user/dev/react-portfolio-site/express-backend/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/Users/user/dev/react-portfolio-site/express-backend/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (/Users/user/dev/react-portfolio-site/express-backend/node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (node:events:519:28)
    at addChunk (node:internal/streams/readable:559:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)
    at Readable.push (node:internal/streams/readable:390:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  length: 97,
  severity: 'FATAL',
  code: '3D000',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'postinit.c',
  line: '887',
  routine: 'InitPostgres'
}
2

Hi… It looks like your cn is getting ignored, and it’s trying to contact localhost instead.

Have you run across Fly’s Databases doc for Javascript yet? It gives several examples of how to connect using the handy DATABASE_URL secret.

3

Thank you for the input. I was using this document, but it appears using the DATABASE_URL worked or perhaps I needed to define connectionString instead of cn. However, I have this error now:

2024-03-10T21:39:43.106 app[17816201f92558] ewr [info] error: relation "users" does not exist

I’m assuming the app is attached to Postgres correctly. This is probably more of a Postgres issue, but do you know how I could query a specific database while using DATABASE_URL?

I’ve created tables in multiple databases using fly pg connect --app express-backend-db , connecting to a database in the console, and then manually inserting the data, but the query still doesn’t find users.

EDIT: Do I need to somehow deploy the Postgres database after making changes to it via the method stated above?

4

No problem, glad there was progress… The final component of the DATABASE_URL is the database it’s connecting to.

postgres://{username}:{password}@{hostname}:{port}/{database}?options
                                                   ^^^^^^^^^^

It’s likely called express_backend in this case, being based on the web app’s name by default.

You can see the different options using fly pg db list -a express-backend-db and (as you may already know) switch between them in psql with \c express_backend, etc.

The DATABASE_URL can be temporarily revealed via:

fly m start  # ensure at least one running
fly ssh console -C env | fgrep DATABASE
5

Thank you, with your help I’m able to query the correct database from the express app. I was inserting data into the wrong database.

Do you happen to know how I can do this with my Express app ran locally? Using the full DATABASE_URL string does not work. I have tried proxying with

fly proxy 8080:5432 --app express-backend-db

and setting my .env.local to the revealed DATABASE_URL, except I replaced the hostname with localhost and the port to the one I connect to locally as follows:

DATABASE_URL="postgres://express_backend:{password}@localhost:8080/express_backend?sslmode=disable"

With all of this I get:

Error: Connection terminated unexpectedly

Perhaps I should make a separate post for this.

6

By default, the .internal and .flycast addresses are not accessible from your desktop, but the fly proxy way should work.

You might need 15432 locally, though, since you were already using port 8080 for web-app development.