auto_increment during testing

[song-william]

In my testing, I currently insert a row to a table that has an auto-incrementing, primary_key, Integer column called id. Even with the db_session fixture, every time I run the test, the id column increments across tests runs. The first time I run the test, id == 1, but the next time I run the test, id == 2. Everything else in the test gets rolled back, but the id count persists. Is there a way to ensure that the count resets across test runs?

To get around this, at the beginning of each test that involves auto-incrementing an id, I manually run a SQL command to reset the count. The SQL below is for my Postgresql backend.

sql = text("ALTER SEQUENCE table_id_seq RESTART WITH 1")
_db.engine.execute(sql)

I feel like this breaks isolation across unit tests, so I'm hoping there is a better pytest-flask-sqlalchemy solution.

[WhiteExponent]

Having a similar issue here, not sure if related. Table IDs increment between different unit tests.

[dfeinzeig]

don't use hardcoded ids in your tests. try using something like factory boy and then just assert that the id of your created test data object matches after you perform your various operations.

[ehenry09]

I've ran into this same issue. As a dumb work around I've been querying down the ID to test against before making an assertion.

[pmdarrow]

I know this is quite old now but I thought it would be worth pointing out that this is expected behaviour and is a limitation of running tests within transactions. See https://stackoverflow.com/questions/449346/mysql-auto-increment-does-not-rollback for a good explanation.