【Next.js】使用Next.js写 CRUD API 接口并与数据库交互（PostgreSQL + Prisma ORM 和 MongoDb + mongoose 两种方案）

概要

本文详细的描述了 Next.js CRUD API 使用方法，使用 PostgreSQL 数据库和 Prisma ORM，提供完整的创建、读取、更新和删除操作。

Next.js 中选择将 API 路由放在 app 还是 pages 目录下，取决于项目需求和技术栈版本，本文采用的 app 目录。

特性	App Router (app/api)	Pages Router (pages/api)
Next.js版本	Next.js 13.4+ 的新项目	Next.js 12 及以下的项目
类型支持	自动类型推断	需手动定义类型
文件定义	route.ts 导出方法函数	导出默认函数处理 req/res
中间件	使用 Next.js 中间件	支持 Express 等中间件
请求处理	分离的 HTTP 方法函数	需手动检查 req.method
流式响应	原生支持	手动实现
ISR 支持	支持	不支持
架构	基于 Server Components	传统 Node.js 风格
学习曲线	较新概念多	更传统简单

目录结构如下

app/
  api/
    products/
      route.ts        # GET (列表), POST 
      [id]/
        route.ts      # GET (单个), PUT, DELETE 

完整流程和代码示例 (PostgreSQL + Prisma ORM)

1. 项目初始化与配置

创建项目并安装依赖

提示：如果已有Next.js前端工程那么只需要在项目根目录下运行 npm install prisma @prisma/client pg

npx create-next-app@latest nextjs-crud-api 
cd nextjs-crud-api 
# 如果已有前端工程只需要在根目录运行下面这句话，无需创建新工程
npm install prisma @prisma/client pg 

初始化 Prisma

npx prisma init 

这会创建一个 prisma 目录和 .env 文件。

配置数据库连接 (上面创建的 .env)

环境变量: 确保敏感信息不在代码中硬编码

DATABASE_URL="postgresql://your_username:your_password@localhost:5432/nextjs_db?schema=public"

替换 your_username、your_password和 nextjs_db 为你的 PostgreSQL 凭据和数据库名称。

2. 数据模型定义 (prisma/schema.prisma)

generator client {
  provider = "prisma-client-js"
}
 
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}
 
model Product {
  id          Int      @id @default(autoincrement())
  name        String 
  description String?
  price       Decimal  @default(0)
  stock       Int      @default(0)
  category    String 
  isActive    Boolean  @default(true)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt 
}

运行数据库迁移

npx prisma migrate dev --name init 

3. 创建可重用的 Prisma 客户端 (lib/prisma.ts)

import { PrismaClient } from '@prisma/client'
 
declare global {
  var prisma: PrismaClient | undefined 
}
 
const prisma = global.prisma  || new PrismaClient()
 
if (process.env.NODE_ENV  === 'development') {
  global.prisma  = prisma 
}
 
export default prisma 

4. 完整的 CRUD API 实现 (app/api/products/route.ts)

import prisma from '@/lib/prisma'
import { NextResponse } from 'next/server'
 
// 获取所有产品 (GET)
export async function GET(request: Request) {
  try {
    const { searchParams } = new URL(request.url) 
    const category = searchParams.get('category') 
    const minPrice = searchParams.get('minPrice') 
    const maxPrice = searchParams.get('maxPrice') 
    const isActive = searchParams.get('isActive') 
    
    const products = await prisma.product.findMany({ 
      where: {
        ...(category && { category }),
        ...(minPrice && { price: { gte: parseFloat(minPrice) } }),
        ...(maxPrice && { price: { lte: parseFloat(maxPrice) } }),
        ...(isActive && { isActive: isActive === 'true' })
      },
      orderBy: {
        createdAt: 'desc'
      }
    })
    
    return NextResponse.json(products) 
  } catch (error) {
    return NextResponse.json( 
      { error: 'Failed to fetch products' },
      { status: 500 }
    )
  }
}
 
