如何在NodeJS中PayPal交易成功后更新MySQL数据库？

我正在使用NodeJS，想了解如何在PayPal交易成功后更新MySQL数据库，不确定应该在app.js还是server.js中执行数据库插入语句？
我启动项目时运行server.js，视图view.ejs与app.js关联，希望交易成功后更新数据库并跳转到成功页面。

server.js 原代码

import "dotenv/config";
import express from "express";
import * as paypal from "./paypal-api.js";
import mysql from "mysql";

const app = express();
var mysqlConnection = mysql.createConnection({
  host: "localhost",
  user: "xxx",
  password: "xxx",
  database: "xxx"
});

mysqlConnection.connect((err)=> {
  if(!err)
  {
      console.log("Connected");
  }
  else
  {
      console.log("Connection Failed");
  }
})

const {PORT = 8888} = process.env;

app.set("view engine", "ejs");
app.use(express.static("public"));

// render checkout page with client id & unique client token
app.get("/", async (req, res) => {
  const clientId = process.env.CLIENT_ID;
  try {
    const clientToken = await paypal.generateClientToken();
    res.render("checkout", { clientId, clientToken });
  } catch (err) {
    res.status(500).send(err.message);
  }
});

// create order
app.post("/api/orders", async (req, res) => {
  try {
    const order = await paypal.createOrder();
    res.json(order);
    console.log(`test1`);
  } catch (err) {
    res.status(500).send(err.message);
  }
});

// capture payment
app.post("/api/orders/:orderID/capture", async (req, res) => {
  const { orderID } = req.params;
  try {
    const captureData = await paypal.capturePayment(orderID);
    res.json(captureData);
    console.log(`test2`);
  } catch (err) {
    res.status(500).send(err.message);
  }
});

app.listen(PORT, () => {
  console.log(`Server listening at http://localhost:${PORT}/`);
});

app.js 原代码

paypal
  .Buttons({
    // Sets up the transaction when a payment button is clicked
    createOrder: function () {
      return fetch("/api/orders", {
        method: "post",
        // use the "body" param to optionally pass additional order information
        // like product skus and quantities
        body: JSON.stringify({
          cart: [
            {
              sku: "<YOUR_PRODUCT_STOCK_KEEPING_UNIT>",
              quantity: "<YOUR_PRODUCT_QUANTITY>",
            },
          ],
        }),
      })
        .then((response) => response.json())
        .then((order) => order.id);
    },
    // Finalize the transaction after payer approval
    onApprove: function (data) {
      return fetch(`/api/orders/${data.orderID}/capture`, {
        method: "post",
      })
        .then((response) => response.json())
        .then((orderData) => {
          // Successful capture! For dev/demo purposes:
          console.log(
            "Capture result",
            orderData,
            JSON.stringify(orderData, null, 2)
          );
          const transaction = orderData.purchase_units[0].payments.captures[0];

          alert(`Transaction ${transaction.status}: ${transaction.id}

            See console for all available details
          `);
          location.replace(`/success.html?id=${transaction.id}`)
          // When ready to go live, remove the alert and show a success message within this page. For example:
          // var element = document.getElementById('paypal-button-container');
          // element.innerHTML = '<h3>Thank you for your payment!</h3>';
          // Or go to another URL:  actions.redirect('thank_you.html');
        });
    },
  })
  .render("#paypal-button-container");

