Next.js 14从数据库取数生成随机结果及连接报错求助

问题说明 #

需要从data.ts获取商家数据，实现点击按钮生成随机商家结果的功能，但尝试用useState和useEffect时出现数据库连接错误，且未修改过数据库凭据。

报错原因 #

在Next.js的Server Component中直接使用客户端钩子（useState/useEffect）调用数据库函数，会导致数据库请求在客户端环境执行。而Vercel Postgres的凭据仅在服务端环境可用，客户端没有权限访问数据库，因此触发连接错误。

解决方案 #

提供两种可行方案，可根据数据量和需求选择：

方案一：服务端获取随机数据（API路由方式） #

适合数据量较大的场景，避免一次性加载所有数据。

1. 修改data.ts，新增随机获取函数

import { sql } from '@vercel/postgres';
import { Businesses } from './definitions';

export async function getBusinesses() {
    try {
        console.log("Fetching Businesses...");
        const data = await sql<Businesses>`SELECT * FROM businesses`;
        return data.rows;
    } catch (error) {
        console.error('Database Error:', error);
        throw new Error('Failed to fetch businesses data.');
    }
}

// 新增：从数据库直接获取单条随机数据
export async function getRandomBusiness() {
    try {
        console.log("Fetching random business...");
        // PostgreSQL语法：ORDER BY RANDOM() 随机排序，LIMIT 1取第一条
        const data = await sql<Businesses>`SELECT * FROM businesses ORDER BY RANDOM() LIMIT 1`;
        return data.rows[0];
    } catch (error) {
        console.error('Database Error:', error);
        throw new Error('Failed to fetch random business data.');
    }
}

2. 创建API路由app/api/random-business/route.ts

import { getRandomBusiness } from '@/lib/data';
import { NextResponse } from 'next/server';

export async function GET() {
    try {
        const business = await getRandomBusiness();
        return NextResponse.json(business);
    } catch (error) {
        return NextResponse.json({ error: 'Failed to fetch random business' }, { status: 500 });
    }
}

3. 创建客户端组件components/RandomBusinessButton.tsx

'use client';

import { useState } from 'react';
import { Businesses } from '../lib/definitions';

export default function RandomBusinessButton() {
    const [randomBusiness, setRandomBusiness] = useState<Businesses | null>(null);
    const [loading, setLoading] = useState(false);

    const fetchRandomBusiness = async () => {
        setLoading(true);
        try {
            const res = await fetch('/api/random-business');
            if (!res.ok) throw new Error('Failed to fetch data');
            const data = await res.json();
            setRandomBusiness(data);
        } catch (error) {
            console.error(error);
        } finally {
            setLoading(false);
        }
    };

    return (
        <div style={{ marginTop: '1.5rem' }}>
            <button onClick={fetchRandomBusiness} disabled={loading}>
                {loading ? '加载中...' : '获取随机商家'}
            </button>
            {randomBusiness && (
                <div style={{ marginTop: '1rem', padding: '1rem', border: '1px solid #eee' }}>
                    <h4>随机商家：</h4>
                    <p><strong>名称：</strong>{randomBusiness.name}</p>
                    <p><strong>地址：</strong>{randomBusiness.address}，{randomBusiness.city}</p>
                </div>
            )}
        </div>
    );
}

4. 更新dashboard/page.tsx引入组件

import { getBusinesses } from "../lib/data";
import RandomBusinessButton from "../components/RandomBusinessButton";

export default async function Dashboard() {
    const businesses = await getBusinesses();
    
    return (
        <div>            
            <h1>Dashboard Page</h1>
            <br></br>

            <h3>商家列表</h3>
            <ul>
                {businesses.map((business) => (
                    <li key={business.id}>{business.name}</li>
                ))}
            </ul>
            
            <RandomBusinessButton />
        </div>
    );
}

方案二：客户端随机选择（预加载所有数据） #

适合数据量较小的场景，无需额外API路由。

1. 创建客户端组件components/RandomBusinessPicker.tsx

'use client';

import { useState } from 'react';
import { Businesses } from '../lib/definitions';

type Props = {
    businesses: Businesses[];
};

export default function RandomBusinessPicker({ businesses }: Props) {
    const [selectedBusiness, setSelectedBusiness] = useState<Businesses | null>(null);

    const pickRandom = () => {
        if (businesses.length === 0) return;
        const randomIndex = Math.floor(Math.random() * businesses.length);
        setSelectedBusiness(businesses[randomIndex]);
    };

    return (
        <div style={{ marginTop: '1.5rem' }}>
            <button onClick={pickRandom}>随机选择商家</button>
            {selectedBusiness && (
                <div style={{ marginTop: '1rem', padding: '1rem', border: '1px solid #eee' }}>
                    <h4>选中的随机商家：</h4>
                    <p><strong>名称：</strong>{selectedBusiness.name}</p>
                    <p><strong>地址：</strong>{selectedBusiness.address}，{selectedBusiness.city}</p>
                </div>
            )}
        </div>
    );
}

2. 更新dashboard/page.tsx传递数据给组件

import { getBusinesses } from "../lib/data";
import RandomBusinessPicker from "../components/RandomBusinessPicker";

export default async function Dashboard() {
    const businesses = await getBusinesses();
    
    return (
        <div>            
            <h1>Dashboard Page</h1>
            <br></br>

            <h3>商家列表</h3>
            <ul>
                {businesses.map((business) => (
                    <li key={business.id}>{business.name}</li>
                ))}
            </ul>
            
            <RandomBusinessPicker businesses={businesses} />
        </div>
    );
}

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