6. Datenbank produktionsnah

Datenbank produktionsnah

In Informationssysteme entwerfen Sie Datenmodelle, in Softwareentwicklung speichern Sie Daten in einer Datei oder in SQLite. Beides bleibt gültig. Was hier dazukommt, ist der Umstand, dass die Datenbank ab jetzt ein eigener Dienst ist, der auf Ihrem Server läuft, den mehrere Anfragen gleichzeitig benutzen und in dem Daten liegen, die Sie nicht mehr wegwerfen dürfen.

Dieser letzte Punkt verändert die Arbeitsweise mehr als alles andere in diesem Kapitel. Solange eine Datenbank beim nächsten Start neu erzeugt wird, ist eine Schemaänderung eine Zeile Code. Sobald echte Buchungen darin stehen, ist dieselbe Änderung ein Vorgang mit Vorher, Nachher und einem Rückweg.

Von SQLite zu PostgreSQL

	SQLite	PostgreSQL
Form	eine Datei im Projektverzeichnis	eigener Serverprozess
Zugriff	direkt aus der Anwendung	über das Netz, mit Anmeldung
Gleichzeitiges Schreiben	ein Schreibvorgang zur Zeit	viele, über Transaktionen geregelt
Typen	nachgiebig, Text in Zahlenspalte möglich	streng, Fehler beim Einfügen
Benutzer und Rechte	keine	Rollen, Rechte pro Tabelle
Betrieb	nichts zu tun	Dienst, Volume, Sicherung, Aktualisierung

Für die Entwicklung ist SQLite großartig, und genau deshalb verdeckt es die Fragen, um die es hier geht. Ein zweiter Benutzer, der gleichzeitig schreibt, ein abgebrochener Vorgang mitten in einer Buchung, ein Feld, das plötzlich Text statt einer Zahl enthält: All das taucht erst auf, wenn die Datenbank ein Dienst mit mehreren Aufrufern ist.

Der Dienst selbst steht seit Kapitel 3 in Ihrer Compose-Datei, mit Volume und ohne veröffentlichten Port. Was jetzt dazukommt, sind die Rechte. Die Anwendung verbindet sich nicht als Superuser, sondern mit einer Rolle, die genau das darf, was sie braucht:

CREATE DATABASE project;

-- Role for the application: reads and writes data, does not change the schema
CREATE USER app WITH PASSWORD 'from-the-environment';
GRANT CONNECT ON DATABASE project TO app;
GRANT USAGE ON SCHEMA public TO app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app;

-- Role for migrations: may change the schema
CREATE USER migrator WITH PASSWORD 'from-the-environment';
GRANT ALL PRIVILEGES ON DATABASE project TO migrator;

Zwei Rollen statt einer sind der Unterschied zwischen einer Anwendung, die im Fehlerfall Daten verliert, und einer, die im Fehlerfall Tabellen verliert. Die Anwendung läuft dauerhaft mit app, und nur der Migrationsschritt im Deployment benutzt migrator. Für das CMS aus Kapitel 14 gilt dasselbe mit einer eigenen Datenbank und einer eigenen Rolle: Zwei Systeme, die einander nicht in die Tabellen sehen, ersparen später eine ganze Klasse von Problemen.

Die Zugriffsschicht

Der Schichtgedanke aus SEW gilt hier unverändert, nur mit einer echten Datenbank am Ende:

Route Handler  ->  Dienstschicht  ->  Repository  ->  Datenbank
(HTTP, Codes)      (fachliche         (Abfragen)
                    Regeln)

Der Punkt an dieser Aufteilung ist nicht die Ordnung, sondern die Antwort auf eine einzige Frage: An wie vielen Stellen im Projekt steht eine Datenbankabfrage? Wenn die Antwort „an einer pro Ressource” lautet, können Sie eine Abfrage optimieren, einen Index ergänzen oder von ORM auf handgeschriebenes SQL wechseln, ohne die halbe Anwendung anzufassen. Lautet sie „überall dort, wo Daten gebraucht werden”, dann nicht.

