Unit-Testing in PostgreSQL

Es gibt verschiedene Stellen im Tech-Stack, an denen man seine Businesslogik liegen haben will.

In einem Symfony-Stack hat man beispielsweise eine „dumme“ Datenbank hinter einem ORM, die einfach nur die Daten speichert. Dann hat man ein beliebiges Templating-System im Frontend, dass für die Anzeige verantwortlich ist. Und dazwischen liegt Symfony im Backend und beinhaltet die Businesslogik. In diesem Fall ergibt es Sin, den Symfony-PHP-Code mit Unit-Tests abzudecken, so dass man sicher sein kann, dass da alles läuft.

Mit einem PostgreSQL-GraphQL-TypeScript-Stack braucht man aber dieses Backend nicht mehr. Oder genauer gesagt: Das Backend wandert in die Datenbank.

Businesslogik in der Datenbank

Die Features, die Postgres mitbringt, ermöglichen es, Businesslogik in der Datenbank abzulegen. Mit RLS kann man sicherstellen, dass nur autorisierte Nutzer an die Daten herankommen, Berechnungen auf den Daten können in Computed Columns abgelegt werden. Das API wird über Annotations oder Smart-Tags erweitert und modelliert. Validierungen können per Trigger in der Datenbank automatisiert ausgeführt werden. Und es gibt sicher noch einige andere Features, die dabei nützlich sind. Dabei ist die Datenbank in der Lage, all das sehr performant durchzuberechnen.

Doch natürlich ist es weiterhin wichtig, per Unit-Tests sicherzustellen, dass alles so läuft, wie es soll. Wie gehen also Unit-Test in einer Postgres-DB?

Aber … warum?

Natürlich könnte man die Datenbank auch aus dem Frontend testen. Einfach eine Connection aufbauen, Tests durchlaufen lassen und Ergebnisse vergleichen, Connection wieder schließen. Da stelle ich die Warum-Frage aber direkt zurück: Warum Datenbank-Typen in meine Frontend-Sprache konvertieren, anstatt direkt zu testen, ob sie richtig sind?

Tests könnten an dieser Stelle ja schon aus mindestens zwei Gründen failen, die nichts mit der Datenbank zu tun haben. Grund eins ist, dass die Connection nicht zustande kommt. Grund zwei ist, dass meine Konvertierung des Ergebnisses falsch gecoded ist.

Ich glaube, dass es vorteilhaft ist, Code in der Sprache zu testen, in der er geschrieben wurde. Und so wird die testgetriebene Entwicklung von Datenbank und Logik möglich.

Die pgTAP-Extension

PostgreSQL ist über Extensions erweiterbar. Die Extension, die wir heute genauer anschauen wollen, heißt pgTAP. 

pgTap bringt viele nützliche Assertion-Funktionen mit – wie man das von Unit-Test-Suiten kennt. Der primäre Unterschied ist, dass man seine Tests in SQL schreibt. Das sieht vom Aufbau her so aus:

BEGIN;
 
SELECT plan(3);
 
[... ASSERTION 1];
[... ASSERTION 2];
[... ASSERTION 3];
 
SELECT finish();
 
ROLLBACK;

Mit SELECT plan(3) stellt man klar, dass in diesem Test drei Assertions geplant sind. Mit SELECT finish() wird der Test beendet. Das ganze wird mit BEGINN; ... ROLLBACK; umklammert, um sicherzustellen, dass es keine permanenten Änderungen an der Datenbank gibt. Um die Tests vom CLI zu starten, nutzt man das Tool pg_prove.

Assertions (und was man mit ihnen testen kann)

Werfen wir nun einen kurzen Blick auf die Assertions und was man mit ihnen testen kann.

• Wir wollen testen, ob das DB-Schema mit dem Namen „schema_foo“ korrekt erstellt wurde: SELECT has_schema(schema_foo::name);
• Wir wollen testen, ob unsere Tabelle mit dem Namen „table_foo“ existiert: SELECT has_table('table_foo');
• Wir wollen testen, ob unsere Tabelle den richtigen Primary Key hat:  SELECT col_is_pk('table_foo', 'id');

