Flask-SQLAlchemy: Neither SQLALCHEMY_DATABASE_URI nor SQLALCHEMY_BINDS is set. Defaulting SQLALCHEMY_DATABASE_URI to "sqlite:///:memory:"

Question

I am first time trying flask application factory pattern and pytest framework together. I started with a basic sanity test for the sqlite db backend and, although the tests are working fine so far and I see test db file created successfully, the falsk_sqlalchemy is telling me that it doesn't have a db backend defined. I tried to find the problem with pdb and the interactive console - everything looks normal. It looks like it is somehow related to could anyone help me understand where the problem is?

(venv) C:\Users\dv\PycharmProjects\ste-speach-booking>python -m pytest tests/
=========================== test session starts ============================
platform win32 -- Python 3.6.8, pytest-5.1.1, py-1.8.0, pluggy-0.12.0
rootdir: C:\Users\dv\PycharmProjects\ste-speach-booking
collected 3 items

tests\test_models.py ...                                              [100%]

============================= warnings summary =============================
tests/test_models.py::test_init
  C:\Users\d837758\PycharmProjects\ste-speach-booking\venv\lib\site-packages\flask_sqlalchemy\__init__.py:814: UserWarning: Neither SQLALCHEMY_DATABASE_URI nor SQLALCHEMY_BINDS is set. Defaulting SQLALCHEMY_DATABASE_URI to "sqlite:///:memory:".
    'Neither SQLALCHEMY_DATABASE_URI nor SQLALCHEMY_BINDS is set. '

initial tests in the test_models:

import pytest
import src.models
import datetime

def test_ActionTypes(db):
    actiontype1 = src.models.Act_types(action_tyoe='workshop')
    db.session.add(actiontype1)
    db.session.commit()
    actiontype2 = src.models.Act_types(action_tyoe='speech')
    db.session.add(actiontype2)
    db.session.commit()
    count = db.session.query(src.models.Act_types).count()
    assert count is 2

def test_meeting_creation(db):
    meeting = src.models.Meeting(
        _date = datetime.datetime.strptime('2018-12-19', "%Y-%m-%d"),
    )
    db.session.add(meeting)
    db.session.commit()

conftest fixture for the db:

import os
import pytest
import src.config
from src import create_app
from src import db as _db

@pytest.fixture(scope='session')
def db():
    """Session-wide test database."""
    TESTDB_PATH = src.config.testDB
    print(TESTDB_PATH)
    if os.path.exists(TESTDB_PATH):
        os.unlink(TESTDB_PATH)
    app = create_app(config=src.config.TestingConfig)
    with app.app_context():
        _db.create_all()
        yield _db  
        _db.drop_all()
    os.unlink(TESTDB_PATH)

app factory:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()

def create_app(config=None):
    """Construct the core application."""
    app = Flask(__name__, instance_relative_config=True)
    db.init_app(app)
    if config is None:
        app.config.from_object(config.BaseConfig)
    else:
        app.config.from_object(config)

    with app.app_context():
        # Imports
        from . import routes
        db.create_all()

        return app

config.py:

basedir = os.path.abspath(os.path.dirname(__file__))
baseDB = os.path.join(basedir, 'app.db')
devDB =  os.path.join(basedir, 'dev_app.db')
testDB = os.path.join(basedir, 'testing_app.db')

class BaseConfig(object):
    DEBUG = False
    TESTING = False
    SECRET_KEY = os.environ.get('SECRET_KEY') or 'you-will-never-guess'
    SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URL') or \
        'sqlite:///' + baseDB
    SQLALCHEMY_TRACK_MODIFICATIONS = False

class TestingConfig(BaseConfig):
    DEBUG = False
    TESTING = True
    SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URL') or \
        'sqlite:///' + testDB

Answer 1

The issue is with the order of configuration of the components of your application in create_app().

When you call db.init_app(app) the first operations it performs are (source):

        if (
            'SQLALCHEMY_DATABASE_URI' not in app.config and
            'SQLALCHEMY_BINDS' not in app.config
        ):
            warnings.warn(
                'Neither SQLALCHEMY_DATABASE_URI nor SQLALCHEMY_BINDS is set. '
                'Defaulting SQLALCHEMY_DATABASE_URI to "sqlite:///:memory:".'
            )

Recognize that warning?

Immediately it looks in app.config for required configurations. The method goes on to either accept the supplied configuration from the app or set a default, in this case the default is the in memory database.

In your implementation of create_app() the call to db.init_app() comes before the app itself is configured, with this:

    db.init_app(app)
    if config is None:
        app.config.from_object(config.BaseConfig)
    else:
        app.config.from_object(config)

Until app.config is populated, none of the SQLALCHEMY_ prefixed configurations exist on the app and so when db.init_app() goes looking for them, they aren't found and the defaults are used. Moving the config of db to after the config of the app fixes the issue:

    if config is None:
        app.config.from_object(config.BaseConfig)
    else:
        app.config.from_object(config)
    db.init_app(app)

This is quite similar to this question, however I think yours is a better example of a typical setup (scope of create_app() and configuration method) so worth answering.

Answer 2

Ensure the app.config dictionary has the following:

app = Flask(__name__) # The URI config should be initialized after flask
['SQLALCHEMY_DATABASE_URI'] = 'to your database string'

then:

db = SQAlchemy(app)

was having the same issue because I had the Flask initialization after the database Uri connection.