Search code examples
postgresqlmigrationknex.jsseeding

Knex seed and primary key sequence

I using knex.js for seedings and migrations. With code similar to following.

exports.down = function(knex, Promise) {
  knex.schema.table('users', function(table) {
    table.dropColumn('fullname')
  })
}


exports.seed = function(knex, Promise) {
  // Deletes ALL existing entries
  return knex('users').del()
  .then(function () {
    // Inserts seed entries
    return knex('users').insert([
      {
        id: 1,
        email: '[email protected]',
        password: 'dorwssap'
      },
      {
        id: 2,
        email: '[email protected]',
        password: 'password1'
      },
      {
        id: 3
        email: '[email protected]',
        password: 'password123'
      }
    ]);
  });
};

But when I trying to insert new data into the table - autoincrement doesn't work until I update sequence manually. How could I fix this issue?

Solution

  • When data is inserted with explicit id given, postgresql does not read the value from id_sequence thus, the sequence will not be incremented on every insert.

    You need to create data without giving ids explicitly or otherwise you need to update each table's id sequence to correct values after creating the initial data.