Improve Testing Performance & Stability In Now LMS

Aug 8, 2025 by Omar Yusuf 51 views

Improving Test Suite Performance and Stability in Now LMS

Introduction

In this article, we'll dive into the crucial improvements made to the testing suite of Now LMS, addressing significant performance and stability issues. The initial setup faced challenges, particularly with database interactions, causing tests to run excessively long or even fail under specific database configurations. Let's explore the problems, solutions, and the journey to a more robust and efficient testing environment. Our main goal is to ensure that the test suite runs smoothly across different database systems, providing reliable feedback on the application's functionality. This involves optimizing database interactions, streamlining test setups, and resolving specific issues that arose with PostgreSQL and MySQL. By implementing these changes, we aim to create a more maintainable and scalable testing process, which is essential for the long-term success of Now LMS. Improving the test suite not only enhances the reliability of our application but also significantly reduces the time and resources required for testing, allowing the development team to focus on delivering new features and improvements.

Problem: Initial Testing Challenges

The initial testing process revealed some critical issues that needed immediate attention. When running tests, we observed that while they passed correctly with SQLite, they failed with MySQL and took an excessively long time—up to 20 minutes—without finishing on PostgreSQL. This discrepancy highlighted significant performance bottlenecks and compatibility issues with different database backends. The root cause appeared to be an excessive number of calls to database.drop_all() and database.create_all() within the tests. These operations, which drop and recreate the entire database schema, are resource-intensive and can significantly slow down the testing process, especially on larger databases or systems with slower I/O. Furthermore, it was discovered that PostgreSQL connections were getting blocked after drop_all() operations, leading to further delays and potential test failures. This blocking issue was particularly concerning as it indicated a problem with session management and cleanup within the testing framework.

Specific Issues Identified

Database Incompatibility: Tests passed with SQLite but failed or timed out with MySQL and PostgreSQL.
Performance Bottleneck: Tests took up to 20 minutes to run on PostgreSQL due to excessive database operations.
Excessive Database Calls: Numerous calls to database.drop_all() and database.create_all() in every test.
PostgreSQL Blocking: PostgreSQL connections were getting blocked after drop_all() operations.

These issues underscored the need for a more efficient and database-agnostic testing strategy. We needed to reduce the overhead of database interactions, ensure proper session management, and create a flexible testing environment that could seamlessly support different database backends. The primary focus was on optimizing the database configuration and minimizing the number of full database resets during testing.

Solution: Optimizing the Testing Suite

To tackle the challenges, we implemented a comprehensive solution focused on improving database configuration, fixing PostgreSQL stability issues, and optimizing test files. The core of our solution was to centralize test configuration and introduce different fixture types tailored to specific testing needs. This approach allowed us to minimize unnecessary database operations and ensure that tests only created the database structures and data they required. Let's delve into the key steps taken to address these issues and enhance the overall testing process.

1. Improve Database Configuration

The first step was to add a centralized test configuration file (tests/conftest.py) that respects the DATABASE_URL environment variable. This variable allows users to specify the database connection URL, making it easier to switch between different database backends for testing. If the DATABASE_URL is not set, the system defaults to an in-memory SQLite database, which is lightweight and fast for tests that don't require a persistent database.

Implementation Details

Centralized Configuration: Created tests/conftest.py to manage database connections and fixtures.
Environment Variable: Respect DATABASE_URL environment variable for database connection.
Default to SQLite: Use SQLite in-memory database when DATABASE_URL is not set.

Efficient Fixture Types

To further optimize database interactions, we implemented different fixture types for varying test needs:

minimal_db_setup: Creates only the schema without any data population. This is ideal for tests that only need the database structure but not any specific data.
basic_config_setup: Creates the schema and essential configuration data. This fixture is suitable for tests that require basic settings but not a full dataset.
full_db_setup: Creates a complete database with predefined data. This fixture is specifically designed for tests that require a fully populated database, such as those navigating URLs with predefined IDs.

2. PostgreSQL Stability Fixes

PostgreSQL presented unique challenges due to connection blocking after drop_all() operations. To address this, we implemented proper session rollback and close operations before calling drop_all(). This ensures that all active transactions are properly terminated and connections are released, preventing database locks. Additionally, we implemented comprehensive error handling for database connection issues and fixed session cleanup procedures to ensure that connections are properly closed and resources are released.

Implementation Details

