Flask SQLAlchemy skips id if I got an error with adding new users

Question

My model:

class User(db.Model, UserMixin):
__tablename__ = 'api_user'

id = db.Column(db.Integer, primary_key=True)
first_name = db.Column(db.String(30), nullable=False, index=True)
last_name = db.Column(db.String(40), nullable=False, index=True)
email = db.Column(db.String(50), unique=True, nullable=False, index=True)
password = db.Column(db.String(50), nullable=False)
birth_date = db.Column(db.Date, nullable=False, index=True)

Even if it's a minor error (e.g. invalid email format etc.) it throws an error and just skips id it would give to this new user. table:

bulletin_board=# SELECT * FROM api_user;
 id | first_name | last_name |       email       | password | birth_date 
----+------------+-----------+-------------------+----------+------------
  1 | Rejep      | Mammedov  | reppon@mail.ru    | test123  | 1997-11-16
  3 | Rejep      | Mammedov  | repposn@mail.ru   | test123  | 1997-11-16
  4 | Rejep      | Mammedov  | repposan@mail.ru  | test123  | 1997-11-16
  8 | Rejep      | Mammedov  | repposasn@mail.ru | test123  | 1997-11-16
(4 rows)

user = User(first_name='Rejep', last_name='Mammedov', email='repposasn@mail.ru', password='test123', birth_date='16-11-1997')
db.session.add(user)
db.commit()

Answer 1

SQLA makes an integer primary key 'auto-increment' by default, so for whatever reason your attempted insertions failed, the auto-id increments. It is not really feasible to reset the auto-increment value while running. If that is a serious issue for you, you could force it to use a manually calculated primary key. Let me know if this is what you are getting at.