Cannot create records due to IDENTITY_INSERT Being ON

Reda · October 6, 2020, 5:09pm

Hello,

Our Forest Admin Backend is all setup and running, the problem is when inserting a new row.

Expected behavior

When inserting a new row for any model that have model creation allowed set to true the new row should be in the database

Actual behavior

We get the following error
Brands creation failed: Cannot insert explicit value for identity column in table ‘Brands’ when IDENTITY_INSERT is set to OFF.

The actual behavior is normal because we are using SQL Server and the ID column is Autogenerated. I need help turning off this feature and letting SQL Server Generating the new Id for the newly created row.

Thanks in advance

vince · October 7, 2020, 9:18am

Hi @Reda and welcome in our community !

Okey so to help you I’ll need the definition of your model (brands) and possibly the insert query that fails. TO print the query you juste have to set NODE_ENV=development in your .env or environment variables.

Reda · October 7, 2020, 9:32am

Hello Vince,

Thank you for taking the time to help, here’s the output of the log when I try to add a new brand record

0|server  | (node:22547) Warning: a promise was created in a handler at /home/ec2-user/wafr/node_modules/forest-express/node_modules/express/lib/router/route.js:137:13 but was not returned from it, see http://goo.gl/rRqMUw
0|server  |     at Function.Promise.each (/home/ec2-user/wafr/node_modules/forest-express/node_modules/bluebird/js/release/each.js:24:12)
0|server  | Executing (default): INSERT INTO [dbo].[Brands] ([Id],[Name],[Description],[Picture],[order],[Active]) OUTPUT INSERTED.* VALUES (@0,@1,@2,@3,@4,@5);
0|server  | [forest] 🌳🌳🌳  Unexpected error: Cannot insert explicit value for identity column in table 'Brands' when IDENTITY_INSERT is set to OFF.
0|server  | SequelizeDatabaseError: Cannot insert explicit value for identity column in table 'Brands' when IDENTITY_INSERT is set to OFF.
0|server  |     at Query.formatError (/home/ec2-user/wafr/node_modules/sequelize/lib/dialects/mssql/query.js:313:12)
0|server  |     at Request.connection.lib.Request [as userCallback] (/home/ec2-user/wafr/node_modules/sequelize/lib/dialects/mssql/query.js:73:23)
0|server  |     at Request.callback (/home/ec2-user/wafr/node_modules/tedious/lib/request.js:56:14)
0|server  |     at Connection.endOfMessageMarkerReceived (/home/ec2-user/wafr/node_modules/tedious/lib/connection.js:2407:20)
0|server  |     at Connection.dispatchEvent (/home/ec2-user/wafr/node_modules/tedious/lib/connection.js:1279:15)
0|server  |     at Parser.tokenStreamParser.on (/home/ec2-user/wafr/node_modules/tedious/lib/connection.js:1072:14)
0|server  |     at Parser.emit (events.js:198:13)
0|server  |     at Parser.parser.on.token (/home/ec2-user/wafr/node_modules/tedious/lib/token/token-stream-parser.js:37:14)
0|server  |     at Parser.emit (events.js:198:13)
0|server  |     at addChunk (/home/ec2-user/wafr/node_modules/tedious/node_modules/readable-stream/lib/_stream_readable.js:298:12)
0|server  |     at readableAddChunk (/home/ec2-user/wafr/node_modules/tedious/node_modules/readable-stream/lib/_stream_readable.js:280:11)
0|server  |     at Parser.Readable.push (/home/ec2-user/wafr/node_modules/tedious/node_modules/readable-stream/lib/_stream_readable.js:241:10)
0|server  |     at Parser.Transform.push (/home/ec2-user/wafr/node_modules/tedious/node_modules/readable-stream/lib/_stream_transform.js:139:32)
0|server  |     at Parser.afterTransform (/home/ec2-user/wafr/node_modules/tedious/node_modules/readable-stream/lib/_stream_transform.js:88:10)
0|server  |     at Parser._transform (/home/ec2-user/wafr/node_modules/tedious/lib/token/stream-parser.js:88:14)
0|server  |     at Parser.Transform._read (/home/ec2-user/wafr/node_modules/tedious/node_modules/readable-stream/lib/_stream_transform.js:177:10)
0|server  |     at Parser.Transform._write (/home/ec2-user/wafr/node_modules/tedious/node_modules/readable-stream/lib/_stream_transform.js:164:83)
0|server  |     at doWrite (/home/ec2-user/wafr/node_modules/tedious/node_modules/readable-stream/lib/_stream_writable.js:409:139)
0|server  |     at writeOrBuffer (/home/ec2-user/wafr/node_modules/tedious/node_modules/readable-stream/lib/_stream_writable.js:398:5)
0|server  |     at Parser.Writable.write (/home/ec2-user/wafr/node_modules/tedious/node_modules/readable-stream/lib/_stream_writable.js:307:11)
0|server  |     at Parser.addEndOfMessageMarker (/home/ec2-user/wafr/node_modules/tedious/lib/token/token-stream-parser.js:55:24)
0|server  |     at Connection.message (/home/ec2-user/wafr/node_modules/tedious/lib/connection.js:2396:32)
0|server  |     at Connection.dispatchEvent (/home/ec2-user/wafr/node_modules/tedious/lib/connection.js:1279:15)
0|server  |     at MessageIO.messageIo.on (/home/ec2-user/wafr/node_modules/tedious/lib/connection.js:1139:14)
0|server  |     at MessageIO.emit (events.js:198:13)
0|server  |     at Message.message.on (/home/ec2-user/wafr/node_modules/tedious/lib/message-io.js:46:14)
0|server  |     at Message.emit (events.js:203:15)
0|server  |     at endReadableNT (/home/ec2-user/wafr/node_modules/tedious/node_modules/readable-stream/lib/_stream_readable.js:1094:12)
0|server  |     at process._tickCallback (internal/process/next_tick.js:63:19)
0|server  | From previous event:
0|server  |     at Query._run (/home/ec2-user/wafr/node_modules/sequelize/lib/dialects/mssql/query.js:45:12)
0|server  |     at Promise.using.connection (/home/ec2-user/wafr/node_modules/sequelize/lib/dialects/mssql/query.js:107:69)
0|server  | From previous event:
0|server  |     at Query.run (/home/ec2-user/wafr/node_modules/sequelize/lib/dialects/mssql/query.js:107:20)
0|server  |     at runHooks.then.then (/home/ec2-user/wafr/node_modules/sequelize/lib/sequelize.js:641:29)
0|server  | From previous event:
0|server  |     at Promise.try.then.connection (/home/ec2-user/wafr/node_modules/sequelize/lib/sequelize.js:641:12)
0|server  | From previous event:
0|server  |     at Promise.resolve.retry (/home/ec2-user/wafr/node_modules/sequelize/lib/sequelize.js:637:10)
0|server  |     at /home/ec2-user/wafr/node_modules/retry-as-promised/index.js:70:21
0|server  |     at new Promise (<anonymous>)
0|server  |     at retryAsPromised (/home/ec2-user/wafr/node_modules/retry-as-promised/index.js:60:10)
0|server  |     at Promise.try (/home/ec2-user/wafr/node_modules/sequelize/lib/sequelize.js:627:30)
0|server  | From previous event:
0|server  |     at Sequelize.query (/home/ec2-user/wafr/node_modules/sequelize/lib/sequelize.js:576:23)
0|server  |     at QueryInterface.insert (/home/ec2-user/wafr/node_modules/sequelize/lib/query-interface.js:885:27)
0|server  |     at Promise.try.then.then.then (/home/ec2-user/wafr/node_modules/sequelize/lib/model.js:3985:52)
0|server  | From previous event:
0|server  |     at model.save (/home/ec2-user/wafr/node_modules/sequelize/lib/model.js:3962:8)
0|server  |     at /home/ec2-user/wafr/node_modules/forest-express-sequelize/dist/services/resource-creator.js:38:28
0|server  |     at tryCatcher (/home/ec2-user/wafr/node_modules/bluebird/js/main/util.js:24:31)
0|server  |     at Promise._settlePromiseFromHandler (/home/ec2-user/wafr/node_modules/bluebird/js/main/promise.js:454:31)
0|server  |     at Promise._settlePromiseAt (/home/ec2-user/wafr/node_modules/bluebird/js/main/promise.js:530:18)
0|server  |     at Async._drainQueue (/home/ec2-user/wafr/node_modules/bluebird/js/main/async.js:182:12)
0|server  |     at Async._drainQueues (/home/ec2-user/wafr/node_modules/bluebird/js/main/async.js:187:10)
0|server  |     at Immediate.Async.drainQueues (/home/ec2-user/wafr/node_modules/bluebird/js/main/async.js:15:14)
0|server  |     at runCallback (timers.js:705:18)
0|server  |     at tryOnImmediate (timers.js:676:5)
0|server  |     at processImmediate (timers.js:658:5)

