mitmario.dev

Daten einfügen

Node.js Sandbox 4 Min Lesezeit 3 BeispieleLektion 3 von 9

Eine einzelne Zeile ist schnell in der Tabelle. Schwierig wird es bei vielen Zeilen und bei Änderungen, die zusammengehören. Tausend einzelne INSERT in eine Datenbankdatei dauern mehrere Sekunden. Und bricht ein Programm zwischen zwei solchen Änderungen ab, bleibt die erste allein in der Tabelle stehen.

Der Platzhalter und das Vorbereiten

Eine Zeile einfügen
import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync(":memory:");
db.exec(`CREATE TABLE buecher (
  id INTEGER PRIMARY KEY,
  titel TEXT NOT NULL,
  jahr INTEGER
)`);

// Die Fragezeichen sind Platzhalter. Was hineinkommt, sagt run().
const einfuegen = db.prepare("INSERT INTO buecher (titel, jahr) VALUES (?, ?)");

const ergebnis = einfuegen.run("Node in der Praxis", 2020);

console.log("geschriebene Zeilen:", ergebnis.changes);
console.log("vergebene Kennung:  ", ergebnis.lastInsertRowid);

// Kein id angegeben, und trotzdem steht eine drin.
const zweites = einfuegen.run("HTTP verstehen", 2017);
console.log("nächste Kennung:    ", zweites.lastInsertRowid);

Ein Stück SQL heißt Anweisung (englisch statement). Eine Anweisung, die etwas liest, heißt Abfrage (query). db.prepare() nimmt eine Anweisung mit Fragezeichen darin und gibt etwas zurück, das sich ausführen lässt. Die Fragezeichen sind Platzhalter, diesmal in der Anweisung und nicht im Pfad wie :id bei Express. Die Werte kommen erst bei run() dazu, in der Reihenfolge der Fragezeichen.

Warum man die Werte nicht einfach in den Text schreibt, ist die wichtigste Sicherheitsregel des ganzen Kurses. Sie bekommt in Lektion 12.5 eine eigene Lektion. Hier zählt erst einmal nur, dass Anweisung und Werte getrennt zur Datenbank gehen.

Was run() zurückgibt

run() gibt ein Objekt mit zwei Eigenschaften zurück, und beide braucht man ständig.

  • changes sagt, wie viele Zeilen betroffen waren. Beim Einfügen ist das langweilig, da steht immer 1. Beim Ändern und Löschen zeigt es, ob es die Zeile überhaupt gab, und wird damit zur Grundlage für den ehrlichen 404.
  • lastInsertRowid ist die Kennung, die die Tabelle gerade vergeben hat. Die Location-Kopfzeile beim Anlegen braucht sie, und du bekommst sie geschenkt, statt selbst zu zählen.

Die Kennung kommt von der Tabelle

Im Beispiel steht in keinem INSERT eine id, und trotzdem haben beide Zeilen eine. Das liegt an id INTEGER PRIMARY KEY aus der vorigen Lektion. Lässt man den Wert weg, setzt SQLite die nächste freie Zahl ein.

Die Regel aus Lektion 11.3 hält damit von selbst. Die Kennung vergibt der Server und nicht der Aufrufer, und das Programm muss nichts dafür tun. Der Zähler naechsteId im Speicher kann weg, und die Kennungen der Tabelle überstehen einen Neustart.

In manchen Anleitungen steht noch AUTOINCREMENT dabei. In SQLite braucht man das fast nie. Es verhindert nur, dass eine gelöschte Kennung später noch einmal vergeben wird. Dafür legt SQLite eine zusätzliche Verwaltungstabelle an, und Arbeit kostet es auch. Ohne guten Grund lässt man es weg.

Eine Anweisung, viele Werte

Eine Anweisung, viele Werte
import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync(":memory:");
db.exec("CREATE TABLE buecher (id INTEGER PRIMARY KEY, titel TEXT NOT NULL)");

const titel = ["Erstes", "Zweites", "Drittes", "Viertes", "Fünftes"];

// Einmal vorbereitet, fuenfmal ausgefuehrt. Die Anweisung wird dabei nur
// einmal zerlegt und geprueft, nicht bei jedem Durchlauf neu.
const einfuegen = db.prepare("INSERT INTO buecher (titel) VALUES (?)");

db.exec("BEGIN");
for (const eintrag of titel) einfuegen.run(eintrag);
db.exec("COMMIT");

console.log("Zeilen:", db.prepare("SELECT count(*) AS n FROM buecher").get().n);
console.log("letzte Kennung:", db.prepare("SELECT max(id) AS m FROM buecher").get().m);