// 创建新产品 (POST)
export async function POST(request: Request) {
  try {
    const body = await request.json() 
    
    // 验证输入数据 
    if (!body.name  || !body.category)  {
      return NextResponse.json( 
        { error: 'Name and category are required' },
        { status: 400 }
      )
    }
    
    const product = await prisma.product.create({ 
      data: {
        name: body.name, 
        description: body.description  || null,
        price: parseFloat(body.price)  || 0,
        stock: parseInt(body.stock)  || 0,
        category: body.category, 
        isActive: body.isActive  !== undefined ? body.isActive  : true 
      }
    })
    
    return NextResponse.json(product,  { status: 201 })
  } catch (error) {
    return NextResponse.json( 
      { error: 'Failed to create product' },
      { status: 500 }
    )
  }
}

5. 单个产品的 CRUD 操作 (app/api/products/[id]/route.ts)

import prisma from '@/lib/prisma'
import { NextResponse } from 'next/server'
 
// 获取单个产品 (GET)
export async function GET(
  request: Request,
  { params }: { params: { id: string } }
) {
  try {
    const product = await prisma.product.findUnique({ 
      where: { id: parseInt(params.id)  }
    })
    
    if (!product) {
      return NextResponse.json( 
        { error: 'Product not found' },
        { status: 404 }
      )
    }
    
    return NextResponse.json(product) 
  } catch (error) {
    return NextResponse.json( 
      { error: 'Failed to fetch product' },
      { status: 500 }
    )
  }
}
 
// 更新产品 (PUT)
export async function PUT(
  request: Request,
  { params }: { params: { id: string } }
) {
  try {
    const body = await request.json() 
    
    const updatedProduct = await prisma.product.update({ 
      where: { id: parseInt(params.id)  },
      data: {
        name: body.name, 
        description: body.description, 
        price: parseFloat(body.price), 
        stock: parseInt(body.stock), 
        category: body.category, 
        isActive: body.isActive  
      }
    })
    
    return NextResponse.json(updatedProduct) 
  } catch (error) {
    return NextResponse.json( 
      { error: 'Failed to update product' },
      { status: 500 }
    )
  }
}
 
// 删除产品 (DELETE)
export async function DELETE(
  request: Request,
  { params }: { params: { id: string } }
) {
  try {
    await prisma.product.delete({ 
      where: { id: parseInt(params.id)  }
    })
    
    return NextResponse.json( 
      { message: 'Product deleted successfully' },
      { status: 200 }
    )
  } catch (error) {
    return NextResponse.json( 
      { error: 'Failed to delete product' },
      { status: 500 }
    )
  }
}

6. 前端组件示例 (app/products/page.tsx)

'use client'
 
import { useState, useEffect } from 'react'
import { useRouter } from 'next/navigation'
 
type Product = {
  id: number 
  name: string 
  description: string | null 
  price: number 
  stock: number 
  category: string 
  isActive: boolean 
}
 
