Next.js部署Vercel生产环境Prisma触发max_user_connections错误

开发环境运行正常，仅创建1个数据库连接；但部署到Vercel生产环境后，立即触发数据库最大连接数限制（当前设置为5），导致API请求返回500错误，且错误发生后应用持续每秒发送GET请求，均失败。

当前Prisma客户端代码 #

client.js：

import { PrismaClient } from "./generators/app";

export const prisma =
  global.prisma ||
  new PrismaClient();

if (process.env.NODE_ENV !== 'production') global.prisma = prisma;

API文件导入方式：

import { prisma } from "../../../prisma/client";

错误日志 #

2022-10-27T20:58:32.492Z    07594f4a-727a-4e98-a4aa-264d060dc08b ERROR  PrismaClientInitializationError: 
Invalid `prisma.user.findUnique()` invocation:
Error querying the database: Server error: `ERROR 42000 (1226): User 'xxx' has exceeded the 'max_user_connections' resource (current value: 5)'
    at RequestHandler.handleRequestError (/var/task/.next/server/chunks/7738.js:31215:19)
    at RequestHandler.request (/var/task/.next/server/chunks/7738.js:31188:18)
    at async PrismaClient._request (/var/task/.next/server/chunks/7738.js:32176:24)
    at async havePermission (/var/task/.next/server/chunks/3433.js:49:40)
    at async getServerData (/var/task/.next/server/pages/api/app/user/get/[column].js:181:34)
    at async Object.apiResolver (/var/task/node_modules/next/dist/server/api-utils/node.js:366:9)
    at async NextNodeServer.runApi (/var/task/node_modules/next/dist/server/next-server.js:469:9)
    at async Object.fn (/var/task/node_modules/next/dist/server/next-server.js:719:37)
    at async Router.execute (/var/task/node_modules/next/dist/server/router.js:247:36)
    at async NextNodeServer.run (/var/task/node_modules/next/dist/server/base-server.js:346:29) {
  clientVersion: '4.3.1',
  errorCode: undefined
}
2022-10-27T20:58:32.501Z    07594f4a-727a-4e98-a4aa-260d060dc08b    ERROR   PrismaClientInitializationError: 
Invalid `prisma.user.findUnique()` invocation:
Error querying the database: Server error: `ERROR 42000 (1226): User 'xxx' has exceeded the 'max_user_connections' resource (current value: 5)'
    at RequestHandler.handleRequestError (/var/task/.next/server/chunks/7738.js:31215:19)
    at RequestHandler.request (/var/task/.next/server/chunks/7738.js:31188:18)
    at async PrismaClient._request (/var/task/.next/server/chunks/7738.js:32176:24)
    at async havePermission (/var/task/.next/server/chunks/3433.js:49:40)
    at async getServerData (/var/task/.next/server/pages/api/app/user/get/[column].js:181:34)
    at async Object.apiResolver (/var/task/node_modules/next/dist/server/api-utils/node.js:366:9)
    at async NextNodeServer.runApi (/var/task/node_modules/next/dist/server/next-server.js:469:9)
    at async Object.fn (/var/task/node_modules/next/dist/server/next-server.js:719:37)
    at async Router.execute (/var/task/node_modules/next/dist/server/router.js:247:36)
    at async NextNodeServer.run (/var/task/node_modules/next/dist/server/base-server.js:346:29) {
  clientVersion: '4.3.1',
  errorCode: undefined
}
RequestId: 07594f4a-727a-4e98-a4aa-264d060dc08b Error: Runtime exited with error: exit status 1
Runtime.ExitError

问题原因 #

Vercel的Serverless函数是请求触发式，每次请求可能启动独立运行实例。你当前的代码仅在非生产环境将Prisma实例挂载到global对象，生产环境下每个请求都会创建新的PrismaClient实例，每个实例占用数据库连接，并发请求数超过数据库最大连接数时就会触发错误。

修复方案 #

1. 修改Prisma客户端初始化代码 #

调整client.js，在生产环境也将Prisma实例挂载到global对象，确保每个运行实例只创建一个PrismaClient：

import { PrismaClient } from "./generators/app";

const prisma = global.prisma || new PrismaClient();

// 生产环境也挂载到global，复用实例
if (process.env.NODE_ENV !== 'development') global.prisma = prisma;

export default prisma;

2. 限制Prisma连接池大小 #

在schema.prisma中配置连接池大小，避免单个实例占用过多连接：

datasource db {
  provider = "mysql" // 替换为你使用的数据库类型
  url      = env("DATABASE_URL")
  pool_size = 2 // 根据数据库最大连接数调整，比如总连接数5时，每个实例设为2
}

3. 修复重复请求逻辑 #

排查前端代码，取消错误发生后每秒发送请求的逻辑，避免进一步加剧连接耗尽问题。

内容的提问来源于stack exchange，提问作者Angel Luis