sequelize incrementing id on validation error

Question

Sequelize is incrementing the id, even though it's not adding it into the DB.

For example for my user mode:

var User = sequelize.define("User", {
    email: {
        type: DataTypes.STRING,
        unique: true,
        allowNull: false,
    },
    password: {
        type: DataTypes.STRING,
        allowNull: false,
    },
},

If I run

var newUser = User.create({
        email: "a",
        password: "a"
    })

I get id 1, but if I run it again I get a validation error

next if I run it with a different email

var newUser = User.create({
        email: "b",
        password: "a"
    })

The id is 3, even though the db only has 2 enteries (id 1 and 3)

I don't quite understand why the id keeps increasing even though its not being added to the db. Is there anyway to get around this?

Answer 1

Sequelize isn't providing the id values, PostgreSQL itself is. Presumably Sequelize uses a serial type for the id column and that gets its values from a database sequence and from the fine manual:

Note: Because smallserial, serial and bigserial are implemented using sequences, there may be "holes" or gaps in the sequence of values which appears in the column, even if no rows are ever deleted. A value allocated from the sequence is still "used up" even if a row containing that value is never successfully inserted into the table column. This may happen, for example, if the inserting transaction rolls back. See nextval() in Section 9.16 for details.

You're trying to do an INSERT that violates your uniqueness constraint and there's your rolled back transaction.

You shouldn't waste time caring about the particular id values, they're unique numbers and that's all that matters.