NodeJS 使用PostgreSQL：列不存在

qacovj5a 于 2023-06-29 发布在 Node.js

关注(0)|答案(1)|浏览(167)

我试图用nodejs，express，pogreSQL数据库使用sequelise做一个小的web服务。在psql中使用此创建数据库

CREATE TABLE Contacts (
    id SERIAL PRIMARY KEY,
    phoneNumber bigint,
    email VARCHAR(255),
    linkedId INTEGER,
    linkPrecedence VARCHAR(20),
    createdAt TIMESTAMPTZ DEFAULT NOW(),
    updatedAt TIMESTAMPTZ DEFAULT NOW(),
    deletedAt TIMESTAMPTZ, 
    FOREIGN KEY (linkedId) REFERENCES Contacts (id)
 );

将contacts.js中的联系人模型定义为

const { DataTypes } = require("sequelize");
const sequelize = require("./database");

// Define the Contact model
const contacts = sequelize.define(
  "contacts",
  {
    id: {
      type: DataTypes.INTEGER,
      autoIncrement: true,
      allowNull: false,
      primaryKey: true,
    },
    phoneNumber: {
      type: DataTypes.BIGINT,
      allowNull: true,
    },
    email: {
      type: DataTypes.STRING,
      allowNull: true,
    },
    linkedId: {
      type: DataTypes.INTEGER,
      allowNull: true,
    },
    linkPrecedence: {
      type: DataTypes.ENUM("primary", "secondary"),
      allowNull: false,
    },
    createdAt: {
      type: DataTypes.DATE,
      allowNull: false,
    },
    updatedAt: {
      type: DataTypes.DATE,
      allowNull: false,
    },
    deletedAt: {
      type: DataTypes.DATE,
      allowNull: true,
    },
  },
  {
    modelName: "contact",
    tableName: "contacts", 
    timestamps: true,
    freezeTableName: true, // Prevent Sequelize from pluralizing the table name
  }
);

module.exports = contacts;

我的webservice收到一个post请求，它的格式如下

email: example@example.com (string)
phoneNumber: 9999999999 (numbers / int)

在identifyContact.js文件中，我试图找到包含此电子邮件或此电话号码的行，然后处理它并发送一些东西回来。它在find.one（）方法行给出错误。此文件的内容如下：

const express = require("express");
const contacts = require("./contacts");
const { Op } = require("sequelize");

const router = express.Router();

// Identify endpoint
router.post("/", async (req, res) => {
  try {
    const { email: email_, phoneNumber: phoneNumber_ } = req.body;
    
    // Find the primary contact based on email or phoneNumber
    // getting error at this next line
    const primaryContact = await contacts.findOne({
      where: {
        [Op.or]: [{ email: email_ }, { phoneNumber: phoneNumber_ }],
        linkPrecedence: "primary",
      },
    });

    // If primary contact exists, find secondary contacts linked to it
    if (primaryContact) {
      const secondaryContacts = await contacts.findAll({
        where: {
          linkedId: primaryContact.id,
        },
      });

      // Consolidate the contact information      

      // Send the response
      res.status(200).json({ newContact });
    }
  } catch (error) {
    console.error("Error identifying contact:", error);
    res.status(500).json({ error: "Internal server error" });
  }
});

module.exports = router;

config.js看起来像这样

module.exports = {
  database: "fluxkart",
  username: "dev1",
  password: "password",
  host: "localhost",
};

database.js

const { Sequelize } = require('sequelize');
const config = require('./config');

// Create a Sequelize instance and connect to the database
const sequelize = new Sequelize(config.database, config.username, config.password, {
  host: config.host,
  dialect: 'postgres',
});

// Test the database connection
sequelize
  .authenticate()
  .then(() => {
    console.log('Database connection has been established successfully.');
  })
  .catch((error) => {
    console.error('Unable to connect to the database:', error);
  });

module.exports = sequelize;

app.js

const express = require("express");
const bodyParser = require("body-parser");
const identifyCustomer = require("./identifyCustomer");

const app = express();

// Middleware
app.use(bodyParser.json());

// Routes
app.use("/identify", identifyCustomer);

// Server
const port = 3000;
app.listen(port, () => {
  console.log(`Server is running on port ${port}`);
});

当我使用axios发送post请求时，我收到一个错误，说列“phoneNumber不存在”
这是发送请求后的控制台

