Back to all posts

Open SQL Test Doubles in ABAP

André Schärpf
sapabapsqltesting

The moment a SELECT runs inside an ABAP Unit test, the test borrows whatever rows happen to live on the current system. Different client, different test result. Someone deletes a customizing entry, the test goes red. The data generator hasn't run on a fresh box, the test goes red. Nothing about the production code changed.

ABAP developers tend to live with this. The usual responses are bad in different ways: trust the system data, build a test fixture and clean it up by hand, or wrap every SELECT in a tiny data access object (DAO) so the unit test can mock the wrapper. SAP added CL_OSQL_TEST_ENVIRONMENT to NetWeaver 7.52 as a fourth option, and it has aged well. The production code keeps its real Open SQL. The framework swaps the underlying table for a double during the test.

What the framework actually does

The framework is exposed through a single interface, IF_OSQL_TEST_ENVIRONMENT, and a single static factory:

DATA(env) = cl_osql_test_environment=>create(
  i_dependency_list = VALUE #( ( 'SCARR' ) ) ).

i_dependency_list is a list of database artifacts you want under control: transparent tables, classic views, CDS views, CDS view entities, CDS table entities, CDS projection views, CDS table functions, and external views. From the moment create( ) returns, every Open SQL statement the test executes against SCARR reads from a managed double instead of the real table. The double starts empty. You fill it with insert_test_data( ), empty it again with clear_doubles( ), and tear the whole thing down with destroy( ).

That last sentence is the entire mental model. Everything else is plumbing.

A working example

Here is the production class. It does plain Open SQL against SCARR and SFLIGHT. There is no interface, no seam, no test mode. Nothing about it would suggest the class is built for testability.

CLASS zcl_carrier_reader DEFINITION
  PUBLIC FINAL CREATE PUBLIC.

  PUBLIC SECTION.
    TYPES:
      BEGIN OF ts_carrier,
        carrid   TYPE scarr-carrid,
        carrname TYPE scarr-carrname,
      END OF ts_carrier,
      tt_carriers TYPE STANDARD TABLE OF ts_carrier WITH EMPTY KEY.

    METHODS list_carriers
      RETURNING VALUE(rt_result) TYPE tt_carriers.

    METHODS find_by_id
      IMPORTING iv_carrid      TYPE scarr-carrid
      RETURNING VALUE(rv_name) TYPE scarr-carrname.

    METHODS count_flights
      RETURNING VALUE(rv_count) TYPE i.
ENDCLASS.

CLASS zcl_carrier_reader IMPLEMENTATION.

  METHOD list_carriers.
    SELECT carrid, carrname FROM scarr
      ORDER BY carrid
      INTO TABLE @rt_result.
  ENDMETHOD.

  METHOD find_by_id.
    SELECT SINGLE carrname FROM scarr
      WHERE carrid = @iv_carrid
      INTO @rv_name.
  ENDMETHOD.

  METHOD count_flights.
    SELECT COUNT(*) FROM sflight
      INTO @rv_count.
  ENDMETHOD.

ENDCLASS.

The local test class registers SCARR as a dependency and seeds two rows in setup. SFLIGHT is deliberately left out of the dependency list to show what happens to tables the framework does not know about.

CLASS ltcl_carriers DEFINITION
  FOR TESTING DURATION SHORT RISK LEVEL HARMLESS.

  PRIVATE SECTION.
    CLASS-DATA test_environment TYPE REF TO if_osql_test_environment.
    DATA carrier_reader TYPE REF TO zcl_carrier_reader.

    CLASS-METHODS class_setup.
    CLASS-METHODS class_teardown.
    METHODS setup.

    METHODS:
      list_returns_doubles      FOR TESTING,
      find_returns_match        FOR TESTING,
      empty_after_clear         FOR TESTING,
      unmocked_table_passes     FOR TESTING,
      insert_unregistered_raises FOR TESTING,
      writes_go_to_double       FOR TESTING.
ENDCLASS.

CLASS ltcl_carriers IMPLEMENTATION.

  METHOD class_setup.
    test_environment = cl_osql_test_environment=>create(
                         i_dependency_list = VALUE #( ( 'SCARR' ) ) ).
  ENDMETHOD.

  METHOD class_teardown.
    test_environment->destroy( ).
  ENDMETHOD.

  METHOD setup.
    DATA seed TYPE STANDARD TABLE OF scarr.

    carrier_reader = NEW #( ).
    test_environment->clear_doubles( ).
    seed = VALUE #(
      ( mandt = sy-mandt carrid = 'AA' carrname = 'American Airlines' currcode = 'USD' url = '' )
      ( mandt = sy-mandt carrid = 'LH' carrname = 'Lufthansa'         currcode = 'EUR' url = '' )
    ).
    test_environment->insert_test_data( seed ).
  ENDMETHOD.

  METHOD list_returns_doubles.
    DATA(actual) = carrier_reader->list_carriers( ).
    cl_abap_unit_assert=>assert_equals( exp = 2 act = lines( actual ) ).
    cl_abap_unit_assert=>assert_equals( exp = 'AA' act = actual[ 1 ]-carrid ).
    cl_abap_unit_assert=>assert_equals( exp = 'LH' act = actual[ 2 ]-carrid ).
  ENDMETHOD.

  METHOD find_returns_match.
    cl_abap_unit_assert=>assert_equals(
      exp = 'Lufthansa'
      act = carrier_reader->find_by_id( 'LH' ) ).
  ENDMETHOD.

  METHOD empty_after_clear.
    test_environment->clear_doubles( ).
    cl_abap_unit_assert=>assert_initial( carrier_reader->list_carriers( ) ).
  ENDMETHOD.

  METHOD unmocked_table_passes.
    DATA(count) = carrier_reader->count_flights( ).
    cl_abap_unit_assert=>assert_true( xsdbool( count >= 0 ) ).
  ENDMETHOD.

  METHOD insert_unregistered_raises.
    DATA flights TYPE STANDARD TABLE OF sflight.
    DATA raised TYPE abap_bool.

    flights = VALUE #(
      ( mandt = sy-mandt carrid = 'AA' connid = '0017' fldate = '20260101' ) ).
    TRY.
        test_environment->insert_test_data( flights ).
      CATCH cx_root.
        raised = abap_true.
    ENDTRY.

    cl_abap_unit_assert=>assert_equals( exp = abap_true act = raised ).
  ENDMETHOD.

  METHOD writes_go_to_double.
    DATA carrier TYPE scarr.
    DATA actual_name TYPE scarr-carrname.
    DATA updated_name TYPE scarr-carrname VALUE 'Updated Name'.

    test_environment->clear_doubles( ).
    carrier = VALUE #(
      mandt = sy-mandt carrid = 'ZZ' carrname = 'Inserted Name' currcode = 'USD' url = '' ).

    INSERT scarr FROM @carrier.
    UPDATE scarr SET carrname = @updated_name WHERE carrid = @carrier-carrid.
    SELECT SINGLE carrname FROM scarr WHERE carrid = @carrier-carrid INTO @actual_name.
    cl_abap_unit_assert=>assert_equals( exp = updated_name act = actual_name ).

    carrier-carrname = 'Modified Name'.
    MODIFY scarr FROM @carrier.
    SELECT SINGLE carrname FROM scarr WHERE carrid = @carrier-carrid INTO @actual_name.
    cl_abap_unit_assert=>assert_equals( exp = carrier-carrname act = actual_name ).

    DELETE FROM scarr WHERE carrid = @carrier-carrid.
    cl_abap_unit_assert=>assert_initial( carrier_reader->list_carriers( ) ).
  ENDMETHOD.

