sqlalchemy session not getting removed properly in flask testing

Question

I'm using Flask-Testing which says:

Another gotcha is that Flask-SQLAlchemy also removes the session instance at the end of every request (as should any threadsafe application using SQLAlchemy with scoped_session). Therefore the session is cleared along with any objects added to it every time you call client.get() or another client method.

However, I'm not seeing that. This test fails:

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
from flask.ext.testing import TestCase

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
db = SQLAlchemy(app)
@app.route('/')
def index():
  print 'before request:', `db.session`
  u = db.session.query(User).first()
  u.name = 'bob'
  return ''

class User(db.Model):
  id = db.Column(db.Integer, primary_key=True)
  name = db.Column(db.String)

class SessionTest(TestCase):

  def create_app(self):
    return app

  def test_remove(self):
    db.drop_all()
    db.create_all()

    u = User()
    u.name = 'joe'
    db.session.add(u)
    db.session.commit()
    client = app.test_client()
    client.get('/')
    print 'after request:', `db.session`
    print u.name
    assert u not in db.session

(Run with $ nosetests test_file.py to see it in action.)

stdout:

-------------------- >> begin captured stdout << ---------------------
before request: <sqlalchemy.orm.scoping.ScopedSession object at 0x10224c610>
after request: <sqlalchemy.orm.scoping.ScopedSession object at 0x10224c610>
bob

--------------------- >> end captured stdout << ----------------------

According to the docs, user u should not be in the session after a get request, but it is! Does anybody know why this is happening?

Furthermore, u.name is bob and not joe, even though the request never committed! (So I'm convinced it's the same session.)

For the record,

$ pip freeze | grep Flask
Flask==0.10.1
Flask-Bcrypt==0.5.2
Flask-DebugToolbar==0.8.0
Flask-Failsafe==0.1
Flask-SQLAlchemy==0.16
Flask-Script==0.6.2
Flask-Testing==0.4
Flask-Uploads==0.1.3
Flask-WTF==0.8

Answer 1

I'm pretty sure the confusion comes from the fact that sessions in SQLAlchemy are scoped, meaning that each request handler creates and destroys its own session.

This is necessary because web servers can be multi-threaded, so multiple requests might be served at the same time, each working with a different database session.

For this reason, the session that you used outside of the context of a request is likely not the same session that the view function that handles the '/' route gets and then destroys at the end.

UPDATE: I dug around a bit and figured this thing out.

Flask-SQLAlchemy installs a hook on app.teardown_appcontext, and here is where it calls db.session.remove().

The testing environment does not fully replicate the environment of a real request because it does not push/pop the application context. Because of that the session is never removed at the end of the request.

As a side note, keep in mind that functions registered with before_request and after_request are also not called when you call client.get().

You can force an application context push and pop with a small change to your test:

def test_remove(self):
  db.drop_all()
  db.create_all()

  u = User()
  u.name = 'joe'
  db.session.add(u)
  db.session.commit()
  with app.app_context():
      client = app.test_client()
      client.get('/')
  print 'after request:', `db.session`
  print u.name
  assert u not in db.session

with this change the test passes.

The documentation for Flask-Testing seems to be wrong or more likely outdated. Maybe things worked like they describe at some point, but that isn't accurate for current Flask and Flask-SQLAlchemy versions.

I hope this helps!