Einzigartige, lesefreundliche Identifier mit PostgreSQL

In einem meiner Projekte stelle ich zur Zeit von PHP auf JavaScript und von REST auf GraphQL um, in der Hoffnung, schlankere Services zu erhalten. Bei meinen Recherchen zu dem Thema stieß ich auf PostGraphile, das als zugrundeliegende Datenbank PostgreSQL möchte. Bisher war ein klassisches MySQL im Einsatz.

Eine Funktionalität, die ich ersetzen muss, sind einzigartige, lesefreundliche Bezeichner oder auch Slugs. Bislang gab es eine Funktionalität in meinem alten PHP-Code, die mir aus einem Titel den Slug generierte und dafür sorgte, dass dieser einzigartig in meinem Datenbestand ist. 

Mit PostgGaphile hatte ich diesen Code allerdings nicht mehr. Meine Recherchen diesbezüglich führten mich zu dem Ergebnis, dass die erwartete Funktionalität gar nicht mal in meinem Service umgesetzt gehört, sondern besser auf Datenbankebene aufgehoben ist. PostgreSQL kann das, denn ich kann hier bequem Funktionen und Trigger definieren. So wäre auch gewährleistet, dass immer ein Slug erzeugt wird, selbst wenn die Daten nicht über diesen speziellen Service kommen.

Wie ich das Problem lösen konnte

Eine Beispiel-Tabelle könnte folgendermaßen aussehen:

CREATE TABLE public.books (
    id SERIAL PRIMARY KEY,
    titel VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE
);

Ich habe hier neben dem Primary Key noch den Titel und den Slug. Der Titel wird beim Anlegen des Datensatzes angegeben, der Slug soll erzeugt werden.

Da ich in Sachen PostgreSQL eher in die Sparte „blutiger Anfänger” gehöre, nutzte ich die Suchmaschine meiner Wahl, um an mögliche Lösungen für dieses Problem zu kommen. Die Top-Treffer ergaben, dass es für mein Problem bereits eine mögliche Lösung gibt:

CREATE EXTENSION IF NOT EXISTS "unaccent"
 
CREATE OR REPLACE FUNCTION slugify("value" TEXT)
RETURNS TEXT AS $$
  -- removes accents (diacritic signs) from a given string --
  WITH "unaccented" AS (
    SELECT unaccent("value") AS "value"
  ),
  -- lowercases the string
  "lowercase" AS (
    SELECT lower("value") AS "value"
    FROM "unaccented"
  ),
  -- remove single and double quotes
  "removed_quotes" AS (
    SELECT regexp_replace("value", '[''"]+', '', 'gi') AS "value"
    FROM "lowercase"
  ),
  -- replaces anything that's not a letter, number, hyphen('-'), or underscore('_') with a hyphen('-')
  "hyphenated" AS (
    SELECT regexp_replace("value", '[^a-z0-9\\-_]+', '-', 'gi') AS "value"
    FROM "removed_quotes"
  ),
  -- trims hyphens('-') if they exist on the head or tail of the string
  "trimmed" AS (
    SELECT regexp_replace(regexp_replace("value", '\-+$', ''), '^\-', '') AS "value"
    FROM "hyphenated"
  )
  SELECT "value" FROM "trimmed";
$$ LANGUAGE SQL STRICT IMMUTABLE;

Erweiterungen ftw

Zuerst wird die PostgreSQL-eigene Erweiterung „unaccent“ installiert. Sie sorgt dafür, dass Umlaute wie z. B. á in ein normales a umgewandelt werden. In der Datei unaccent.rules, die bei mir im postgresql/tsearch_data-Ordner liegt, werden die Regeln der Umwandlungen verwaltet. Sollte einem eine spezielle Regel fehlen, kann sie dort eingetragen werden.

Danach wird die Funktion „slugify“ erstellt, die als Parameter einen String akzeptiert; in unserem Fall wird das der Titel des Buches sein. Anschließend gibt es eine Sequenz an Umwandlungen, nach deren durchlaufen ein String wie „JavaScript für Anfänger“ in „javascript-fuer-anfaenger“ umgewandelt wurde.

Damit diese Funktion auch beim Eintragen von neuen Daten aufgerufen wird, benötigen wir noch einen Mechanismus dafür – genauer: einen Trigger.

CREATE FUNCTION public.set_slug_from_title() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    NEW.slug := slugify(NEW.title);
    RETURN NEW;
