使用PostgreSQL的Sequelize：列不存在

huangapple
go评论88阅读模式
英文:

sequelise with postgreSQL: column doesnt exist

问题

你发送的请求中出现了一个错误，指出“phoneNumber”列不存在。错误信息中还提到了可能是指向了“contacts.phonenumber”。根据你提供的代码，问题可能出在对列名的大小写敏感上。在SQL中，列名的大小写通常是不敏感的，但在某些情况下，ORM（如Sequelize）可能会将列名视为区分大小写的。

在你的代码中，定义了一个名为phoneNumber的列，但在数据库中，它可能以不同的形式存在，比如phonenumber。这可能导致Sequelize在查询时无法正确匹配列名。

你可以尝试在查询中使用与数据库中列名完全匹配的形式，或者在定义模型时使用与数据库列名匹配的名称。

如果问题仍然存在，建议你仔细检查数据库中的列名以及模型中的定义，确保它们完全匹配。

英文:

i am trying to make a small webservice with nodejs, express, pogreSQL database using sequelise. Created the database using this in 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)
 );

Defined the contact model in contacts.js as

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;

My webservice receives a post request which has this format

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

In a identifyContact.js file i am trying to find the row which has this email or this phoneNumber and then i process it and send something back. It is giving error at the find.one() method line. The contents of this file are as follows:


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 looks like this

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}`);
});

when i am sending the post request using axios, i am getting an error that says column "phoneNumber doesnt exist"

this is the console after sending request

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: sending post request using axois to webservice, which is connected to pg databse and using sequelise ORM, but it gives an error that 'column_name' doesnt exist. But it does! What am i doing wrong? I asked chatgpt too but it says to double check my export and import statements, which didnt help.

答案1

得分: 1

请不要与编译器争论，如果它说某一列不存在，那么简单的事实就是该列不存在。您的任务是找出原因。在这种情况下，您定义了phoneNumber，但由于它没有用双引号（"）括起来，Postgres将其转换为小写，导致列名为phonenumber。随后在SQL中使用"phoneNunber"时，该列不存在。您需要在生成SQL时要么不使用双引号，要么重新定义表以将列定义为"phoneNumber"。根据您的表定义和SQL，对几个其他列也需要进行相同的操作。

英文:

Do not argue with the compiler, if it says a column does not exist, then the simple fact is the column does not exist. Your job is to discover why. In this case you define phoneNumber, but since it is not enclosed in double quotes (") Postgres folds it to lower case resulting in the column name phonenumber. When you subsequently use "phoneNunber" in your SQL that column does not exist. You need to either generate the SQL without double quotes or redefine your table to define the column as "phoneNumber". Taking a look at your table definition and SQL the same is necessary on several other columns, as well.

答案2

得分: 0

字段名原先是驼峰式的，我将它们改成了下划线小写格式，包括了 createdAt、updatedAt 以及 deletedAt。

因为我启用了时间戳，在将 Sequelize 字段更改为 created_at 后，我再次遇到了错误，比如说 "在表中找不到 createdAt"，这是正确的，因为在表中只有 created_at 存在，createdAt 是由 Sequelize 自动添加到我的查询中的，因为启用了时间戳。这解决了这个问题。

同时，我也修改了 contacts.js 中更新时间字段的定义：

createdAt: {
    type: DataTypes.DATE,
    field: 'created_at', // 在表中列名为 created_at
    allowNull: false,
},
updatedAt: {
    type: DataTypes.DATE,
    field: 'updated_at',
    allowNull: false,
},
deletedAt: {
    type: DataTypes.DATE,
    field: 'deleted_at', 
    allowNull: true,
}
英文:

the field names that were in cameCase, i changed them to underscored_small_case, including createdAt, updatedAt and deletedAt.

Because i have enabled timestamps, when I change the sequelise field to created_at, i was again running in to the errors like, createdAt doesnt exist in table, which is true because in table, only created_at exists, createdAt was being added automatically by sequelise in my query because of timestamps enabled. this fixed the problem.
Also changed the definition of updated at fields in contacts.js as

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,
}

huangapple
  • 本文由 发表于 2023年6月29日 03:03:43
  • 转载请务必保留本文链接：https://go.coder-hub.com/76576024.html
  • express
  • javascript
  • node.js
  • postgresql
  • sequelize.js
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定