Integration Tests & Test Database

Relevant source files

The following files were used as context for generating this wiki page:

• tests/integration/test_oracle.py
• tests/artifacts/oracle-init/01-create-test-schema.sql
• tests/conftest.py
• tests/README.md
• CONTRIBUTING.md
• pyproject.toml

The Integration Tests and Test Database infrastructure for flext-target-oracle provide a robust environment for verifying the target's behavior against a real Oracle instance. This system ensures that DDL (Data Definition Language), DML (Data Manipulation Language), and bulk loading operations function correctly within an actual Oracle environment, moving beyond the isolated unit test layer.

The suite utilizes Docker-based automation to spin up an Oracle XE container, initialize a specialized test schema (FLEXT_TEST), and execute a variety of Singer-compliant workflows. This ensures high reliability for production data pipelines by validating type mappings, JSON storage, and performance characteristics under realistic conditions.

Sources: tests/README.md:1-15, tests/integration/test_oracle.py:1-12, CONTRIBUTING.md:46-75

Test Infrastructure Architecture

The testing architecture is built on pytest and leverages Docker Compose for lifecycle management of the Oracle database. The system is designed to be self-healing and automated, handling container startup, health checks, and schema preparation before tests begin.

Database Lifecycle and Automation

Integration tests are marked with @pytest.mark.integration and @pytest.mark.oracle. When these tests are triggered, the shared_oracle_container session-scoped fixture ensures the flext-oracle-db-test container is running and healthy. If the container is unavailable, the suite skips the dependent tests to avoid false negatives.

The diagram shows the automated flow from test invocation to database readiness and final cleanup.
Sources: tests/conftest.py:59-125, tests/README.md:43-55

Connection Configuration

The test suite defaults to a set of standard environment variables for database connectivity, which are automatically set by fixtures if not provided by the host environment.

Variable	Default Value	Description
FLEXT_TARGET_ORACLE_HOST	localhost	Oracle DB Host
FLEXT_TARGET_ORACLE_PORT	1521	TNS Listener Port
FLEXT_TARGET_ORACLE_SERVICE	XE	Oracle Service Name
FLEXT_TARGET_ORACLE_USER	FLEXT_TEST	Main Test User
FLEXT_TARGET_ORACLE_PASSWORD	test_password	Test User Password

Sources: tests/README.md:85-92, tests/conftest.py:141-155

Test Database Schema Initialization

The test database is initialized using a specialized SQL script that prepares the environment for multi-tenant and complex data structure testing. This script creates the target_test_user and grants the SELECT_CATALOG_ROLE and CREATE TABLE privileges required for the target to inspect and modify the database.

Core Test Objects

The initialization script creates several entities to simulate real-world data scenarios:

• Tables: test_customers, test_orders, test_order_items, and test_products.
• Sequences & Triggers: Automated ID generation for all primary keys.
• Indexes: Performance-optimized indexes on emails, categories, and foreign keys.
• Views: customer_orders and order_summary to verify relational integrity.

Sources: tests/artifacts/oracle-init/01-create-test-schema.sql:1-177

Schema Relationships

The test database defines specific relationships to validate foreign key handling and index creation within the loader.

The diagram represents the entity-relationship model initialized in the Oracle test container.
Sources: tests/artifacts/oracle-init/01-create-test-schema.sql:40-100

Integration Test Scenarios

The TestsFlextTargetOracleOracle class contains the core logic for verifying the target's integration with Oracle. Each test typically utilizes the clean_database fixture to ensure isolation.

DDL and Table Management

The suite verifies that the ensure_table_exists method correctly creates tables based on Singer JSON schemas. This includes checking for the existence of audit columns like _SDC_EXTRACTED_AT and _SDC_LOADED_AT.
Sources: tests/integration/test_oracle.py:65-95

Data Loading Modes

The integration tests cover three primary loading strategies:

1. Simple Insert: Basic record insertion and retrieval verification.
2. Merge Mode: Validates that existing records are updated rather than duplicated when sdc_mode="merge" is configured.
3. Bulk Insert: Performance validation using large datasets (e.g., 5000 records) to ensure bulk operations are efficient (under 10 seconds).

Sources: tests/integration/test_oracle.py:97-158

Advanced Storage and Mapping

• JSON Storage: Tests the ability to store complex nested Singer objects in an Oracle CLOB or JSON column using storage_mode="json".
• Column Ordering: Verifies that columns are created in a specific order (e.g., Primary Keys -> Regular -> Audit -> SDC) using column_ordering="alphabetical".
• Column Mapping/Filtering: Ensures that sensitive fields (like password) are ignored and other fields are renamed according to column_mappings.

Sources: tests/integration/test_oracle.py:195-236, tests/integration/test_oracle.py:293-345

Development Workflow and Markers

The project uses pytest markers to categorize and control test execution.

Marker	Usage	Execution Requirement
@pytest.mark.unit	Isolated logic tests	No database required
@pytest.mark.integration	Interaction tests	Oracle container required
@pytest.mark.oracle	Oracle-specific DDL/DML	Oracle container required
@pytest.mark.slow	Long-running benchmarks	Oracle container required

Sources: tests/README.md:73-83, tests/conftest.py:270-280

Running the Suite

Developers can run tests through the make contract or directly via pytest:

# Run all integration tests
make test-integration

# Run a specific test case
pytest tests/integration/test_oracle.py::TestsFlextTargetOracleOracle::test_merge_mode_updates -v

Sources: CONTRIBUTING.md:65-75, tests/README.md:28-41

Summary

The Integration Tests & Test Database system provides the necessary safety net for developing and maintaining the Oracle Singer target. By combining automated Docker orchestration with comprehensive SQL initialization and targeted integration scenarios, the project ensures that data integrity is maintained across various Oracle configurations and loading strategies.
Sources: tests/README.md:145-155, CONTRIBUTING.md:220-230