// If this returns false or the card fields aren't visible, see Step #1.
if (paypal.HostedFields.isEligible()) {
  let orderId;

  // Renders card fields
  paypal.HostedFields.render({
    // Call your server to set up the transaction
    createOrder: () => {
      return fetch("/api/orders", {
        method: "post",
        // use the "body" param to optionally pass additional order information
        // like product skus and quantities
        body: JSON.stringify({
          cart: [
            {
              sku: "<YOUR_PRODUCT_STOCK_KEEPING_UNIT>",
              quantity: "<YOUR_PRODUCT_QUANTITY>",
            },
          ],
        }),
      })
        .then((res) => res.json())
        .then((orderData) => {
          orderId = orderData.id; // needed later to complete capture
          return orderData.id;
        });
    },
    styles: {
      ".valid": {
        color: "green",
      },
      ".invalid": {
        color: "red",
      },
    },
    fields: {
      number: {
        selector: "#card-number",
        placeholder: "4111 1111 1111 1111",
      },
      cvv: {
        selector: "#cvv",
        placeholder: "123",
      },
      expirationDate: {
        selector: "#expiration-date",
        placeholder: "MM/YY",
      },
    },
  }).then((cardFields) => {
    document.querySelector("#card-form").addEventListener("submit", (event) => {
      event.preventDefault();
      cardFields
        .submit({
          // Cardholder's first and last name
          cardholderName: document.getElementById("card-holder-name").value,
          // Billing Address
          billingAddress: {
            // Street address, line 1
            streetAddress: document.getElementById(
              "card-billing-address-street"
            ).value,
            // Street address, line 2 (Ex: Unit, Apartment, etc.)
            extendedAddress: document.getElementById(
              "card-billing-address-unit"
            ).value,
            // State
            region: document.getElementById("card-billing-address-state").value,
            // City
            locality: document.getElementById("card-billing-address-city")
              .value,
            // Postal Code
            postalCode: document.getElementById("card-billing-address-zip")
              .value,
            // Country Code
            countryCodeAlpha2: document.getElementById(
              "card-billing-address-country"
            ).value,
          },
        })
        .then(() => {
          fetch(`/api/orders/${orderId}/capture`, {
            method: "post",
          })
            .then((res) => res.json())
            .then((orderData) => {
              // Two cases to handle:
              //   (1) Other non-recoverable errors -> Show a failure message
              //   (2) Successful transaction -> Show confirmation or thank you
              // This example reads a v2/checkout/orders capture response, propagated from the server
              // You could use a different API or structure for your 'orderData'
              const errorDetail =
                Array.isArray(orderData.details) && orderData.details[0];
              if (errorDetail) {
                var msg = "Sorry, your transaction could not be processed.";
                if (errorDetail.description)
                  msg += "\n\n" + errorDetail.description;
                if (orderData.debug_id) msg += " (" + orderData.debug_id + ")";
                return alert(msg); // Show a failure message
              }
              // Show a success message or redirect
              alert("Transaction completed!");
            });
        })
        .catch((err) => {
          alert("Payment could not be captured! " + JSON.stringify(err));
        });
    });
  });
} else {
  // Hides card fields if the merchant isn't eligible
  document.querySelector("#card-form").style = "display: none";
}

核心结论：必须在server.js中执行数据库更新操作 #

app.js是运行在浏览器端的前端代码，绝对不能在这里处理数据库操作——会直接暴露数据库的账号密码，导致严重的安全风险；而且前端代码可以被用户篡改，无法保证数据的真实性和完整性。

正确的做法是在服务器端的支付捕获接口中完成数据库更新，也就是server.js里的/api/orders/:orderID/capture路由，因为这个接口是PayPal支付成功后，服务器端确认交易有效性的关键节点，这里的数据是可信的。

具体修改步骤 #

1. 在server.js的支付捕获接口中添加数据库插入逻辑
   把PayPal返回的交易信息（比如交易ID、状态、金额等）插入到MySQL数据库中，注意要处理异步操作，确保数据库操作完成后再给前端返回响应。

修改后的/api/orders/:orderID/capture路由代码如下：

// capture payment
app.post("/api/orders/:orderID/capture", async (req, res) => {
  const { orderID } = req.params;
  try {
    const captureData = await paypal.capturePayment(orderID);
    // 获取关键交易信息
    const transaction = captureData.purchase_units[0].payments.captures[0];
    const transactionId = transaction.id;
    const status = transaction.status;
    const amount = transaction.amount.value;
    const currency = transaction.amount.currency_code;

    // 插入数据库的SQL语句，根据你的表结构调整字段
    const insertQuery = `INSERT INTO transactions (order_id, transaction_id, status, amount, currency) VALUES (?, ?, ?, ?, ?)`;
    
    // 执行数据库插入，转为Promise处理异步
    await new Promise((resolve, reject) => {
      mysqlConnection.query(insertQuery, [orderID, transactionId, status, amount, currency], (err, results) => {
        if (err) {
          console.error("数据库插入失败:", err);
          reject(err);
        } else {
          console.log("交易记录已保存到数据库");
          resolve(results);
        }
      });
    });

    // 返回数据给前端，前端再跳转成功页面
    res.json(captureData);
    console.log(`test2`);
  } catch (err) {
    res.status(500).send(err.message);
  }
});

2. 确保前端跳转逻辑正常
   当前app.js中的location.replace(/success.html?id=${transaction.id})逻辑可以保留，因为服务器端已经完成了数据库更新，前端收到响应后再跳转是安全的。

额外注意事项 #

• 确保你的MySQL表结构已经创建，比如上面示例中的transactions表，需要包含对应的字段（order_id、transaction_id、status、amount、currency等）。
• 建议使用连接池替代单一连接，避免高并发下的连接问题：可以用mysql.createPool()代替mysql.createConnection()。
• 要处理数据库操作失败的情况，比如如果插入失败，应该返回错误给前端，避免用户以为支付成功但实际数据库没有记录。

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