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 TestCase

from myapp import create_app, db

class 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 Flask
import unittest

from app import db
from app.models import Log
from constants import test_logs

class 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 yet
db = 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 production
def 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 TestCase
from myapp import create_app, db

class 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.