14_Database

Keine Dateien in diesem Thema verfügbar.

Lernmaterialien

docker postgres

docker run --name postgres -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=students -p 5432:5432 -d postgres:16

docker run

This tells Docker to start a new container based on an image.

--name postgres

This gives your container a name so you can easily refer to it later.
docker stop postgres
docker start postgres
docker logs postgres

-e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=students

Sets an environment variable inside the container. PostgreSQL uses this variable to set the database superuser’s password (for user postgres).

This tells Postgres to create a database named students automatically when it first starts.

Database: students

User: postgres

Password: postgres

-p 5432:5432

This maps ports between your computer and the container.

The first 5432 → your host machine’s port (outside the container)

The second 5432 → the port inside the container that PostgreSQL listens on

Conect to localhost:5432

-d

Means detached mode — run the container in the background (like a service).

If you omit -d, the container runs in your terminal and shows logs directly.

postgres:16

That’s the Docker image you’re using — in this case, PostgreSQL version 16 from Docker Hub.

Docker will automatically:

Download the image if you don’t have it (docker pull postgres:16)

Use it to start a new container

docker ps

After running this command:

PostgreSQL runs inside Docker
Accessible at → Host: localhost, Port: 5432
Database: students
Username: postgres
Password: postgres

Introduction

We will upgrade our project to use PostgreSQL + Prisma and replace the in-memory array with real database CRUD operations.

Operation	Meaning	SQL Equivalent	Example in your app
C — Create	Add new data	`INSERT`	Add a new student
R — Read	Retrieve data	`SELECT`	Show all students
U — Update	Modify existing data	`UPDATE`	Change a student’s course
D — Delete	Remove data	`DELETE`	Delete a student

HTTP Method	Route	CRUD Action
`POST /students`	Create	Inserts a new row into your database
`GET /students`	Read (All)	Retrieves all students
`GET /students/:id`	Read (One)	Retrieves one student by ID
`PATCH /students/:id`	Update	Updates an existing record
`DELETE /students/:id`	Delete	Removes a record

prisma

Prisma is a modern database toolkit (often called an ORM — Object-Relational Mapper) for Node.js and TypeScript.

Prisma helps your JavaScript/TypeScript code talk to a database (like PostgreSQL, MySQL, SQLite, etc.) without writing raw SQL.

JavaScript:

const students = await prisma.student.findMany();

Equals:

SELECT * FROM Student;

Prisma main parts:

Part	Role
Prisma Client	A generated JS/TS library that you use in your code to read/write data.
Prisma Schema	A single file (`schema.prisma`) where you define your database models and relationships.
Prisma Migrate	Tool for creating and running database migrations (changes to tables/columns).
Prisma Studio	A simple web UI to view and edit data in your database visually.

Prisma CRUD:

Action	Prisma Code	SQL Equivalent
Create	`await prisma.student.create({ data: { name: "Anna", course: "Math" } })`	`INSERT INTO "Student" ...`
Read	`await prisma.student.findMany()`	`SELECT * FROM "Student"`
Update	`await prisma.student.update({ where: { id: 1 }, data: { course: "Physics" } })`	`UPDATE "Student" SET course='Physics' WHERE id=1`
Delete	`await prisma.student.delete({ where: { id: 1 } })`	`DELETE FROM "Student" WHERE id=1`

remove prisma

rm -rf prisma
rm -rf node_modules/.prisma
rm -f prisma.config.*

packages

killall node
npm i -D prisma
npm i @prisma/client
npx prisma init --datasource-provider postgresql

.env (Prisma creates the file) – add your database connecion:

DATABASE_URL="postgresql://postgres:postgres@localhost:5432/students?schema=public"

Prisma Schema

prisma/schema.prisma:

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model Student {
  id        Int      @id @default(autoincrement())
  name      String
  course    String
  createdAt DateTime @default(now())
}

Execute the migration:

rm prisma.config.ts 
npx prisma migrate dev --name init_students

Restart the client

npx prisma generate

Generate testdata

package.json ergänzen:

"prisma": { "seed": "node prisma/seed.js" }

prisma/seed.js:

const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

async function main() {
  const data = [
    { name: "Anna", course: "Informatik" },
    { name: "Ben", course: "Mathematik" },
    { name: "Clara", course: "Physik" },
    { name: "David", course: "Chemie" },
    { name: "Eva", course: "Biologie" },
  ];
  await prisma.student.createMany({ data, skipDuplicates: true });
  console.log("Seed completed.");
}

