Database testing with the builder pattern

If you are anything like me, you try to at least include some unit tests into your projects to keep your sanity while making changes to core functionality. The problem (which is of course the main feature) with unit tests in the Oracle database (or any database with stored procedures for that matter) is that these stored procedure tend to work with that data rather closely. You cannot easily mock the database, since you are already in the middle of it. Combine this with the lack of certain language features (I am looking at you PL/SQL…please give us proper interfaces) and you get some especially gnarly code to test. This blog post will shortly motivate the builder pattern (inspired by Chapter 10 of Effective Software Testing which was inspired by Nat Pryce) to create your test data in an intuitive way and showcase a small tool I created that does the boring stuff for you (you can just skip the fluff and get it on my GitHub).

How do we setup data?

For a large portion of our stored procedures we will need to setup data to properly test them. In many cases this is not much and can be done with a few inserts. But still, this can become quite unwieldy rather quickly. I stumbled upon this blog post from Mike Smithers a while ago, in which he presents the idea of populating collections before the test, then inserting that data into the tables and use those same collections to verify the test results. This idea is intriguing but, unfortunately, it didn’t work out for me with more complex logic as I found myself replicating business logic in my tests all the time which is a bad practice, of course.

What is the builder pattern?

The builder pattern is a software pattern describing the process of building out an object piece-by-piece by refining it with each function call. You do this by chaining them together like this:

my_object
  .change_text('New text')
  .change_some_more(123)
  .almost_there('2024-12-09')
  .done();

By reading this code you immediately get an idea of what information is included in the object. The last part done() can do anything with the object. In our case this will be inserting it into the database.

How to use the builder pattern in the database?

In the database, we can use the builder pattern to build rows we can then insert into our tables. You might argue that we can do this Insert-Statements just as well. The advantages of the builder pattern are:

1. It provides a more direct connection between column and data. You can see the data inserted into each column at a glance.
2. It can take advantage of defaults to greatly reduce the number of parameters you have to set.
3. It can be used to combine independent scenarios for specific test cases. (Which is the one benefit over classic TAPIs)

How do these two look like in practice? Assume that we have a table with the following structure:

create table my_user_table (
  id number,
  first_name varchar2(300),
  last_name varchar2(300),
  date_of_birth date
);

We can create a builder-pattern-style object that would look something like this:

create type my_user_table_builder_type authid current_user as object
(
  id number,
  first_name varchar2(300),
  last_name varchar2(300),
  date_of_birth date,

  constructor function my_user_table_builder_type return self as result,
  member procedure build(
    self in my_user_table_builder_type
  ),

  member function with_id(
    self in my_user_table_builder_type,
    i_id number
  ) return my_user_table_builder_type,
  
  member function with_first_name(
    self in my_user_table_builder_type,
    i_first_name varchar2
  ) return my_user_table_builder_type,
  
  member function with_last_name(
    self in my_user_table_builder_type,
    i_last_name varchar2
  ) return my_user_table_builder_type,
  
  member function with_date_of_birth(
    self in my_user_table_builder_type,
    i_date_of_birth date
  ) return my_user_table_builder_type

);
/

create type body my_user_table_builder_type as
  constructor function my_user_table_builder_type return self as result as
  begin
    return;
  end my_user_table_builder_type;

  member procedure build(
    self in my_user_table_builder_type
  ) as
  begin
    insert into my_user_table(id, first_name, last_name, date_of_birth)
    values (self.id, self.first_name, self.last_name, self.date_of_birth);
  end build;


  member function with_id(
    self in my_user_table_builder_type,
    i_id number
  ) return my_user_table_builder_type is
    -- This is necessary to enable chaining multiple function calls
    l_self my_user_table_builder_type := self;
  begin
    l_self.id := i_id;
    return l_self;
  end with_id;

  member function with_first_name(
    self in my_user_table_builder_type,
    i_first_name varchar2
  ) return my_user_table_builder_type is
    l_self my_user_table_builder_type := self;
  begin
    l_self.first_name := i_first_name;
    return l_self;
  end with_first_name;

  member function with_last_name(
    self in my_user_table_builder_type,
    i_last_name varchar2
  ) return my_user_table_builder_type is
    l_self my_user_table_builder_type := self;
  begin
    l_self.last_name := i_last_name;
    return l_self;
  end with_last_name;

  member function with_date_of_birth(
    self in my_user_table_builder_type,
    i_date_of_birth date
  ) return my_user_table_builder_type is
    l_self my_user_table_builder_type := self;
  begin
    l_self.date_of_birth := i_date_of_birth;
    return l_self;
  end with_date_of_birth;

end;
/

Which would enable us to do this:

begin
  my_user_table_builder_type()
    .with_id(1)
    .with_first_name('Jakob')
    .with_last_name('Drees')
    .with_date_of_birth(to_date('1992-09-12', 'yyyy-mm-dd'))
    .build();
end;

I hope you can see the beauty of this code. You can immediately understand what data is used for which column. It should also make clear how we can leverage default values to minimize our setup effort. If you often need a user in the table but don’t really care about its name and date of birth you can just set a dummy user as default values in the type definition and only use this snippet to create a fully fledged user:

begin
  my_user_table_builder_type().build();
end;

You can also think of more complex functions to add to this object that might save you some time. If we had a lot of logic relying on certain ages for our users (being older than 18, for example), you can just create a function that tells the caller explicitly what it’s going to do:

begin
  my_user_table_builder_type()
    .with_age_over_18()
    .build();
end;

This would then have a default value for date of birth which results in a user who is more than 18 years old. You can also chain these custom functions together to create even more powerful combinations.

begin
  my_user_table_builder_type()
    .with_age_over_18()
    .with_origin_usa()
    .with_eligible_for_tax_credit()
    .build()
end;

In the example above I made up some additional information that might be relevant in a testing context. I hope you get the idea.

Now you might say: “This is just my TAPI with more boilerplate!” and you would be correct. In essence, this is a TAPI with individual functions for each column in the underlying table. But this “boilerplate” enables you to dynamically create new scenarios for your test cases without having to code them out every time. One major difference is that TAPIs, traditionally, have been packages, while the builder pattern requires object types to enable function chaining.

How do I get started?

The start is a bit tedious, as you have to create these objects by hand and include all the columns, again, by hand. If I look at my client projects, this is not fun at all. Fortunately, the base objects are pretty straightforward and always follow the same pattern. This is an ideal opportunity for this data builder generator I have built. You just run the installation script (only on dev of course…) and let it handle the object generation. You can then further enrich them with your own functions and defaults to further streamline the testing process. This implementation is far from finished and could be improved a lot but for the moment it is just intended as a rough starting point.

After creating the builder objects you can then use them in your test cases. The idea is, to use the builder pattern to set up the data you need for the test, then perform your business logic and assert the outcome with utPL/SQL (for example). Just give it a whirl!

Conclusion

There is no best way to set up your data during testing. What works in one project and for one group of developers might be atrocious for another group. I found the builder pattern to be of great use when creating test data because it allowed me to describe the test data while I was building it and still maintaining flexibility with the default with_... functions. I would love to hear some feedback from other devs on this regard, though.