[Bug]: Db ECONNRESET on first request after 45 mins idle(fix ready)

[SteelRazor47]

Package.json file

{
  "name": "medusa-starter-default",
  "version": "0.0.1",
  "description": "A starter for Medusa projects.",
  "author": "Medusa (https://medusajs.com)",
  "license": "MIT",
  "keywords": [
    "sqlite",
    "postgres",
    "typescript",
    "ecommerce",
    "headless",
    "medusa"
  ],
  "scripts": {
    "build": "medusa build",
    "seed": "medusa exec ./src/scripts/seed.ts",
    "start": "medusa start",
    "dev": "medusa develop",
    "test:integration:http": "TEST_TYPE=integration:http NODE_OPTIONS=--experimental-vm-modules jest --silent=false --runInBand --forceExit",
    "test:integration:modules": "TEST_TYPE=integration:modules NODE_OPTIONS=--experimental-vm-modules jest --silent --runInBand --forceExit",
    "test:unit": "TEST_TYPE=unit NODE_OPTIONS=--experimental-vm-modules jest --silent --runInBand --forceExit"
  },
  "dependencies": {
    "@medusajs/admin-sdk": "2.1.3",
    "@medusajs/cli": "2.1.3",
    "@medusajs/framework": "2.1.3",
    "@medusajs/medusa": "2.1.3",
    "@mikro-orm/core": "5.9.7",
    "@mikro-orm/knex": "5.9.7",
    "@mikro-orm/migrations": "5.9.7",
    "@mikro-orm/postgresql": "5.9.7",
    "awilix": "^8.0.1",
    "jwt-decode": "^4.0.0",
    "pg": "^8.13.0",
    "postmark": "^4.0.5"
  },
  "devDependencies": {
    "@medusajs/test-utils": "2.1.3",
    "@mikro-orm/cli": "5.9.7",
    "@swc/core": "1.5.7",
    "@swc/jest": "^0.2.36",
    "@types/jest": "^29.5.13",
    "@types/node": "^20.0.0",
    "@types/react": "^18.3.2",
    "@types/react-dom": "^18.2.25",
    "jest": "^29.7.0",
    "prop-types": "^15.8.1",
    "react": "^18.2.0",
    "react-dom": "^18.2.0",
    "ts-node": "^10.9.2",
    "typescript": "^5.6.2",
    "vite": "^5.2.11"
  },
  "engines": {
    "node": ">=20"
  },
  "packageManager": "[email protected]"
}

Node.js version

v22.12.0

Database and its version

Postgres 17.2

Operating system name and version

Ubuntu 24.04

Browser name

No response

What happended?

If the backend(in staging/production mode) idles, without any request, for about 45 minutes, the first request gives an error ECONNRESET from postgres itself:
{"level":"http","message":"172.18.0.1 - - [24/Dec/2024:01:30:55 +0000] \"GET /store/regions HTTP/1.1\" 500 86 \"-\" \"Next.js Middleware\"","timestamp":"2024-12-24 01:30:55"} Connection Error: Connection ended unexpectedly {"level":"error","message":"select \"a0\".\"id\", \"a0\".\"token\" from \"public\".\"api_key\" as \"a0\" where \"a0\".\"deleted_at\" is null and \"a0\".\"token\" = 'pk_f914979a893a6a6e66cab3aa24344889c84dd64055c20d5e8c4ec8747e9b64d9' and \"a0\".\"type\" = 'publishable' and (\"a0\".\"revoked_at\" is null or \"a0\".\"revoked_at\" > '2024-12-24T02:09:36.211Z') order by \"a0\".\"id\" asc - read ECONNRESET","stack":[{"columnNumber":16 ....
full error: stacktrace.txt
Subsequent requests work fine. This cascades into an initial 500 response from the nextjs starter, as the first thing to be checked is /store/regions inside the middleware.

After a while i found a solution: as recommended in the official knex docs, pool.min is set to 2 for historical reasons, but should really be set to 0 to avoid stale connection errors. This is indeed what happens in my case and setting databaseDriverOptions.pool.min = 0 fixes it.

I can't seem to reproduce it locally on a fresh clone of medusa and I dont want to waste too much on it right now and since it can be fixed on our side, but since it's recommended by the knex docs I think it should be changed upstream regardless.

I'll look more into a reproducible setup in the future if need be.

Expected behavior

Any well-formed request should work, even after a "long" idling time

Actual behavior

The first request after a total idle period(about 45 mins) errors with ECONNRESET, subsequent requests work fine.

Link to reproduction repo

TBD