How to do unit test on database connection function?

Question

I have a Flask app that use this function to connect to DB server:

@contextmanager
def db_connection(name):
    server = os.environ.get(name + '_SERVER')
    database = os.environ.get(name + '_DATABASE')
    username = os.environ.get(name + '_USERNAME')
    password = os.environ.get(name + '_PASSWORD')
    info = dict(server=server, database=database, user=username, password=password, timeout=timeout)
    conn = pymssql.connect(**info)
    conn.autocommit(True)

    try:
        yield conn
    finally:
        conn.close()

How to construct test cases? I assume for unit test case, I would just mock the pymssql.connect. For integration test, I need use a fixture to set up a test database, is it correct? Is there any examples?

What if the connection function has logging, app.logger.info("..."), the test complains about not setup app context properly. How to resolve it?

Answer 1

I figured it out as below. test ensure the conn is establish and released without connecting to a real db.

def test_db_connection(test_client):
    connection_mock = MagicMock()
    connection_mock.autocommit.return_value = None

    name = "DC1"
    server = os.environ.get(name + '_SERVER')
    database = os.environ.get(name + '_DATABASE')
    username = os.environ.get(name + '_USERNAME')
    password = os.environ.get(name + '_PASSWORD')
    timeout = int(os.environ.get(name + 'TIMEOUT_DB', 60))

    with patch('pymssql.connect', return_value=connection_mock) as connect_mock:
        with db_connection(name) as conn:
            connect_mock.assert_called_once_with(server=server,
                                                 database=database,
                                                 user=username,
                                                 password=password,
                                                 timeout=timeout)

            connection_mock.autocommit.assert_called_once_with(True)

        assert connection_mock.close.called

test fixture is

@pytest.fixture(scope='module')
def test_client():
    # Set the Testing configuration prior to creating the Flask application
    os.environ['CONFIG_TYPE'] = 'config.TestingConfig'
    flask_app = create_app()

    # Create a test client using the Flask application configured for testing
    with flask_app.test_client() as testing_client:
        # Establish an application context
        with flask_app.app_context():
            yield testing_client  # this is where the testing happens!