Use in-memory database separately for testing - how?

Question

I have this code:

my_app.py:

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

import os

app = Flask(__name__)
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
app.config["BASE_DIR"] = os.path.abspath(os.path.dirname(__file__))
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///" + os.path.abspath(os.path.join(app.config["BASE_DIR"], "app.db"))

db = SQLAlchemy(app)

from user import User

# create all tables
db.create_all()

if not User.query.filter_by(username="test").first():
    dummy_user = User(username="test", password="", email="")
    db.session.add(dummy_user)
    db.session.commit()

user.py:

from flask.ext.login import UserMixin

from my_app import db


class User(db.Model):
    __tablename__ = "users"

    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(255), nullable=False, unique=True)
    email = db.Column(db.String(255), nullable=False, unique=True)
    password = db.Column(db.String(255), nullable=False)

tests.py:

from flask.ext.testing import TestCase
from my_app import app, db
from user import User

import os
import unittest


class MyTestCase(TestCase):
    def create_app(self):
        app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///:memory:"
        app.config["TESTING"] = True
        return app

    def setUp(self):
        db.create_all()

    def tearDown(self):
        db.session.remove()
        db.drop_all()

    def test_dummy(self):
        assert User.query.count() == 0

if __name__ == '__main__':
    unittest.main()

How can I make the unit test work?

I want my unit tests to use an in-memory database separate from the main application, but apparently I can not do it the way I'm trying to (by just changing the SQLALCHEMY_DATABASE_URI value)

Thanks in advance!

There are some helpful bits in here: http://flask.pocoo.org/docs/0.10/config/#development-production — reptilicus, Feb 18 '16 at 23:56
I don't think that's helpful to my problem. I am already defining a different config than the one used for production. — John Reese, Feb 19 '16 at 00:47
The problem, I believe, is that you create the `db` from app when you import. Any changes to app after that have no effect. If you split the config and db into 2 files you can first import the app, then change it and last import db from the other module. — Goswin von Brederlow, Apr 20 '17 at 09:24

franklin · Answer 1 · 2016-02-19T05:01:24.353

Sounds like the perfect case for inherited configurations!

Have you tried using the template found here? The Config base class contains settings that are common to all your different environments. But you can have a development environment that uses an in-memory database. For example:

class Config:
  # pass

class DevConfig(Config):
  SQLALCHEMY_DATABASE_URI = 'sqlite://path_to_in_memory_db'

class ProductionConfig(Config):
  SQLALCHEMY_DATABASE_URI = 'postgresql://path_to_production_db'

It's also worth looking into using a factory pattern to create your app.

score 2 · Answer 2 · answered Dec 23 '19 at 22:40

I used environment variables to choose the db with pytest. This works well when using docker-compose to specify a production DB instead of sqllite for dev.

# app.py
BASE_DIR = os.path.abspath(os.path.dirname(__file__))
DATABASE = os.environ.get("DB_URI", f"sqlite:///{os.path.join(BASE_DIR, 'app.db')}")

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = DATABASE

And then in test.py inject the DB_URI environment variable to specify a memory db before you import the app or db:

import pytest
import os

os.environ['DB_URI'] = "sqlite:///:memory:"
from app import app, db
from models import Model1, Model2


@pytest.fixture
def client():
    client = app.test_client()

    with app.app_context():
        db.create_all()
        prepare_data()
    yield client

    db.drop_all()

score 0 · Answer 3 · answered Feb 19 '16 at 03:05

Okay, so I found a solution or rather a workaround:

I used an environment variable to specify an additional config file to be loaded before initializing the db instance.

So I ended up doing this:

app.config.from_object("app.config.production")
additional_config = os.environ.get("ADDITIONAL_CONFIG")
if additional_config:
    app.config.from_object(additional_config)

in my my_app.py and this in my tests.py:

os.environ["ADDITIONAL_CONFIG"] = "app.config.testing"

from my_app import app, db

(it is of course important to define the environment variable before importing the app object)

Thanks anyway.

Use in-memory database separately for testing - how?

3 Answers3