Database Migrations

Table of Contents

• Writing Migrations

  • Running Migrations
  • Rolling Back Migrations
  • Fresh Migrations

• Tables

  • Creating and Updating Tables
  • Table Options
  • Checking if Table Exists
  • Dropping Tables
  • Renaming Tables

• Columns

  • Fluent Column Syntax
  • Convenience Helpers
  • Column Types
  • Column Modifiers
  • Checking if Column Exists
  • Renaming a Column
  • Adding Column After Another
  • Dropping a Column

• Indexes

• Foreign Keys

• CLI Options Reference (Migration Commands)

• Compatibility Note

Migrations add versioning to database changes over time. The migration system implemented in Phenix is based on Phinx, and migrations extend Phenix\Database\Migration.

In Phenix, Migration::table() returns Phenix\Database\Migrations\Table, which includes fluent builders for columns and foreign keys.

Writing Migrations

To create a migration, run:

php phenix make:migration CreateUserTable

The migration class extends Phenix\Database\Migration:

<?php

declare(strict_types=1);

use Phenix\Database\Migration;

class CreateUsersTable extends Migration
{
    public function up(): void
    {
        // ...
    }

    public function down(): void
    {
        // ...
    }
}

Use up() to apply changes and down() to revert them.

Running Migrations

Run all pending migrations:

php phenix migrate

Useful options for migrate:

• --environment|-e: target environment (default: default)
• --target|-t: migrate up to a specific version
• --date|-d: migrate up to a specific date (YYYY[MM[DD[HH[II[SS]]]]])
• --fake: mark migrations as run without executing SQL

Examples:

php phenix migrate --environment=default
php phenix migrate -t 20231006144210
php phenix migrate -d 20231006
php phenix migrate --fake

Rolling Back Migrations

Rollback the latest migration batch:

php phenix migrate:rollback

Useful options for migrate:rollback:

• --environment|-e: target environment (default: default)
• --target|-t: rollback to a target version
• --date|-d: rollback to a target date (YYYY[MM[DD[HH[II[SS]]]]])
• --fake: mark rollbacks as executed without running SQL
• --force|-f: ignore breakpoints
• --dry-run|-x: print SQL without executing it

Examples:

php phenix migrate:rollback --environment=default
php phenix migrate:rollback -t 20231006144210
php phenix migrate:rollback -d 20231006
php phenix migrate:rollback --fake
php phenix migrate:rollback -t 0 -f
php phenix migrate:rollback --dry-run

Fresh Migrations

Drop all tables, rollback all migrations, and run migrations again:

php phenix migrate:fresh

Useful options for migrate:fresh:

• --seed|-s: run seeders after migrations
• --environment|-e: target environment (default: default)
• --dry-run|-x: print SQL without executing it

Examples:

php phenix migrate:fresh --seed
php phenix migrate:fresh --environment=default
php phenix migrate:fresh --dry-run

When you pass --seed, Phenix runs seeders after migrations. See Database Seeders.

Tables

Creating and Updating Tables

Use table() to get a fluent Table instance. Define columns and then call create(), update(), or save().

public function up(): void
{
    $table = $this->table('users');

    $table->id();
    $table->string('name', 100);
    $table->string('email', 150)->unique();
    $table->timestamps();

    $table->create();
}

Important: Explicit create(), update(), or save() is recommended for clarity.

Table Options

The second argument of table($name, $options) is an associative options array.

Option	Values
primary_key	array, string, false
engine	e.g. InnoDB (MySQL)
collation	e.g. utf8mb4_unicode_ci
signed	true, false

Example with custom primary key:

$table = $this->table('personal_access_tokens', ['id' => false, 'primary_key' => 'id']);
$table->uuid('id');
$table->create();

Checking if Table Exists

public function up(): void
{
    if (! $this->hasTable('users')) {
        $table = $this->table('users');
        $table->string('name', 100);
        $table->create();
    }
}

Dropping Tables

public function down(): void
{
    $this->table('users')->drop()->save();
}

Renaming Tables

$this->table('users')
    ->rename('legacy_users')
    ->update();

Columns

Fluent Column Syntax

Phenix exposes fluent methods directly on Table:

$table = $this->table('users');

$table->string('name', 100)->comment('User name');
$table->integer('age')->default(0);
$table->boolean('is_active')->default(true);

$table->create();

Convenience Helpers

• id(string $name = 'id'): unsigned big integer with identity.
• timestamps(bool $timezone = false): adds created_at and updated_at timestamp columns.

$table = $this->table('posts');

$table->id();
$table->string('title');
$table->timestamps();

$table->create();

Column Types

Text and character

• string($name, int $limit = 255)
• text($name, ?int $limit = null)
• char($name, int $limit = 255)
• uuid($name)
• ulid($name)

$table->string('title', 150);
$table->text('content')->nullable();
$table->char('country_code', 2);
$table->uuid('external_id');
$table->ulid('public_id');

Numeric

