CODEWITHSUNDEEP
node.jspostgresqlknex.js
D.J.
D.J.

Reputation: 1215

Knex primary key auto increment

I'm using knex to create a simple table in postgres database: 

function up(knex, Promise) {
return knex.schema.createTableIfNotExists('communities', (table) => {

    table.increments('id').primary().unsigned();

    table.string('name', 255).notNullable();
    table.string('slug', 100).notNullable();

    table.timestamp('createdAt').defaultTo( knex.fn.now() );
    table.timestamp('updatedAt');
});

};

function down(knex, Promise) {
  return knex.schema.dropTableIfExists(tableName);
};

module.exports = {
    tableName,
    up,
    down
}

My problem is that table.increments('id').primary() creates a primary key that for default value has nextval('communities_id_seq'::regclass) and I can't do an insert without an id (even in raw sql).

Does anyone know how to make the id increment by default?

Upvotes: 14

Views: 27727

Answers (3)

Lachlan Young
Lachlan Young

Reputation: 1342

A bit late to the party but I was having this issue with the same use case. However my solution was I didnt have all the correct permissions granted to my sequences, only my tables when i ceated the DB.

So something along the lines of "GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO PUBLIC"

Upvotes: 1

Beknazar
Beknazar

Reputation: 81

In js: table.increments()

Output sql: id int unsigned not null auto_increment primary key

check this out for more information

Upvotes: 8

D.J.
D.J.

Reputation: 1215

My problem was that the value for id was an empty string and not undefined or null, so that was braking the constraint for integer as data type.

Hope it helps!

Upvotes: 2

Related Questions