main()
  .catch((e) => console.error(e))
  .finally(() => prisma.$disconnect());

Seed ausführen:

npx prisma db seed

Prisma server.js

server.js ersetzen/ergänzen:

const express = require("express");
const path = require("path");
const { PrismaClient } = require("@prisma/client");

const app = express();
const prisma = new PrismaClient();
const port = 3000;

// Static Frontend (public/)
app.use(express.static(path.join(__dirname, "public")));
app.use(express.json());

// --- ROUTES ---

// Root
app.get("/", (req, res) => {
  res.sendFile(path.join(__dirname, "public", "index.html"));
});

// Alle Studenten
app.get("/students", async (req, res) => {
  try {
    const students = await prisma.student.findMany({ orderBy: { id: "asc" } });
    res.json(students);
  } catch (e) {
    console.error(e);
    res.status(500).json({ error: "Failed to fetch students" });
  }
});

// Einzelner Student
app.get("/students/:id", async (req, res) => {
  const id = Number(req.params.id);
  try {
    const student = await prisma.student.findUnique({ where: { id } });
    if (!student) return res.status(404).json({ error: "Student not found" });
    res.json(student);
  } catch (e) {
    console.error(e);
    res.status(500).json({ error: "Failed to fetch student" });
  }
});

// Anlegen
app.post("/students", async (req, res) => {
  const { name, course } = req.body;
  if (!name || !course) {
    return res.status(400).json({ error: "Name und Kurs sind erforderlich" });
  }
  try {
    const created = await prisma.student.create({ data: { name, course } });
    res.status(201).json(created);
  } catch (e) {
    console.error(e);
    res.status(500).json({ error: "Failed to create student" });
  }
});

// Vollständig aktualisieren (PUT)
app.put("/students/:id", async (req, res) => {
  const id = Number(req.params.id);
  const { name, course } = req.body;
  if (!name || !course) {
    return res.status(400).json({ error: "Name und Kurs sind erforderlich (PUT)" });
  }
  try {
    const updated = await prisma.student.update({ where: { id }, data: { name, course } });
    res.json(updated);
  } catch (e) {
    if (e.code === "P2025") return res.status(404).json({ error: "Student not found" });
    console.error(e);
    res.status(500).json({ error: "Failed to update student" });
  }
});

// Teilweise aktualisieren (PATCH)
app.patch("/students/:id", async (req, res) => {
  const id = Number(req.params.id);
  const data = {};
  if (req.body.name !== undefined) data.name = req.body.name;
  if (req.body.course !== undefined) data.course = req.body.course;

  if (Object.keys(data).length === 0) {
    return res.status(400).json({ error: "Nichts zu ändern übergeben" });
  }

  try {
    const updated = await prisma.student.update({ where: { id }, data });
    res.json(updated);
  } catch (e) {
    if (e.code === "P2025") return res.status(404).json({ error: "Student not found" });
    console.error(e);
    res.status(500).json({ error: "Failed to update student" });
  }
});

// Löschen
app.delete("/students/:id", async (req, res) => {
  const id = Number(req.params.id);
  try {
    await prisma.student.delete({ where: { id } });
    res.status(204).send();
  } catch (e) {
    if (e.code === "P2025") return res.status(404).json({ error: "Student not found" });
    console.error(e);
    res.status(500).json({ error: "Failed to delete student" });
  }
});

// Graceful shutdown
process.on("SIGINT", async () => {
  await prisma.$disconnect();
  process.exit(0);
});

// Start
app.listen(port, () => {
  console.log(`Server läuft unter http://localhost:${port}`);
});

Test curl

Let’s go through all typical CRUD endpoints for your /students API and show you exactly what each curl command should look like.

npm run dev

GET all students

Fetch all rows from the database:

curl -X GET http://localhost:3000/students

GET a single student (by ID)

Example: get the student with id = 1

curl -X GET http://localhost:3000/students/1

POST – create a new student

curl -X POST http://localhost:3000/students \
  -H "Content-Type: application/json" \
  -d '{"name": "Lukas", "course": "Physik"}'

PATCH – update a student

curl -X PATCH http://localhost:3000/students/6 \
  -H "Content-Type: application/json" \
  -d '{"name": "Lukas", "course": "Chemie"}'

DELETE – remove a student

curl -X DELETE http://localhost:3000/students/6

Git

Do not forget.

Semester_4