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
declare
l_exists integer;
begin
-- 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
rollback;
-- Assert: There should be exactly one entry here
if l_exists = 0 then
raise_application_error(
-20000,
'Expected Testperson123 to be in view, but wasnt'
);
elsif l_exists > 1 then
raise_application_error(
-20000,
'More than 1 Testperson123 in the view, HALP!'
);
end if;
end;
/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