… und so weiter. Es gibt Tests auf einzelne Dinge, wie zum Beispiel has_table, oder auch auf mehrere, mit tables_are. Damit kann man alle Tabellen auf einmal überprüfen. Gleiches gilt für Schemas, Views, Materialized Views, usw. Man kann überprüfen ob Spalten in Tabellen NULL sein dürfen oder nicht, ob die Inheritance zwischen Tabellen korrekt aufgebaut ist, ob Extension korrekt installiert wurden.

Doch man kann nicht nur die Struktur der Datenbank bis in die kleinste Definitionsebene überprüfen, sondern auch alle Funktionen. Ganz grundlegend ist dabei function_returns, um das Ergebnis zu überprüfen, doch auch hier geht es weit darüber hinaus: Ist die Funktion STRICT, ist sie VOLATILE, ist es eine Procedure oder ob sie von einem Trigger korrekt getriggert wird.

Natürlich möchte man auch Zugriffsberechtigungen überprüfen können. Stimmt der Owner auf meiner Tabelle, View, …? Laufen meine Funktionen unter einem Security Definer – also mit Adminrechten – oder nicht? Was kommt bei meinen Tests raus, wenn ich den User-Level, mit dem ich die Tests ausführe, ein paar Ebenen nach unten korrigiere?

Ein Beispiel für TDD anhand von Triggern

Wir wollen, dass alle Inserts oder Updates in die Tabelle table_foo Spalte col_2 großgeschrieben werden.

Das ist unser erster Test:

BEGIN;
 
SELECT plan(2); 
 
SELECT has_table('table_foo');
SELECT columns_are('table_foo', ARRAY ['col_1', 'col_2']);
 
SELECT finish();
 
ROLLBACK;

Der Test wird grün, wenn wir die Tabelle mit den zwei Spalten erstellen. 

BEGIN;
 
CREATE TABLE table_foo (
  col_1   int    NOT NULL,
  col_2   text   NOT NULL
);
 
COMMIT;

Dann wollen wir eine Funktion für die Großschreibung und einen Trigger, der vor dem Insert oder Update ausgeführt wird.

BEGIN;
 
SELECT plan(3); 
 
SELECT function_returns( 'to_upper_case', 'trigger', 'Function musst return a trigger.' );
SELECT trigger_is( 'table_foo', 'table_foo_trigger_biu', 'to_upper_case', 'Function to_upper_case has to be triggered by table_foo_trigger_biu on table table_foo.' );
 
INSERT INTO table_foo VALUES (1, 'foo');
SELECT is(col_2, 'FOO', 'Inserted values into column "col_2" should be uppercased' ) FROM table_foo WHERE col_1 = 1;
 
SELECT finish();
 
ROLLBACK;

Hier testen wir also zuerst, dass es eine Funktion to_upper_case gibt, die einen Trigger zurückgibt. Der nächste Test stellt sicher, dass unsere Funktion von dem Trigger table_foo_trigger_biu ausgelöst wird. Der dritte Test in dem Plan wird erst ausgelöst, nachdem ein Insert auf die Tabelle table_foo ausgeführt wurde. Dieser Test wird nur grün, wenn unsere Funktion das richtige tut – nämlich die Eingabe „foo“ in „FOO“ umzuwandeln.

Es werde grün!

Damit dieser Testplan grün wird, erstellen wir also folgende Funktion und Trigger:

BEGIN;
 
CREATE FUNCTION to_upper_case()
RETURNS trigger AS
$$
  BEGIN
    NEW.col_2 := upper(NEW.col_2);
    RETURN NEW;
  END;
$$ LANGUAGE plpgsql VOLATILE;
 
CREATE TRIGGER table_foo_trigger_biu
BEFORE INSERT OR UPDATE
ON table_foo
FOR EACH ROW
EXECUTE PROCEDURE to_upper_case();
 
COMMIT;

Es geht immer mehr

Natürlich kann man für korrektes TDD ausführlichere Tests schreiben. So könnte man absichern, dass die Typen der Tabellen-Spalten korrekt sind, dass die Funktion im richtigen Dialekt geschrieben ist, u.v.m. Damit das Beispiel hier übersichtlich bleibt, soll das aber erstmal reichen.

Wer jetzt neugierig ist, was pgTap sonst noch alles mitbringt, kann in der Dokumentation nachschauen.

Viel Spaß beim Ausprobieren …

… und wenn ihr wissen wollt, was mit PostgreSQL und vor allem PostGraphile noch alles möglich ist, möchte ich euch unsere passende Serie zum Thema ans Herz legen:

PostGraphile in-depth