Session Rollback and Close: Added proper session rollback and close operations before drop_all().
Error Handling: Implemented comprehensive error handling for database connection issues.
Session Cleanup: Fixed session cleanup to prevent database locks.

3. Optimized Test Files

With the database configuration and stability issues addressed, we moved on to optimizing individual test files to use the appropriate fixtures. This involved analyzing each test file and determining the level of database setup required. For instance, test_vistas.py was updated to use full_db_setup because it requires predefined course and user IDs for URL navigation tests. Similarly, test_endtoend.py, test_i18n_caching.py, and test_webforms.py were updated to use the most appropriate fixtures, reducing unnecessary database operations. Additionally, test_multipledb.py was enhanced with better PostgreSQL and MySQL driver error handling to ensure robustness across different database systems.

Implementation Details

test_vistas.py: Updated to use full_db_setup.
test_endtoend.py, test_i18n_caching.py, test_webforms.py: Updated to use appropriate fixtures based on their needs.
test_multipledb.py: Enhanced with better PostgreSQL/MySQL driver error handling.

Proposed `tests/conftest.py` File

Below is the proposed tests/conftest.py file, which implements the core of our solution. This file defines the fixtures and database connection logic used throughout the testing suite.

# Copyright 2025 BMO Soluciones, S.A.
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#    http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#

"""Shared test configuration and fixtures."""

import os
import pytest
from sqlalchemy.exc import OperationalError, ProgrammingError
from pg8000.dbapi import ProgrammingError as PGProgrammingError
from pg8000.exceptions import DatabaseError

from now_lms import log

# Database URL configuration following the requirements:
# 1. If DATABASE_URL environment variable is defined with valid URL, use it
# 2. If not defined, use SQLite in-memory database by default
DB_URL = os.environ.get("DATABASE_URL")
if not DB_URL:
    # Default to SQLite in-memory database for tests
    DB_URL = "sqlite:///:memory:"

log.info(f"Using test database URL: {DB_URL}")


@pytest.fixture(scope="session")
def database_url():
    """Provide the database URL for tests."""
    return DB_URL


@pytest.fixture
def lms_application(database_url):
    """Create Flask application with test configuration."""
    from now_lms import app
    
    # Configure the application for testing
    app.config.update(
        {
            "TESTING": True,
            "SECRET_KEY": "test-secret-key-for-testing",
            "SQLALCHEMY_TRACK_MODIFICATIONS": False,
            "WTF_CSRF_ENABLED": False,
            "DEBUG": True,
            "PRESERVE_CONTEXT_ON_EXCEPTION": True,
            "SQLALCHEMY_DATABASE_URI": database_url,
        }
    )
    
    yield app


@pytest.fixture
def minimal_db_setup(lms_application):
    """
    Minimal database setup that only creates schema without full data population.
    Use this for tests that don't need the complete database setup.
    """
    from now_lms import database
    
    with lms_application.app_context():
        try:
            # Only create schema, don't populate with full data
            database.create_all()
            log.debug("Minimal database schema created.")
        except (OperationalError, ProgrammingError, PGProgrammingError, DatabaseError) as e:
            log.warning(f"Database setup error (continuing): {e}")
            
    yield lms_application  # Return the application with minimal database setup
    
    # Clean up after test
    with lms_application.app_context():
        try:
            # For PostgreSQL, handle potential rollback issues
            db_url = lms_application.config.get("SQLALCHEMY_DATABASE_URI", "")
            if "postgresql" in db_url.lower():
                database.session.rollback()
                database.session.close()
            database.drop_all()
            database.session.close()
            log.debug("Minimal database cleaned up.")
        except (OperationalError, ProgrammingError, PGProgrammingError, DatabaseError) as e:
            log.warning(f"Database cleanup warning: {e}")


@pytest.fixture
def full_db_setup(lms_application):
    """
    Full database setup with complete data population.
    Use this for tests that need the complete populated database (like test_vistas).
    """
    from now_lms import database, initial_setup
    
    with lms_application.app_context():
        try:
            # For PostgreSQL, ensure clean state before setup
            db_url = lms_application.config.get("SQLALCHEMY_DATABASE_URI", "")
            if "postgresql" in db_url.lower():
                try:
                    database.session.rollback()
                    database.session.close()
                except:
                    pass
                    
            # Full database setup with test data
            database.drop_all()
            initial_setup(with_tests=True, with_examples=False)
            log.debug("Full database setup completed.")
        except (OperationalError, ProgrammingError, PGProgrammingError, DatabaseError) as e:
            log.warning(f"Full database setup error (continuing): {e}")
            
    yield lms_application  # Return the application with the full database setup
    
    # Clean up after test
    with lms_application.app_context():
        try:
            # For PostgreSQL, handle potential rollback issues
            db_url = lms_application.config.get("SQLALCHEMY_DATABASE_URI", "")
            if "postgresql" in db_url.lower():
                database.session.rollback()
                database.session.close()
            database.drop_all()
            database.session.close()
            log.debug("Full database cleaned up.")
        except (OperationalError, ProgrammingError, PGProgrammingError, DatabaseError) as e:
            log.warning(f"Database cleanup warning: {e}")


