[SOLVED] How to make SQLite to use unique autoincrement id with SQLAlchemy?

How to make SQLite to use unique autoincrement id with SQLAlchemy?

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 behavior 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 the desired behavior was first to create a 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, and 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 use it everywhere and not resort to bare SQL anytime you hit a wall. That's why I would like to ask maybe 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')

Solution

I hope this example from the documentation will help you:

Table('scripts', metadata,
       Column('id', Integer, primary_key=True),
       sqlite_autoincrement=True)

As you can see they use sqlite_autoincrement.

But considering the Barmar's comment, I would also try (without at all autoincrements):

Table('scripts', metadata,
       Column('id', Integer, primary_key=True))