When I try to perform the same query in SQL Server it fails because the ID column is autogenerated. Here’s the model:

module.exports = (sequelize, DataTypes) => {
  const { Sequelize } = sequelize;
  // This section contains the fields of your model, mapped to your table's columns.
  // Learn more here: https://docs.forestadmin.com/documentation/v/v6/reference-guide/models/enrich-your-models#declaring-a-new-field-in-a-model
  const Brands = sequelize.define('brands', {
    id: {
      type: DataTypes.INTEGER,
      field: 'Id',
      primaryKey: true,
    },
    name: {
      type: DataTypes.STRING,
      field: 'Name',
    },
    description: {
      type: DataTypes.STRING,
      field: 'Description',
    },
    picture: {
      type: DataTypes.STRING,
      field: 'Picture',
    },
    order: {
      type: DataTypes.INTEGER,
    },
    active: {
      type: DataTypes.BOOLEAN,
      field: 'Active'
    }
  }, {
    tableName: 'Brands',
    timestamps: false,
    schema: process.env.DATABASE_SCHEMA,
  });

  // This section contains the relationships for this model. See: https://docs.forestadmin.com/documentation/v/v6/reference-guide/relationships#adding-relationships.
  Brands.associate = (models) => {
    Brands.hasMany(models.products, {
      foreignKey: {
        name: 'brandIdKey',
        field: 'BrandId',
      },
      target: {
        name: 'Id',
        field: 'Id',
      },
      as: 'brandProducts',
    });
  };

  return Brands;
};

Reda · October 7, 2020, 12:37pm

The problem has been resolved by adding autoincrement true in the brands model. Thank you for your time.

Topic		Replies	Views
Cannot create row from Forest Admin because identifier cannot be null Help me!	5	479	October 11, 2021
"Creation Failed: ID Must Be Unique" Error Help me!	1	788	October 15, 2020
Silent validation error with Sequelize Help me!	13	4797	August 17, 2020
Can't create data Help me!	15	394	February 2, 2021
500 error creating a record Help me!	2	158	October 16, 2023

Cannot create records due to IDENTITY_INSERT Being ON

Expected behavior

Actual behavior

Related Topics