Cannot access remote DB - BLOCKING network issue

[oscar6echo]

Version: Deno 2.2.4 - latest as of writing

I try to connect to a remote DB from an Redhat 8.x Linux VM - in a corp env (within LAN, no proxy involved).
This does not work in deno but works in node and python - showing there is no credential or network problem.
Possibly (loosely ?) related to #27803 and #27670 ?

---

Important Question

IS THERE AN IMMEDIATE WORKAROUND ?
THIS IS A PROBLEM FOR USE IN CORP ENV WHEN SUCH "BASIC" CAPABILITY FAILS - CASTS DOUBTS TO ABILITY TO USE IN PRODUCTION.
(caps to capture attention - hopefully).

---

Scripts

deno -> error

import postgres from "postgres";
import c from "../common/conf.ts";

const certPath = Deno.env.get("REQUESTS_CA_BUNDLE");

const sql = postgres({
  host: c.PGHOST,
  port: c.PGPORT,
  database: c.PGDATABASE,
  username: c.PGUSERNAME,
  password: c.PGPASSWORD,
  ssl: {
    rejectUnauthorized: true,
    ca: Deno.readTextFileSync(`${certPath}`).toString(),
  },
});

const get_some_data = async () => {
  const script = `
SELECT 
  schema_name
FROM information_schema.schemata
WHERE
    schema_name NOT in ('information_schema', 'hdb_catalog')
  AND
    NOT starts_with(schema_name, 'pg_')
  AND
    NOT starts_with(schema_name, 'fms_')
;
`;
  console.log(script);
  const query = sql`${script}`;
  const data = await query;
  return data;
};

const data = await get_some_data();
console.log({ data });
console.log("END");

Output:

error: Uncaught (in promise) Error: getaddrinfo ENOTFOUND my-internal-url.fr.world.socgen
    at __node_internal_captureLargerStackTrace (ext:deno_node/internal/errors.ts:93:9)
    at __node_internal_ (ext:deno_node/internal/errors.ts:246:10)
    at GetAddrInfoReqWrap.onlookupall [as oncomplete] (node:dns:43:26)
    at ext:deno_node/internal_binding/cares_wrap.ts:78:9
    at eventLoopTick (ext:core/01_core.js:214:9)

node -> works

import pg from "pg";
import * as fs from "node:fs";

const script_1 = `
SELECT 
  schema_name
FROM information_schema.schemata 
WHERE
    schema_name NOT in ('information_schema', 'hdb_catalog')
  AND
    NOT starts_with(schema_name, 'pg_')
  AND
    NOT starts_with(schema_name, 'fms_')
;
`;

const certPath = process.env.REQUESTS_CA_BUNDLE

const client = new pg.Client({
  host: process.env.PGHOST,
  user: process.env.PGUSERNAME,
  password: process.env.PGPASSWORD,
  port: process.env.PGPORT,
  database: process.env.PGDATABASE,
  ssl: {
    rejectUnauthorized: true,
    ca: fs.readFileSync(`${certPath}`).toString(),
  },
});

await client.connect();
const res = await client.query(script_1);
console.log({ res });
console.log({ data: res.rows });
await client.end();

python -> works

import psycopg2
import os

PGHOST = os.getenv('PGHOST')
PGPORT = os.getenv('PGPORT')
PGUSERNAME = os.getenv('PGUSERNAME')
PGPASSWORD = os.getenv('PGPASSWORD')
PGDATABASE = os.getenv('PGDATABASE')
 
conn = psycopg2.connect(
  host= PGHOST,
  port = PGPORT,
  user = PGUSERNAME,
  password = PGPASSWORD,
  database = PGDATABASE,
)

cur = conn.cursor()

script = """
SELECT 
  schema_name
FROM information_schema.schemata

WHERE
    schema_name NOT in ('information_schema', 'hdb_catalog')
  AND
    NOT starts_with(schema_name, 'pg_')
  AND
    NOT starts_with(schema_name, 'fms_')
;
"""

cur.execute(script)
rows = cur.fetchall()
for row in rows:
    print(row)

cur.close()
conn.close()

[oscar6echo]

Additional info:
The issue is independent of the postgresl client lib used. I tried with deno & pg: Same error.
The problem appears to be related to deno somehow.

[oscar6echo]

Additional info:

• ping -> works

  ping $PGHOST

• psql -> works

  PGURL="postgres://$PGUSERNAME:$PGPASSWORD@$PGHOST:$PGPORT/$PGDATABASE"
  psql $PGURL

[oscar6echo]

After some trial and error, it appears that the dns/network is broken if you use an npm package with deno.
But it works if you use a denoland or jsr package in deno.

This script works for all ways of importing:

// import { Client } from https://deno.land/x/postgres@v0.19.3/mod.ts; // WORKS
import { Client } from "jsr:@bartlomieju/postgres"; // WORKS

const script_1 = `
SELECT 
  schema_name
FROM information_schema.schemata
WHERE
    schema_name NOT in ('information_schema', 'hdb_catalog')
  AND
    NOT starts_with(schema_name, 'pg_')
  AND
    NOT starts_with(schema_name, 'fms_')
;
`;
console.log(script_1);

const client = new Client({
  hostname: Deno.env.get("PGHOST"),
  user: Deno.env.get("PGUSERNAME"),
  password: Deno.env.get("PGPASSWORD"),
  port: Deno.env.get("PGPORT"),
  database: Deno.env.get("PGDATABASE"),
});
await client.connect();
{
  const result = await client.queryObject(script_1);
  console.log(result.rows);
}
await client.end();
console.log("END");

This script works does not work with npm import but works with denoland or jsr imports:

// NOT OK -- error "Error: getaddrinfo ENOTFOUND"
// import postgres from "npm:postgres"; 

// OK run and types
// import postgres from https://deno.land/x/postgresjs@v3.4.5/mod.js; 

// runs OK but types different from https://deno.land/x/postgresjs
// apparent lib issue bad export in lib deno.jsonc on jsr
import postgres from "@y0/postgres"; 


const certPath = Deno.env.get("REQUESTS_CA_BUNDLE");

const sql = postgres({
  host: Deno.env.get("PGHOST") || "",
  port: parseInt(Deno.env.get("PGPORT") || "3000"),
  database: Deno.env.get("PGDATABASE") || "",
  username: Deno.env.get("PGUSERNAME") || "",
  password: Deno.env.get("PGPASSWORD") || "",
  ssl: {
    rejectUnauthorized: true,
    ca: Deno.readTextFileSync(`${certPath}`).toString(),
  },
});

const get_some_data = async () => {
  const script = sql`
SELECT 
  schema_name
FROM information_schema.schemata
WHERE
    schema_name NOT in ('information_schema', 'hdb_catalog')
  AND
    NOT starts_with(schema_name, 'pg_')
  AND
    NOT starts_with(schema_name, 'fms_')
;
`;
  const query = sql`${script}`;
  const data = await query;
  return data;
};
const data = await get_some_data();
console.log({ data });
sql.end();
console.log("END");

So the current state is

• ONLY USE libs imported from jsr or denoland
• POTENTIAL getaddrinfo ENOTFOUND with libs imported from npm

Next question for postgres connection is: Which is best maintained ?

• jsr:@bartlomieju/postgres seems to work fine but doc does not even mention 2.0 ? @bartlomieju can we assume it will be maintained ? or better use another ?
• @y0/postgres: Is this one maintained ? looks like it. Am I right in thinking that the export is wrong because it leaves out the types:

Compare this:

with the package doc suggesting this import from denoland:

Please advise !

[oscar6echo]

It looks like this issue could have the "node compat" tag.