ENDCLASS.

The SELECT statements inside the production class are unchanged from how they would look in shipped code. Nothing in ZCL_CARRIER_READER knows about the double; the redirection is configured entirely by the test fixture.

The lifecycle in one paragraph

create( ) lives in class_setup, because building the doubles is expensive and the dependency list rarely changes between test methods. clear_doubles( ) lives in setup, so each test method starts from a known empty state. insert_test_data( ) lives wherever the test's "given" section makes sense, usually in setup for shared rows and inside the test method for scenario-specific rows. destroy( ) lives in class_teardown and removes the doubles. Skip destroy( ) and the doubles linger in the database until garbage collection cleans them up. The framework provides cl_osql_test_environment=>garbage_collection( ) for that, but the cleaner habit is to call destroy( ) and not depend on cleanup.

A subtle point worth knowing: clear_doubles( ) empties the double, it does not switch back to the real table. The test method empty_after_clear above proves this. After clear_doubles( ) and no insert_test_data( ), list_carriers( ) returns zero rows even though the live SCARR table has plenty. The framework remains in the path. To temporarily route a SELECT to the real database, IF_OSQL_TEST_ENVIRONMENT exposes disable_double_redirection( ) and enable_double_redirection( ). Use sparingly. A test that toggles redirection mid-flight is usually trying to be two tests at once.