END
$$;
 
CREATE TRIGGER "trg_slug_insert"
BEFORE INSERT ON "books"
FOR EACH ROW
WHEN (NEW.title IS NOT NULL AND NEW.slug IS NULL)
EXECUTE PROCEDURE set_slug_from_title();

Um genau zu sein, definieren wir hier zwei Dinge. Zum einen eine weitere Funktion, zum anderen den versprochenen Trigger. Der Trigger soll immer dann auslösen, wenn ein neuer Datensatz in unserer Tabelle angelegt wird, und zwar wenn ein Titel vorhanden ist, aber der Slug null ist. In diesem Fall ruft der Trigger die Funktion auf.

Die Funktion wiederum ruft unsere Funktion slugify auf, übergibt dieser den Titel des Buches und setzt das Ergebnis in das Slug-Feld ein. Tadaa, und schon wird uns automatisch der passende Slug zum Titel generiert.

Verfeinerung der Lösung

Der aufmerksame Leser wird jetzt mutmaßlich den Zeigefinger heben und sagen: „SirToby, das ist ja gut und schön, aber der Slug, der da erzeugt wird, ist ja gar nicht einzigartig!”.

Das ist wahr. Deshalb müssen wir da nochmal ran und eine Verbesserung einfügen. Im Endeffekt bleibt uns nichts übrig, als in der Tabelle nachzusehen, ob der Slug bereits vorhanden ist. Man könnte dann zum Beispiel die ID noch an den Slug anhängen, oder – was ich persönlich etwas eleganter finde – mitzählen, wie oft der Slug vorkommt und die Anzahl anhängen (das gibt nicht ganz so viel von der internen Struktur preis).

CREATE FUNCTION public.set_slug_from_title() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
sql_string varchar;
tmp_slug varchar;
increment integer;
tmp_row record;
tmp_row_count integer;
 
BEGIN
    tmp_row_count = 1;
    increment = 0;
    while tmp_row_count > 0 LOOP
 
    if increment > 0 then
        tmp_slug = slugify(NEW.name || ' ' || increment::varchar);
    ELSE
        tmp_slug = slugify(NEW.name);
    end if;
 
      sql_string = format(' select count(1) cnt from ' || TG_TABLE_NAME || ' where slug = ''' || tmp_slug || '''; ');
    for tmp_row in  execute(sql_string)
      loop
        raise notice '%', tmp_row;
        tmp_row_count = tmp_row.cnt;
    end loop;
 
    increment = increment + 1;
    END LOOP;
 
    NEW.slug := tmp_slug;
    RETURN NEW;
END
$$;

Erweitern wir also unsere Funktion set_slug_from_title und zählen mit, wie häufig unser gewünschter Slug bereits vorkommt. Sollte er mehrfach vorhanden sein, wird das Inkrement erhöht und anschließend an den Slug angehängt. So erhalten wir jedes Mal einen einzigartigen Slug. Das erzeugt in etwa dieses Ergebnis:

Wenn wir nun in unserer Tabelle die Slug-Spalte zu einem unique index machen, können wir unsere Datensätze anhand des Slugs sogar performanter identifizieren.

Fazit

PostgreSQL bietet mir die Möglichkeit von Funktionen und Triggern. Dadurch kann ich kleinere Features statt wie bisher im Code meines Services auf Datenbankebene umsetzen. Damit stelle ich sicher, dass meine Daten – sollten sie auf einem anderen Weg als über meinen Service in die Datenbank gelangen – trotzdem in der gewünschten Form vorliegen.

In der Diskussion mit Kollegen über dieses Thema wurde mir allerdings auch eine potenzielle Schwachstelle offenbart: Werden Stored Procedures zusätzlich zu weiterer Logik in z. B. einem Backend eingesetzt, muss im Fehlerfall auch daran gedacht werden, diese Stored Procedures ebenfalls zu bearbeiten; sie werden schließlich im normalen Debugging-Workflow nicht auftauchen. Auch das Testing spielt eine Rolle … wobei es hierfür bereits mit Unit-Testing in PostgreSQL eine Lösung gibt.

Ich bin dennoch sehr begeistert von diesen Möglichkeiten und werde mich zukünftig häufiger fragen, ob ich ein Feature im Service-Code oder doch bereits auf Datenbankebene umsetzen kann.

PostGraphile in-depth