Datenbanken

Kurz gesagt: Eine Datenbank speichert Daten in Tabellen und findet sie mit einer SQL-Abfrage schnell; in Node.js sprichst du über node:sqlite mit ihr, und ?-Parameter schützen vor SQL-Injection.

Problem

Ein Katalog in einer Datei

Der Katalog des Shops ist eine CSV-Datei, wie im Modul über Dateien. Solange es hundert Produkte sind, geht das. Dann werden es hunderttausend, und es gibt Probleme:

• um ein Produkt zu finden, liest du die ganze Datei;
• „alles unter 5 €, nach Preis sortiert“ ist eine Schleife, ein if und eine Sortierung, alles von Hand geschrieben;
• zwei Kassen schreiben gleichzeitig in die Datei, und ein Eintrag geht verloren oder die Datei wird beschädigt;
• mitten beim Schreiben fällt der Strom aus, und die halbe Datei ist weg.

Deshalb liegen die Daten fast jedes echten Programms in einer Datenbank: einem Programm, dessen Aufgabe es ist, Daten aufzubewahren, sie schnell zu finden und nie zu verlieren.

Die Idee

Tabellen und SQL

Eine relationale Datenbank speichert Daten in Tabellen. Eine Tabelle hat Spalten (Name, Typ) und Zeilen (eine für jedes Produkt). Die Spalte id ist der Primärschlüssel: Ihr Wert ist in jeder Zeile anders, deshalb findet man mit ihm genau eine Zeile.

Mit der Datenbank sprichst du in SQL. Die fünf häufigsten Befehle:

SQL

CREATE TABLE products (
    id    INT PRIMARY KEY,
    name  VARCHAR(50) NOT NULL,
    price DECIMAL(8, 2)
);

INSERT INTO products VALUES (1, 'Kaffee', 7.50);
SELECT name, price FROM products WHERE price < 5 ORDER BY price;
UPDATE products SET price = 6.90 WHERE id = 1;
DELETE FROM products WHERE id = 3;

• CREATE TABLE legt eine Tabelle an, INSERT fügt eine Zeile ein, UPDATE ändert Zeilen und DELETE löscht sie.
• SELECT liest: welche Spalten, aus welcher Tabelle, unter welcher Bedingung (WHERE) und in welcher Reihenfolge (ORDER BY).

Du sagst, was du willst, und die Datenbank entscheidet, wie sie es schnell findet.

Genauer, für Neugierige

• Bekannte relationale Datenbanken sind PostgreSQL, MySQL, Oracle und SQL Server. Sie laufen als eigene Server. Hier benutzen wir SQLite: eine kleine Datenbank, die in einer Datei oder ganz im Speicher des Programms lebt. Das Modul node:sqlite ist in Node 22 eingebaut und braucht kein Flag, ist aber noch experimentell: Beim Ausführen schreibt es ExperimentalWarning: SQLite is an experimental feature nach stderr. Die Adresse ":memory:" ist eine Datenbank im Speicher: Jeder Lauf beginnt mit einer leeren.
• Text steht in SQL in einfachen Anführungszeichen: 'Kaffee'. In PostgreSQL und MySQL ist DECIMAL(8, 2) eine exakte Zahl mit 2 Nachkommastellen, das passt zu Geld. SQLite speichert keine exakten Dezimalzahlen: Dort ist 7.50 eine gewöhnliche Gleitkommazahl.

Sieh es dir an

node:sqlite: Verbindung, Abfrage, Ergebnis

Das Modul node:sqlite spricht mit SQLite. Die Verbindung ist eine DatabaseSync, und eine Abfrage mit Ergebnis machst du mit prepare. Die Funktion setup legt die Tabelle an und fügt drei Produkte ein:

function setup(db) {
  db.exec("CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, price DECIMAL(8, 2))");
  db.exec("INSERT INTO products VALUES (1, 'Kaffee', 7.50), (2, 'Tee', 3.20), (3, 'Schokolade', 2.40)");
}

Wir lesen die Zeilen und machen aus jeder ein Objekt der Klasse Product mit den Feldern id, name und price:

class Product {
  constructor(id, name, price) {
    this.id = id;
    this.name = name;
    this.price = price;
  }
}

const db = new DatabaseSync(":memory:");
setup(db);
const products = [];
for (const row of db.prepare("SELECT id, name, price FROM products ORDER BY price").all()) {
  products.push(new Product(row.id, row.name, row.price));
}
for (const p of products) {
  console.log(p);
}
db.close();

