Flask-SQLAlchemy integration tests can't find a way to rollback changes

Question

I'm trying to learn flask technology stack and for my application I'm using Flask-SQLAlchemy. Everything works perfect, but I'm struggling with writing integration tests. I don't want to use SQLite since on production I'm using PostgreSQL and putting tons of mocks will actually more test my own implementation not the logic itself.

So, after some research I decided to implement tests that will write data in the test database and after each tests rollback the changes (for performance sake). Actually, I'm trying to implement something similar to this approach: http://sontek.net/blog/detail/writing-tests-for-pyramid-and-sqlalchemy.

My problem is creating correct transaction and being able to rollback it. Here is the code of my base class:

from flask.ext.sqlalchemy import SQLAlchemy
db = SQLAlchemy()

class MyAppIntegrationTestCase(unittest.TestCase):
    @classmethod
    def setUpClass(cls):
    app.config['TESTING'] = True
    app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql+psycopg2:///db_test'
    init_app()
    db.app = app
    db.create_all(app=app)

    @classmethod
    def tearDownClass(cls):
        db.drop_all(app=app)

    def setUp(self):
        db.session.rollback()
        self.trans = db.session.begin(subtransactions=True)

    def tearDown(self):
        self.trans.rollback()

When I'm trying to execute tests I got a following error:

Traceback (most recent call last):
 File "myapp/src/core/tests/__init__.py", line 53, in tearDown
   self.trans.rollback()
 File "myapp/venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 370, in rollback
   self._assert_active(prepared_ok=True, rollback_ok=True)
 File "myapp/venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 203, in _assert_active
   raise sa_exc.ResourceClosedError(closed_msg)
ResourceClosedError: This transaction is closed

I bet that this is the problem with scoped_session and that when I'm running tests it reuse one global session for all tests, but my knowledge in SQLAlchemy is not deep enough yet.

Any help will be highly appreciated! Thanks!

I'm not sure how flask handles the whole transaction thing (in a normal case it will commit/rollback at the end of a request). On an integration test, this operation is likely to happen (committing). There is not really a clean solution. Either disable this behaviour or remove the data manually. — javex

AdriVelaz AdriVelaz · Accepted Answer · 2015-07-23T03:34:42

You're tearDownClass and setUpClass are causing the issues.

The setUpClass is called once before all the tests, and the tearDownClass is after all the tests in the class.

So if you have 3 tests.

setUpClass is called

setUp is called

tearDown is called (You rollback, but you don't begin a session, this throws an error)

setUp is called (another rollback that's going to error)

etc...

Add a db.session.begin to your tearDown and you'll be fine.

from flask.ext.sqlalchemy import SQLAlchemy
db = SQLAlchemy()

class MyAppIntegrationTestCase(unittest.TestCase):
    @classmethod
    def setUpClass(cls):
        app.config['TESTING'] = True
        app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql+psycopg2:///db_test'
        init_app()
        db.app = app
        db.create_all(app=app)

    @classmethod
    def tearDownClass(cls):
        db.drop_all(app=app)

    def setUp(self):
        db.session.rollback()
        self.trans = db.session.begin(subtransactions=True)

    def tearDown(self):
        self.trans.rollback()
        db.session.begin()

Flask-SQLAlchemy integration tests can't find a way to rollback changes

3 Answers