Q:

How to properly run consecutive tests querying a Flask-SQLAlchemy database?

I'm setting up unit-testing for a Flask project using SQLAlchemy as ORM. For my tests I need to setup a new test database every time I run a single unit-test. Somehow, I cannot seem to run consecutive tests that query the database, even though if I run these tests in isolation they succeed.
I use the flask-testing package, and follow their documentation here. 
Here is a working example to illustrate the problem:
app.py:
from flask import Flask

def create_app():
    app = Flask(__name__)
    return app

if __name__ == '__main__':
    app = create_app()
    app.run(port=8080)

database.py:
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

models.py:
from database import db

class TestModel(db.Model):
    """Model for testing."""

    __tablename__ = 'test_models'
    id = db.Column(db.Integer,
                   primary_key=True
                   )

test/__init__.py:
from flask_testing import TestCase

from app import create_app
from database import db

class BaseTestCase(TestCase):
    def create_app(self):
        app = create_app()
        app.config.update({
            'SQLALCHEMY_DATABASE_URI': 'sqlite:///:memory:',
            'SQLALCHEMY_TRACK_MODIFICATIONS': False,
            'TESTING': True
        })
        db.init_app(app)
        return app

    def setUp(self):
        db.create_all()

    def tearDown(self):
        db.session.remove()
        db.drop_all()

test/test_app.py:
from models import TestModel
from test import BaseTestCase
from database import db

test_model = TestModel()

class TestApp(BaseTestCase):
    """WebpageEnricherController integration test stubs"""

    def _add_to_db(self, record):
        db.session.add(record)
        db.session.commit()
        self.assertTrue(record in db.session)

    def test_first(self):
        """
        This test runs perfectly fine
        """
        self._add_to_db(test_model)
        result = db.session.query(TestModel).first()
        self.assertIsNotNone(result, 'Nothing in the database')

    def test_second(self):
        """
        This test runs fine in isolation, but fails if run consecutively
        after the first test
        """
        self._add_to_db(test_model)
        result = db.session.query(TestModel).first()
        self.assertIsNotNone(result, 'Nothing in the database')

if __name__ == '__main__':
    import unittest
    unittest.main()

So, I can run TestApp.test_first and TestApp.test_second fine if run in isolation. If I run them consecutively, the first test passes, but the second test fails with:
=================================== FAILURES ===================================
_____________________________ TestApp.test_second ______________________________

self = <test.test_app.TestApp testMethod=test_second>

    def test_second(self):
        """
        This test runs fine in isolation, but fails if run consecutively
        after the first test
        """
        self._add_to_db(test_model)
        result = db.session.query(TestModel).first()
>       self.assertIsNotNone(result, 'Nothing in the database')
E       AssertionError: unexpectedly None : Nothing in the database

Something is going wrong in the database setup and teardown, but I cannot figure out what. How do I set this up correctly?

A:

The answer is that you are leaking state between one test and the next by reusing a single TestModel instance defined once in the module scope (test_model = TestModel()).
The state of that instance at the commencement of the first test is transient:

an instance that’s not in a session, and is not saved to the database;
  i.e. it has no database identity. The only relationship such an object
  has to the ORM is that its class has a mapper() associated with it.

The state of the object at commencement of the second test is detached:

Detached - an instance which corresponds, or previously corresponded,
  to a record in the database, but is not currently in any session. The
  detached object will contain a database identity marker, however
  because it is not associated with a session, it is unknown whether or
  not this database identity actually exists in a target database.
  Detached objects are safe to use normally, except that they have no
  ability to load unloaded attributes or attributes that were previously
  marked as “expired”.

This kind of interdependence between tests is almost always a bad idea. You could use make_transient() on the object at the end of every test:
class BaseTestCase(TestCase):
    ...
    def tearDown(self):
        db.session.remove()
        db.drop_all()
        make_transient(test_model)

Or you should construct a new TestModel instance for each test:
class BaseTestCase(TestCase):
    ...
    def setUp(self):
        db.create_all()
        self.test_model = TestModel()

class TestApp(BaseTestCase):
    ...
    def test_xxxxx(self):
        self._add_to_db(self.test_model)

I think the latter is the better choice as there is no danger of any other leaky state getting carried between tests.