Ein ORM wie Prisma nimmt Ihnen die Übersetzung zwischen Objekten und Tabellen ab, erzeugt Typen aus dem Schema, kümmert sich um das Maskieren von Werten und verwaltet Migrationen. Verdeckt wird dabei, welche SQL-Abfragen tatsächlich entstehen, wie viele es sind und ob sie einen Index benutzen. Beide Seiten sind wichtig, deshalb die Arbeitsregel für dieses Jahr: Das ORM ist der Normalfall, und Sie müssen jederzeit sehen können, was es erzeugt. Für Auswertungen mit Gruppierungen und Aggregaten ist eine handgeschriebene Abfrage oft kürzer, schneller und verständlicher.

model Machine {
  id       Int       @id @default(autoincrement())
  name     String
  bookings Booking[]
}

model Booking {
  id        Int      @id @default(autoincrement())
  machine   Machine  @relation(fields: [machineId], references: [id])
  machineId Int
  start     DateTime
  end       DateTime
  version   Int      @default(0)

  @@index([machineId, start])
}

Aus diesem Schema erzeugt Prisma die Typen, die im Repository und über die Schnittstellenbeschreibung aus Kapitel 4 bis ins Frontend durchgereicht werden. Damit gibt es im Projekt genau eine Stelle, an der die Struktur einer Buchung definiert ist.

Migrationen

Eine Migration ist eine versionierte, benannte Schemaänderung, die als Datei im Repository liegt und auf jeder Umgebung in derselben Reihenfolge angewendet wird. Damit ist das Schema kein Zustand, den jemand irgendwann hergestellt hat, sondern eine Folge von Schritten, die sich wiederholen lässt.

npx prisma migrate dev --name booking_version_added   # local: creates and applies it
npx prisma migrate deploy                             # production: only applies it

Der zweite Befehl gehört in den Deployment-Ablauf aus Kapitel 3, und zwar vor dem Start der neuen Anwendungsversion. Was er niemals tun darf, ist Schema zu erraten oder zu verwerfen; migrate deploy wendet ausschließlich vorhandene Migrationsdateien an.

Änderungen ohne Ausfall

Hier verbindet sich das Kapitel mit der Rollback-Einschränkung aus Kapitel 3. Während eines Deployments laufen für einige Sekunden alte und neue Version gleichzeitig, und nach einem Rollback läuft die alte Version gegen das neue Schema. Beides funktioniert nur, wenn die Migration abwärtskompatibel ist.

Additive Änderungen sind unproblematisch: eine neue Tabelle, eine neue Spalte mit Vorgabewert, ein neuer Index. Die alte Version kennt sie nicht und ignoriert sie.

Entfernende und umbenennende Änderungen sind es nicht. Eine Spalte umzubenennen bricht die alte Version sofort. Der Ausweg ist die Zwei-Phasen-Änderung, hier am Beispiel einer Umbenennung von name nach bezeichnung:

1. Hinzufügen. Migration legt bezeichnung an, die alte Spalte bleibt. Die neue Anwendungsversion schreibt in beide Spalten und liest aus bezeichnung, falls gefüllt, sonst aus name.

2. Befüllen. Eine Migration kopiert die vorhandenen Werte. Ab jetzt sind beide Spalten aktuell, und ein Rollback auf die vorige Version funktioniert weiterhin.

3. Umstellen. Die nächste Version liest und schreibt nur noch bezeichnung.

4. Entfernen. Erst wenn diese Version stabil läuft und ein Rollback auf Schritt 2 nicht mehr in Frage kommt, entfernt eine Migration die Spalte name.

Das sind vier Deployments für eine Umbenennung, und beim ersten Mal wirkt das übertrieben. Es ist genau das Verfahren, mit dem große Systeme ohne Wartungsfenster auskommen, und Sie sollten es einmal an Ihrem eigenen Projekt durchgeführt haben, damit Sie den Aufwand einschätzen können.

Zwei Dinge, die man nicht tut

Von Hand auf dem Server migrieren. Ein ALTER TABLE über psql auf der Produktionsdatenbank wirkt sofort und ist danach nirgends aufgeschrieben. Die nächste Umgebung, die aus dem Repository aufgebaut wird, hat diese Änderung nicht, und der Unterschied fällt erst auf, wenn etwas nicht funktioniert.