export default function ProductsPage() {
  const [products, setProducts] = useState<Product[]>([])
  const [loading, setLoading] = useState(true)
  const [error, setError] = useState<string | null>(null)
  const router = useRouter()
 
  // 获取产品列表 
  const fetchProducts = async () => {
    try {
      const response = await fetch('/api/products')
      if (!response.ok)  throw new Error('Failed to fetch products')
      const data = await response.json() 
      setProducts(data)
    } catch (err) {
      setError(err instanceof Error ? err.message  : 'Unknown error')
    } finally {
      setLoading(false)
    }
  }
 
  // 创建新产品 
  const handleCreate = async () => {
    const newProduct = {
      name: 'New Product',
      description: 'Sample description',
      price: 99.99,
      stock: 100,
      category: 'Electronics',
      isActive: true 
    }
 
    try {
      const response = await fetch('/api/products', {
        method: 'POST',
        headers: {
          'Content-Type': 'application/json',
        },
        body: JSON.stringify(newProduct), 
      })
      
      if (!response.ok)  throw new Error('Failed to create product')
      fetchProducts() // 刷新列表 
    } catch (err) {
      setError(err instanceof Error ? err.message  : 'Failed to create product')
    }
  }
 
  // 更新产品 
  const handleUpdate = async (id: number) => {
    const updatedData = {
      name: 'Updated Product Name',
      price: 199.99,
      stock: 50 
    }
 
    try {
      const response = await fetch(`/api/products/${id}`, {
        method: 'PUT',
        headers: {
          'Content-Type': 'application/json',
        },
        body: JSON.stringify(updatedData), 
      })
      
      if (!response.ok)  throw new Error('Failed to update product')
      fetchProducts() // 刷新列表 
    } catch (err) {
      setError(err instanceof Error ? err.message  : 'Failed to update product')
    }
  }
 
  // 删除产品 
  const handleDelete = async (id: number) => {
    try {
      const response = await fetch(`/api/products/${id}`, {
        method: 'DELETE',
      })
      
      if (!response.ok)  throw new Error('Failed to delete product')
      fetchProducts() // 刷新列表 
    } catch (err) {
      setError(err instanceof Error ? err.message  : 'Failed to delete product')
    }
  }
 
  useEffect(() => {
    fetchProducts()
  }, [])
 
  if (loading) return <div>Loading...</div>
  if (error) return <div>Error: {error}</div>
 
  return (
    <div className="container mx-auto p-4">
      <h1 className="text-2xl font-bold mb-6">Products Management</h1>
      
      <button 
        onClick={handleCreate}
        className="bg-blue-500 text-white px-4 py-2 rounded mb-4"
      >
        Create Sample Product 
      </button>
      
      <div className="grid gap-4">
        {products.map((product)  => (
          <div key={product.id}  className="border p-4 rounded shadow">
            <h2 className="text-xl font-semibold">{product.name}</h2> 
            <p>{product.description}</p> 
            <p>Price: ${product.price.toFixed(2)}</p> 
            <p>Stock: {product.stock}</p> 
            <p>Category: {product.category}</p> 
            <p>Status: {product.isActive  ? 'Active' : 'Inactive'}</p>
            
            <div className="mt-3 space-x-2">
              <button 
                onClick={() => router.push(`/products/${product.id}`)} 
                className="bg-green-500 text-white px-3 py-1 rounded"
              >
                View Details 
              </button>
              <button 
                onClick={() => handleUpdate(product.id)} 
                className="bg-yellow-500 text-white px-3 py-1 rounded"
              >
                Update 
              </button>
              <button 
                onClick={() => handleDelete(product.id)} 
                className="bg-red-500 text-white px-3 py-1 rounded"
              >
                Delete 
              </button>
            </div>
          </div>
        ))}
      </div>
    </div>
  )
}

添加 TypeScript 类型定义 (types/product.ts)

export interface Product {
  id: number 
  name: string 
  description: string | null 
  price: number 
  stock: number 
  category: string 
  isActive: boolean 
  createdAt: Date 
  updatedAt: Date 
}
 
export interface ProductCreateInput {
  name: string 
  description?: string 
  price?: number 
  stock?: number 
  category: string 
  isActive?: boolean 
}
 
export interface ProductUpdateInput {
  name?: string 
  description?: string | null 
  price?: number 
  stock?: number 
  category?: string 
  isActive?: boolean 
}

总结

这个完整的示例展示了：

• 使用 Prisma ORM 定义数据模型和数据库连接
• 实现完整的 CRUD API 端点
• 前端组件与 API 的交互
• 表单处理和状态管理
• 类型安全和错误处理
• 响应式设计和良好的用户体验

测试 API 接口：

• GET /api/products - 获取所有产品
• POST /api/products - 创建新产品
• GET /api/products/:id - 获取单个产品
• PUT /api/products/:id - 更新产品
• DELETE /api/products/:id - 删除产品

完整流程和代码示例 (MongoDb + mongoose)

1. 项目初始化与配置

安装必要依赖

npm install mongoose mongodb

配置数据库链接 (.env.local)

