CODEWITHSUNDEEP
sqlnode.jspostgresqlnullsequelize-cli
user21100383
user21100383

Reputation: 1

Missing column while adding data into table using sequelize

I am working with nodejs web application, just started, I created a Postgresql database and added some tables using sequelize.

In the process I created a table QuestionOptions which has columns Title:string and QueId:foreign key which were created and id, createdAt and updatedAt columns by default. Now if I try to add data into the table using this code:

app.post("/submitOptions", async (request, response) => {
  console.log("Options request body", request.body);
  try {
    for (const key in request.body) {
      if (key.startsWith("q-")) {
        const questionIDfromRequestBody = key.split("-")[1];
        const optionIndex = key.split("-")[3];
        const optionValueKey = `q-${questionIDfromRequestBody}-option-${optionIndex}`;
        const optionValue = request.body[optionValueKey];
        console.log(optionValue);
        const option = await QuestionOptions.addOption({
          Title: optionValue,
          QueId: questionIDfromRequestBody
        });
      }
    }
    // Store the added questions in a session variable
    return response.redirect(`/addElectors`);
  } catch (error) {
    console.log(error);
    error.errors.forEach((element) => {
      request.flash("error", element.message);
    });
    response.redirect("/addOptions");
  }
});

I have a request body like this:

Options request body [Object: null prototype] {
  _csrf: '550a1baf-04d2-46b1-9123-3977034d65c2',
  'q-11-option-1': '11',
  'q-11-option-2': '12',
  'q-12-option-1': '21',
  'q-12-option-2': '22'
}

but I get an

null values are inserted into the Title column