Ausgabe

Product { id: 3, name: 'Schokolade', price: 2.4 }
Product { id: 2, name: 'Tee', price: 3.2 }
Product { id: 1, name: 'Kaffee', price: 7.5 }

• new DatabaseSync(address) öffnet die Verbindung. Die Adresse sagt, welche Datenbank: hier ":memory:", oder ein Dateiname wie "shop.db". „Sync“ bedeutet, dass die Methoden das Ergebnis sofort zurückgeben, ohne await.
• db.exec(sql) schickt Befehle ohne Ergebnis (CREATE, INSERT, UPDATE, DELETE). db.prepare(sql) bereitet eine Abfrage vor, und .all() gibt alle Zeilen als Array zurück. Jede Zeile ist ein Objekt mit einem Feld für jede Spalte: row.name.
• .get() gibt nur die erste Zeile zurück, und .run() ist für Änderungen und gibt zurück, wie viele Zeilen sich geändert haben (changes).
• Standardmäßig wird jede Abfrage sofort gespeichert. db.close() schließt die Verbindung.
• Fehler werden als Error mit dem code "ERR_SQLITE_ERROR" geworfen.

const db = new DatabaseSync(":memory:");
setup(db);
const products = [];
for (const row of db.prepare("SELECT id, name, price FROM products ORDER BY price").all()) {
  products.push(new Product(row.id, row.name, row.price));
}
for (const p of products) {
  console.log(p);
}
db.close();

Schritt für Schritt, in Worten

1. Eine Verbindung zur Datenbank im Speicher. setup legt die Tabelle products an und fügt 3 Zeilen ein.
2. Eine leere Liste, in die die Objekte kommen.
3. SELECT mit ORDER BY price: all() gibt alle Zeilen auf einmal als Array zurück, die günstigste zuerst.
4. Die Schleife nimmt Zeile 1: ein Objekt mit einem Feld für jede Spalte. new Product macht daraus ein Objekt: Schokolade.
5. Die Schleife nimmt Zeile 2: ein Objekt mit einem Feld für jede Spalte. new Product macht daraus ein Objekt: Tee.
6. Die Schleife nimmt Zeile 3: ein Objekt mit einem Feld für jede Spalte. new Product macht daraus ein Objekt: Kaffee.
7. Das Array ist zu Ende, und die Schleife hört auf.
8. Das Array enthält gewöhnliche Objekte. console.log zeigt die Klasse und ihre Felder.

Genauer, für Neugierige

• Bei einer echten Datenbank auf einem Server ist die Adresse anders, und es gibt einen Benutzer und ein Passwort. Außerdem brauchst du den Treiber der Datenbank als Abhängigkeit im Projekt (für MySQL: das Paket mysql2):

import mysql from "mysql2/promise";

const conn = await mysql.createConnection({ host: "localhost", port: 3306, database: "shop", user: "shop_app", password: "geheim" });
console.log("Mit der Datenbank verbunden!");
await conn.end();

• Das Passwort steht nicht im Code. Es wird aus den Einstellungen oder aus einer Umgebungsvariable gelesen (process.env).

• Die Preise sind hier number, damit es kurz bleibt. In einem echten Shop wird Geld exakt gespeichert: in ganzen Cent, weil JavaScript keinen exakten Dezimaltyp hat.
• console.log(row) zeigt [Object: null prototype] { … }: Die Zeilen sind Objekte ohne Prototyp, nur mit den Feldern der Spalten.

Probier es aus

Was wird ausgegeben?

Die Produkte sind Kaffee 7,50 €, Tee 3,20 € und Schokolade 2,40 €. Was gibt das Programm aus?

const db = new DatabaseSync(":memory:");
setup(db);
for (const row of db.prepare("SELECT name FROM products WHERE price < 5 ORDER BY price DESC").all()) {
  console.log(row.name);
}
db.close();

Antwort

Kaffee
Tee
Schokolade

Tee
Schokolade

Schokolade
Tee

Prüfen

Problem

Die Suche, die alles zeigt

Die Website sucht ein Produkt nach dem Namen, den der Nutzer eintippt. Die Abfrage wird mit einem Template `…${name}…` zusammengesetzt:

function search(db, name) {
  const sql = `SELECT name, price FROM products WHERE name = '${name}'`;
  for (const row of db.prepare(sql).all()) {
    console.log(`  ${row.name}: ${row.price} €`);
  }
}

