mitmario.dev

Daten abfragen

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

Auf einer Übersichtsseite sollen die neuesten Bücher zuerst stehen, nur drei davon, und eine Zahl darüber. Mit filter und sort in JavaScript geht das. Nur holt das Programm dafür jede Zeile aus der Tabelle, auch die, die es gleich wieder wegwirft.

Vier Wörter, die man ständig braucht

Filtern, sortieren, begrenzen
import { DatabaseSync } from "node:sqlite";
import { AUFBAU } from "./bestand.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 braucht eine Übersicht mit mehreren Seiten nicht.

ORDER BY jahr DESC sortiert absteigend, ohne DESC aufsteigend. Für mehrere Spalten stehen sie mit Komma hintereinander, etwa ORDER BY jahr DESC, titel.

LIMIT und OFFSET teilen das Ergebnis in Seiten. Seite eins ist LIMIT 3 OFFSET 0, Seite zwei LIMIT 3 OFFSET 3, und so weiter. Ohne ORDER BY ist die Reihenfolge nicht zugesagt, und dann kann derselbe Eintrag auf Seite eins und auf Seite zwei auftauchen.

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. Über JavaScript liest das Programm alle hunderttausend Zeilen von der Platte und baut aus jeder ein Objekt im Arbeitsspeicher, nur um danach 99.990 davon wieder wegzuwerfen. Mit WHERE fragst du die Datenbank, und die liefert zehn.

Dazu blockiert das alles, denn node:sqlite arbeitet synchron. Solange der Prozess die hunderttausend Objekte baut, nimmt er keine andere Anfrage an. Mit WHERE, ORDER BY und LIMIT in der Abfrage bekommt JavaScript nur noch, was es wirklich zeigt.

all, get und run

all, get und run
import { DatabaseSync } from "node:sqlite";
import { AUFBAU } from "./bestand.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}`);

Alle drei Methoden hängen an derselben vorbereiteten Anweisung. Welche passt, richtet sich danach, was zurückkommen soll.

  • all() gibt immer ein Array. Keine Treffer heißt leeres Array, weder null noch undefined, und darüber lässt sich ohne Prüfung laufen.
  • get() gibt die erste Zeile oder undefined. Für eine Abfrage über den Schlüssel ist das die richtige Wahl, und das undefined ist der Fall, aus dem in der Bücher-API ein 404 wird.
  • run() gibt keine Zeilen, nur die Bilanz mit changes und lastInsertRowid. Es ist die Methode für INSERT, UPDATE und DELETE.

get() auf eine Abfrage mit vielen Treffern ist kein Fehler, es liefert kommentarlos den ersten. Fehlen Daten ohne erkennbaren Grund, steht oft ein get() da, wo ein all() hingehört.

Zählen, rechnen, gruppieren

Zählen, rechnen, gruppieren
import { DatabaseSync } from "node:sqlite";
import { AUFBAU } from "./bestand.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} Bücher, ${zahlen.aeltestes} bis ${zahlen.neuestes}, im Schnitt ${zahlen.schnitt}`);

// GROUP BY macht aus einer Zeile je Buch eine Zeile je Jahrzehnt.
console.log("Bücher 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 den kleinsten und den größten Wert. Diese Rechenfunktionen geben eine einzige Zeile zurück, auch über eine Million Zeilen. Deshalb holt man sie mit get().

avg() liefert eine Kommazahl, bei zwei Büchern von 2019 und 2020 wäre es 2019.5. Im Beispiel wird deshalb gleich in SQL mit round() gerundet, also dort, wo auch gerechnet wurde. Bei diesen zehn Büchern käme auch ohne Rundung 2018 heraus.

Ohne AS heißt die Spalte in der Rückgabe wörtlich count(*). In der Zeile steht dann dieser Schlüssel, und zahlen.anzahl ist undefined. Deshalb hat in den Beispielen jede gerechnete Spalte ein AS mit einem Namen.

GROUP BY fasst Zeilen zusammen, und die Rechenfunktionen rechnen dann je Gruppe statt über alles. Im Beispiel wird aus zehn Büchern eine Zeile je Jahrzehnt.

In der Aufgabe fragst du nach Anzahl, neuesten Büchern und Durchschnittsjahr des Bestands, jeweils mit einer eigenen Abfrage.

Zum Mitnehmen

WHERE, ORDER BY und LIMIT gehören in die Abfrage, sonst gibt die Datenbank jede Zeile heraus. all liefert immer ein Array, get die erste Zeile oder undefined, und eine gerechnete Spalte bekommt mit AS einen Namen.

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.