Destruktiv migrieren ohne Sicherung. Ein DROP COLUMN ist nicht rückgängig zu machen, und die Daten darin sind weg. Vor jeder entfernenden Migration: Sicherung erstellen, und zwar so, dass Sie sie auch zurückspielen können.

Für die Entwicklung gehören Seed-Daten dazu: ein Skript, das eine leere Datenbank mit brauchbaren Beispieldaten füllt. Das ist mehr als Bequemlichkeit. Ein Seed mit realistischer Datenmenge ist die Voraussetzung dafür, dass Sie die Probleme im nächsten Abschnitt überhaupt bemerken.

Transaktionen und Nebenläufigkeit

Eine Transaktion fasst mehrere Schreibvorgänge zu einem zusammen: Entweder alle wirken, oder keiner. Die vier Eigenschaften dahinter kennen Sie aus INSY als ACID; im Betrieb zählt vor allem die erste, die Atomarität.

Gebraucht wird sie überall dort, wo zwei Änderungen nur gemeinsam einen gültigen Zustand ergeben. Eine Buchung anlegen und das Kontingent des Mitglieds verringern, eine Zahlung verbuchen und den Status setzen, eine Bestellung anlegen und den Lagerbestand abziehen: Wenn dazwischen der Prozess abstürzt, ist die Datenbank in einem Zustand, den es fachlich nicht geben darf.

await prisma.$transaction(async (tx) => {
  const booking = await tx.booking.create({ data: { machineId, start, end } });
  await tx.quota.update({
    where: { memberId },
    data: { remaining: { decrement: 1 } },
  });
  return booking;
});

Isolationsstufen

Sobald mehrere Transaktionen gleichzeitig laufen, stellt sich die Frage, was die eine von der anderen sieht. PostgreSQL kennt drei Stufen, und die Vorgabe ist READ COMMITTED.

Stufe	Was noch passieren kann
READ COMMITTED (Vorgabe)	nicht wiederholbares Lesen, Phantomzeilen
REPEATABLE READ	Phantomzeilen bei Aggregaten, Konflikt führt zu Abbruch
SERIALIZABLE	nichts davon, dafür mehr abgebrochene Transaktionen

Dass PostgreSQL kein schmutziges Lesen kennt, auch nicht auf der niedrigsten Stufe, ist ein Unterschied zu manchen anderen Systemen und eine beliebte Prüfungsfrage.

An Ihrem Projekt lässt sich das nachstellen. Öffnen Sie zwei psql-Sitzungen, starten Sie in beiden eine Transaktion, und sehen Sie nach, was die zweite von den Änderungen der ersten sieht, vor und nach dem COMMIT. Dieselbe Übung mit REPEATABLE READ zeigt den Unterschied deutlicher als jede Tabelle.

Wenn zwei dasselbe ändern

Das Lost-Update-Problem aus Kapitel 4 hat auf Datenbankebene eine schlanke Lösung: eine Versionsspalte, die bei jedem Schreiben mitgeprüft und erhöht wird.

const updated = await prisma.booking.updateMany({
  where: { id, version: expectedVersion },
  data: { end: newEnd, version: { increment: 1 } },
});

if (updated.count === 0) {
  throw new ConflictError("The booking was changed in the meantime.");
}

Wenn zwischen Lesen und Schreiben jemand anderer geändert hat, passt die Version nicht mehr, es wird keine Zeile geändert, und die Anwendung antwortet mit 409 statt zu überschreiben. Diese Versionsspalte ist die serverseitige Entsprechung des ETag aus Kapitel 4: derselbe Gedanke, einmal in HTTP und einmal in der Datenbank.

Der Ansatz heißt optimistische Sperre, weil er davon ausgeht, dass Konflikte selten sind und erst beim Schreiben auffallen müssen. Die pessimistische Variante (SELECT ... FOR UPDATE) sperrt die Zeile beim Lesen und ist dort richtig, wo Konflikte häufig und teuer sind, etwa beim letzten freien Platz.

Verbindungen verwalten

