DT
Drizzle Team•5d ago
Misc

Consistently hitting max clients/connections on postgres

Hey, I just launched my app 2 days ago, its being hosted on vercel atm. Despite having barely no users (max spike was 34 users/h) I keep getting an error about hitting the max connections allowed on the db. This is a Nextjs app using Drizzle along with Supabase. Only using the supabase client for checking auth, always awaited, and all db queries are done through Drizzle. I am using the transaction pool url and followed the setup on the supabase docs. I will also be posting this on the Supabase discord. Has anyone experienced something like this before? I imagine its something related on how the clients are getting called or the client setup but any help is more than welcome.
4 Replies
Tyler
Tyler•4d ago
I had this a lot on dev mode, but not production. What's your config look like?
Misc
MiscOP•4d ago
I was having this on dev as well due the HRM but i changed a bit the config and it helped a lot, just not sure why this would happen on prod tho. 
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";

declare global {
  // eslint-disable-next-line no-var
  var postgresSqlClient: ReturnType<typeof postgres> | undefined;
}

let postgresSqlClient;

const connectionString = process.env.DATABASE_URL;

if (!connectionString) {
  throw new Error("DATABASE_URL is not set in .env.local");
}

// Connection pool settings
const poolConfig = {
  max: 20, // Below Supabase limit of 30
  idle_timeout: 10, // 10 seconds
  connect_timeout: 5, // Timeout after 5 seconds if connection cannot be established
  max_lifetime: 60 * 30, // Connection lifetime of 30 minutes
};

if (process.env.NODE_ENV !== "production") {
  if (!global.postgresSqlClient) {
    global.postgresSqlClient = postgres(connectionString, {
      ...poolConfig,
      prepare: false,
    });
  }
  postgresSqlClient = global.postgresSqlClient;
} else {
  postgresSqlClient = postgres(connectionString, {
    ...poolConfig,
    prepare: false,
  });
}

// Disable prefetch as it is not supported for "Transaction" pool mode
export const db = drizzle(postgresSqlClient, { schema });
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";

declare global {
  // eslint-disable-next-line no-var
  var postgresSqlClient: ReturnType<typeof postgres> | undefined;
}

let postgresSqlClient;

const connectionString = process.env.DATABASE_URL;

if (!connectionString) {
  throw new Error("DATABASE_URL is not set in .env.local");
}

// Connection pool settings
const poolConfig = {
  max: 20, // Below Supabase limit of 30
  idle_timeout: 10, // 10 seconds
  connect_timeout: 5, // Timeout after 5 seconds if connection cannot be established
  max_lifetime: 60 * 30, // Connection lifetime of 30 minutes
};

if (process.env.NODE_ENV !== "production") {
  if (!global.postgresSqlClient) {
    global.postgresSqlClient = postgres(connectionString, {
      ...poolConfig,
      prepare: false,
    });
  }
  postgresSqlClient = global.postgresSqlClient;
} else {
  postgresSqlClient = postgres(connectionString, {
    ...poolConfig,
    prepare: false,
  });
}

// Disable prefetch as it is not supported for "Transaction" pool mode
export const db = drizzle(postgresSqlClient, { schema });
Tyler
Tyler•4d ago
Is there a specific reason why you set poolConfig? This is my implementation, which is similar but I just use the defaults of things. 
const connectionString = String(process.env.DATABASE_URL);

declare module global {
  let postgresSqlClient: ReturnType<typeof postgres> | undefined;
}

let postgresSqlClient;

if (process.env.NODE_ENV !== 'production') {
  if (!global.postgresSqlClient) {
    global.postgresSqlClient = postgres(connectionString);
  }
  postgresSqlClient = global.postgresSqlClient;
} else {
  postgresSqlClient = postgres(connectionString);
}

export const client = postgresSqlClient;
export const db = drizzle(postgresSqlClient);
const connectionString = String(process.env.DATABASE_URL);

declare module global {
  let postgresSqlClient: ReturnType<typeof postgres> | undefined;
}

let postgresSqlClient;

if (process.env.NODE_ENV !== 'production') {
  if (!global.postgresSqlClient) {
    global.postgresSqlClient = postgres(connectionString);
  }
  postgresSqlClient = global.postgresSqlClient;
} else {
  postgresSqlClient = postgres(connectionString);
}

export const client = postgresSqlClient;
export const db = drizzle(postgresSqlClient);
Misc
MiscOP•4d ago
just testing things out, thought it might solve the issue ahahah

Did you find this page helpful?