const db = new DatabaseSync(":memory:");
setup(db);
console.log("Suche: Tee");
search(db, "Tee");
console.log("Suche: x' OR '1'='1");
search(db, "x' OR '1'='1");
db.close();

Ausgabe

Suche: Tee
  Tee: 3.2 €
Suche: x' OR '1'='1
  Kaffee: 7.5 €
  Tee: 3.2 €
  Schokolade: 2.4 €

Mit „Tee“ funktioniert es. Mit x' OR '1'='1 wird die Abfrage zu ... WHERE name = 'x' OR '1'='1'. Die Bedingung ist immer wahr, und die ganze Tabelle kommt heraus. Die Eingabe des Nutzers ist Teil des SQL geworden. Das ist SQL-Injection, einer der häufigsten Angriffe auf Websites. Mit demselben Trick kann sich jemand ohne Passwort anmelden oder eine Tabelle löschen.

Die Idee

Parameter mit ?

Die Lösung ist, die Abfrage und die Werte getrennt zu schicken. Die Abfrage aus prepare hat ? an den Stellen der Werte, und du übergibst sie als Argumente von all, get oder run, in derselben Reihenfolge:

function search(db, name) {
  const sql = "SELECT name, price FROM products WHERE name = ?";
  for (const row of db.prepare(sql).all(name)) {
    console.log(`  ${row.name}: ${row.price} €`);
  }
}

Ausgabe

Suche: Tee
  Tee: 3.2 €
Suche: x' OR '1'='1

Jetzt ist x' OR '1'='1 nur Text, der als Name gesucht wird. Ein solches Produkt gibt es nicht, und nichts kommt heraus. Was der Nutzer auch eintippt, es kann nicht zu SQL werden.

Die Regel ist einfach: Ein Wert vom Nutzer, aus einer Datei oder aus dem Netzwerk wird nie in SQL hineingeklebt. Immer ?.

Probier es aus

Kaffee mit Apostroph

Die Funktion benennt ein Produkt um. Mit dem Namen Kaffee 'Arabica' bricht das Programm mit Error: near "Arabica": syntax error ab. In welcher Zeile ist der Fehler?

Wähle die Zeile mit dem Fehler.

1function rename(db, id, newName) {2 db.exec(`UPDATE products SET name = '${newName}' WHERE id = ${id}`);3}

Prüfen

Die Idee

Transaktion: alles oder nichts

Maria überweist 150 € an Jonas. Das sind zwei Abfragen: bei Jonas hinzufügen, bei Maria abziehen. Die Tabelle hat die Bedingung CHECK (balance >= 0), deshalb schlägt die zweite Abfrage fehl: Maria hat nur 100 €.

function openAccounts(db) {
  db.exec("CREATE TABLE accounts (id INT PRIMARY KEY, owner VARCHAR(50), balance INT CONSTRAINT no_debt CHECK (balance >= 0))");
  db.exec("INSERT INTO accounts VALUES (1, 'Maria', 100), (2, 'Jonas', 50)");
}

function move(db, id, amount) {
  db.prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?").run(amount, id);
}

function printBalances(db) {
  for (const row of db.prepare("SELECT owner, balance FROM accounts ORDER BY id").all()) {
    console.log(`  ${row.owner}: ${row.balance} €`);
  }
}

function transfer(db, from, to, amount) {
  move(db, to, amount);
  move(db, from, -amount);
}

const db = new DatabaseSync(":memory:");
openAccounts(db);
try {
  transfer(db, 1, 2, 150);
} catch {
  console.log("Die Überweisung von 150 € wurde abgelehnt.");
}
printBalances(db);
db.close();

Ausgabe

Die Überweisung von 150 € wurde abgelehnt.
  Maria: 100 €
  Jonas: 200 €

Die Überweisung wurde abgelehnt, aber Jonas hat schon 150 € aus dem Nichts bekommen. Standardmäßig wird jede Abfrage sofort gespeichert. Die zwei Abfragen müssen eine Transaktion sein: Entweder gehen beide durch, oder keine.

function transfer(db, from, to, amount) {
  db.exec("BEGIN");
  try {
    move(db, to, amount);
    move(db, from, -amount);
    db.exec("COMMIT");
  } catch (error) {
    db.exec("ROLLBACK");
    throw error;
  }
}

Ausgabe

