CODEWITHSUNDEEP
node.jspostgresqlsequelize.js
Omkommersind
Omkommersind

Reputation: 295

Sequelize node js create record with autoincrement field validation error

I have sequelize model:

'use strict';

module.exports = (sequelize, DataTypes) => {
    const Image = sequelize.define('Image', {
        id: {
            type: DataTypes.BIGINT,
            autoIncrement: true,
            unique: true,
            primaryKey: true
        },
        url: {
            type: DataTypes.STRING,
            allowNull: false,
        }
    },
    {
        timestamps: false
    });

    Image.associate = (models) => {
        //some association here
    };

    return Image;
};

I am trying to create a new record like that: 

const img = await Images.create({
    url: '/newUrl'
}).catch(error => {
    throw errors.initError(error);
});

it executes a query

Executing (default): INSERT INTO "Images" ("id","url") VALUES (DEFAULT,'/newUrl') RETURNING *;

and I receive an error

{
  "message": "id must be unique",
  "type": "unique violation",
  "path": "id",
  "value": "18",
  "origin": "DB",
  "instance": {
    "id": null,
    "url": "/newUrl"
  },
  "validatorKey": "not_unique",
  "validatorName": null,
  "validatorArgs": []
}

Should not sequelize handle autoincrement field by itself?

Upvotes: 0

Views: 5091

Answers (3)

Ucha
Ucha

Reputation: 184

Just to add to Rodolofo's answer, Postgres users should replace 'YourTable_id_seq' with '"YourTable_id_seq"'. Table name is case-sensitive.

Refer this answer

Upvotes: 0

Rodolfo A. Ramirez-Valenzuela
Rodolfo A. Ramirez-Valenzuela

Reputation: 31

This happens when you have inserted a record and have provided the id manually. next_val('test_id_seq') is only called when the system needs a value for this column and you have not provided one. What you need to do is to update this next_val value.

To fix this you can execute the following in your table

   SELECT setval('YourTable_id_seq', (SELECT MAX(id) from "YourTable"));

The name of the sequence is always autogenerated and it follows the following format tablename_columnname_seq.

Upvotes: 3

AbhinavD
AbhinavD

Reputation: 7282

This happens when you have manually created entries into the database providing the id.

To demonstrate this, you can try these into postgres

create table users (id serial NOT NULL PRIMARY KEY, name varchar(40));
insert into users (name) values ('abhinavd');
select * from users;
insert into users (id, name) values (2, 'abhinavd');
insert into users (name) values ('abhinavd');

The last insert will fail complaining that id must be unique

Duplicate key

Upvotes: 3

Related Questions