Writes are redirected too

The redirection is not read-only. If the registered dependency is a database table, SAP documents both ABAP SQL reads and writes as redirected to the double table.1 In practical terms, INSERT, UPDATE, MODIFY, and DELETE against SCARR affect the test double while redirection is enabled. The writes_go_to_double test above clears the double, inserts ZZ, updates it, modifies it, deletes it again, and then reads through the production class to verify the double is empty.

That does not make unit tests a good place for broad persistence workflows. It just means database-writing production code can still be tested without touching the real table, as long as every written table is in i_dependency_list.

Tables you forgot to register

The unmocked_table_passes method is the interesting one. count_flights( ) reads SFLIGHT. The test class registered only SCARR. The SELECT COUNT(*) runs against the real SFLIGHT and returns whatever number the system happens to hold. The framework does not warn, does not fail, does not log. The query is simply not redirected.

This is the failure mode worth internalizing. A test that uses CL_OSQL_TEST_ENVIRONMENT looks isolated even when it is not. If the production code reads five tables and the dependency list mentions four, one statement still touches the live database, and the test can flip green or red based on data the test author never controlled. The framework does exactly what you ask of it. Anything you forget to ask for stays real.

The corollary: insert_test_data( ) only accepts data for tables that were passed to create( ). Trying to seed SFLIGHT while SFLIGHT is not in the dependency list raises an exception. The framework has nowhere to put the rows.

Why this beats wrapping every SELECT

The architectural reflex when faced with untestable database code is to extract a repository or DAO: ZCL_CARRIER_REPOSITORY with read_by_id( ) and read_all( ), an interface to inject a fake, and a tidy unit test that asserts on the fake's stub data. That is the right answer when persistence is a real boundary in the domain. It is the wrong answer when the only motivation is testability.

A repository class with one method per SELECT adds indirection without adding isolation. The fake's stubbed return value never executes the original Open SQL. A wrong join, a mistyped WHERE clause, a missing client handling, a typed-field mismatch: none of it is exercised by the test. The fake just returns whatever the test author already believed.

CL_OSQL_TEST_ENVIRONMENT keeps the actual SELECT in the path. The query is parsed, joins are executed, the WHERE clause filters, and the result is mapped back into the receiving variable. The only thing you replaced is the row source. A broken query still fails the test.

That is a stronger guarantee than a mocked DAO can give, with less code in the production class.

Compared to TEST-SEAM

TEST-SEAM and TEST-INJECTION solve a different problem. They cut a hole in the production code that a test can fill with a different statement block.

TEST-SEAM read_carrier.
  SELECT SINGLE carrname FROM scarr
    INTO @rv_name
    WHERE carrid = @iv_carrid.
END-TEST-SEAM.

TEST-INJECTION read_carrier.
  rv_name = 'Lufthansa'.
END-TEST-INJECTION.

