Drizzle Team•16mo ago

Best practice on connections for PostgreSQL

I got the following error; remaining connection slots are reserved for non-replication superuser connections So I'm currently the only person using my DB in dev mode - and only 2 apps in a Turborepo are connected to it. One is a Next.js back-end and one is an SST AWS stack - they both are connected through the code below;

import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';

const pool = postgres(process.env.DATABASE_URL as string);

const client = drizzle(pool, { logger: true });

export { client, pool };

import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';

const pool = postgres(process.env.DATABASE_URL as string);

const client = drizzle(pool, { logger: true });

export { client, pool };

Drizzle is my ORM and postgres (also known as postgres.js) is my PG client. Whenever, either in my Next.js app or in my SST app, I want to connect to my database - I'll just call my client constant, and apply either a client.select() or any other query I want to run. The problem ? Well, running a little SELECT * FROM pg_stat_activity returned me 76 connections (all from my machine, ClientRead, on status idle)... It's as if whenever I call my client it opens a new connection, ultimately reaching PSQL limit. What's the best approach ? I believe if many users connect to my app once it's live, it'd be problematic. I've heard about pgBouncer but I don't know if I need session or transaction mode to be enabled. Any best practice ? Again, I just want my Next.js app to support any number of user I want (like 1000 daily users) without any problem with my PSQL reaching connection limits.

6 Replies

Angelelz•16mo ago

Is this happening in the Dev environment with HMR?

rphlmr ⚡•16mo ago

If it is on dev, you need to persist drizzle db client on global, to survive hmr. Take a look at this tiny package from Kent. https://github.com/epicweb-dev/remember

GitHub

GitHub - epicweb-dev/remember: Simple, type-safe, "singleton" imple...

Simple, type-safe, "singleton" implementation. Contribute to epicweb-dev/remember development by creating an account on GitHub.

rphlmr ⚡•16mo ago

Basically, it creates a singletons in global

sevenwestonroadsOP•16mo ago

Yes ! Like below @rphlmr ?

import { remember } from '@epic-web/remember';
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';

const pool = postgres(process.env.DATABASE_URL as string);

const client = remember('client', () => drizzle(pool, { logger: true }));

export { client, pool };

import { remember } from '@epic-web/remember';
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';

const pool = postgres(process.env.DATABASE_URL as string);

const client = remember('client', () => drizzle(pool, { logger: true }));

export { client, pool };

Angelelz•16mo ago

You need to create the drizzle object as as Singleton. This is an example implementation https://discord.com/channels/1043890932593987624/1043890932593987627/1141696708099711148

rphlmr ⚡•16mo ago

This is what does remember 🥹

Gaming

Programming

Best practice on connections for PostgreSQL