Migrations & database

Migrations & database

Migrations describe table structure in code. They're idempotent: already-applied ones are skipped (tracked in the cl_celena_migrations table), new ones are applied automatically — on plugin enable or on startup (for the core).

A migration file

It lives in plugins/<slug>/migrations/ (or core/Database/migrations/). The name is alphabetical, with a numeric prefix: 0001_create_reviews.php. The file returns a closure:

<?php
declare(strict_types=1);

use Celena\Core\Database\Blueprint;
use Celena\Core\Database\Connection;
use Celena\Core\Database\Schema;

return function (Schema $schema, Connection $conn): void {
    $schema->create('reviews', function (Blueprint $t) {
        $t->id();                                   // auto-increment PK
        $t->string('author', 191)->default('');
        $t->text('text')->nullable();
        $t->integer('rating')->default(5);
        $t->boolean('is_published')->default(false);
        $t->json('meta')->nullable();
        $t->datetime('created_at')->nullable();
        $t->index('is_published');
    });
};

Available Blueprint types: id, string, text, integer, bigInteger, boolean, decimal, json, datetime, timestamps, plus default(), nullable(), unique(), index().

Cross-database

Local development is on PostgreSQL, production is on MySQL. Therefore:

  • Use only the Blueprint API and QueryBuilder — they compile for the right driver.
  • Don't write MySQL-only SQL (or PG-only). For example, sort versions in PHP (version_compare), not with a DB-specific SQL function.
  • MySQL implicitly commits DDL: after CREATE TABLE the transaction is already closed. Don't mix DDL and DML in one migration if you rely on rollback. MigrationRunner accounts for this.

The table prefix (cl_) is added automatically: in code refer to a table without the prefix (builder('reviews') → cl_reviews).

Queries: QueryBuilder

// reads
$rows = $conn->builder('reviews')
    ->select('id', 'author', 'rating')
    ->where('is_published', '=', true)
    ->whereIn('rating', [4, 5])
    ->orderBy('created_at', 'desc')
    ->limit(10)->offset(0)
    ->get();

$one   = $conn->builder('reviews')->where('id', '=', 1)->first();  // ?array
$count = $conn->builder('reviews')->count();

// writes
$id = $conn->builder('reviews')->insert(['author' => 'Anna', 'rating' => 5]);
$conn->builder('reviews')->where('id', '=', 1)->update(['is_published' => true]);
$conn->builder('reviews')->where('id', '=', 1)->delete();

Raw SQL — only via prepared statements:

$stmt = $conn->run('SELECT count(*) FROM ' . $conn->table('reviews') . ' WHERE rating >= ?', [4]);

Never concatenate user data into SQL — only parameters. See Security.

Transactions

$conn->transaction(function () use ($conn) {
    $conn->builder('orders')->insert([...]);
    $conn->builder('order_items')->insert([...]);
});

transaction() handles MySQL's implicit DDL commit correctly.

Running

  • Plugin: migrations run on enable and idempotently on startup (if the plugin is active, as in the plugin.php example).
  • Core: on startup from core/Database/migrations/.
  • CLI: php bin/celena has commands for migrations and maintenance.
In production, back up before DELETE/ALTER and use narrow WHERE conditions — no broad LIKE '%...'.