Prisma•4w ago

Connection Pooling issues with Azure SQL

I continue to see issues in our error logs in production with prisma using Azure SQL:

Timed out fetching a new connection from the connection pool. More info: http://pris.ly/d/connection-pool (Current connection pool timeout: 10, connection limit: 3)

Timed out fetching a new connection from the connection pool. More info: http://pris.ly/d/connection-pool (Current connection pool timeout: 10, connection limit: 3)

The connection string looks like this:

sqlserver://<server>.database.windows.net:1433;database=<dbname>;user=<username>;password=<password>;encrypt=true;trustServerCertificate=true;

sqlserver://<server>.database.windows.net:1433;database=<dbname>;user=<username>;password=<password>;encrypt=true;trustServerCertificate=true;

This is in a nextjs app that we are using it.

6 Replies

RaphaelEtim•2w ago

Hi @Jonathan 👋 Apologies for the late response here. This must have slipped through the cracks. Can you increase the connectionLimit parameter from the default of 3 to say 20?

Microsoft SQL Server | Prisma Documentation

This page explains how Prisma can connect to a Microsoft SQL Server database using the Microsoft SQL Server database connector.

Jonathan•2w ago

ok I will give it a try

RaphaelEtim•2w ago

Do let us know if increasing it solves the issue for for you.

Jonathan•2w ago

ok thanks

JTB•2w ago

Hey, You've actually opened up a lot of connections. This can lead to further problems. I wouldn't increase the limit but consider using a kind of "lib" for this. I would build something like that: "lib/prisma.js"

import { PrismaClient } from '@prisma/client';

let prisma;

if (typeof window === 'undefined') { // Ensure Prisma only runs on the server
    if (process.env.NODE_ENV === 'production') {
        prisma = new PrismaClient();
    } else {
        // Ensure the global object is used so that in development you don't hit
        // a problem with too many clients being instantiated.
        if (!global.prisma) {
            global.prisma = new PrismaClient();
        }
        prisma = global.prisma;
    }
}

export default prisma;

import { PrismaClient } from '@prisma/client';

let prisma;

if (typeof window === 'undefined') { // Ensure Prisma only runs on the server
    if (process.env.NODE_ENV === 'production') {
        prisma = new PrismaClient();
    } else {
        // Ensure the global object is used so that in development you don't hit
        // a problem with too many clients being instantiated.
        if (!global.prisma) {
            global.prisma = new PrismaClient();
        }
        prisma = global.prisma;
    }
}

export default prisma;

I would then remove the dependencies from Prisma and only use the Lib. Then you only have one connection (usually enough, unless you have a social media site, but then you need other things anyway) example:

import prisma from '@/lib/prisma';

export default async function handler(req, res) {
    const users = await prisma.user.findMany();
    res.json(users);
}

import prisma from '@/lib/prisma';

export default async function handler(req, res) {
    const users = await prisma.user.findMany();
    res.json(users);
}

Jonathan•2w ago

Yeah I have a few thousand active users at a time generally and I am doing something like this in the nextjs app

import { PrismaClient } from "@prisma/client";

import { env } from "@/env";

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
};

export const db =
  globalForPrisma.prisma ??
  new PrismaClient({
    log:
      env.NODE_ENV === "development" ? ["query", "error", "warn"] : ["error"],
  });

if (env.NODE_ENV !== "production") globalForPrisma.prisma = db;

import { PrismaClient } from "@prisma/client";

import { env } from "@/env";

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
};

export const db =
  globalForPrisma.prisma ??
  new PrismaClient({
    log:
      env.NODE_ENV === "development" ? ["query", "error", "warn"] : ["error"],
  });

if (env.NODE_ENV !== "production") globalForPrisma.prisma = db;

Gaming

Programming

Connection Pooling issues with Azure SQL