Drizzle Team•3mo ago

Accumulating connections

For the longest time, whenever I'm working (in dev) and my app restarts, more often times than not, I will get a connection error because I have achieved the max number of concurrent connections for my PostgreSQL DB (5). I have that limit because I use a free provider for the dev database. The only way to keep working, is to login to the DB's admin panel and kill all the connections. Problem here is, why the heck is it accumulating connections? It seems to happen randomly, I cannot track exactly when it doesn't close the connections, so I don't know what causes it. I might simply be editing a Handlebars view, which requires server restart, and once it does, there's a chance I've reached the number of connections. WTF is going on?

9 Replies

Darren•3mo ago

I think it's to do with hot reloading. Either set the idle timeout to be really short or set a global.drizzle then only set if its undefined otherwise use the one from global. Hope that makes sense ofc only do this when node_env = development

patoOP•3mo ago

Hey! Would you mind explaining how HMR might be affecting this? I’m not following

Darren•3mo ago

i think each time a module is reloaded it would generate a new instance of the drizzleclient, and the old one doesnt close the connection properly, and since there is no idle timeout set it stays open indefinitely in postgres, that is my understanding of it anyway

Angelelz•3mo ago

The way people solve this type of problems with HMR is by creating a singleton drizzle instance attached to globalThis. I think that if you search for singleton or HMR in this server you’ll find several answers

rphlmr ⚡•3mo ago

You can look at https://www.npmjs.com/package/@epic-web/remember. It's the same thing as globalThis

patoOP•2mo ago

thank you for all the replies, I only had time to look into this now I just tried this and spent some time testing around, so far so good. I'll awake this post again if I did something wrong reviving this since I tried using that package, and it was working fine so far, but I'm getting the errors again here is the function that connects to the db:

async function connect() {
    if (!db) {
        try {
            logger.info('Connecting to database...');

            pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
            await pool.connect();

            addListeners(pool);

            // Prevent HMR from constantly creating new connections
            // by "remembering" the connection (only useful in dev mode).
            db = remember('db', () => drizzle(pool, { schema }));

            logger.info('Connected successfully to database.');
        } catch (err) {
            logger.fatal('Failed to connect to database.', err);
            process.exit(1);
        }
    }
}

async function connect() {
    if (!db) {
        try {
            logger.info('Connecting to database...');

            pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
            await pool.connect();

            addListeners(pool);

            // Prevent HMR from constantly creating new connections
            // by "remembering" the connection (only useful in dev mode).
            db = remember('db', () => drizzle(pool, { schema }));

            logger.info('Connected successfully to database.');
        } catch (err) {
            logger.fatal('Failed to connect to database.', err);
            process.exit(1);
        }
    }
}

by the way, I don't think I'm using HMR exactly, it's Turborepo's watch mode that triggers my app restarts and, therefore, causes them to reconnect I don't get why connections aren't ending gracefully though. it shouldn't be doing anything out of the ordinary. If the app restarts, why doesn't it properly close and open the connections?

rphlmr ⚡•2mo ago

hard to guess. Maybe Turbo don't close the app / memory leak that keeps. What is addListeners? I am using turbo too with a Remix App but not their watch mode, only what Remix provides

Darren•2mo ago

await pool.connect();

await pool.connect();

you are creating a new client here and not doing anything with it i think you are mixing client and pool you would do this if it was a client you were creating https://orm.drizzle.team/docs/get-started-postgresql#node-postgres check the part about not releasing a client https://node-postgres.com/apis/pool

patoOP•2mo ago

function addListeners(pool: pg.Pool) {
    pool.on('error', (err) => {
        logger.error('An idle client has experienced an error', err.stack);
    });
}

function addListeners(pool: pg.Pool) {
    pool.on('error', (err) => {
        logger.error('An idle client has experienced an error', err.stack);
    });
}

oh I see, then if I need to create my own clients from the pool, what's the point of creating a pool? I thought pools existed so they'd handle multiple connections for you when needed because you can also do pool.query so what's the point of having to extract a client i'm confused

import 'dotenv/config';
import { drizzle, type NodePgDatabase } from 'drizzle-orm/node-postgres';
import { remember } from '@epic-web/remember';
import pg from 'pg';

import logger from '@quacker/logger';

import * as schema from '../schema/schema.js';

let pool: pg.Pool;
let db: NodePgDatabase<typeof schema>;

/**
 * Attempts to connect to the database.
 * 
 * **This is a process-breaking method.** If it fails, it will cause the application to crash.
 */
async function connect() {
    if (!db) {
        try {
            logger.info('Connecting to database...');

            pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
            await pool.connect();

            addListeners(pool);

            // Prevent HMR from constantly creating new connections
            // by "remembering" the connection (only useful in dev mode).
            db = remember('db', () => drizzle(pool, { schema }));

            logger.info('Connected successfully to database.');
        } catch (err) {
            logger.fatal('Failed to connect to database.', err);
            process.exit(1);
        }
    }
}

function addListeners(pool: pg.Pool) {
    pool.on('error', (err) => {
        logger.error('An idle client has experienced an error', err.stack);
    });
}

export { db };

export default {
    connect,
};

import 'dotenv/config';
import { drizzle, type NodePgDatabase } from 'drizzle-orm/node-postgres';
import { remember } from '@epic-web/remember';
import pg from 'pg';

import logger from '@quacker/logger';

import * as schema from '../schema/schema.js';

let pool: pg.Pool;
let db: NodePgDatabase<typeof schema>;

/**
 * Attempts to connect to the database.
 * 
 * **This is a process-breaking method.** If it fails, it will cause the application to crash.
 */
async function connect() {
    if (!db) {
        try {
            logger.info('Connecting to database...');

            pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
            await pool.connect();

            addListeners(pool);

            // Prevent HMR from constantly creating new connections
            // by "remembering" the connection (only useful in dev mode).
            db = remember('db', () => drizzle(pool, { schema }));

            logger.info('Connected successfully to database.');
        } catch (err) {
            logger.fatal('Failed to connect to database.', err);
            process.exit(1);
        }
    }
}

function addListeners(pool: pg.Pool) {
    pool.on('error', (err) => {
        logger.error('An idle client has experienced an error', err.stack);
    });
}

export { db };

export default {
    connect,
};

this is what I'm working with

Gaming

Programming

Accumulating connections