Eine Datenbankverbindung ist teuer. PostgreSQL startet für jede einen eigenen Prozess, und die Zahl ist begrenzt, üblicherweise auf etwa 100. Eine Anwendung, die pro Anfrage eine Verbindung öffnet und schließt, verbringt einen erheblichen Teil ihrer Zeit mit Verbindungsaufbau und geht unter Last an dieser Grenze in die Knie.

Deshalb hält ein Verbindungspool eine feste Anzahl offener Verbindungen bereit und teilt sie den Anfragen zu. Die passende Größe ist kleiner, als die meisten vermuten: Zehn bis zwanzig Verbindungen bedienen sehr viele gleichzeitige Anfragen, weil die wenigsten davon dauerhaft die Datenbank beschäftigen. Ein zu großer Pool verlagert das Problem nur in die Datenbank.

Die Hot-Reload-Falle in Next.js

Im Entwicklungsmodus lädt Next.js geänderte Module neu, und dabei entsteht bei jeder Änderung ein neuer PrismaClient mit eigenem Pool. Nach zwanzig Minuten Arbeit sind die Verbindungen der Datenbank aufgebraucht, und die Fehlermeldung deutet in eine völlig andere Richtung.

Die Lösung ist ein einziger Client, der die Neuladevorgänge überlebt:

import { PrismaClient } from "@prisma/client";

const global = globalThis as unknown as { prisma?: PrismaClient };

export const prisma = global.prisma ?? new PrismaClient();

if (process.env.NODE_ENV !== "production") {
  global.prisma = prisma;
}

Dieselbe Frage stellt sich in Serverless-Umgebungen, wo viele kurzlebige Instanzen gleichzeitig existieren. Dort ist ein vorgelagerter Verbindungsverteiler wie PgBouncer die übliche Antwort. Auf Ihrem eigenen Server mit einem langlaufenden Container tritt das Problem nicht auf.

Abfragen, die skalieren

Das N+1-Problem

Der häufigste Leistungsfehler in datenbankgestützten Anwendungen entsteht nicht durch eine langsame Abfrage, sondern durch viele schnelle.

// One query for the list ...
const bookings = await prisma.booking.findMany({ where: { machineId } });

// ... and another one per row
for (const b of bookings) {
  const member = await prisma.member.findUnique({ where: { id: b.memberId } });
  // ...
}

Bei zehn Buchungen sind das elf Abfragen und niemandem fällt etwas auf. Bei zweihundert Buchungen sind es 201, und die Seite braucht drei Sekunden. Genau deshalb steht oben der Hinweis auf realistische Seed-Daten: Lokal mit zehn Datensätzen ist dieser Fehler unsichtbar.

Sichtbar machen lässt er sich sofort, indem man die Abfragen protokolliert:

new PrismaClient({ log: ["query"] });

Wer das einmal eingeschaltet und eine Übersichtsseite geladen hat, sieht die Wiederholung in der Konsole vorbeirauschen. Behoben wird sie, indem die Beziehung mitgeladen wird:

const bookings = await prisma.booking.findMany({
  where: { machineId },
  include: { member: true },
  take: 50,
});

Aus 201 Abfragen werden ein oder zwei. Die Zeile take: 50 gehört übrigens dazu: Eine Abfrage ohne Obergrenze zieht irgendwann die ganze Tabelle, und in Kapitel 7 wird daraus eine Paginierung.

Indizes

Ein Index ist eine zusätzliche Datenstruktur, die das Finden beschleunigt und jedes Schreiben verlangsamt, weil er mitgepflegt werden muss. Er lohnt sich für Spalten, nach denen häufig gefiltert, sortiert oder verbunden wird, und er lohnt sich nicht für alles andere.

Ob er benutzt wird, sagt Ihnen die Datenbank selbst:

EXPLAIN ANALYZE
SELECT * FROM "Booking" WHERE "machineId" = 7 AND start > now();

Zwei Zeilen der Ausgabe zählen. Seq Scan bedeutet, dass die gesamte Tabelle gelesen wurde; bei kleinen Tabellen ist das in Ordnung, bei großen ein Befund. Index Scan bedeutet, dass ein Index benutzt wurde. Dahinter stehen die tatsächlich verbrauchte Zeit und die Zeilenanzahl, und der Vergleich dieser Werte vor und nach dem Anlegen eines Index ist der Nachweis, den dieses Kapitel verlangt.