• integer($name, ?int $limit = null, bool $identity = false)
• bigInteger($name, bool $identity = false)
• smallInteger($name, bool $identity = false)
• unsignedInteger($name, ?int $limit = null, bool $identity = false)
• unsignedBigInteger($name, bool $identity = false)
• unsignedSmallInteger($name, bool $identity = false)
• decimal($name, int $precision = 10, int $scale = 2)
• unsignedDecimal($name, int $precision = 10, int $scale = 2)
• float($name)
• unsignedFloat($name)
• double($name, bool $signed = true)

$table->unsignedBigInteger('user_id');
$table->decimal('price', 10, 2)->default(0.00);
$table->double('ratio')->default(1.0);

Date and time

• date($name)
• dateTime($name)
• time($name, bool $timezone = false)
• timestamp($name, bool $timezone = false)
• interval($name) (PostgreSQL)

$table->date('published_on');
$table->dateTime('published_at')->nullable();
$table->timestamp('created_at')->currentTimestamp();
$table->timestamp('updated_at')->nullable()->onUpdateCurrentTimestamp();

Special and structured

• boolean($name)
• json($name)
• jsonb($name) (PostgreSQL)
• enum($name, array $values)
• set($name, array $values)

$table->boolean('is_published')->default(false);
$table->json('metadata')->nullable();
$table->enum('status', ['draft', 'published'])->default('draft');

Binary and network

• binary($name, ?int $limit = null)
• blob($name, ?int $limit = null)
• bit($name, int $limit = 1)
• inet($name) (PostgreSQL)
• cidr($name) (PostgreSQL)
• macaddr($name) (PostgreSQL)

$table->blob('payload')->nullable();
$table->bit('flags', 8)->default(0);
$table->inet('ip_address')->nullable();

Column Modifiers

Common modifiers available in fluent builders:

• nullable()
• comment(string $comment)
• default(...) (typed by column)
• limit(int $limit) / length(int $length)
• after(string $column)
• first()
• unique()

Additional modifiers by column category:

• Numeric sign and identity: signed(), unsigned(), identity()
• Decimal precision: precision(int), scale(int)
• Timestamps: currentTimestamp(), onUpdateCurrentTimestamp(), update(string)
• Timezone-related: timezone(bool) or withTimezone(bool) depending on type
• Enum/set values: values(array $values)
• Foreign key builder options: see Foreign Keys

$table->string('email')->unique();
$table->string('nickname')->nullable()->after('email');
$table->unsignedInteger('visits')->default(0);
$table->timestamp('updated_at')->onUpdateCurrentTimestamp();

Adapter notes:

• first() is meaningful for MySQL column positioning.
• deferrable() in foreign keys is PostgreSQL-only.
• enum/set use string fallback in SQLite.

Checking if Column Exists

$table = $this->table('users');

if ($table->hasColumn('username')) {
    // ...
}

Renaming a Column

$this->table('users')
    ->renameColumn('nickname', 'username')
    ->save();

Adding Column After Another

$table = $this->table('users');
$table->string('city', 100)->after('email');
$table->update();

Dropping a Column

$this->table('users')
    ->removeColumn('nickname')
    ->save();

Indexes

Use addIndex() for explicit indexes:

$table = $this->table('users');

$table->string('email', 150);
$table->unsignedInteger('tenant_id');

$table->addIndex(['email'], ['unique' => true]);
$table->addIndex(['tenant_id', 'email'], ['name' => 'idx_users_tenant_email']);

$table->create();

You can also create a unique single-column index with ->unique():

$table->string('username')->unique();

Foreign Keys

Phenix supports both direct and fluent APIs.

Direct API:

use Phenix\Database\Constants\ColumnAction;

$table = $this->table('posts');

$table->unsignedBigInteger('user_id');
$table->foreignKey('user_id', 'users', 'id', [
    'delete' => ColumnAction::CASCADE->value,
    'update' => ColumnAction::NO_ACTION->value,
]);

$table->create();

Fluent API:

use Phenix\Database\Constants\ColumnAction;

$table = $this->table('posts');

$table->unsignedBigInteger('author_id');
$table->foreign('author_id')
    ->references('id')
    ->on('users')
    ->onDelete(ColumnAction::SET_NULL)
    ->onUpdate(ColumnAction::RESTRICT)
    ->constraint('fk_posts_author_id');

$table->create();

Composite keys are supported in fluent style:

$table->foreign(['user_id', 'role_id'])
    ->references(['user_id', 'role_id'])
    ->on('user_roles');

PostgreSQL deferrable example:

$table->foreign('user_id')
    ->references('id')
    ->on('users')
    ->deferrable('IMMEDIATE');

CLI Options Reference (Migration Commands)

php phenix migrate -e default -t 20231006144210
php phenix migrate -d 20231006 --fake

php phenix migrate:rollback -e default -t 20231006144210
php phenix migrate:rollback -d 20231006 -f --dry-run --fake

php phenix migrate:fresh -e default
php phenix migrate:fresh --seed
php phenix migrate:fresh --dry-run

Compatibility Note

addColumn(...) from Phinx is still available when you need low-level control, but Phenix documentation recommends fluent migration builders as the default style.