prepare() einmal, run() fünfmal. Das ist kürzer und auch schneller, denn die Datenbank zerlegt und prüft die Anweisung einmal und führt danach nur noch aus. Bei fünf Zeilen ist das egal, bei fünftausend nicht mehr.

Im Beispiel steht prepare() vor der Schleife und run() darin. Schreibst du beides in die Schleife, bezahlst du das Vorbereiten bei jedem Durchlauf noch einmal.

BEGIN und COMMIT um viele Zeilen

Ohne Transaktion behandelt SQLite jedes einzelne INSERT als abgeschlossenen Vorgang, und vor der nächsten Zeile wartet es jedes Mal, bis die Platte das Geschriebene bestätigt hat. Dieses Warten ist der ganze Aufwand. Tausend einzeln eingefügte Zeilen brauchen so mehrere Sekunden, dieselben tausend Zeilen zwischen BEGIN und COMMIT ein paar Millisekunden. Das ist ungefähr das Tausendfache.

Wie groß der Unterschied in der Sandbox ist, zeigt messen.js. Die Datei liegt in der Aufgabe dieser Lektion unter Dateien, gehört zum Kurs und bleibt, wie sie ist. Sie fügt tausend Zeilen einmal einzeln und einmal in einer Transaktion ein und misst beide Male die Zeit. Du startest sie im Terminal mit node messen.js. Die Zahlen fallen auf jeder Maschine und bei jedem Lauf etwas anders aus, aber der große Abstand bleibt.

Höre ich „SQLite ist langsam“, frage ich zuerst nach der Transaktion.

Ganz oder gar nicht

Ganz oder gar nicht
import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync(":memory:");
db.exec("CREATE TABLE konten (name TEXT PRIMARY KEY, stand INTEGER NOT NULL)");
db.exec("INSERT INTO konten VALUES ('Anna', 100), ('Bert', 100)");

const abbuchen = db.prepare("UPDATE konten SET stand = stand - ? WHERE name = ?");
const gutschreiben = db.prepare("UPDATE konten SET stand = stand + ? WHERE name = 'gibtsnicht'");

const staende = () =>
  db
    .prepare("SELECT name, stand FROM konten ORDER BY name")
    .all()
    .map((zeile) => `${zeile.name} ${zeile.stand}`)
    .join(", ");

console.log("vorher: ", staende());

db.exec("BEGIN");
try {
  abbuchen.run(30, "Anna");
  // Die Gutschrift trifft niemanden. Das faellt hier auf, also wird alles
  // zurueckgedreht.
  const gebucht = gutschreiben.run(30);
  if (gebucht.changes === 0) throw new Error("Empfänger gibt es nicht");
  db.exec("COMMIT");
} catch (fehler) {
  db.exec("ROLLBACK");
  console.log("abgebrochen:", fehler.message);
}

console.log("nachher:", staende());

Alles zwischen BEGIN und COMMIT passiert entweder vollständig oder gar nicht. Im Beispiel wird Anna abgebucht, die Gutschrift geht ins Leere, und am Ende hat Anna trotzdem ihre hundert.

BEGIN öffnet die Klammer, COMMIT schließt sie und macht alles gültig. ROLLBACK verwirft alles seit dem BEGIN, als wäre nichts gewesen.

Im Beispiel ist es mit Absicht ein Fall ohne Absturz. Die Gutschrift läuft technisch fehlerfrei, sie trifft nur niemanden. Darum steht dort die Prüfung auf changes === 0. Wenn ein Programm etwas Sinnloses tut, dreht eine Transaktion nicht von selbst zurück. Das ROLLBACK steht deshalb im catch.

Zehn Bücher kommen in der Aufgabe mit einer vorbereiteten Anweisung in die Tabelle.

Zum Mitnehmen

Eine Zeile kommt über prepare und run in die Tabelle, mit Fragezeichen für die Werte, und run meldet changes und die vergebene Kennung. Viele Zeilen gehören in eine Transaktion, die um ein Vielfaches schneller ist und bei einem Fehler mit ROLLBACK alles zurücknimmt.

Jetzt du

Basis Konto, kostenlos

Zu dieser Lektion gehört eine Aufgabe. Du schreibst den Code selbst, und nach jedem Lauf sagt dir eine Prüfliste, was schon stimmt.

Dafür brauchst du das Basis Konto. Es kostet nichts, und ein Passwort gibt es auch nicht.

In diesem Kurs läuft dein Code auf einem Server. Dafür hat das Basis Konto 1 Stunde im Monat, mehr Zeit gibt es mit dem Premium Konto.

Was in dieser Lektion steckt

  • Artikel mit 3 Beispielen zum Ausprobieren

    Steht hier, ohne Konto lesbar.

  • Aufgabe, dein Code läuft auf einem Server

    Öffnet sich mit dem Basis Konto.