Question 196445 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – sequelise with postgreSQL: column doesnt exist

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: [email protected] (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.
[email protected]  ---  1234567890
Executing (default): SELECT "id", "phoneNumber", "email", "linkedId", "linkPrecedence", "createdAt", "updatedAt", "deletedAt" FROM "contacts" AS "contacts" WHERE ("contacts"."email" = '[email protected]' 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" = '[email protected]' 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" = '[email protected]' 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" = '[email protected]' 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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

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

  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search