Bei zusammengesetzten Indizes ist die Reihenfolge der Spalten entscheidend. Ein Index über (machineId, start) hilft bei einer Abfrage nach machineId allein und bei beiden gemeinsam. Für eine Abfrage nur nach start nützt er nichts, weil die vordere Spalte fehlt.

Nur holen, was gebraucht wird

Zwei Gewohnheiten sparen mehr, als die meisten Optimierungen später einbringen: Felder auswählen statt SELECT *, und eine Obergrenze setzen. Ein Bild oder ein langer Beschreibungstext, der in einer Listenansicht nie angezeigt wird, geht bei jeder Zeile über das Netz und durch den Speicher.

Zur Paginierung: OFFSET 10000 zwingt die Datenbank, zehntausend Zeilen zu lesen und wegzuwerfen, und wird mit jeder Seite langsamer. Die Alternative merkt sich den letzten gesehenen Wert und filtert danach. Wie das im Vertrag aussieht, steht in Kapitel 7.

Sicherheit auf Datenebene

Eine SQL-Injection entsteht, wenn Eingaben eines Benutzers als Teil des Abfragetexts landen statt als Wert. Ein ORM schützt davor bei normaler Verwendung, weil es Werte immer als Parameter übergibt. Es schützt nicht überall, und die Ausnahmen sind genau die Stellen, an denen man ausweicht.

// safe: the values are passed as parameters
await prisma.$queryRaw`SELECT * FROM "Booking" WHERE "machineId" = ${id}`;

// unsafe: the string is assembled by hand
await prisma.$queryRawUnsafe(`SELECT * FROM "Booking" WHERE "machineId" = ${id}`);

Der zweite Fall ist nicht theoretisch. Er entsteht regelmäßig dort, wo etwas dynamisch sein soll, das kein Parameter sein kann: Tabellen- und Spaltennamen, Sortierrichtungen, ganze WHERE-Bedingungen. Ein Endpunkt mit ?sort=start und einem direkt eingesetzten Spaltennamen ist eine offene Tür. Die Lösung ist eine Positivliste:

const allowedSortFields = { start: "start", end: "end", name: "name" } as const;
const field = allowedSortFields[input] ?? "start";

Was der Benutzer schickt, wird damit nie zu SQL, sondern höchstens zu einem Schlüssel in einer festen Zuordnung. Dieselbe Positivliste taucht in Kapitel 7 bei Filtern und Sortierung wieder auf, dort als Teil des Schnittstellenvertrags.

Zwei Fragen gehören ebenfalls in dieses Kapitel, auch wenn sie erst in der 5. Klasse vertieft werden. Welche Daten speichern Sie überhaupt? Ein Geburtsdatum, das nirgends gebraucht wird, ist kein Feature, sondern Haftung. Und welche Daten dürfen niemals im Klartext liegen? Passwörter gehören gehasht (Kapitel 8), und personenbezogene Daten, die Sie brauchen, aber nicht anzeigen müssen, kann man verschlüsselt ablegen.

Sicherung und Rückspielung

# Create a backup
docker compose exec -T db pg_dump -U project project > backup-2026-11-14.sql

# Restore it (into an empty database)
docker compose exec -T db psql -U project project < backup-2026-11-14.sql

Dokploy kann diese Sicherung planmäßig erstellen und ablegen. Richten Sie das ein, und richten Sie sich darauf ein, dass die Einrichtung der leichte Teil ist.

Eine Sicherung, die nie zurückgespielt wurde, ist eine Vermutung

Der Fehlerfall bei Sicherungen ist selten „es gibt keine”, sondern fast immer „sie lässt sich nicht zurückspielen”: falsche Version, fehlende Erweiterung, leere Datei, weil das Skript seit sechs Wochen mit einem Fehler endet, den niemand gelesen hat.

Führen Sie die Rückspielung deshalb einmal vollständig durch, in eine zweite, leere Datenbank, und schreiben Sie in docs/betrieb.md, wie lange es gedauert hat und welche Schritte nötig waren. Diese Notiz ist an dem Tag Gold wert, an dem Sie sie unter Zeitdruck brauchen.

