Flask-SQLAlchemy integration test clear database after each test with foreign key constraints

user_3590686 0
Viewed 728

I want to know how to write fast integration tests with flask-sqlalchemy and unittest, without having to create and drop tables on each test. I am using Postgres as my database.

Right now, the tables are created and dropped in setUpClass and tearDownClass respectively, which is fine from a performance standpoint. What I need is a way to delete all data and "reset" the database in each individual test, without re-creating all tables.

The closest I have gotten is with this code, but it raises an IntegrityError due to foreign key constraints.

def tearDown(self):
    meta = db.metadata
    for table in reversed(meta.sorted_tables):
        db.session.execute(table.delete())

    db.session.commit()

Important note: Since I am doing integration tests, I inevitably hit db.session.commit in my application code, which invalidates any session transaction, so I have not been able to use that as a solution.

flask-sqlalchemy python-unittest sqlalchemy python
1 Answers

Try this:

for table in reversed(meta.sorted_tables):
    db.session.execute(f"TRUNCATE {table.name} RESTART IDENTITY CASCADE;")

The CASCADE does the trick here, from the postgres docs:

Automatically truncate all tables that have foreign-key references to any of the named tables, or to any tables added to the group due to CASCADE.

So it tells the postgres to remove all rows that points to rows in truncated table.

On the other hard, RESTART IDENTITY:

Automatically restart sequences owned by columns of the truncated table(s).

Makes your autoincrement columns start from the beginning.

user_33614620
Related