Node.js中使用Sequelize查询PostgreSQL的contas表报错排查

在Express搭建的简单服务中，尝试通过Sequelize查询PostgreSQL中名为contas的表的所有数据。在bash中执行SELECT "user_id", "username", "password" FROM "contas" AS "Conta";能正常返回数据，但调用Conta.findAll()时却抛出SequelizeDatabaseError，提示“relation "contas" does not exist”。

以下是相关代码及配置：

server.js #

import express from 'express';
import sequelize from './db_connect.js';
import { getAllContas } from './models.js';

sequelize.authenticate()
    .then(() => {
        console.log('Connection has been established successfully.');
        return getAllContas()
 })
    .then((allContas => {console.log(allContas)} ))
    .catch(error => {console.log("erro: ", error)});


const app = express();

app.get('/', (req, res) => {
    res.send("Hello world!");
});

app.listen(3000);

db_connect.js #

import {Sequelize} from 'sequelize';

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

export default sequelize;

models.js #

import { Sequelize, DataTypes, Model } from 'sequelize';
import sequelize from './db_connect.js';

class Conta extends Model {}

Conta.init({
  user_id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  username: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: true
  },
  password: {
    type: DataTypes.STRING,
    allowNull: false
  }
}, {
  sequelize,
  modelName: 'Conta',
  tableName: 'contas',
  timestamps: false
});

export async function getAllContas() {
    try {
        const allContas = await Conta.findAll();
        return allContas;
    }
    catch (error) {
        console.log("Erro: ", error);
        throw error;
    }
}

建表SQL #

CREATE TABLE contas (user_id serial PRIMARY KEY, username VARCHAR ( 50 ) UNIQUE NOT NULL, password VARCHAR ( 50 ) NOT NULL);

报错信息 #

Erro:  Error
    at Query.run (C:\Users\mecha\elearning\backEnd\node_modules\sequelize\lib\dialects\postgres\query.js:50:25)      
    at C:\Users\mecha\elearning\backEnd\node_modules\sequelize\lib\sequelize.js:315:28
    at processTicksAndRejections (node:internal/process/task_queues:96:5)
    at async PostgresQueryInterface.select (C:\Users\mecha\elearning\backEnd\node_modules\sequelize\lib\dialects\abstract\query-interface.js:407:12)
    at async Function.findAll (C:\Users\mecha\elearning\backEnd\node_modules\sequelize\lib\model.js:1140:21)
    at async getAllContas (file:///C:/Users/mecha/elearning/backEnd/models.js:33:27) {
  name: 'SequelizeDatabaseError',
  parent: error: relation "contas" does not exist
      at Parser.parseErrorMessage (C:\Users\mecha\elearning\backEnd\node_modules\pg-protocol\dist\parser.js:287:98)  
      at Parser.handlePacket (C:\Users\mecha\elearning\backEnd\node_modules\pg-protocol\dist\parser.js:126:29)       
      at Parser.parse (C:\Users\mecha\elearning\backEnd\node_modules\pg-protocol\dist\parser.js:39:38)
      at Socket.<anonymous> (C:\Users\mecha\elearning\backEnd\node_modules\pg-protocol\dist\index.js:11:42)
      at Socket.emit (node:events:527:28)
      at addChunk (node:internal/streams/readable:315:12)
      at readableAddChunk (node:internal/streams/readable:289:9)
      at Socket.Readable.push (node:internal/streams/readable:228:10)
      at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
    length: 105,
    severity: 'ERROR',
    code: '42P01',
    detail: undefined,
    hint: undefined,
    position: '47',
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: 'parse_relation.c',
    line: '1395',
    routine: 'parserOpenTable',
    sql: 'SELECT "user_id", "username", "password" FROM "contas" AS "Conta";',
    parameters: undefined
  }

这个问题的核心是PostgreSQL的Schema匹配问题——你手动执行SQL时默认指向了表所在的Schema，但Sequelize连接时没有明确指定，导致无法找到目标表。以下是具体解决步骤：

1. 全局指定连接的Schema #

修改db_connect.js中的Sequelize配置，添加schema参数指向表所在的Schema（PostgreSQL默认表都在public Schema下）：

//db_connect.js
import {Sequelize} from 'sequelize';

const sequelize = new Sequelize(
    'devtest',
    'postgres',
    'a',
    {
        host: 'localhost',
        dialect: 'postgres',
        schema: 'public' // 新增此行，明确指定表所在的Schema
    }
);

export default sequelize;

2. 或在模型中单独指定Schema #

如果不想全局配置，也可以在Conta模型的定义中单独添加Schema参数：

//models.js
Conta.init({
  // 字段定义不变
}, {
  sequelize,
  modelName: 'Conta',
  tableName: 'contas',
  timestamps: false,
  schema: 'public' // 新增此行
});

3. 验证数据库连接一致性 #

确认你手动执行SQL时使用的数据库确实是devtest：
在bash中执行以下命令验证：

psql -U postgres
\c devtest
SELECT * FROM contas;

如果能返回数据，说明数据库一致；否则检查Sequelize连接配置中的数据库名称是否正确。

另外注意：PostgreSQL中如果创建表时未用双引号包裹表名，会自动将表名转为小写存储，Sequelize中tableName: 'contas'的配置是正确匹配的，只要加上Schema指定就能解决找不到表的问题。

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