CODEWITHSUNDEEP
node.jssequelize.js
AwDogsGo2Heaven
AwDogsGo2Heaven

Reputation: 972

Sequelize throwing 'id must be unique' on create

new to Sequelize library. From my understanding, 'id' is created automatically by sequelize (and thats what I see in the database). However when I go to 'create' an object it will throw this error:

    { [SequelizeUniqueConstraintError: Validation error]
  name: 'SequelizeUniqueConstraintError',
  message: 'Validation error',
  errors: 
   [ { message: 'id must be unique',
       type: 'unique violation',
       path: 'id',
       value: '1' } ],
  fields: { id: '1' } }

The offending code:

  db.Account.create({
    email: req.body.email,
    password: req.body.password,
    allowEmail: req.body.allowEmail,
    provider: 'local',
    role: 'user'
  })

Notice ID is not specified anywhere, neither is it specified in my model definition. Also the query it generates runs fine if I run it in postgres admin:

INSERT INTO "Accounts" ("id","email","role","verifyCode","provider","cheaterScore","isBanned","allowEmail","updatedAt","createdAt") VALUES (DEFAULT,'[email protected]','user','','local',0,false,false,'2016-01-27 04:31:54.350 +00:00','2016-01-27 04:31:54.350 +00:00') RETURNING *;

Any ideas to what I could be missing here?

edit:

postgres version: 9.5 stack trace starts here: /node_modules/sequelize/lib/dialects/postgres/query.js:326

Upvotes: 14

Views: 7282

Answers (2)

Dzmitry Dranitski
Dzmitry Dranitski

Reputation: 659

TLDR: use:

await sequelize.query(`ALTER SEQUENCE "SharedLogs_id_seq" RESTART WITH ${maxId};`);

Here is my typescript full migration code which makes bulk insert and alters postgres sequence accordingly:

import {Migration, MigrationParams} from '../../types/db.migrations';
import {SharedLogRecord/* , SharedStateRecord*/} from '../../types/db';
import fs from 'fs';

type SharedLogRecordFromSqlite = SharedLogRecord & {
  updatedAt: string;
  deletedAt: string;
};

export const up: Migration = async ({context: queryInterface}: MigrationParams) => {
  const data: SharedLogRecordFromSqlite[] = JSON.parse(fs.readFileSync(__dirname + '/20_shared_logs.json', 'utf8'));

  let maxId: number = 0;
  data.forEach((record: SharedLogRecordFromSqlite) => {
    delete record.updatedAt;
    delete record.deletedAt;

    if (record.id > maxId) maxId = record.id;
  });
  maxId++;

  await queryInterface.bulkInsert('SharedLogs', data);

  await queryInterface.sequelize.query(`ALTER SEQUENCE "SharedLogs_id_seq" RESTART WITH ${maxId};`); // <--- key line
};

export const down: Migration = async () => {};

module.exports = {up, down};

Please see: https://stackoverflow.com/a/8750984/10099510

Upvotes: 0

Ben Polge
Ben Polge

Reputation: 646

Postgres has a habit of not resetting the next number in the sequence (autoincrement field) after bulk inserts. So if you're doing any pre-filling of the data in an init routine or from a SQL dump file, that's probably your issue.

Check out this post https://dba.stackexchange.com/questions/65662/postgres-how-to-insert-row-with-autoincrement-id

Upvotes: 12

Related Questions