The seam version is useful for legacy code that cannot be refactored safely, for authority checks, for spots where Open SQL is not the right abstraction. It is also blunter than what the OSQL framework offers: the injected block replaces the production block entirely, so the original SELECT is no longer compiled into the executed path. The test can pass while the real SELECT is broken in ways nobody notices until production.

The Clean ABAP guide treats test seams as a temporary workaround, not a default design technique.2 SAP's keyword documentation frames them as a way to replace specific statements that block unit tests, including persistent reads and writes.3 The OSQL framework is the better tool whenever the dependency is ABAP SQL itself.

What the framework does not solve

Open SQL test doubles are not a license to skip integration tests.

The framework controls one layer: the data sources used by ABAP SQL inside ABAP Unit. It does not validate that real customizing exists, that a database-specific execution plan is acceptable, that locks behave correctly under load, that update task behavior matches expectations, or that a CDS view with a non-trivial join chain returns what you think it does end-to-end. A doubled CDS view returns whatever you seeded into the double, regardless of how the underlying entities would actually behave together.

CDS access control is a separate caveat. SAP's documentation says DCL access-control logic is disabled for dependent CDS objects during these tests.1 If DCL behavior matters, test it explicitly with the framework's access-control double or with a separate integration test that runs against the real authorization setup.

Test data design also stays a real problem. A doubled table is empty, and large VALUE #( ... ) blocks turn ugly fast. Build helper methods named for the scenario (given_two_active_carriers, given_a_carrier_with_no_flights) and keep each test's seed minimal. If the setup is bigger than the behavior under test, the test design is wrong, not the framework.

Some practical limits worth knowing:

  • The framework arrived in NetWeaver 7.52. Older systems do not have it.
  • Database views and database functions are read-only, but the framework still allows seeding their doubles. This works for the test, but it does not mirror how the real artifact would compute its result.
  • For wide tables or deep CDS hierarchies, fixture data becomes the bottleneck before the framework does. Test Data Containers (insert_from_tdc( )) exist for this and trade inline visibility for less noise.4

Unit, integration, and accidental integration

Two categories of tests are worth keeping on purpose:

  1. A unit test that controls its dependencies and asserts behavior in a small scope.
  2. An integration test that intentionally uses real dependencies to verify wiring, customizing, authorizations, or end-to-end behavior.

The third category is the accidental one: a test that uses real dependencies because nobody isolated them, but still runs in the unit test bucket. It is neither a useful unit test nor an honest integration test. It has the cost of both and the guarantees of neither.

Most of the pain attributed to "ABAP Unit is unreliable" comes from that accidental category. CL_OSQL_TEST_ENVIRONMENT exists to move database-heavy tests out of it. Real integration tests still need to exist, named and bucketed accordingly, run on schedules where flakiness is tolerable. The goal is not to pretend integration risk disappeared. The goal is to stop smuggling integration risk into unit tests by accident.

A short checklist

When writing a database-isolated ABAP Unit test:

  • List every Open SQL data source the production code reaches, and put each one in i_dependency_list. Missing entries silently pass through to the real database.
  • Create the environment in class_setup, destroy it in class_teardown.
  • Call clear_doubles( ) at the start of setup so each test starts clean.
  • Insert only the rows the current test needs. Use named helper methods for fixtures shared across tests.
  • Let production Open SQL run unchanged. The point of the framework is that the SELECT is real.
  • Assert the production code's observable behavior, not the framework's internals.
  • Keep an integration test bucket for the things CL_OSQL_TEST_ENVIRONMENT cannot prove: real customizing, real authorizations, real database execution, real RAP behavior.

The framework is small and well-scoped. The hardest part is remembering which tables the code actually touches, which is the same skill that makes any test of database-heavy code work in the first place.

Further reading

Sources

Footnotes

  1. SAP Help, ABAP SQL Test Double Framework ↩ ↩2

  2. SAP Clean ABAP Style Guide, Use Test Seams as Temporary Workaround ↩

  3. SAP Help, Test Seams ↩

  4. abapedia, IF_OSQL_TEST_ENVIRONMENT ↩