sequelize: Running sequelize:seed for Postgres with autoIncrement key breaks SERIAL data type in tables

You do NOT need to add the id attribute to your model definition.

Do it like this:

let Book = sequelize.define('Book', {

  /*
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true,
  },
  */

  ...bookAttributes,
})

Sequelize will assume your table has a id primary key property by default.

The documentation you are reading is intended only for tables that use a column name other than id for your primary key, like books_id, or something.

@mickhansen

I found a better solution since last visiting this issue. At least in my case, the id values don’t necessarily correlate with how many records there are (eg, deletions).

Assuming you know the sequence name, you can just do this:

await DB.query(`
  select setval('users_id_seq', (select max(id) from users), true);
`);

If you had 100 records, for example, but your largest users.id value is 126 then relying on Users.count() alone will set you up for conflict, since 101 (the next ID) probably/can already exist.

You can verify what the next id value will be with this:

select nextval('users_id_seq');

I ended up doing something similar to what @lukeed did. A new seed with this code:

const { Users } = require('../models');


module.exports = {
  up: async (queryInterface, Sequelize) => {
    const extistingUsers = await Users.count();
    await queryInterface.sequelize.query(`ALTER SEQUENCE "Users_id_seq" RESTART WITH ${extistingUsers + 1}`);
  },


};

For anyone that stumbles onto this 😄

return await model.bulkCreate(bulk)
        .then(async () => db.query(`ALTER SEQUENCE "${model.tableName}_id_seq" RESTART WITH ${await model.count() + 1}`))
        .catch(error => {
            if (error.message.indexOf('already exists') > -1) return
            console.error(error)
            // logger.error(error)
        })

Addition: it stops working after using sequelize:seed to insert several lines to table. Is any way to sync somehow sequelize models with latest id in table?

So the indexing is incorrect on the database side (too?). I’m really not familiar with Sequelize code, so not sure if it’s relying on the DB index only or if it had its own.

Temporary workaround (to fix the DB sequence) is to run an ALTER sequence after the appropriate seeder has run:

await DB.query(`ALTER SEQUENCE users_id_seq RESTART WITH ${users.length + 1}`);

Thanks a lot, thanks to the ideas above I solved this problem. As a result, I got the following:

Model:

module.exports = (sequelize, DataTypes) => {
  const Course = sequelize.define(
    'course',
    {
      title: {
        type: DataTypes.STRING,
        allowNull: false
      },
      subtitle: {
        type: DataTypes.STRING,
        allowNull: false
      },
      image: {
        type: DataTypes.STRING,
        allowNull: true,
      },
      version: {
        type: DataTypes.STRING,
        allowNull: false
      },
    },
    {
      tableName: 'courses',
      timestamps: true,
    }
  );

  return Course;
}

Seed File:

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.bulkInsert('courses', [
      {
        id: 1,
        title: 'New Course',
        subtitle: 'JavaScript',
        image: 'https://url',
        version: '0.0.1',
        createdAt: new Date(),
        updatedAt: new Date(),
      }
    ], {
      ignoreDuplicates: true
    });

    // SOLUTION: Updating the index after inserting the data
    await queryInterface.sequelize.query("SELECT setval('courses_id_seq', max(id)) FROM courses;");
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.bulkDelete('courses', null, {});
  }
};

Confirmed. Running any seeding operation with id values included in the seed data will break Sequelize.

I’ve tried with items.map(o => User.create(o), User.bulkCreate(items), ... { individualHooks:true } and Q.bulkInsert('users', items). The data is entered fine, but Sequelize totally “loses track” of how many items there are in the database, so any new inserts or updates will throw the error @Darmikon mentioned.

Worth noting that the id Sequelize wants to use is always wrong. It also varies. It can be (id=1) or (id=89)… no rhythm to the number it tries to use.

For anyone who stumbles across this, my mistake was defaultValue: uuid() instead of defaultValue: uuid. defaultValue was being set to a static value rather than generating a new uuid each time a record was created.

I got past this issue by setting id attribute to <Model>.max(‘id’) + 1 during creation of model instance after seeding. This is also one of the solutions suggested by the article shared by @sushantdhiman

Postgres serial needs to be reset if you want to supply your own ids. This error appears when serial counter is ahead than the id you are trying to insert.

https://dba.stackexchange.com/questions/60802/fixing-table-structure-to-avoid-error-duplicate-key-value-violates-unique-cons

In my case I was forced to use UUID instead of INTEGER. So no more autoIncrement and INTEGER for my id fields