Executing (default): SELECT 1+1 AS result
Database connection has been established successfully.
example@example.com  ---  1234567890
Executing (default): SELECT "id", "phoneNumber", "email", "linkedId", "linkPrecedence", "createdAt", "updatedAt", "deletedAt" FROM "contacts" AS "contacts" WHERE ("contacts"."email" = 'example@example.com' OR "contacts"."phoneNumber" = 1234567890) LIMIT 1;
Error identifying contact: Error
    at Query.run (/home/amrit/ByteSpeed1/node_modules/sequelize/lib/dialects/postgres/query.js:50:25)
    at /home/amrit/ByteSpeed1/node_modules/sequelize/lib/sequelize.js:315:28
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async PostgresQueryInterface.select (/home/amrit/ByteSpeed1/node_modules/sequelize/lib/dialects/abstract/query-interface.js:407:12)
    at async contacts.findAll (/home/amrit/ByteSpeed1/node_modules/sequelize/lib/model.js:1140:21)
    at async contacts.findOne (/home/amrit/ByteSpeed1/node_modules/sequelize/lib/model.js:1240:12)
    at async /home/amrit/ByteSpeed1/identifyCustomer.js:15:28 {
  name: 'SequelizeDatabaseError',
  parent: error: column "phoneNumber" does not exist
      at Parser.parseErrorMessage (/home/amrit/ByteSpeed1/node_modules/pg-protocol/dist/parser.js:287:98)
      at Parser.handlePacket (/home/amrit/ByteSpeed1/node_modules/pg-protocol/dist/parser.js:126:29)
      at Parser.parse (/home/amrit/ByteSpeed1/node_modules/pg-protocol/dist/parser.js:39:38)
      at Socket.<anonymous> (/home/amrit/ByteSpeed1/node_modules/pg-protocol/dist/index.js:11:42)
      at Socket.emit (node:events:513:28)
      at addChunk (node:internal/streams/readable:324:12)
      at readableAddChunk (node:internal/streams/readable:297:9)
      at Readable.push (node:internal/streams/readable:234:10)
      at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
    length: 178,
    severity: 'ERROR',
    code: '42703',
    detail: undefined,
    hint: 'Perhaps you meant to reference the column "contacts.phonenumber".',
    position: '14',
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: 'parse_relation.c',
    line: '3398',
    routine: 'errorMissingColumn',
    sql: `SELECT "id", "phoneNumber", "email", "linkedId", "linkPrecedence", "createdAt", "updatedAt", "deletedAt" FROM "contacts" AS "contacts" WHERE ("contacts"."email" = 'example@example.com' OR "contacts"."phoneNumber" = 1234567890) LIMIT 1;`,
    parameters: undefined
  },
  original: error: column "phoneNumber" does not exist
      at Parser.parseErrorMessage (/home/amrit/ByteSpeed1/node_modules/pg-protocol/dist/parser.js:287:98)
      at Parser.handlePacket (/home/amrit/ByteSpeed1/node_modules/pg-protocol/dist/parser.js:126:29)
      at Parser.parse (/home/amrit/ByteSpeed1/node_modules/pg-protocol/dist/parser.js:39:38)
      at Socket.<anonymous> (/home/amrit/ByteSpeed1/node_modules/pg-protocol/dist/index.js:11:42)
      at Socket.emit (node:events:513:28)
      at addChunk (node:internal/streams/readable:324:12)
      at readableAddChunk (node:internal/streams/readable:297:9)
      at Readable.push (node:internal/streams/readable:234:10)
      at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
    length: 178,
    severity: 'ERROR',
    code: '42703',
    detail: undefined,
    hint: 'Perhaps you meant to reference the column "contacts.phonenumber".',
    position: '14',
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: 'parse_relation.c',
    line: '3398',
    routine: 'errorMissingColumn',
    sql: `SELECT "id", "phoneNumber", "email", "linkedId", "linkPrecedence", "createdAt", "updatedAt", "deletedAt" FROM "contacts" AS "contacts" WHERE ("contacts"."email" = 'example@example.com' OR "contacts"."phoneNumber" = 1234567890) LIMIT 1;`,
    parameters: undefined
  },
  sql: `SELECT "id", "phoneNumber", "email", "linkedId", "linkPrecedence", "createdAt", "updatedAt", "deletedAt" FROM "contacts" AS "contacts" WHERE ("contacts"."email" = 'example@example.com' OR "contacts"."phoneNumber" = 1234567890) LIMIT 1;`,
  parameters: {}
}

tldr：使用axois向连接到pg数据库并使用sequelise ORM的webservice发送post请求，但它给出'column_name'不存在的错误。但它确实如此！我做错了什么？我也问了chatgpt，但它说要仔细检查我的出口和进口声明，这没有帮助。

node.js

来源：https://stackoverflow.com/questions/76576024/sequelise-with-postgresql-column-doesnt-exist

1条答案

按热度按时间

zd287kbt1#

cameCase中的字段名称，我将它们改为underlined_small_case，包括createdAt，updatedAt和deletedAt。
因为我已经启用了时间戳，当我将sequelise字段更改为created_at时，我再次遇到错误，例如，createdAt不存在于表中，这是真的，因为在表中，只有created_at存在，createdAt是由sequelise自动添加到我的查询中的，因为启用了时间戳。这解决了问题。还将contacts.js中updated at字段的定义更改为

createdAt: {
      type: DataTypes.DATE,
      field: 'created_at', //in table the column name is created_at
      allowNull: false,
    },
    updatedAt: {
      type: DataTypes.DATE,
      field: 'updated_at',
      allowNull: false,
    },
    deletedAt: {
      type: DataTypes.DATE,
      field: 'deleted_at', 
      allowNull: true,
    }

赞(0）回复(0）举报 2023-06-29

我来回答

NodeJS 使用PostgreSQL：列不存在

1条答案

相关问题

热门标签

最新问答