# 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
<?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

```php
// 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:

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

**Never concatenate user data into SQL** — only parameters. See [Security](/docs/code/security).

## Transactions

```php
$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 '%...'`.
