I am working on a project and need to store info about scripts in a database. As I understood in SQLite primary keys integer fields are autoincremented by default. The problem is, that when you delete rows then their ids will be reused. For me it is not accepted behaviour so I read in the docs that it is possible to create autoincremented id that will keep track of the highest used value in a sequence table and so values won’t be reused. From the docs:

If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a slightly different ROWID selection algorithm is used. The ROWID chosen for the new row is at least one larger than the largest ROWID that has ever before existed in that same table.

I use SQLAlchemy in my project and create/modify tables with migrations. The only thing that worked to replicate desired behaviour was first to create table with SQL command and then add columns:

op.execute(sa.text("CREATE TABLE scripts (id INTEGER PRIMARY KEY AUTOINCREMENT)"))
op.add_column('scripts',
    sa.Column('filename', sa.Unicode(length=64), nullable=False)
)
op.add_column('scripts',
    sa.Column('upload_date', sa.DateTime(), server_default=sa.text('(CURRENT_TIMESTAMP)'),
              nullable=False)
)

If I do it that way everything works as expected: I add new entries, they get new ids, when I delete old entries, new entries will still get new ids and previously used ones won’t be used again. But I don’t think that it is right to create tables this way. I was told that when you use ORM tools it is advised to used it everywhere and not resort to bare SQL anytime you hit a wall. That’s why I would like to ask mayby someone knows a way how to do it another way?

I defined the fields in the model this way

id = db.Column(db.Integer, primary_key=True, autoincrement=True)
filename = db.Column(db.Unicode(64), nullable=False)
upload_date = db.Column(db.DateTime, nullable=False, server_default=func.now(), onupdate=func.now())

Migration that didn’t work as expected looked this way:

def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('scripts',
sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
sa.Column('filename', sa.Unicode(length=64), nullable=False),
sa.Column('upload_date', sa.DateTime(), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
sa.PrimaryKeyConstraint('id')