@pytest.fixture
def basic_config_setup(lms_application):
    """
    Basic configuration setup that only creates essential configuration.
    For tests that need basic config but not full database.
    """
    from now_lms import database
    from now_lms.db.tools import crear_configuracion_predeterminada
    
    with lms_application.app_context():
        try:
            # Only create schema and basic configuration
            database.create_all()
            crear_configuracion_predeterminada()
            log.debug("Basic configuration setup completed.")
        except (OperationalError, ProgrammingError, PGProgrammingError, DatabaseError) as e:
            log.warning(f"Basic config setup error (continuing): {e}")
            
    yield lms_application  # Return the application with basic configuration
    
    # Clean up after test
    with lms_application.app_context():
        try:
            # For PostgreSQL, handle potential rollback issues
            db_url = lms_application.config.get("SQLALCHEMY_DATABASE_URI", "")
            if "postgresql" in db_url.lower():
                database.session.rollback()
                database.session.close()
            database.drop_all()
            database.session.close()
            log.debug("Basic configuration cleaned up.")
        except (OperationalError, ProgrammingError, PGProgrammingError, DatabaseError) as e:
            log.warning(f"Database cleanup warning: {e}")

This file defines three key fixtures: minimal_db_setup, full_db_setup, and basic_config_setup. Each fixture sets up the database in a specific way, catering to the varying needs of the tests. The use of these fixtures significantly reduces the overhead of database operations, making the tests run faster and more reliably.

Addressing MySQL Foreign Key Issue

During the testing process, a foreign key constraint issue was identified in MySQL, specifically in test_endtoend.py. This issue occurred when trying to insert a master class record due to a foreign key constraint failure related to the diploma_template_id. The error message indicated that a child row could not be added or updated because the foreign key constraint failed.

Error Details

sqlalchemy.exc.IntegrityError: (MySQLdb.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (lmsctl.master_classes, CONSTRAINT master_classes_ibfk_1 FOREIGN KEY (diploma_template_id) REFERENCES certificado (code))')
[SQL: INSERT INTO master_classes (title, slug, description_public, description_private, date, start_time, end_time, is_paid, price, early_discount, discount_deadline, platform_name, platform_url, is_certificate, diploma_template_id, video_recording_url, image_path, instructor_id, id, timestamp, creado, creado_por, modificado, modificado_por) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, now(), date(now()), %s, %s, %s)]
[parameters: ('Introducción al Machine Learning', 'intro-machine-learning', 'Aprende los fundamentos del Machine Learning en esta clase magistral gratuita.', 'Contenido exclusivo para estudiantes inscritos: ejercicios prácticos y Q&A session.', datetime.date(2025, 9, 6), datetime.time(14, 0), datetime.time(16, 0), 0, None, None, None, 'Zoom', 'https://zoom.us/j/1234567890', 1, '01HNP0TTQNTR03J7ZQHR09YMKK', None, None, 'admin', '01K23CFW1M2AWTAVM5J8GXC9PX', None, None, None)]

This error indicates that the diploma_template_id being inserted into the master_classes table does not exist in the certificado table. To resolve this, we need to ensure that the certificado table is populated with the necessary data before inserting into master_classes. This can be achieved by either creating the required certificado entry directly in the test or by using the full_db_setup fixture, which populates the database with predefined data, including the necessary certificate templates.

Addressing Legacy SQLAlchemy Usage

During the testing process, several warnings related to legacy SQLAlchemy usage were identified. These warnings indicate that the tests are using deprecated methods from Flask-SQLAlchemy, specifically concerning the use of flask_sqlalchemy.query.Query objects directly in SQL statement contexts. This usage will be disallowed in future releases, so it's essential to update the tests to use the latest SQLAlchemy query API.