同样放到环境变量中

MONGODB_URI=mongodb://localhost:27017/yourdbname 
# 如果是 Atlas 云数据库 
# MONGODB_URI=mongodb+srv://<username>:<password>@cluster0.example.mongodb.net/yourdbname?retryWrites=true&w=majority 

2. 数据模型定义（models/Product.ts）

import mongoose, { Document, Model, Schema } from 'mongoose'
 
export interface IProduct extends Document {
  name: string 
  price: number
  description: string 
  category: string
  stock: number 
  images?: string[]
  ratings?: number 
  featured?: boolean
  createdAt: Date 
  updatedAt: Date 
}
 
const productSchema: Schema = new mongoose.Schema(
  {
    name: {
      type: String,
      required: [true, 'Please enter product name'],
      trim: true,
      maxLength: [100, 'Product name cannot exceed 100 characters']
    },
    price: {
      type: Number,
      required: [true, 'Please enter product price'],
      maxLength: [5, 'Product price cannot exceed 5 characters'],
      default: 0.0
    },
    description: {
      type: String,
      required: [true, 'Please enter product description']
    },
    category: {
      type: String,
      required: [true, 'Please select category for this product'],
      enum: {
        values: [
          'Electronics',
          'Cameras',
          'Laptops',
          'Accessories',
          'Headphones',
          'Food',
          'Books',
          'Clothes/Shoes',
          'Beauty/Health',
          'Sports',
          'Outdoor',
          'Home'
        ],
        message: 'Please select correct category for product'
      }
    },
    stock: {
      type: Number,
      required: [true, 'Please enter product stock'],
      maxLength: [5, 'Product stock cannot exceed 5 characters'],
      default: 0
    },
    images: [
      {
        public_id: {
          type: String,
          required: true 
        },
        url: {
          type: String,
          required: true 
        }
      }
    ],
    ratings: {
      type: Number,
      default: 0
    },
    featured: {
      type: Boolean,
      default: false
    }
  },
  { timestamps: true }
)
 
// 导出模型
const Product: Model<IProduct> = mongoose.models.Product  || mongoose.model('Product',  productSchema)
 
export default Product

3. 创建数据库连接工具文件 (lib/dbConnect.ts)

import mongoose from 'mongoose'
 
const MONGODB_URI = process.env.MONGODB_URI  
 
if (!MONGODB_URI) {
  throw new Error('Please define the MONGODB_URI environment variable inside .env.local') 
}
 
/**
 * Global is used here to maintain a cached connection across hot reloads 
 * in development. This prevents connections growing exponentially 
 * during API Route usage.
 */
let cached = (global as any).mongoose
 
if (!cached) {
  cached = (global as any).mongoose = { conn: null, promise: null }
}
 
async function dbConnect() {
  if (cached.conn)  {
    return cached.conn  
  }
 
  if (!cached.promise)  {
    const opts = {
      bufferCommands: false,
    }
 
    cached.promise  = mongoose.connect(MONGODB_URI,  opts).then((mongoose) => {
      return mongoose 
    })
  }
  
  try {
    cached.conn  = await cached.promise  
  } catch (e) {
    cached.promise  = null 
    throw e 
  }
 
  return cached.conn  
}
 
export default dbConnect 

4. 完整的 CRUD API 实现 （app/api/products/route.ts）

import { NextResponse } from 'next/server'
import dbConnect from '@/lib/dbConnect'
import Product from '@/models/Product'
 
export async function GET(request: Request) {
  try {
    await dbConnect()
 
    const { searchParams } = new URL(request.url) 
    const page = parseInt(searchParams.get('page')  || '1')
    const limit = parseInt(searchParams.get('limit')  || '10')
    const category = searchParams.get('category') 
    const featured = searchParams.get('featured') 
    const search = searchParams.get('search') 
 
    const query: any = {}
 
    if (category) query.category  = category 
    if (featured) query.featured  = featured === 'true'
    if (search) query.name  = { $regex: search, $options: 'i' }
 
    const products = await Product.find(query) 
      .limit(limit)
      .skip((page - 1) * limit)
      .exec()
 
    const count = await Product.countDocuments(query) 
 
    return NextResponse.json({ 
      success: true,
      data: products,
      pagination: {
        page,
        limit,
        totalPages: Math.ceil(count  / limit),
        totalItems: count
      }
    })
  } catch (error: any) {
    return NextResponse.json( 
      { success: false, error: error.message  },
      { status: 500 }
    )
  }
}
 
