Node.js中Sequelize连接PostgreSQL遭拒问题求助

问题背景 #

开发基于Express和Sequelize的Node.js后端，连接PostgreSQL时触发SequelizeConnectionRefusedError，已检查PostgreSQL运行状态、默认端口5432、psql登录正常、尝试替换localhost为127.0.0.1、检查pg_hba.conf，但问题仍存在。

1. index.js 文件 #

import express from 'express';
import pool from './db/db.js';
import cors from 'cors';
import authRoutes from './routes/authRoutes.js';
import userRouter from './routes/userRoutes.js';
import productRoutes from './routes/productRoutes.js';
import orderRoutes from './routes/orderRoutes.js';
import cartRoutes from './routes/cartRoutes.js';

const app = express();
const PORT = 3000;

app.use(express.json());
app.use(
    cors({
      origin: 'http://localhost:8081',
      credentials: true,
    })
);

// Testing DB Connection;
app.get('/api/test-db', async (req, res) => {
    try {
        const result = await pool.query("SELECT NOW()");
        res.json({ message: "Database connected!", time: result.rows[0].now });
    } catch (error) {
        console.error('Error connecting to the database:', error.stack);
        res.status(500).json({ message: "Database connection error!" });
    }
});

app.use('/api/auth', authRoutes);
app.use('/api/user', userRouter);
app.use('/produce', productRoutes);
app.use('/orders', orderRoutes);
app.use('/cart', cartRoutes);

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

2. 错误日志 #

Server is running on http://localhost:3000
Unable to connect to the database: ConnectionRefusedError [SequelizeConnectionRefusedError]
    at Client._connectionCallback (D:\Farm-Bridge-App\Farm-Bridge-App\Backend\node_modules\sequelize\lib\dialects\postgres\connection-manager.js:133:24)
    at Client._handleErrorWhileConnecting (D:\Farm-Bridge-App\Farm-Bridge-App\Backend\node_modules\pg\lib\client.js:326:19)
    ...
  parent: AggregateError [ECONNREFUSED]:
      at internalConnectMultiple (node:net:1118:18)
      at afterConnectMultiple (node:net:1685:7) {
    code: 'ECONNREFUSED',
  },
  original: AggregateError [ECONNREFUSED]:
      at internalConnectMultiple (node:net:1118:18)
      at afterConnectMultiple (node:net:1685:7) {
    code: 'ECONNREFUSED',
  }
}

3. db.js 数据库连接文件 #

import { Sequelize } from 'sequelize';

const sequelize = new Sequelize('farmbridge_app', 'postgres', 'myPostgres', {
  host: 'localhost',
  dialect: 'postgres',
});

// Function to test the database connection
const testConnection = async () => {
  try {
    await sequelize.authenticate();
    console.log('Connection has been established successfully.');

    // Synchronize the models with the database
    await sequelize.sync({ alter: true }); // alter: true matches schema to the models
    console.log('All models were synchronized successfully.');
  } catch (error) {
    console.error('Unable to connect to the database:', error);
  }
};

// Run the connection test
testConnection();

export default sequelize;

排查与解决方案 #

1. 确认PostgreSQL监听端口 #

打开PostgreSQL配置文件postgresql.conf（通常在data目录下），检查port参数是否为5432。如果是自定义端口，需在Sequelize配置中添加port字段：

const sequelize = new Sequelize('farmbridge_app', 'postgres', 'myPostgres', {
  host: 'localhost',
  dialect: 'postgres',
  port: 自定义端口号 // 例如5433
});

2. 允许本地TCP连接 #

检查postgresql.conf中的listen_addresses参数，确保设置为'*'或'localhost,127.0.0.1'，允许本地TCP连接，修改后重启PostgreSQL服务。

3. 验证pg_hba.conf权限规则 #

确保pg_hba.conf中存在允许本地用户TCP连接的规则：

host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256

修改后重启PostgreSQL生效。

4. 排查防火墙拦截 #

本地防火墙或第三方安全软件可能拦截5432端口，临时关闭防火墙测试，或在防火墙上添加允许PostgreSQL端口的入站规则。

5. 确认目标数据库存在 #

使用psql登录后执行\l查看数据库列表，若farmbridge_app不存在则创建：

CREATE DATABASE farmbridge_app;

6. 修正index.js中的连接对象误用 #

index.js中导入的pool与db.js导出的sequelize实例不匹配，Sequelize实例无query方法（这是pg原生pool的方法），需修改：

// 修正导入语句
import sequelize from './db/db.js';

// 修正测试接口
app.get('/api/test-db', async (req, res) => {
    try {
        const result = await sequelize.query("SELECT NOW()");
        res.json({ message: "Database connected!", time: result[0][0].now });
    } catch (error) {
        console.error('Error connecting to the database:', error.stack);
        res.status(500).json({ message: "Database connection error!" });
    }
});

7. 检查依赖兼容性 #

部分版本的Sequelize与pg库存在兼容性问题，尝试更新依赖：

npm update sequelize pg

内容的提问来源于stack exchange，提问作者Sourabh Singh Bais