Die Überweisung von 150 € wurde abgelehnt.
  Maria: 100 €
  Jonas: 50 €

• db.exec("BEGIN") beginnt eine Transaktion: Die Änderungen sind noch nicht endgültig.
• db.exec("COMMIT") speichert sie alle auf einmal.
• db.exec("ROLLBACK") macht sie alle rückgängig, wenn etwas fehlschlägt. Die Exception geht mit throw weiter nach oben, damit auch der Aufrufer davon erfährt.
• BEGIN, COMMIT und ROLLBACK sind gewöhnliche SQL-Befehle: So macht man Transaktionen auch in SQL selbst.

Genauer, für Neugierige

• Der Fehler für die fehlgeschlagene Abfrage hat den code ERR_SQLITE_ERROR und die Meldung CHECK constraint failed: no_debt. no_debt ist der Name der Bedingung: In der Tabelle steht balance INT CONSTRAINT no_debt CHECK (balance >= 0).

• Datenbanken versprechen bei Transaktionen vier Dinge, bekannt als ACID: alles oder nichts (Atomarität), die Daten bleiben nach den Regeln der Tabelle korrekt (Konsistenz), gleichzeitig laufende Transaktionen stören sich nicht gegenseitig (Isolation), und was gespeichert ist, geht nicht verloren, auch wenn der Strom ausfällt (Dauerhaftigkeit).

Die Idee

ORM: Objekte statt Zeilen

Mit node:sqlite schreibst du für jede Klasse SQL und machst die Zeilen von Hand zu Objekten. Ein ORM (objektrelationale Abbildung, object-relational mapping) nimmt dir das ab: Du beschreibst das Model, und die Bibliothek macht die Tabelle, die Abfragen und die Objekte selbst. Node hat kein ORM. Eines der ältesten und bekanntesten ist Sequelize (die Pakete sequelize und sqlite3 aus npm). Andere sind Prisma, TypeORM und Drizzle.

models.js

import { DataTypes, Op, Sequelize } from "sequelize";

const sequelize = new Sequelize({ dialect: "sqlite", storage: ":memory:", logging: false });

const Product = sequelize.define("Product", {
  name: { type: DataTypes.STRING(50), allowNull: false },
  price: DataTypes.DOUBLE,
}, { tableName: "products", timestamps: false });

• sequelize.define beschreibt ein Model: eine Tabelle mit dem Namen products (tableName).
• Jedes Feld ist eine Spalte mit einem Typ aus DataTypes. allowNull: false ist NOT NULL.
• Sequelize fügt von selbst eine Spalte id als Primärschlüssel hinzu, und die Datenbank gibt ihr einen Wert.
• timestamps: false schaltet die Spalten createdAt und updatedAt ab, die Sequelize sonst hinzufügt.

Speichern und Lesen gehen über das Model:

await sequelize.sync();
await Product.create({ name: "Kaffee", price: 7.50 });
await Product.create({ name: "Tee", price: 3.20 });

const cheap = await Product.findAll({ where: { price: { [Op.lt]: 5 } }, order: [["price", "ASC"]] });
for (const p of cheap) {
  console.log(`${p.name}: ${p.price} €`);
}

Ausgabe (mit installiertem sequelize und sqlite3)

Tee: 3.2 €

new Sequelize({ dialect: "sqlite", storage: ":memory:" }) sagt, welche Datenbank: hier SQLite im Speicher. sync() legt die Tabellen an. Product.create speichert ein Objekt. findAll baut die Abfrage aus einem Objekt: { price: { [Op.lt]: 5 } } ist WHERE price < 5. Das Ergebnis ist ein Array fertiger Objekte. Alle Methoden geben Promises zurück, deshalb benutzt du await.

Sequelize ist ein Paket, und die Prüfung der Website hat es nicht, deshalb läuft dieser Code hier nicht. Die Ausgabe stammt aus einem Lauf mit installiertem sequelize 6 und sqlite3 5 (npm install sequelize sqlite3).

Genauer, für Neugierige

• Die Bedingungen von findAll sind ein gewöhnliches Objekt, deshalb lassen sie sich leicht aus der Auswahl des Nutzers zusammensetzen. Eine Funktion, die nach dem Namen sucht:

function named(name) {
  return Product.findAll({ where: { name } });
}

• Eine Transaktion, die bei einem Fehler rückgängig gemacht wird: sequelize.transaction mit einer Funktion speichert am Ende alles und macht alles rückgängig, wenn die Funktion wirft. Jede Abfrage darin bekommt { transaction }:

