mitmario.dev

Abschnitt 12 · Lektion 4

Daten abfragen

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

In Lektion 11.2 hast du eine Sammlung ausgeliefert, gefiltert und ein Einzelstück herausgesucht, alles mit filter, sort und slice. Dasselbe geht jetzt eine Etage tiefer, und der Unterschied ist größer, als er aussieht.

Vier Wörter, die man ständig braucht

Filtern, sortieren, begrenzen 
import { DatabaseSync } from "node:sqlite";
import { AUFBAU } from "./fixture.js";

const db = new DatabaseSync(":memory:");
db.exec(AUFBAU);

const jung = db.prepare("SELECT titel, jahr FROM buecher WHERE jahr >= ? ORDER BY jahr DESC").all(2020);
console.log("ab 2020, neueste zuerst:");
for (const buch of jung) console.log(`  ${buch.jahr}  ${buch.titel}`);

// LIMIT sagt wie viele, OFFSET sagt ab wo. Zusammen sind sie eine Seite.
const seite = db.prepare("SELECT titel FROM buecher ORDER BY titel LIMIT ? OFFSET ?").all(3, 3);
console.log("Seite 2 nach Titel sortiert:");
for (const buch of seite) console.log(`  ${buch.titel}`);

WHERE filtert, ORDER BY sortiert, LIMIT begrenzt, OFFSET überspringt. Mehr ist eine Übersichtsseite mit Blättern nicht.

ORDER BY jahr DESC sortiert absteigend, ohne DESC aufsteigend. Wer nach mehreren Spalten sortieren will, schreibt sie mit Komma hintereinander: ORDER BY jahr DESC, titel.

LIMIT und OFFSET sind die Blätterfunktion. Seite eins ist LIMIT 3 OFFSET 0, Seite zwei LIMIT 3 OFFSET 3, und so weiter. Ein Hinweis, der später Zeit spart: Ohne ORDER BY ist die Reihenfolge nicht zugesagt, und dann kann derselbe Eintrag auf Seite eins und auf Seite zwei auftauchen. Wer blättert, sortiert.

Warum das die Datenbank macht und nicht JavaScript

Man könnte alles laden und in JavaScript filtern. Bei zehn Zeilen macht das keinen Unterschied, und deshalb fällt es lange nicht auf.

Bei hunderttausend Zeilen macht es einen sehr großen. Der Weg über JavaScript liest alle hunderttausend Zeilen von der Platte, baut aus jeder ein Objekt im Arbeitsspeicher auf und wirft danach 99.990 davon weg. Der Weg über WHERE fragt die Datenbank, und die liefert zehn.

Und weil node:sqlite synchron arbeitet, ist das nicht nur langsam, sondern blockierend: Solange dein Prozess die hunderttausend Objekte baut, nimmt er keine andere Anfrage an.

Die Regel dazu passt in einen Satz: Was sich als WHERE, ORDER BY oder LIMIT schreiben lässt, gehört in die Abfrage.

all, get und run

all, get und run 
import { DatabaseSync } from "node:sqlite";
import { AUFBAU } from "./fixture.js";

const db = new DatabaseSync(":memory:");
db.exec(AUFBAU);

// all: immer ein Array, auch bei null oder einem Treffer.
const alle = db.prepare("SELECT titel FROM buecher WHERE jahr = ?").all(2019);
console.log("all mit zwei Treffern: ", JSON.stringify(alle));
console.log("all ohne Treffer:      ", JSON.stringify(db.prepare("SELECT titel FROM buecher WHERE jahr = ?").all(1900)));

// get: die erste Zeile oder undefined.
const eins = db.prepare("SELECT titel FROM buecher WHERE id = ?").get(3);
console.log("get mit Treffer:       ", JSON.stringify(eins));
console.log("get ohne Treffer:      ", JSON.stringify(db.prepare("SELECT titel FROM buecher WHERE id = ?").get(999)));

