P
Prisma2mo ago
Jonathan

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
RaphaelEtim4w 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
Jonathan4w ago
ok I will give it a try
RaphaelEtim
RaphaelEtim4w ago
Do let us know if increasing it solves the issue for for you.
Jonathan
Jonathan4w ago
ok thanks
JTB
JTB4w 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
Jonathan4w 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;