When working with a database and ORM layer, we often have to create test databases, files, directories, or server processes. We may need to tear down a test database after the tests pass, to be sure that the other tests can run. We may not want to tear down a database after failed tests; we may need to leave the database alone so that we can examine the resulting rows to diagnose the test failures.

It's important to manage the scope of testing in a complex, multilayered architecture. Looking back at Chapter 12, Storing and Retrieving Objects via SQLite, we don't need to specifically test the SQLAlchemy ORM layer or the SQLite database. These components have their own test procedures outside our application tests. However, because of the way the ORM layer creates database definitions, SQL statements, and Python objects from our code, we can't easily mock SQLAlchemy and hope that we've used it properly. We need to test the way our application uses the ORM layer without digressing into testing the ORM layer itself.

One of the more complex test case setup situations will involve creating a database and then populating it with appropriate sample data for the given test. When working with SQL, this can involve running a fairly complex script of SQL DDL to create the necessary tables and then another script of SQL DML to populate those tables. The associated teardown will be another complex SQL DDL script.

This kind of test case can become long-winded, so we'll break it into three sections: a useful function to create a database and schema, the setUpClass() method, and the rest of the unit test.

Here's the create database function:

from Chapter_12.ch12_ex4 import Base, Blog, Post, Tag, assoc_post_tag
import datetime
import sqlalchemy.exc
from sqlalchemy import create_engine

def build_test_db(name="sqlite:///./data/ch17_blog.db"):
    """
    Create Test Database and Schema
    """
    engine = create_engine(name, echo=True)
    Base.metadata.drop_all(engine)
    Base.metadata.create_all(engine)
    return engine

This builds a fresh database by dropping all of the tables associated with the ORM classes and recreating the tables. The idea is to ensure a fresh, empty database that conforms to the current design, no matter how much that design has changed since the last time the unit tests were run.

In this example, we built an SQLite database using a file. We can use the in-memory SQLite database feature to make the test run somewhat more quickly. The downside of using an in-memory database is that we have no database that we can use to debug failed tests.

Here's how we use this in a TestCase subclass:

from sqlalchemy.orm import sessionmaker


class Test_Blog_Queries(unittest.TestCase):

    @staticmethod
    def setUpClass():
        engine = build_test_db()
        Test_Blog_Queries.Session = sessionmaker(bind=engine)
        session = Test_Blog_Queries.Session()

        tag_rr = Tag(phrase="#RedRanger")
        session.add(tag_rr)
        tag_w42 = Tag(phrase="#Whitby42")
        session.add(tag_w42)
        tag_icw = Tag(phrase="#ICW")
        session.add(tag_icw)
        tag_mis = Tag(phrase="#Mistakes")
        session.add(tag_mis)

        blog1 = Blog(title="Travel 2013")
        session.add(blog1)
        b1p1 = Post(
            date=datetime.datetime(2013, 11, 14, 17, 25),
            title="Hard Aground",
            rst_text="""Some embarrassing revelation. Including ☹ and ⚓""",
            blog=blog1,
            tags=[tag_rr, tag_w42, tag_icw],
        )
        session.add(b1p1)
        b1p2 = Post(
            date=datetime.datetime(2013, 11, 18, 15, 30),
            title="Anchor Follies",
            rst_text="""Some witty epigram. Including ☺ and ☀""",
            blog=blog1,
            tags=[tag_rr, tag_w42, tag_mis],
        )
        session.add(b1p2)

        blog2 = Blog(title="Travel 2014")
        session.add(blog2)
        session.commit()

We defined setUpClass() so that a database is created before the tests from this class are run. This allows us to define a number of test methods that will share a common database configuration. Once the database has been built, we can create a session and add data.

We've put the session maker object into the class as a class-level attribute, Test_Blog_Queries.Session = sessionmaker(bind=engine). This class-level object can then be used in setUp() and individual test methods.

Here is setUp() and two of the individual test methods:

def setUp(self):
    self.session = Test_Blog_Queries.Session()

def test_query_eqTitle_should_return1Blog(self):
    results = self.session.query(Blog).filter(Blog.title == "Travel 2013").all()
    self.assertEqual(1, len(results))
    self.assertEqual(2, len(results[0].entries))

def test_query_likeTitle_should_return2Blog(self):
    results = self.session.query(Blog).filter(Blog.title.like("Travel %")).all()
    self.assertEqual(2, len(results))

The setUp() method creates a new, empty session object from the class-level sessionmaker instance. This will ensure that every query is able to properly generate SQL and fetch data from the database using an SQLAlchemy session.

The query_eqTitle_should_return1Blog() test will find the requested Blog instance and navigate to the Post instances via the entries relationship. The filter() portion of the request doesn't really test our application definitions; it exercises SQLAlchemy and SQLite. The results[0].entries test in the final assertion is a meaningful test of our class definitions.

The query_likeTitle_should_return2Blog() test is almost entirely a test of SQLAlchemy and SQLite. It isn't really making meaningful use of anything in our application except the presence of an attribute named title in Blog. These kinds of tests are often left over from creating initial technical spikes. They can help clarify an application API, even if they don't provide much value as a test case.

Here are two more test methods:

def test_query_eqW42_tag_should_return2Post(self):
    results = self.session.query(Post).join(assoc_post_tag).join(Tag).filter(
        Tag.phrase == "#Whitby42"
    ).all()
    self.assertEqual(2, len(results))

def test_query_eqICW_tag_should_return1Post(self):
    results = self.session.query(Post).join(assoc_post_tag).join(Tag).filter(
        Tag.phrase == "#ICW"
    ).all()
    self.assertEqual(1, len(results))
    self.assertEqual("Hard Aground", results[0].title)
    self.assertEqual("Travel 2013", results[0].blog.title)
    self.assertEqual(
        set(["#RedRanger", "#Whitby42", "#ICW"]),
        set(t.phrase for t in results[0].tags),
    )

The query_eqW42_tag_should_return2Post() test performs a more complex query to locate the posts that have a given tag. This exercises a number of relationships defined in the classes. When both of the relevant blog entries are located, this test has been passed.

The query_eqICW_tag_should_return1Post() test, similarly, exercises a complex query. It tests the navigation from Post to the Blog instance which contains the Post via results[0].blog.title. It also tests navigation from Post to an associated collection of Tags via set(t.phrase for t in results[0].tags). We must use an explicit set() because the order of results in SQL is not guaranteed.

What's important about this Test_Blog_Queries subclass of TestCase is that it creates a database schema and a specific set of defined rows via the setUpClass() method. This kind of test setup is helpful for database applications. It can become rather complex and is often supplemented by loading sample rows from files or JSON documents, rather than coding the rows in Python.