sequelize autoIncrement starting id for postgres

Question

I'm in a situation where I want to have autoIncrement id keys on my models, but I also need to create fixtures and relationships between them. When I create fixtures, I manually specify ids (so that I don't have to depend too much on order and assumptions that may turn out to be wrong). But when I go to create more instances (without specifying ids, letting autoIncrement take over), I get collisions with the ids from the fixtures.

I'd like to be able to set the initial auto increment number to something like 1000 so that I can create plenty of fixtures with manually chosen ids without running into trouble... Or is there another way to resolve this maybe?

Answer 1

I'm running into the same issue. One way around it is to leave 'id' (autoincremented) out in your fixtures, and assuming you've cleared the data in your db, link foreign keys (in your fixtures) with what they should be after autoincrementing.

Doing this, since you left id out in your fixture data, Postgres will be updating the sequence it uses to assign autoincremented values for table X (from X_id_seq - you can psql; \c ; \d (to list dbs); select * from X_id_seq).

Another way is to keep doing what you (and I) are doing, i.e. set autoincrementing ids manually, and then somehow change the sequence table X_id_seq to set its last_value to whatever value it should be.

You'll probably have to run a manual sql query via sequelize.

Yet another option is to do your own fixtures via your own models. Something which I was doing and am now seriously considering reverting to…

Edit: also, if you're using sequelize-fixtures, you might want to take a look at the last comment here: https://github.com/domasx2/sequelize-fixtures/issues/43#issuecomment-147955330

Edit: opened issue: https://github.com/domasx2/sequelize-fixtures/issues/71

Edit: As a temp work-around before re-writing everything without sequelize-fixtures, I used the following to manually set the sequence:

await db.sequelize.query(
    `ALTER SEQUENCE "ItemInstances_id_seq" RESTART WITH ${itemInstances.length + 1};`
);

I don't think it matters when you do this (before / after loading fixtures) as you're by-passing the sequence anyway.