Temporary Tables in Tests

The theme for this series is Super fast tests covering 100% of your code.

This 2025 series of articles is a continuation of the 2024 series called the Clean Code Initiative.

Clean Code is a condition for writing good tests, but it does not guarantee code coverage by tests.

Scenario

The app we are building communicates with an external API.

We face several key requirements:

  • The app can only be in production mode when running in a production environment. If running in a sandbox, it must operate in test mode.
  • Our tests must cover all code paths, including those only reachable in production mode.
  • Our tests cannot call the actual API, but we still need to verify both the requests we send and how we decode the API responses.
  • Tests must be extremely fast, with runtimes measured in milliseconds.
  • Writing tests should be quick and cost-effective.
  • We want to test only our own code, assuming that dependencies and external systems work as expected. We assume everyone else knows what they are doing

Temporary Tables will satisfy the need for speed and isolation from the database.

Avoid Touching the Database in Tests

We have looked at Environment Interfaces and how they save us time and improve speed, but there is one more thing to avoid: touching the database. Here, we have to think differently—we are not going to develop an interface towards the database, and we don’t have to.

If we want to run hundreds of tests in seconds, we have to change the way we write functions. While the Clean Code Initiative told you to stick to the standard patterns, we are now going to challenge this a little bit in some cases.

Pure Functions

First of all, we want to have pure functions—that is, functions with no side effects. Touching the database is a side effect.

This has important consequences for where we place database operations like Record.Get(), Record.Modify(), Record.Insert(), and Record.FindSet() in our code. In this project, they are now all at the top level.

Passing Records by Reference

To achieve fast, isolated tests, we use temporary tables, which run entirely in memory. When a function operates on a temporary record, you must pass that record by reference using the var keyword. This ensures that any changes made to the record inside the function are retained and that the function works with the same in-memory instance. In the example below, both EncodePhoneNumber and the helper method GetPhoneNumber take Setup as a var parameter, allowing the test to run against a temporary, in-memory version of the table instead of querying or modifying the actual database.

internal procedure EncodePhoneNumber(var Setup: Record Setup; ObjectKey: Text; PhoneNumber: Text; var Object: JsonObject)
var
    PhoneNumberAsText: Text;
begin
    PhoneNumberAsText := this.GetPhoneNumber(Setup, PhoneNumber);
    Object.Add(ObjectKey, PhoneNumberAsText);
end;

local procedure GetPhoneNumber(var Setup: Record Setup; PhoneNumber: Text): Text
begin
    if Setup."Application State" <> Enum::"Application State"::Production then
        exit(Setup."Phone Number Test");
    exit(PhoneNumber);
end;

Previously, I would simply have made a Setup.Get() at the bottom level where I needed the information. This new approach forces me to have a well organized architecture of my code.

Testing in the context of a production environment

[Test]
internal procedure TestEncodePhoneNumberProduction()
var
    TempEasyPaymentSetup: Record "Easy Payment Setup" temporary;
    Decoders: Codeunit Decoders;
    Encoders: Codeunit Encoders;
    Object: JsonObject;
    CalculatedPhoneNumber: Text;
    CustomerPhoneNumber: Text;
begin
    // [SCENARIO #015] Testing encoding a phone number to a JSON object in a production environment.
    // [GIVEN] A phone number
    // [WHEN] encoded to JSON object
    // [THEN] we should get the same phone number

    this.TestHelper.InitializeSetup(TempEasyPaymentSetup);
    TempEasyPaymentSetup."Application State" := Enum::"Application State"::Production;
    CustomerPhoneNumber := '1234567890';
    Encoders.EncodePhoneNumber(TempEasyPaymentSetup, 'Test', CustomerPhoneNumber, Object);
    Decoders.DecodeText(Object, 'Test', CalculatedPhoneNumber);
    this.Assert.AreEqual(CustomerPhoneNumber, CalculatedPhoneNumber, 'In the production environment we expected to encode the customer phone number directly.');
end;

Testing in the context of a test environment

[Test]
internal procedure TestEncodePhoneNumberTest()
var
    TempEasyPaymentSetup: Record "Easy Payment Setup" temporary;
    Decoders: Codeunit Decoders;
    Encoders: Codeunit Encoders;
    Object: JsonObject;
    CalculatedPhoneNumber: Text;
    CustomerPhoneNumber: Text;
begin
    // [SCENARIO #016] Testing encoding a phone number to a JSON object in a test environment.
    // [GIVEN] A phone number
    // [WHEN] encoded to JSON object
    // [THEN] we should get the test phone number

    this.TestHelper.InitializeSetup(TempEasyPaymentSetup);
    TempEasyPaymentSetup."Application State" := Enum::"Application State"::Test;
    TempEasyPaymentSetup."Phone Number Test" := '1234567890';
    CustomerPhoneNumber := '0987654321';
    Encoders.EncodePhoneNumber(TempEasyPaymentSetup, 'Test', CustomerPhoneNumber, Object);
    Decoders.DecodeText(Object, 'Test', CalculatedPhoneNumber);
    this.Assert.AreEqual(TempEasyPaymentSetup."Phone Number Test", CalculatedPhoneNumber, 'In the test environment we expected to ignore the customer phone number and encode the test phone number from the setup.');
end;

Running the actual code

In the actual code, the Setup.Get() is executed once at the highest level. This also gives me a better understanding of all dependencies on the setup table in the code. That is, we do not retrieve the setup table in multiple places throughout the code. We get it once and pass it around by reference.

Conclusion

This new design has several advantages:

  • Better performance: Fewer reads to the database.
  • Fast, isolated tests: Run tests without database access using just temporary tables.
  • Improved code overview: Dependencies are clear and easy to manage.

Overall, this approach leads to a better design, even though it is not entirely how we used to write code. It is essential for writing modern, maintainable, and high-performance AL test code.

Clean AL Code Initiative

Super fast tests covering 100% of your code