Executing (default): INSERT INTO "QuestionOptions" ("id","createdAt","updatedAt","QueId") VALUES (DEFAULT,$1,$2,$3) RETURNING "id","Title","createdAt","updatedAt","QueId";
Error
    at Query.run (/home/hemanth/Voting-app-master/node_modules/sequelize/lib/dialects/postgres/query.js:50:25)
    at /home/hemanth/Voting-app-master/node_modules/sequelize/lib/sequelize.js:315:28
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async PostgresQueryInterface.insert (/home/hemanth/Voting-app-master/node_modules/sequelize/lib/dialects/abstract/query-interface.js:308:21)
    at async QuestionOptions.save (/home/hemanth/Voting-app-master/node_modules/sequelize/lib/model.js:2432:35)
    at async QuestionOptions.create (/home/hemanth/Voting-app-master/node_modules/sequelize/lib/model.js:1344:12)
    at async /home/hemanth/Voting-app-master/app.js:269:24 {
  name: 'SequelizeDatabaseError',
  parent: error: null value in column "Title" of relation "QuestionOptions" violates not-null constraint
      at Parser.parseErrorMessage (/home/hemanth/Voting-app-master/node_modules/pg-protocol/dist/parser.js:287:98)
      at Parser.handlePacket (/home/hemanth/Voting-app-master/node_modules/pg-protocol/dist/parser.js:126:29)
      at Parser.parse (/home/hemanth/Voting-app-master/node_modules/pg-protocol/dist/parser.js:39:38)
      at Socket.<anonymous> (/home/hemanth/Voting-app-master/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: 275,
    severity: 'ERROR',
    code: '23502',
    detail: 'Failing row contains (6, null, 2023-03-19 04:39:56.652+00, 2023-03-19 04:39:56.652+00, 11).',
    hint: undefined,
    position: undefined,
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: 'public',
    table: 'QuestionOptions',
    column: 'Title',
    dataType: undefined,
    constraint: undefined,
    file: 'execMain.c',
    line: '1883',
    routine: 'ExecConstraints',
    sql: 'INSERT INTO "QuestionOptions" ("id","createdAt","updatedAt","QueId") VALUES (DEFAULT,$1,$2,$3) RETURNING "id","Title","createdAt","updatedAt","QueId";',
    parameters: [
      '2023-03-19 04:39:56.652 +00:00',
      '2023-03-19 04:39:56.652 +00:00',
      '11'
    ]
  },
  original: error: null value in column "Title" of relation "QuestionOptions" violates not-null constraint
      at Parser.parseErrorMessage (/home/hemanth/Voting-app-master/node_modules/pg-protocol/dist/parser.js:287:98)
      at Parser.handlePacket (/home/hemanth/Voting-app-master/node_modules/pg-protocol/dist/parser.js:126:29)
      at Parser.parse (/home/hemanth/Voting-app-master/node_modules/pg-protocol/dist/parser.js:39:38)
      at Socket.<anonymous> (/home/hemanth/Voting-app-master/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: 275,
    severity: 'ERROR',
    code: '23502',
    detail: 'Failing row contains (6, null, 2023-03-19 04:39:56.652+00, 2023-03-19 04:39:56.652+00, 11).',
    hint: undefined,
    position: undefined,
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: 'public',
    table: 'QuestionOptions',
    column: 'Title',
    dataType: undefined,
    constraint: undefined,
    file: 'execMain.c',
    line: '1883',
    routine: 'ExecConstraints',
    sql: 'INSERT INTO "QuestionOptions" ("id","createdAt","updatedAt","QueId") VALUES (DEFAULT,$1,$2,$3) RETURNING "id","Title","createdAt","updatedAt","QueId";',
    parameters: [
      '2023-03-19 04:39:56.652 +00:00',
      '2023-03-19 04:39:56.652 +00:00',
      '11'
    ]
  },
  sql: 'INSERT INTO "QuestionOptions" ("id","createdAt","updatedAt","QueId") VALUES (DEFAULT,$1,$2,$3) RETURNING "id","Title","createdAt","updatedAt","QueId";',
  parameters: [
    '2023-03-19 04:39:56.652 +00:00',
    '2023-03-19 04:39:56.652 +00:00',
    '11'
  ]
}
/home/hemanth/Voting-app-master/app.js:279
    error.errors.forEach((element) => {
                 ^

TypeError: Cannot read properties of undefined (reading 'forEach')
    at /home/hemanth/Voting-app-master/app.js:279:18
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)

Node.js v18.13.0
[nodemon] app crashed - waiting for file changes before starting...

I noticed in the first line of the error it says "Executing (default): INSERT INTO "QuestionOptions" ("id","createdAt","updatedAt","QueId") VALUES (DEFAULT,$1,$2,$3) RETURNING "id","Title","createdAt","updatedAt","QueId";"

it it missing Title column in ("id","createdAt","updatedAt","QueId") but whereas when returning it says "RETURNING "id","Title","createdAt","updatedAt","QueId";" which contains Title column.

I cannot figure out what to do, I tried dropping and creating a new table from scratch but it behaves the same.

I am expecting some value in the Title column of my QuestionOptions table instead of null, I even added notNull constraint.

Upvotes: 0

Views: 951

Answers (1)

BuckX
BuckX

Reputation: 1

The error "null value in column 'Title' of relation 'QuestionOptions' violates not-null constraint" means that the 'Title' field in the 'QuestionOptions' table is set to not-null and a null value is being inserted into that field.

The error message is telling you that there is an issue with inserting null values into the 'Title' column of the 'QuestionOptions' table. You need to make sure that you are passing a non-null value for the 'Title' field when creating a new 'QuestionOption' object.

Check the value of 'optionValue' that you are passing to the 'Title' field. It seems that it is null, causing the error. You may need to add some validation to make sure that 'optionValue' is not null before attempting to create the 'QuestionOption' object.

Also, check that the 'Title' field in the 'QuestionOptions' table is set to allow null values if you want to be able to insert null values into that field. If the field is not set to allow null values, you will need to pass a non-null value for the 'Title' field when creating a new 'QuestionOption' object.

Upvotes: 0

Related Questions