Databázové migrace v PostgreSQL: Tabulky, cizí klíče a JSONB sloupce

Databázové migrace jako verzovací systém pro PostgreSQL

V moderním vývoji softwaru je nemyslitelné spravovat databázové tabulky ručními zásahy přes grafické rozhraní. Databázové schéma musí být verzováno v repozitáři stejně jako aplikační kód.

V Laravelu tuto úlohu plní Migrace (uložené v adresáři database/migrations/). Při nasazení na server stačí spustit příkaz php artisan migrate, který automaticky aplikuje všechny nové migrace.

V našem projektu běží nejnovější verze PostgreSQL 18 (v Docker kontejneru). Podívejme se, jak je schéma naší vzdělávací platformy definováno v souboru database/migrations/0001_01_01_000010_create_course_tables.php.

1. Integrita dat a cizí klíče s kaskádovým mazáním

Základem relační databáze je integrita vztahů mezi tabulkami. V naší migraci vytváříme hierarchii course_modules → course_lessons → quiz_questions:

Schema::create('course_lessons', function (Blueprint $table): void {
    $table->id();
    $table->foreignId('course_module_id')->constrained('course_modules')->cascadeOnDelete();
    $table->unsignedSmallInteger('number');
    $table->string('slug')->unique();
    // ...
});

Co se zde odehrává uvnitř PostgreSQL?

1. $table->foreignId('course_module_id') vytvoří sloupec typu BIGINT odpovídající primárnímu klíči id rodiče.
2. ->constrained('course_modules') vytvoří skutečné databázové omezení cizího klíče (FOREIGN KEY CONSTRAINT). PostgreSQL odmítne vložit lekci, pokud zadaný modul neexistuje.
3. ->cascadeOnDelete() přidá pravidlo ON DELETE CASCADE. Pokud je smazán modul, PostgreSQL na úrovni své databázové enginy automaticky a atomicky smaže všechny jeho lekce (a díky stejnému pravidlu v quiz_questions i všechny navazující kvízové otázky). V databázi tak nikdy nezůstanou sirotčí záznamy.

2. Efektivní datové typy a indexy

Laravel Blueprint nabízí širokou škálu typů optimalizovaných pro PostgreSQL:

• $table->unsignedSmallInteger('number'): Pro čísla modulů a lekcí (1 až 10) by byl klasický integer (4 bajty) nebo bigInteger (8 bajtů) zbytečným plýtváním. unsignedSmallInteger (2 bajty, SMALLINT) šetří místo na disku i v mezipaměti RAM.
• $table->string('slug')->unique(): Vytváří unikátní B-Tree index na sloupci slug. Tento index je nezbytný pro bleskurychlé vyhledávání lekcí podle URL v Route Model Bindingu (O(log n) namísto pomalého full-table scanu O(n)).

3. Využití nativního typu JSON v PostgreSQL

V tabulkách course_lessons a student_profiles najdete sloupce:

$table->json('anatomy_files');
$table->json('completed_lessons');
$table->json('quiz_scores');

V PostgreSQL je JSON prvotřídním občanem. Oproti některým starším databázím, kde byl JSON pouze obyčejným textem, PostgreSQL validuje syntaktickou správnost JSON dokumentu a umožňuje v něm vyhledávat pomocí specializovaných JSON operátorů (->, ->>, @>).

4. Konfigurace spojení v config/database.php

V konfiguračním souboru config/database.php najdete nastavení ovladače 'pgsql':

• 'search_path' => 'public': Určuje výchozí PostgreSQL schéma.
• 'sslmode' => env('DB_SSLMODE', 'prefer'): Umožňuje bezpečné šifrované spojení s databází v produkčním prostředí Hetzneru.