N
Neon•16mo ago
conscious-sapphire

Never-ending CONNECTION_CLOSED errors.

So, my node projects makes about one database query call every 10 minutes, and every now and then, I get errors like these: So, the connection to my database is closing, how do I solve that from happening? Or is it possible to programmatically check if my connection is closed, and if so, re-open it, and run the query again? The error in text-form: 
Error during getGlobalLeaderboards: Error: write CONNECTION_CLOSED ep-wandering-snowflake-a2isl6k2-pooler.eu-central-1.aws.neon.tech:5432
    at TLSSocket.closed (/home/container/node_modules/postgres/cjs/src/connection.js:438:57)
    at TLSSocket.emit (node:events:529:35)
    at node:net:350:12
    at Socket.done (node:_tls_wrap:657:7)
    at Object.onceWrapper (node:events:632:26)
    at Socket.emit (node:events:517:28)
    at TCP.<anonymous> (node:net:350:12)
    at cachedError (/home/container/node_modules/postgres/cjs/src/query.js:170:23)
    at new Query (/home/container/node_modules/postgres/cjs/src/query.js:36:24)
    at sql (/home/container/node_modules/postgres/cjs/src/index.js:112:11)
    at getGlobalLeaderboards (/home/container/Discord Bots/resources/databases/Bomber Bot XP - DB.js:119:41) {
  code: 'CONNECTION_CLOSED',
  errno: 'CONNECTION_CLOSED',
  address: [
    'ep-wandering-snowflake-a2isl6k2-pooler.eu-central-1.aws.neon.tech'
  ],
  port: [ 5432 ]
}
Error during getGlobalLeaderboards: Error: write CONNECTION_CLOSED ep-wandering-snowflake-a2isl6k2-pooler.eu-central-1.aws.neon.tech:5432
    at TLSSocket.closed (/home/container/node_modules/postgres/cjs/src/connection.js:438:57)
    at TLSSocket.emit (node:events:529:35)
    at node:net:350:12
    at Socket.done (node:_tls_wrap:657:7)
    at Object.onceWrapper (node:events:632:26)
    at Socket.emit (node:events:517:28)
    at TCP.<anonymous> (node:net:350:12)
    at cachedError (/home/container/node_modules/postgres/cjs/src/query.js:170:23)
    at new Query (/home/container/node_modules/postgres/cjs/src/query.js:36:24)
    at sql (/home/container/node_modules/postgres/cjs/src/index.js:112:11)
    at getGlobalLeaderboards (/home/container/Discord Bots/resources/databases/Bomber Bot XP - DB.js:119:41) {
  code: 'CONNECTION_CLOSED',
  errno: 'CONNECTION_CLOSED',
  address: [
    'ep-wandering-snowflake-a2isl6k2-pooler.eu-central-1.aws.neon.tech'
  ],
  port: [ 5432 ]
}
No description
2 Replies
conscious-sapphire
conscious-sapphireOP•16mo ago
Here's what my code looks like: 
const postgres = require("postgres");

let { PGHOST, PGDATABASE, PGUSER, PGPASSWORD, ENDPOINT_ID } = process.env;

const sql = postgres({
    host: PGHOST,
    database: PGDATABASE,
    username: PGUSER,
    password: PGPASSWORD,
    port: 5432,
    ssl: "require",
    connection: {
        options: `project=${ENDPOINT_ID}`,
    },
    onnotice: false
});

const getBomber = async (id) => {
    try {
        if (!id) {
            console.log("Error during getBomber: No ID provided.");
            return;
        }
        return (await sql`SELECT * FROM bomber WHERE id = ${id}`)[0];
    } catch (error) {
        console.error('Error during getBomber:', error);
        return 'error';
    }
};
const postgres = require("postgres");

let { PGHOST, PGDATABASE, PGUSER, PGPASSWORD, ENDPOINT_ID } = process.env;

const sql = postgres({
    host: PGHOST,
    database: PGDATABASE,
    username: PGUSER,
    password: PGPASSWORD,
    port: 5432,
    ssl: "require",
    connection: {
        options: `project=${ENDPOINT_ID}`,
    },
    onnotice: false
});

const getBomber = async (id) => {
    try {
        if (!id) {
            console.log("Error during getBomber: No ID provided.");
            return;
        }
        return (await sql`SELECT * FROM bomber WHERE id = ${id}`)[0];
    } catch (error) {
        console.error('Error during getBomber:', error);
        return 'error';
    }
};
Also, I'm using a "pooler", could be apart of the issue -I don't really know the difference between a pool and client, iirc the pool is for many queries in one session or something? And since my bot is 24/7 it seemed appropiate, but I'm not fully sure. - If you respond, please ping me - It's currently 11 pm, I don't exactly plan to, but if I fall asleep, when I wake up I might just forget I even asked this question, so please ping me.
national-gold
national-gold•16mo ago
Hey @TigerYT 👋🏻 1. Is that per 10 minutes in a cron job? 2. Are you using pooled connection string? Also, https://github.com/porsager/postgres/issues/43 seems more like specific to the postgres.js lib. Can you try the same thing with @neondatabase/serverless? Here's how you'd want to do the same with Neon's serverless driver: 
const { neon } = require("@neondatabase/serverless");

const { PGHOST, PGDATABASE, PGUSER, PGPASSWORD, ENDPOINT_ID } = process.env;

const sql = neon(`postgresql://${PGUSER}:endpoint=${ENDPOINT_ID};${PGPASSWORD}@${PGHOST}/${PGDATABASE}?sslmode=require`)

const getBomber = async (id) => {
    try {
        if (!id) {
            console.log("Error during getBomber: No ID provided.");
            return;
        }
        return (await sql`SELECT * FROM bomber WHERE id = ${id}`);
    } catch (error) {
        console.error('Error during getBomber:', error);
        return 'error';
    }
};
const { neon } = require("@neondatabase/serverless");

const { PGHOST, PGDATABASE, PGUSER, PGPASSWORD, ENDPOINT_ID } = process.env;

const sql = neon(`postgresql://${PGUSER}:endpoint=${ENDPOINT_ID};${PGPASSWORD}@${PGHOST}/${PGDATABASE}?sslmode=require`)

const getBomber = async (id) => {
    try {
        if (!id) {
            console.log("Error during getBomber: No ID provided.");
            return;
        }
        return (await sql`SELECT * FROM bomber WHERE id = ${id}`);
    } catch (error) {
        console.error('Error during getBomber:', error);
        return 'error';
    }
};

Did you find this page helpful?