nextjs与数据库创建连接

以登录注册功能为例：

数据库连接配置

(src/lib/db.ts)

import mysql from 'mysql2/promise';const pool = mysql.createPool({host: 'localhost',port: 3306,user: 'heart',password: '020128',database: 'heart',waitForConnections: true,connectionLimit: 10,queueLimit: 0
});export default pool;

这里使用了 mysql2 的连接池来管理数据库连接，好处是：

可以重用数据库连接
自动管理连接数量
提高性能和可靠性

数据库表初始化

(src/app/api/init-db/route.ts):

export async function GET() {const connection = await pool.getConnection();try {// 开启事务await connection.beginTransaction();// 先删除有外键约束的表console.log('开始删除表...');const tablesToDrop = ['comment_like','post_like','comment','community_post','favorite','course_appointment','consultation','test_record','user',// ... 其他表];// 删除旧表for (const table of tablesToDrop) {await connection.execute(`DROP TABLE IF EXISTS ${table}`);}// 创建用户表await connection.execute(`CREATE TABLE user (id INT AUTO_INCREMENT PRIMARY KEY,username VARCHAR(255) NOT NULL UNIQUE,password VARCHAR(64) NOT NULL,email VARCHAR(255) NOT NULL UNIQUE,phone VARCHAR(20) UNIQUE,avatar VARCHAR(255) DEFAULT '/avatars/user.jpg',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)`);// ... 创建其他表 ...// 提交事务await connection.commit();return NextResponse.json({ status: 'success',message: '数据库表创建成功'});} catch (error) {// 如果出错，回滚事务await connection.rollback();return NextResponse.json({ status: 'error',message: '数据库表创建失败',error: error instanceof Error ? error.message : String(error)}, { status: 500 });} finally {// 释放连接connection.release();}
}

这个 API 路由用于初始化数据库，访问 /api/init-db 时会执行。使用了事务来确保所有表的创建要么全部成功，要么全部失败。

登录注册功能实现

(src/app/api/auth/route.ts):

export async function POST(request: Request) {try {const body = await request.json();const { action, username, password, email } = body;if (action === 'login') {// 登录逻辑const hashedPassword = hashPassword(password);const [rows] = await pool.execute<RowDataPacket[]>('SELECT * FROM user WHERE username = ? AND password = ?',[username, hashedPassword]);if (rows.length === 0) {return NextResponse.json({ error: '用户名或密码错误' },{ status: 401 });}const user = rows[0];return NextResponse.json({message: '登录成功',user: {id: user.id,username: user.username,email: user.email}});} else if (action === 'register') {// 注册逻辑// 检查用户名是否已存在const [existingUsers] = await pool.execute<RowDataPacket[]>('SELECT * FROM user WHERE username = ?',[username]);if (existingUsers.length > 0) {return NextResponse.json({ error: '用户名已存在' },{ status: 400 });}// 检查邮箱是否已存在const [existingEmails] = await pool.execute<RowDataPacket[]>('SELECT * FROM user WHERE email = ?',[email]);if (existingEmails.length > 0) {return NextResponse.json({ error: '邮箱已被注册' },{ status: 400 });}// 创建新用户const hashedPassword = hashPassword(password);const [result] = await pool.execute<RowDataPacket[]>('INSERT INTO user (username, password, email) VALUES (?, ?, ?)',[username, hashedPassword, email]);return NextResponse.json({message: '注册成功',user: {id: (result as any).insertId,username,email}});}} catch (error) {return NextResponse.json({ error: '服务器错误' },{ status: 500 });}
}