During tests, Flask-Session intermittently causes the exception “sqlalchemy.exc.InvalidRequestError: Table ‘sessions’ is already defined for this MetaData instance. Specify ‘extend_existing=True’ to redefine options and columns on an existing Table object.” I’m presenting a workaround in this post.

048-feature-images.png
Python: a workaround for SQLAlchemy “Table ‘sessions’ is already defined” exception during tests.

I’m using pytest, and I’ve been experiencing intermittent tests failure due to exception “sqlalchemy.exc.InvalidRequestError: Table ‘sessions’ is already defined for this MetaData instance. Specify ‘extend_existing=True’ to redefine options and columns on an existing Table object.”. It only occurs if some tests are run together, run them individually one at a time, they pass. I’ve ignored this problem till a few days ago. Right now I have around 396 ( three hundreds and ninety six ) test cases, just by chance, I run some three ( 3 ) tests together, and two ( 2 ) consistently fail with the mentioned exception, only one ( 1 ) passes.

It turns out this is an ongoing issue with Flask-Session 0.4.0, and still persists in the latest version 0.4.0. Latest as on the 24th, November 2022.

My research led to the following post, among other posts which report the same issue:

How do you resolve ‘Already defined in this MetaData Instance’ Error with Flask Pytest, SqlAlchemy, and Flask Sessions? The answer by user Tassaron on 01/01/2021 leads to the following URLs:

The general consensus seems to be only creating the Model for the database session table only if it does not already exists via the condition:

if table not in self.db.metadata:

It has been proposed a few years back, but for some reasons, it has not been implemented by the author.

Tassaron, herself, has implemented this in https://github.com/tassaron/muffin-shop/blob/main/src/helpers/main/session_interface.py:

class TassaronSessionInterface(SessionInterface):
    ...

    def __init__(self, app, db):
        ...

        if table not in self.db.metadata:
            # ^ Only create Session Model if it doesn't already exist
            # Fixes the SQLAlchemy "extend_existing must be true" exception during tests
            class Session(self.db.Model):
                ...
            self.sql_session_model = db.session_ext_session_model = Session
        else:
            self.sql_session_model = db.session_ext_session_model

Compared to the original Flask-Session 0.4.0:

class SqlAlchemySessionInterface(SessionInterface):
    ...

    def __init__(self, app, db, table, key_prefix, use_signer=False,
                 permanent=True):
        ...

        class Session(self.db.Model):
            ...

        self.sql_session_model = Session

In TassaronSessionInterface, when the Session Model is first created, it also gets assigned to db new attribute session_ext_session_model, afterwards db.session_ext_session_model is used.

Apart from exceptions intermittently raised during tests, Flask-Session 0.4.0 works fine. I want to stick to it as much as possible. Following is my attempt, it feels like a workaround, a hack rather than a solution, I’m okay with this for the time being:

Content of fixed_session.py

from flask_session.sessions import SqlAlchemySessionInterface
from flask_session import Session

class FixedSqlAlchemySessionInterface( SqlAlchemySessionInterface ):
    def __init__(self, app, db, table, key_prefix, use_signer=False,
                 permanent=True):
        """
        Assumption: the way I use it, db is always a valid instance 
        at this point.
        """
        if table not in db.metadata:
            super().__init__( app, db, table, key_prefix, use_signer, permanent )
            db.session_ext_session_model = self.sql_session_model
        else:
            # print( "`sessions` table already exists..." )

            self.db = db
            self.key_prefix = key_prefix
            self.use_signer = use_signer
            self.permanent = permanent
            self.has_same_site_capability = hasattr(self, "get_cookie_samesite")

            self.sql_session_model = db.session_ext_session_model
            
class FixedSession( Session ):
    def _get_interface(self, app):
        config = app.config.copy()

        if config[ 'SESSION_TYPE' ] != 'sqlalchemy':
            return super()._get_interface( app )

        else:
            config.setdefault( 'SESSION_PERMANENT', True )
            config.setdefault( 'SESSION_KEY_PREFIX', 'session:' )

            return FixedSqlAlchemySessionInterface(
                app, config['SESSION_SQLALCHEMY'],
                config['SESSION_SQLALCHEMY_TABLE'],
                config['SESSION_KEY_PREFIX'], config['SESSION_USE_SIGNER'],
                config['SESSION_PERMANENT'] )

To use this implementation, import FixedSession as Session, then carry on as normal:

try:
    from xxx.yyyy.fixed_session import FixedSession as Session
except ImportError:
    from flask_session import Session

When Flask-Session is fixed, I can just remove fixed_session.py without needing to update any codes – however, I should update the import, exceptions are expensive.

Back to my attempt, in FixedSqlAlchemySessionInterface, I copied the idea of db.session_ext_session_model from TassaronSessionInterface. The following lines are copied from the original codes:

            self.db = db
            self.key_prefix = key_prefix
            self.use_signer = use_signer
            self.permanent = permanent
            self.has_same_site_capability = hasattr(self, "get_cookie_samesite")

That means, if Flask-Session gets updated without fixing this issue, I might have to update my codes!

In class FixedSession, the following lines in the overridden method def _get_interface(self, app):

            config.setdefault( 'SESSION_PERMANENT', True )
            config.setdefault( 'SESSION_KEY_PREFIX', 'session:' )

are also copied from the original codes, I never have SESSION_PERMANENT and SESSION_KEY_PREFIX in my environment variables file.

With, or without the “sessions” table in the database, my tests and application run with no problems. If I drop “sessions” table, it gets created as expected.

It’s been fun investigating this issue. I’m not too happy with my code, but it works for me for the time being. Hopefully, the author will fix it in the future releases. In the meantime, I really do hope this post helps others whom come across this same problem. Thank you for reading and stay safe as always.