// run: keine Zeilen, nur die Bilanz.
const bilanz = db.prepare("UPDATE buecher SET jahr = jahr WHERE jahr = ?").run(2019);
console.log("run gibt die Bilanz:   ", `changes ${bilanz.changes}`);

Drei Methoden auf derselben vorbereiteten Anweisung, und die Wahl richtet sich danach, was zurückkommen soll.

all() gibt immer ein Array. Keine Treffer heißt leeres Array, nicht null, nicht undefined. Das ist bequem, weil du direkt darüber laufen kannst, ohne vorher zu prüfen.

get() gibt die erste Zeile oder undefined. Für eine Abfrage über den Schlüssel ist das die richtige Wahl, und das undefined ist genau der Fall, aus dem in Lektion 11.4 ein 404 geworden ist.

run() gibt keine Zeilen, sondern die Bilanz mit changes und lastInsertRowid. Es ist die Methode für INSERT, UPDATE und DELETE.

Eine Falle steckt darin, die man einmal getroffen haben muss: get() auf eine Abfrage mit vielen Treffern ist kein Fehler. Du bekommst kommentarlos den ersten. Wenn dir Daten fehlen und du nicht weißt warum, ist ein get(), das ein all() sein sollte, ein guter erster Verdacht.

Zählen, rechnen, gruppieren

Zählen, rechnen, gruppieren 
import { DatabaseSync } from "node:sqlite";
import { AUFBAU } from "./fixture.js";

const db = new DatabaseSync(":memory:");
db.exec(AUFBAU);

// Vier Rechnungen in einer Abfrage, eine Zeile zurueck.
const zahlen = db.prepare("SELECT count(*) AS anzahl, min(jahr) AS aeltestes, max(jahr) AS neuestes, round(avg(jahr)) AS schnitt FROM buecher").get();
console.log(`${zahlen.anzahl} Buecher, ${zahlen.aeltestes} bis ${zahlen.neuestes}, im Schnitt ${zahlen.schnitt}`);

// GROUP BY macht aus einer Zeile je Buch eine Zeile je Jahrzehnt.
console.log("Buecher je Jahrzehnt:");
const gruppen = db.prepare(`SELECT (jahr / 10) * 10 AS jahrzehnt, count(*) AS anzahl
  FROM buecher GROUP BY jahrzehnt ORDER BY jahrzehnt`).all();
for (const zeile of gruppen) console.log(`  ${zeile.jahrzehnt}er: ${zeile.anzahl}`);

count(*) zählt Zeilen, sum() summiert, avg() mittelt, min() und max() liefern die Ränder. Alle fünf geben eine Zeile zurück, auch über eine Million Datensätze, und deshalb holt man sie mit get().

Zwei Kleinigkeiten dazu, beide gemessen:

avg() liefert eine Kommazahl, hier 2018 genau, sonst gern etwas wie 2011.5. Wenn du eine glatte Zahl willst, sagst du das mit round(), und zwar am besten in SQL, damit die Rundung dort passiert, wo auch gerechnet wurde.

count(*) heißt in der Rückgabe wirklich count(*), wenn du nichts anderes sagst, und das ist ein unhandlicher Schlüssel in JavaScript. Deshalb steht in den Beispielen überall ein AS anzahl dahinter. Gewöhn dir das an, sobald eine Spalte eine Rechnung ist.

GROUP BY schließlich fasst Zeilen zusammen, und die Aggregate rechnen dann je Gruppe statt über alles. Im Beispiel wird aus zehn Büchern eine Zeile je Jahrzehnt. Es ist die eine SQL-Sache, die man zweimal lesen muss, und danach benutzt man sie ständig.

Zum Mitnehmen

Alles, was du in Lektion 11.2 mit filter, sort und slice gemacht hast, gibt es hier als vier Wörter. Und die Datenbank macht es an der Stelle, an der die Daten schon liegen.

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.