PlanetScale 与 Node.js 集成 #

本章将介绍如何在 Node.js 应用中集成 PlanetScale 数据库，包括多种连接方式和完整的 CRUD 示例。

项目准备 #

创建项目 #

bash

# 创建项目目录
mkdir my-planetscale-app
cd my-planetscale-app

# 初始化项目
npm init -y

# 安装依赖
npm install @planetscale/database dotenv
npm install express

环境配置 #

env

DATABASE_URL=mysql://abc123:pscale_pw_xxx@aws.connect.psdb.cloud/my-database?sslaccept=strict
PORT=3000

使用 @planetscale/database #

基本连接 #

javascript

import { connect } from '@planetscale/database';
import 'dotenv/config';

const config = {
  url: process.env.DATABASE_URL
};

const conn = connect(config);

export default conn;

Express 应用示例 #

javascript

import express from 'express';
import { connect } from '@planetscale/database';
import 'dotenv/config';

const app = express();
app.use(express.json());

const conn = connect({ url: process.env.DATABASE_URL });

app.get('/users', async (req, res) => {
  try {
    const result = await conn.execute('SELECT * FROM users ORDER BY created_at DESC');
    res.json(result.rows);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

app.get('/users/:id', async (req, res) => {
  try {
    const result = await conn.execute(
      'SELECT * FROM users WHERE id = ?',
      [req.params.id]
    );
    if (result.rows.length === 0) {
      return res.status(404).json({ error: 'User not found' });
    }
    res.json(result.rows[0]);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

app.post('/users', async (req, res) => {
  try {
    const { name, email } = req.body;
    const result = await conn.execute(
      'INSERT INTO users (name, email) VALUES (?, ?)',
      [name, email]
    );
    res.status(201).json({ 
      id: result.insertId,
      name,
      email 
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

app.put('/users/:id', async (req, res) => {
  try {
    const { name, email } = req.body;
    await conn.execute(
      'UPDATE users SET name = ?, email = ? WHERE id = ?',
      [name, email, req.params.id]
    );
    res.json({ id: req.params.id, name, email });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

app.delete('/users/:id', async (req, res) => {
  try {
    await conn.execute('DELETE FROM users WHERE id = ?', [req.params.id]);
    res.status(204).send();
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

app.listen(process.env.PORT, () => {
  console.log(`Server running on port ${process.env.PORT}`);
});

使用 mysql2 #

安装 #

bash

npm install mysql2

连接池配置 #

javascript

import mysql from 'mysql2/promise';
import 'dotenv/config';

const pool = mysql.createPool({
  uri: process.env.DATABASE_URL,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

export default pool;

CRUD 示例 #

javascript

import pool from './db.js';

export async function getUsers() {
  const [rows] = await pool.execute('SELECT * FROM users ORDER BY created_at DESC');
  return rows;
}

export async function getUserById(id) {
  const [rows] = await pool.execute(
    'SELECT * FROM users WHERE id = ?',
    [id]
  );
  return rows[0];
}

export async function createUser(data) {
  const [result] = await pool.execute(
    'INSERT INTO users (name, email) VALUES (?, ?)',
    [data.name, data.email]
  );
  return { id: result.insertId, ...data };
}

export async function updateUser(id, data) {
  await pool.execute(
    'UPDATE users SET name = ?, email = ? WHERE id = ?',
    [data.name, data.email, id]
  );
  return { id, ...data };
}

export async function deleteUser(id) {
  await pool.execute('DELETE FROM users WHERE id = ?', [id]);
}

Fastify 集成 #

安装 #

bash

npm install fastify @fastify/cors

Fastify 应用 #

javascript

import Fastify from 'fastify';
import cors from '@fastify/cors';
import { connect } from '@planetscale/database';
import 'dotenv/config';

const fastify = Fastify({ logger: true });
await fastify.register(cors);

const conn = connect({ url: process.env.DATABASE_URL });

fastify.get('/users', async (request, reply) => {
  const result = await conn.execute('SELECT * FROM users ORDER BY created_at DESC');
  return result.rows;
});

fastify.get('/users/:id', async (request, reply) => {
  const { id } = request.params;
  const result = await conn.execute(
    'SELECT * FROM users WHERE id = ?',
    [id]
  );
  if (result.rows.length === 0) {
    reply.code(404);
    return { error: 'User not found' };
  }
  return result.rows[0];
});

fastify.post('/users', async (request, reply) => {
  const { name, email } = request.body;
  const result = await conn.execute(
    'INSERT INTO users (name, email) VALUES (?, ?)',
    [name, email]
  );
  reply.code(201);
  return { id: result.insertId, name, email };
});

fastify.put('/users/:id', async (request, reply) => {
  const { id } = request.params;
  const { name, email } = request.body;
  await conn.execute(
    'UPDATE users SET name = ?, email = ? WHERE id = ?',
    [name, email, id]
  );
  return { id, name, email };
});

fastify.delete('/users/:id', async (request, reply) => {
  const { id } = request.params;
  await conn.execute('DELETE FROM users WHERE id = ?', [id]);
  reply.code(204);
});

const start = async () => {
  try {
    await fastify.listen({ port: process.env.PORT || 3000 });
  } catch (err) {
    fastify.log.error(err);
    process.exit(1);
  }
};

start();

事务处理 #

使用 @planetscale/database #

javascript

import { connect } from '@planetscale/database';

const conn = connect({ url: process.env.DATABASE_URL });

async function transferFunds(fromId, toId, amount) {
  const tx = await conn.transaction();
  
  try {
    await tx.execute(
      'UPDATE accounts SET balance = balance - ? WHERE id = ?',
      [amount, fromId]
    );
    
    await tx.execute(
      'UPDATE accounts SET balance = balance + ? WHERE id = ?',
      [amount, toId]
    );
    
    await tx.commit();
    return { success: true };
  } catch (error) {
    await tx.rollback();
    throw error;
  }
}

使用 mysql2 #

javascript

import pool from './db.js';

async function transferFunds(fromId, toId, amount) {
  const connection = await pool.getConnection();
  
  try {
    await connection.beginTransaction();
    
    await connection.execute(
      'UPDATE accounts SET balance = balance - ? WHERE id = ?',
      [amount, fromId]
    );
    
    await connection.execute(
      'UPDATE accounts SET balance = balance + ? WHERE id = ?',
      [amount, toId]
    );
    
    await connection.commit();
    return { success: true };
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }
}

错误处理 #

统一错误处理 #

javascript

class DatabaseError extends Error {
  constructor(message, code) {
    super(message);
    this.name = 'DatabaseError';
    this.code = code;
  }
}

function handleDatabaseError(error) {
  if (error.message.includes('Duplicate entry')) {
    return new DatabaseError('Record already exists', 'DUPLICATE_ENTRY');
  }
  if (error.message.includes('Cannot delete')) {
    return new DatabaseError('Cannot delete record', 'DELETE_FAILED');
  }
  return new DatabaseError('Database operation failed', 'UNKNOWN_ERROR');
}

app.post('/users', async (req, res) => {
  try {
    const { name, email } = req.body;
    const result = await conn.execute(
      'INSERT INTO users (name, email) VALUES (?, ?)',
      [name, email]
    );
    res.status(201).json({ id: result.insertId, name, email });
  } catch (error) {
    const dbError = handleDatabaseError(error);
    res.status(400).json({ error: dbError.message, code: dbError.code });
  }
});

最佳实践 #

连接池管理 #

javascript

import mysql from 'mysql2/promise';

const pool = mysql.createPool({
  uri: process.env.DATABASE_URL,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 0
});

process.on('SIGINT', async () => {
  await pool.end();
  process.exit();
});

export default pool;

查询封装 #

javascript

class UserRepository {
  constructor(connection) {
    this.conn = connection;
  }

  async findAll() {
    const result = await this.conn.execute('SELECT * FROM users');
    return result.rows;
  }

  async findById(id) {
    const result = await this.conn.execute(
      'SELECT * FROM users WHERE id = ?',
      [id]
    );
    return result.rows[0];
  }

  async create(data) {
    const result = await this.conn.execute(
      'INSERT INTO users (name, email) VALUES (?, ?)',
      [data.name, data.email]
    );
    return { id: result.insertId, ...data };
  }

  async update(id, data) {
    await this.conn.execute(
      'UPDATE users SET name = ?, email = ? WHERE id = ?',
      [data.name, data.email, id]
    );
    return { id, ...data };
  }

  async delete(id) {
    await this.conn.execute('DELETE FROM users WHERE id = ?', [id]);
  }
}

export default UserRepository;

下一步 #

现在你已经掌握了 Node.js 集成，接下来学习 Prisma 集成，了解如何使用 ORM 简化数据库操作！