Häufige Stolperfallen

1. Migrationen von Hand auf der Produktionsdatenbank ausführen. Der Zustand ist danach nirgends beschrieben und lässt sich nicht nachbauen.
2. Umbenennen statt in zwei Phasen ändern. Die laufende Version bricht, und ein Rollback hilft nicht mehr.
3. Datenbank ohne Volume oder ohne geprüfte Sicherung betreiben. Beides fällt genau einmal auf.
4. Datenbankport nach außen öffnen, weil man mit einem Werkzeug draufschauen will. Der richtige Weg ist ein SSH-Tunnel.
5. N+1 nicht bemerken, weil lokal zehn Datensätze liegen und in der Produktion zehntausend.
6. Index auf jede Spalte legen und sich über langsame Schreibvorgänge und wachsenden Speicherbedarf wundern.
7. Die Anwendung als Superuser verbinden. Ein Fehler in der Anwendung kann dann Tabellen löschen und nicht nur Zeilen.
8. Zusammengehörige Schreibvorgänge ohne Transaktion. Beim ersten Abbruch entsteht ein Zustand, den es fachlich nicht geben darf.
9. Dynamische Spaltennamen aus Benutzereingaben. Das ORM schützt hier nicht, und genau hier wird ausgewichen.

Lernergebnisse

• Erklären: die Unterschiede zwischen SQLite und einem Datenbankdienst benennen und begründen, welche Fragen erst im Dienstbetrieb auftreten.
• Anwenden: PostgreSQL als Dienst betreiben und die Anwendung mit einer Rolle anbinden, die nur die nötigen Rechte hat.
• Anwenden: eine Zugriffsschicht aufbauen, in der Datenbankabfragen an einer Stelle je Ressource liegen.
• Anwenden: Migrationen erstellen, im Deployment anwenden und abwärtskompatibel schneiden.
• Erschaffen: eine Umbenennung als Zwei-Phasen-Änderung ohne Ausfall durchführen.
• Anwenden: zusammengehörige Schreibvorgänge in einer Transaktion zusammenfassen.
• Erklären: Isolationsstufen unterscheiden und an einem Beispiel aus dem eigenen Projekt zeigen, welche Anomalie welche Stufe verhindert.
• Anwenden: eine optimistische Sperre über eine Versionsspalte umsetzen und den Zusammenhang zum ETag erklären.
• Analysieren: ein N+1-Problem im Abfrageprotokoll nachweisen und beheben.
• Analysieren: mit EXPLAIN ANALYZE belegen, ob ein Index verwendet wird, und die Wirkung vorher und nachher messen.
• Beurteilen: entscheiden, wann ein ORM angemessen ist und wann eine handgeschriebene Abfrage die bessere Wahl ist.
• Anwenden: SQL-Injection durch parametrisierte Abfragen und Positivlisten verhindern, auch bei dynamischer Sortierung.
• Anwenden: eine Sicherung erstellen und vollständig zurückspielen.

Arbeit am Jahresprojekt

• Das Projekt läuft gegen PostgreSQL, die Anwendung verbindet sich mit einer Rolle ohne Schemarechte.
• Das Schema entsteht ausschließlich aus Migrationen im Repository; eine Umbenennung wurde in zwei Phasen durchgeführt.
• Ein Seed-Skript erzeugt eine realistische Datenmenge, an der Leistungsprobleme sichtbar werden.
• Ein N+1-Problem und ein fehlender Index sind mit Messung vorher und nachher in docs/betrieb.md dokumentiert.
• Eine Sicherung wurde erstellt und in eine leere Datenbank zurückgespielt, mit Zeitangabe im Betriebsprotokoll.

Passende Übungen

• Aufgabe 21 - Umzug von SQLite nach PostgreSQL
• Aufgabe 22 - Migration in zwei Phasen ohne Ausfall
• Aufgabe 23 - N+1 finden und beheben (mit Vorher-Nachher-Messung)
• Aufgabe 24 - Backup, Löschung, Wiederherstellung