Node+Express中pg模块Client与Pool的断开/结束时机咨询

Hey there, let's break down your PostgreSQL connection issues with Node.js/pg step by step—you're closer to getting this right than you think!

1. Fixing Your Connection Pool Code #

First, let's address the critical issue in your pool usage:

app.get('/', (req, res)=>{
  pool.connect()
    .then(client => {
      return client.query('select ....')
        .then(resolved => {
          client.release();
          console.log(resolved.rows);
        })
        .catch(e => {
          client.release();
          console.log('error', e);
        })
      pool.end(); // ❌ This is breaking your pool!
    })
});

Calling pool.end() right after acquiring a client shuts down the entire connection pool immediately. Once this runs, the pool can't accept any new connection requests, and all existing connections are closed—this is why your route would fail after the first request.

Correct Pool Practices: #

• Only call pool.end() when your application is shutting down (e.g., when catching a SIGINT signal for app termination).
• You're already doing the right thing by calling client.release() in both success and error paths—this returns the client to the pool for reuse, preventing connection leaks.

Here's the fixed route code:

app.get('/', (req, res)=>{
  pool.connect()
    .then(client => {
      return client.query('select ....')
        .then(resolved => {
          client.release();
          console.log(resolved.rows);
          res.send(resolved.rows); // Don't forget to send a response to the user!
        })
        .catch(e => {
          client.release();
          console.log('error', e);
          res.status(500).send('Database query failed');
        })
    })
    .catch(poolError => {
      console.log('Failed to get client from pool', poolError);
      res.status(500).send('Pool connection error');
    });
});

// Example: Shut down the pool gracefully when the app exits
process.on('SIGINT', async () => {
  console.log('Shutting down connection pool...');
  await pool.end();
  process.exit(0);
});

2. Fixing the Single Client Issue #

Your CMS route problem stems from a misunderstanding of how a single pg.Client works:

• A single Client represents one persistent connection to the database. When you call client.end(), that connection is closed permanently—you can't reuse that same Client instance for future queries.

In your code, you're calling client.end() inside both getUser and signup. The first time either function runs, the Client is closed, so all subsequent client.query() calls fail because there's no active connection left. That's why removing client.end() makes it work again (the connection stays open for reuse).

Correct Single Client Practices: #

Option 1: Reuse the Client for long-lived operations (best for your CMS use case)

• Connect once when your app starts, and only call client.end() when the app shuts down. Remove all client.end() calls from your query functions.

Fixed code example:

const client = new pg.Client({ 
  user: 'clientuser',
  host: 'localhost',
  database: 'mydb',
  password: 'clientuser',
  port: 5432
});

// Connect once at app startup
client.connect()
  .then(() => console.log('CMS client connected successfully'))
  .catch(err => console.error('CMS client connection error', err));

const signup = (user) => {
  return new Promise((resolved, rejected)=>{
    getUser(user.email)
      .then(getUserRes => {
        if (!getUserRes) {
          return resolved(false);
        }
        client.query('insert into user(username, password) values ($1,$2)',[user.username,user.password])
          .then(() => resolved(true))
          .catch(() => rejected('username already used'));
      })
      .catch(() => rejected('error'));
  });
};

const getUser = (username) => {
  return new Promise((resolved, rejected)=>{
    client.query('select username from user WHERE username= $1',[username])
      .then(res => resolved(res.rows.length === 0))
      .catch(e => {
        console.error('getUser error ', e);
        rejected(e);
      });
  });
};

// Close the client gracefully on app exit
process.on('SIGINT', async () => {
  console.log('Closing CMS client connection...');
  await client.end();
  process.exit(0);
});

Option 2: Create a new Client for each operation (if you don't need a persistent connection)
If you prefer not to keep a long-lived connection, create a new Client instance for each query, connect, run your logic, then close the connection:

const getUser = (username) => {
  return new Promise((resolved, rejected)=>{
    const tempClient = new pg.Client({ /* your config */ });
    tempClient.connect()
      .then(() => tempClient.query('select username from user WHERE username= $1',[username]))
      .then(res => {
        resolved(res.rows.length === 0);
        return tempClient.end();
      })
      .catch(e => {
        console.error('error ', e);
        tempClient.end().finally(() => rejected(e));
      });
  });
};

Key Takeaways #

• pg.Pool:
  • Acquire clients with pool.connect(), release them with client.release() after use.
  • Call pool.end() only when your application is shutting down.
  • The pool handles connection reuse automatically—you don't need to manage individual connection lifecycles beyond releasing clients.
• pg.Client:
  • A single Client is one persistent connection—don't call client.end() until you're done with that connection forever.
  • For most cases, using a pool is safer and more efficient than managing single Clients manually.

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