Next TS 14项目中Prisma连接Azure SQL数据库API报500错误求助

我正在构建Next TS 14项目，制作了一个表单，用户填写后点击提交按钮时需将数据发送至Azure SQL数据库。使用Prisma ORM完成代码与数据库的集成，已初始化Prisma并创建了API路由，但每次用Postman测试时都会显示500：内部服务器错误。

API路由代码 #

import { PrismaClient } from "@prisma/client";
import { NextApiRequest, NextApiResponse } from "next";
import { z } from "zod";

const prisma = new PrismaClient();

const bigIntSchema = z.union([
  z.string().regex(/^\d+$/).transform(val => BigInt(val)),
  z.number().int().transform(val => BigInt(val))
]);

const ownerSchema = z.object({
  fullName: z.string().min(1, "Full name is required"),
  phoneNo: z.string().regex(/^[\d\s-+()]*$/, "Invalid phone number format"),
  gymName: z.string().min(1, "Gym name is required"),
  gymAddress: z.string().min(1, "Gym address is required"),
  images: z.string(),
  subscriptionPackages: z.string(),
  gymContactInfo: z.string(),
  workoutsOffered: z.string().optional(),
  equipmentsAvailable: z.string().optional(),
  ledger_start_transaction_id: bigIntSchema,
  ledger_start_sequence_number: bigIntSchema
});

export async function POST(req: NextApiRequest, res: NextApiResponse) {
    const parsedBody = ownerSchema.safeParse(req.body);
  
    if (!parsedBody.success) {
      return res.status(400).json({ message: "Invalid input", errors: parsedBody.error.flatten() });
    }
  
    try {
      const newGymOwner = await prisma.gymOwners.create({
        data: parsedBody.data
      });
  
      return res.status(201).json({ message: 'Gym owner created successfully', data: newGymOwner });
    } catch (error) {
      console.error('Error creating gym owner:', error);
      return res.status(500).json({ message: "Could not create gym owner", error: error instanceof Error ? error.message : 'Unknown error' });
    } finally {
      await prisma.$disconnect();
    }
}

表单组件代码 #

import AnotherButton from "@/components/buttons/AnotherButton"
import PrimaryButton from "@/components/buttons/PrimaryButton"
import axios from "axios"
import { log } from "console"
import { useState } from "react"


export default function GymOwners() {
    const style = {
        "border": "1px solid black"
    }

    const [formData, setFormData] = useState({
      fullName: '',
      phoneNumber: '',
      gymName: '',
      gymAddress: '',
      images: '',
      subscriptionPackages: '',
      gymContactInfo: '',
      workoutsOffered: '',
      equipmentsAvailable: '',
    })

    const handleSubmit = async (e: React.FormEvent) => {
        e.preventDefault();

        try {
          const res = await axios.post("http://localhost3000/api/v1/gymowners", formData);
          console.log(res.status)
        } catch(e: any) {
          console.log("Error:", e);
        }

    }

    const handleChange =  (e:  React.ChangeEvent<HTMLInputElement | HTMLTextAreaElement>) => {
      setFormData({
        ...formData,
        [e.target.name]: e.target.value
      })
    }
    return (
      <div className="flex justify-center min-h-screen bg-gray-100">
        <div className="flex flex-col w-full max-w-3xl border border-gray-300 bg-white rounded-lg shadow-lg p-8 pt-16">
          <h2 className="text-3xl font-bold text-center text-gray-800 mb-8">
            Create Gym Owner
          </h2>
          <form className="space-y-6">
            <div>
              <label className="block text-lg font-medium text-gray-700">
                Full Name:
                <input
                  className="w-full mt-2 p-3 border border-gray-300 rounded-lg focus:outline-none focus:ring-2 focus:ring-blue-500"
                  onChange={handleChange}
                  type="text"
                  name="fullName"
                  required
                />

问题排查与修复方案 #

1. 字段不匹配与缺失必填项 #

• 字段名不一致：表单state中的phoneNumber与API Schema要求的phoneNo不匹配，导致Zod验证失败；
• 缺失BigInt必填字段：API Schema要求的ledger_start_transaction_id和ledger_start_sequence_number在表单state中完全缺失，Prisma向数据库写入时会因必填字段为空触发500错误。

修复步骤：

• 修改表单state，将phoneNumber改为phoneNo，并添加两个BigInt字段：

  const [formData, setFormData] = useState({
    fullName: '',
    phoneNo: '',
    gymName: '',
    gymAddress: '',
    images: '',
    subscriptionPackages: '',
    gymContactInfo: '',
    workoutsOffered: '',
    equipmentsAvailable: '',
    ledger_start_transaction_id: '',
    ledger_start_sequence_number: ''
  })
  

• 在表单中添加对应输入框，设置name属性与Schema字段一致，比如手机号输入框的name="phoneNo"，两个BigInt字段用文本输入（Zod会自动转换为BigInt）。

2. API请求地址错误 #

表单中axios请求地址http://localhost3000/api/v1/gymowners缺少端口号的冒号，正确地址应为http://localhost:3000/api/v1/gymowners。

3. PrismaClient实例化优化 #

Next.js API路由每次请求会重新创建PrismaClient实例，可能导致数据库连接异常。推荐使用单例模式：

• 在项目根目录创建lib/prisma.ts：

  import { PrismaClient } from '@prisma/client'
  
  const prisma = global.prisma || new PrismaClient()
  
  if (process.env.NODE_ENV !== 'production') global.prisma = prisma
  
  export default prisma
  

• 在API路由中导入单例并移除手动断开连接的代码：

  import prisma from '@/lib/prisma'
  
  // 移除原有的const prisma = new PrismaClient()
  // 移除finally块中的await prisma.$disconnect()
  

4. 查看详细错误日志 #

在API路由的catch块中打印完整错误对象，便于定位具体问题：

catch (error) {
  console.error('Full error details:', error);
  return res.status(500).json({ message: "Could not create gym owner", error: error instanceof Error ? error.message : 'Unknown error', stack: error instanceof Error ? error.stack : undefined });
}

内容的提问来源于stack exchange，提问作者Shreyan Shukla