Developers and testers are often not aware that you can have a lot of functionality right at the database level – and that this might even be a very good thing. Having business logic at the database level is even frowned upon by a lot of developers or considered an anti-pattern.

In my opinion, the strongest reason for this, however, is misconceptions and lack of knowledge about what modern relational databases provide and what tools are available at the database level.

As Scott Ambler and Pramod Sadalage wrote in 2006 in their book “Refactoring Databases: Evolutionary Database Design”:

„The first impediment, and the hardest one to overcome, is cultural.“

One thing that you can do right at the database level is create unit tests that use transactions for easy test-data setup and cleanup.

Here’s an example in pure PL/SQL that will raise an error when we add a new user with alignment “dark” that doesn’t show up in the view v_users_darkside

  l_exists integer;
  -- Arrange
  -- A little trick here to not collide with existing entries: 
  -- use negative primary keys
  insert into deathstar_users ( id, name, alignment )
    values ( -1, 'Testperson123', 'dark' );

  -- Gathering data for assertions later
  select count(*) into l_exists
    from v_users_darkside
    where name = 'Testperson123';

  -- Cleanup our testdata

  -- Assert: There should be exactly one entry here
  if l_exists = 0 then
      'Expected Testperson123 to be in view, but wasnt'
  elsif l_exists > 1 then
      'More than 1 Testperson123 in the view, HALP!'
  end if;

This PL/SQL script could become part of an automated test suite.

However, if you want to embrace automated tests at the database level, I recommend checking out frameworks written for exactly that (depending on the database vendor):

Previous: Example 1: High-level availability checks with NodeJS and the Mocha testing framework

Next: Example 3: Identify, correct, and prevent duplicate Darth Vaders