How can I test a Flask application which uses SQLAlchemy?

I suggest you use the Flask-Testing extension. This is an approved extension which lets you do the unit testing as you desire. It has a specific section for SQLAlchemy as well.

Testing with SQLAlchemy

This covers a couple of points if you are using Flask-Testing with SQLAlchemy. It is assumed that you are using the Flask-SQLAlchemy extension, but if not the examples should not be too difficult to adapt to your own particular setup.

First, ensure you set the database URI to something other than your production database ! Second, it’s usually a good idea to create and drop your tables with each test run, to ensure clean tests:"

from flask.ext.testing import TestCasefrom myapp import create_app, dbclass MyTest(TestCase):    SQLALCHEMY_DATABASE_URI = "sqlite://"    TESTING = True    def create_app(self):        # pass in test configuration        return create_app(self)    def setUp(self):        db.create_all()    def tearDown(self):        db.session.remove()        db.drop_all()

This is the way I've been running unit tests recently. I'm assuming since you are using SQLAlchemy that you're using model classes. I'm also assuming that all of your tables are defined as SQLAlchemy model classes.

from flask import Flaskimport unittestfrom app import dbfrom app.models import Logfrom constants import test_logsclass appDBTests(unittest.TestCase):    def setUp(self):        """        Creates a new database for the unit test to use        """        self.app = Flask(__name__)        db.init_app(self.app)        with self.app.app_context():            db.create_all()            self.populate_db() # Your function that adds test data.    def tearDown(self):        """        Ensures that the database is emptied for next unit test        """        self.app = Flask(__name__)        db.init_app(self.app)        with self.app.app_context():            db.drop_all()

Since you're using the same DB set up as your app this allows you to build and destroy a test database with every unit test you run.

Regarding codegeek's answer using Flask-Testing, I have trouble understanding what createapp() does. Flask-SqlAlchemy Introduction into Contexts provided me some pointer on how to bind SQLAlchemy object dynamically to your application. In this case, binding to the test application.

Basically:

1. Create a flask sqlalchemy object but do not pass in the app object
2. In the create_app function, create your test application, and dynamically bind SQLAlchemy.

Your myapp.py:

# don't pass in the app object yetdb = SQLAlchemy()def create_test_app():    app = Flask(__name__)    app.config['TESTING'] = True    app.config["SQLALCHEMY_DATABASE_URI"] = "xxxxxxtestdatabasexxx"    # Dynamically bind SQLAlchemy to application    db.init_app(app)    app.app_context().push() # this does the binding    return app# you can create another app context here, say for productiondef create_production_app():    app = Flask(__name__)    app.config["SQLALCHEMY_DATABASE_URI"] = "xxxxxxproductionxxxx"    # Dynamically bind SQLAlchemy to application    db.init_app(app)    app.app_context().push()    return app

You can then follow codegeek's solution as outlined in Flask-Test Documentation

from flask.ext.testing import TestCasefrom myapp import create_app, dbclass MyTest(TestCase):    # I removed some config passing here    def create_app(self):        return create_test_app()    def setUp(self):        db.create_all()    def tearDown(self):        db.session.remove()        db.drop_all()

The nice thing about this solution is that you can create different applications and dynamically bind the SQLAlchemy object using a function. Each application can serve different purposes. For example, one for production, and one for unit-test. In the case for production, you can call create_production_application() in your top level flask application.