await sequelize.transaction(async (transaction) => {
  await Product.create({ name: "Schokolade", price: 2.40 }, { transaction });
  // weitere Änderungen: bei einer Exception wird alles rückgängig gemacht, sonst wird alles gespeichert
});

• Mit logging: console.log statt false gibt Sequelize das SQL aus, das es schickt. So siehst du, dass darunter weiterhin SQL steckt.

Übung

Ein Shop mit Datenbank

Löse die Aufgaben in deinem Editor, mit node:sqlite und der Datenbank ":memory:".1. Katalog. Lege eine Tabelle products an und die Funktionen add, setPrice, remove und list, alle außer list mit ?. Füge Kaffee (7,50 €), Tee (3,20 €) und Schokolade (2,40 €) hinzu, gib sie aus, ändere den Preis des Kaffees auf 6,90 €, lösche die Schokolade und gib sie wieder aus:

Ausgabe

1. Kaffee: 7.5 €
2. Tee: 3.2 €
3. Schokolade: 2.4 €
---
1. Kaffee: 6.9 €
2. Tee: 3.2 €

2. Suche nach einem Teil des Namens. Die Produkte sind Kaffee, Kakao, Tee und Éclair. Eine Funktion find(db, text) findet alle Produkte, deren Name den Text enthält, ohne auf Groß- und Kleinschreibung zu achten. LOWER ändert in SQLite nur die Buchstaben A bis Z, deshalb bleibt É, wie es ist. Gib der Datenbank eine eigene Funktion: db.function("lower_text", (text) => text.toLowerCase()), dann WHERE lower_text(name) LIKE ? mit dem Wert `%${text.toLowerCase()}%`. Probiere „KA“, „éclair“ und einen Injection-Versuch:

Ausgabe

Suche nach „KA“:
  Kaffee: 7.5 €
  Kakao: 4.8 €
Suche nach „éclair“:
  Éclair: 2.4 €
Suche nach „' OR '1'='1“:
  keine Produkte gefunden

3. Eine Überweisung, die mittendrin fehlschlägt. Die Konten sind Maria (100 €) und Jonas (50 €). transfer(db, from, to, amount) zieht zuerst von from ab und fügt dann bei to hinzu. Wenn run() changes 0 zurückgibt (kein solches Konto), wirf new Error(`Kein Konto ${id}`). Alles ist zwischen BEGIN und COMMIT. Überweise 30 € von 1 an 2, dann 40 € von 1 an das Konto 99, das es nicht gibt:

Ausgabe

Überwiesen: 30 € von 1 an 2
Abgelehnt: Kein Konto 99
  Maria: 70 €
  Jonas: 80 €

Die zweite Überweisung hat Maria 40 € abgezogen, bevor sie fehlschlug, aber ROLLBACK hat die 40 € zurückgebucht.

Test

Teste dich

1. Was gibt db.prepare("SELECT ...").all() zurück?

Die Anzahl der geänderten ZeilenEin Array der Zeilen: jede ist ein Objekt mit einem Feld für jede SpalteEin Array von Product-Objekten

2. Warum ist ... WHERE name = '${name}' gefährlich?

Weil es langsam istWeil der Text des Nutzers Teil des SQL wird und die Abfrage verändern kannEs ist nicht gefährlich, wenn name ein String ist

3. Eine Überweisung besteht aus zwei Abfragen zwischen BEGIN und COMMIT. Die erste gelingt, die zweite schlägt fehl. Was macht ROLLBACK?

Es macht alle Änderungen seit BEGIN rückgängig, auch die ersteEs wiederholt die zweite AbfrageEs macht nur die zweite rückgängig

4. Was macht ein ORM wie Sequelize?

Es ersetzt die DatenbankEs macht nur SQL-Abfragen schnellerEs macht aus Objekten Zeilen und umgekehrt, ohne dass du für alles SQL schreibst

Prüfen

Lektion von vorn beginnen

Verwandte Lektionen

• Baut auf: Daten in Dateien · Arrays: push, splice und sort · Pfeilfunktionen · Daten, switch und Destrukturierung
• Geht weiter in: Injektionen: wenn Daten zum Befehl werden · Monolith, modularer Monolith, Services · Transactional Outbox · Eine Transaktion über Services hinweg (Saga)
• Aufgaben zum Thema: Programme in der echten Welt