Deprecation Warnings

The following deprecation warnings were observed:

SADeprecationWarning: Object <flask_sqlalchemy.query.Query object at 0x7f40cdad0b00> should not be used directly in a SQL statement context, such as passing to methods such as session.execute().  This usage will be disallowed in a future release.  Please use Core select() / update() / delete() etc. with Session.execute() and other statement execution methods.

These warnings were primarily observed in tests/test_vistas.py and are related to pagination queries. To address this, the tests need to be updated to use Core select(), update(), delete() etc. with Session.execute() and other statement execution methods. This involves rewriting the queries to use the SQLAlchemy Core API, which provides a more explicit and modern way to interact with the database.

Ensuring All Tests Pass

After implementing the above solutions, it is crucial to ensure that all tests continue to pass. This involves running the test suite with different database backends (SQLite, MySQL, PostgreSQL) to verify that the fixes have resolved the identified issues and haven't introduced any new problems. The following commands should be used to run the tests:

pip install -r test.txt
bash dev/test.sh

By running these commands, we can confirm that the test suite passes with all database configurations, ensuring the stability and reliability of the Now LMS application.

Conclusion

In conclusion, the improvements made to the Now LMS testing suite have significantly enhanced its performance and stability. By addressing the database configuration issues, PostgreSQL blocking problems, MySQL foreign key constraints, and legacy SQLAlchemy usage, we have created a more robust and efficient testing environment. These changes not only ensure the reliability of our application but also streamline the testing process, allowing the development team to focus on delivering high-quality features and improvements. The use of centralized configuration, tailored fixtures, and proper session management has transformed the testing suite from a bottleneck into a valuable asset for the Now LMS project. Ensuring the test suite is robust and efficient is crucial for maintaining the quality and stability of the Now LMS application, and these improvements have laid a strong foundation for future development and growth.

Keywords for SEO

Test Suite Optimization
Database Testing
PostgreSQL Stability
MySQL Foreign Key
SQLAlchemy Migration
Now LMS Testing
Test Automation
Continuous Integration
Database Fixtures
Testing Performance

FAQ

1. Why were the tests failing with MySQL and PostgreSQL?

The tests were failing due to excessive database operations (drop_all() and create_all()), PostgreSQL connection blocking, and foreign key constraint issues in MySQL.

2. How was the database configuration improved?

The database configuration was improved by centralizing the configuration in tests/conftest.py, respecting the DATABASE_URL environment variable, and defaulting to SQLite in-memory when the variable is not set.

3. What are the different fixture types used in the testing suite?

The fixture types used are minimal_db_setup (schema only), basic_config_setup (schema + essential configuration), and full_db_setup (complete database).

4. How was PostgreSQL stability ensured?

PostgreSQL stability was ensured by adding proper session rollback and close operations before drop_all(), implementing comprehensive error handling, and fixing session cleanup procedures.

5. What was the MySQL foreign key issue and how was it resolved?

The MySQL foreign key issue was due to a missing entry in the certificado table. It was resolved by ensuring that the certificado table is populated before inserting into master_classes, either by creating the entry directly or using the full_db_setup fixture.

6. What are the legacy SQLAlchemy warnings and how are they being addressed?

The legacy SQLAlchemy warnings indicate the use of deprecated methods. They are being addressed by updating the tests to use the SQLAlchemy Core API with Session.execute().

7. How can I run the tests to ensure they pass?

Run the tests using the following commands:

pip install -r test.txt
bash dev/test.sh

Introduction

Problem: Initial Testing Challenges

Specific Issues Identified

Solution: Optimizing the Testing Suite

1. Improve Database Configuration

Implementation Details

Efficient Fixture Types

2. PostgreSQL Stability Fixes

Implementation Details

3. Optimized Test Files

Implementation Details

Proposed tests/conftest.py File

Addressing MySQL Foreign Key Issue

Error Details

Addressing Legacy SQLAlchemy Usage

Deprecation Warnings

Ensuring All Tests Pass

Conclusion

Keywords for SEO

FAQ

1. Why were the tests failing with MySQL and PostgreSQL?

2. How was the database configuration improved?

3. What are the different fixture types used in the testing suite?

4. How was PostgreSQL stability ensured?

5. What was the MySQL foreign key issue and how was it resolved?

6. What are the legacy SQLAlchemy warnings and how are they being addressed?

7. How can I run the tests to ensure they pass?

Proposed `tests/conftest.py` File