如何使用Node.js向PostgreSQL数据库插入数据？附现有代码

Let's go through fixing and improving your PostgreSQL insertion logic step by step. I'll cover correcting your existing code, adding error handling, and including best practices for security and reliability.

1. Fix the Database Connection Setup #

Your current connection code closes the client immediately after the initial SELECT query, which will cause errors when trying to use it in your POST route. Instead, create a reusable connection (or connection pool) that stays open for your routes.

Create a Reusable DB Client (db.js) #

const { Client } = require('pg');

// Initialize the client
const client = new Client({
  user: 'ozan',
  host: 'localhost',
  database: 'blogpage',
  password: 'test',
  port: 5432,
});

// Connect to the database and handle connection errors
client.connect()
  .then(() => console.log('Successfully connected to PostgreSQL'))
  .catch(err => console.error('Connection failed:', err.stack));

module.exports = client;

2. Complete the POST Signup Route #

Your existing INSERT query is incomplete—let's fix it with parameterized queries (to prevent SQL injection), add input validation, and proper error handling.

Option 1: Callback-Based Implementation #

const express = require('express');
const router = express.Router();
const client = require('./db'); // Import our reusable client

router.post('/signup', (req, res) => {
  // Extract data from request body
  const { usernamebody, emailbody, passwordbody } = req.body;

  // Validate required fields first
  if (!usernamebody || !emailbody || !passwordbody) {
    return res.status(400).json({ error: 'All fields (username, email, password) are required' });
  }

  // Parameterized INSERT query (prevents SQL injection)
  const insertQuery = `
    INSERT INTO persons(username, password, email)
    VALUES ($1, $2, $3)
    RETURNING *; // Optional: returns the inserted user data
  `;

  // Values corresponding to $1, $2, $3 in the query
  const values = [usernamebody, passwordbody, emailbody];

  // Execute the query
  client.query(insertQuery, values, (err, result) => {
    if (err) {
      console.error('Insert error:', err.stack);
      return res.status(500).json({ error: 'Failed to create user account' });
    }

    // Send success response with the created user
    res.status(201).json({
      message: 'User created successfully',
      user: result.rows[0]
    });
  });
});

module.exports = router;

Option 2: Async/Await Implementation (Cleaner Code) #

Async/await makes asynchronous code easier to read and maintain. Here's the same route using async/await:

const express = require('express');
const router = express.Router();
const client = require('./db');

router.post('/signup', async (req, res) => {
  try {
    const { usernamebody, emailbody, passwordbody } = req.body;

    // Validate input
    if (!usernamebody || !emailbody || !passwordbody) {
      return res.status(400).json({ error: 'All fields are required' });
    }

    const insertQuery = `
      INSERT INTO persons(username, password, email)
      VALUES ($1, $2, $3)
      RETURNING *;
    `;

    const values = [usernamebody, passwordbody, emailbody];
    const result = await client.query(insertQuery, values);

    res.status(201).json({
      message: 'User created successfully',
      user: result.rows[0]
    });
  } catch (err) {
    console.error('Insert error:', err.stack);
    res.status(500).json({ error: 'Failed to create user account' });
  }
});

module.exports = router;

3. Critical Security & Reliability Improvements #

a. Never Store Plain Text Passwords #

Storing plain text passwords is a massive security risk. Use a library like bcrypt to hash passwords before inserting them into the database:

npm install bcrypt

Update your route to hash the password:

const bcrypt = require('bcrypt');

// Inside the signup route (async/await example)
const saltRounds = 10;
const hashedPassword = await bcrypt.hash(passwordbody, saltRounds);

// Use hashedPassword in the values array instead of passwordbody
const values = [usernamebody, hashedPassword, emailbody];

b. Use Connection Pools for Production #

For production applications, use a connection pool instead of a single client to handle multiple concurrent requests efficiently:

// db.js (using Pool instead of Client)
const { Pool } = require('pg');

const pool = new Pool({
  user: 'ozan',
  host: 'localhost',
  database: 'blogpage',
  password: 'test',
  port: 5432,
  max: 10, // Maximum number of connections in the pool
  idleTimeoutMillis: 30000, // Close idle connections after 30 seconds
});

module.exports = pool;

Then in your route, use pool.query() instead of client.query().

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