export async function POST(request: Request) {
  try {
    await dbConnect()
 
    const body = await request.json() 
 
    // 基本验证
    if (!body.name  || !body.price  || !body.description  || !body.category)  {
      return NextResponse.json( 
        { success: false, error: 'Missing required fields' },
        { status: 400 }
      )
    }
 
    const product = await Product.create(body) 
 
    return NextResponse.json( 
      { success: true, data: product },
      { status: 201 }
    )
  } catch (error: any) {
    return NextResponse.json( 
      { success: false, error: error.message  },
      { status: 400 }
    )
  }
}

5. 单个产品的 CRUD 操作 (app/api/products/[id]/route.ts)

import { NextResponse } from 'next/server'
import dbConnect from '@/lib/dbConnect'
import Product from '@/models/Product'
 
export async function GET(
  request: Request,
  { params }: { params: { id: string } }
) {
  try {
    await dbConnect()
 
    const product = await Product.findById(params.id) 
 
    if (!product) {
      return NextResponse.json( 
        { success: false, error: 'Product not found' },
        { status: 404 }
      )
    }
 
    return NextResponse.json({  success: true, data: product })
  } catch (error: any) {
    return NextResponse.json( 
      { success: false, error: error.message  },
      { status: 400 }
    )
  }
}
 
export async function PUT(
  request: Request,
  { params }: { params: { id: string } }
) {
  try {
    await dbConnect()
 
    const body = await request.json() 
    const product = await Product.findByIdAndUpdate(params.id,  body, {
      new: true,
      runValidators: true
    })
 
    if (!product) {
      return NextResponse.json( 
        { success: false, error: 'Product not found' },
        { status: 404 }
      )
    }
 
    return NextResponse.json({  success: true, data: product })
  } catch (error: any) {
    return NextResponse.json( 
      { success: false, error: error.message  },
      { status: 400 }
    )
  }
}
 
export async function DELETE(
  request: Request,
  { params }: { params: { id: string } }
) {
  try {
    await dbConnect()
 
    const deletedProduct = await Product.findByIdAndDelete(params.id) 
 
    if (!deletedProduct) {
      return NextResponse.json( 
        { success: false, error: 'Product not found' },
        { status: 404 }
      )
    }
 
    return NextResponse.json({  success: true, data: {} })
  } catch (error: any) {
    return NextResponse.json( 
      { success: false, error: error.message  },
      { status: 400 }
    )
  }
}

6. 前端组件示例与上面一致不做重复了

注意事项

1. 环境变量: 确保生产环境配置了正确的 MONGODB_URI
2. 数据库连接: 在 Vercel 等无服务器环境中，注意数据库连接管理
3. CORS: 如果前端和后端分开部署，需要配置 CORS

总结

可以使用以下 curl 命令测试 API：

# 获取产品列表
curl http://localhost:3000/api/products
 
# 创建新产品 
curl -X POST http://localhost:3000/api/products \
  -H "Content-Type: application/json" \
  -d '{"name":"New Product","price":99.99,"description":"Test product","category":"Electronics","stock":10}'
 
# 获取单个产品 
curl http://localhost:3000/api/products/[PRODUCT_ID]
 
# 更新产品 
curl -X PUT http://localhost:3000/api/products/[PRODUCT_ID] \
  -H "Content-Type: application/json" \
  -d '{"price":109.99}'
 
# 删除产品 
curl -X DELETE http://localhost:3000/api/products/[PRODUCT_ID] 

以上就是两种方案的全部实现方式。