Database migrations

Who this is for#

Pionia Shop’s catalog should survive a laptop reboot — and the same tables should appear when a teammate clones the repo. You want a shared recipe for products, customers, and orders, not a one-off SQL file nobody remembers to run.

What you will learn#

• Create a table with make:table and apply it with migrate
• Describe columns in fluent PHP (email(), nullable(), foreign keys)
• Change, roll back, and (later) ship migrations from a package

Before you start#

How it works#

Think of each migration as a dated diary entry for the database: “on this day we created products.” Pionia keeps a list of which entries have already been applied, so migrate only runs what is new.

flowchart LR
  Make["make:table products"] --> File["database/migrations/…"]
  File --> Migrate["php pionia migrate"]
  Migrate --> DB[("products table")]
  Migrate --> Diary["migrations table"]

Your first Pionia Shop table#

Real-world story: Ada should see mugs in the catalog after you restart the server. That means a real products table — not an in-memory array.

php pionia make:table products \
  --columns="name:string,price:decimal,stock:integer" \
  --timestamps

<?php

use Pionia\Database\Blueprint;
use Pionia\Database\Migrations\Migration;
use Pionia\Database\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('products', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->decimal('price', 10, 2);
            $table->integer('stock')->default(0);
            $table->timestamps();
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('products');
    }
};

php pionia migrate
php pionia migrate:status

Daily commands#

Makers#

Column DSL (comma-separated on makers):

email:email:unique,name:string,phone:phone:nullable,org_id:foreignId:orgs

Fluent columns#

Columns are NOT NULL by default. Chain modifiers in any order:

$table->string('first_name')->nonNullable()->unique();
$table->string('middle_name')->nullable();
$table->email()->unique()->comment('Login identity');
$table->integer('age')->unsigned()->default(0);
$table->foreignId('org_id')->constrained('orgs')->cascadeOnDelete();

Common types#

Schema::create('posts', function (Blueprint $table) {
    $table->id();
    $table->uuid('uuid');
    $table->ulid('ulid');
    $table->string('title', 120);
    $table->text('body')->nullable();
    $table->boolean('published')->default(false);
    $table->decimal('price', 10, 2);
    $table->json('meta')->nullable();      // JSON (PostgreSQL: JSON)
    $table->jsonb('attrs')->nullable();    // JSONB on PostgreSQL
    $table->enum('status', ['draft', 'live']);
    $table->timestamps();
    $table->softDeletes();
});

Specialized fields#

These add useful CHECK constraints at the database level — still validate in your services too.

$table->email();           // VARCHAR(254) + email-like CHECK
$table->phone();           // VARCHAR(32)
$table->url('website');    // must start with http(s)://
$table->slug()->unique();
$table->ipAddress();
$table->macAddress();
$table->year('founded');
$table->money('price');    // DECIMAL(19,4)
$table->currency();        // length 3
$table->rememberToken();
$table->morphs('taggable'); // type + id

Altering tables#

Schema::table('tasks', function (Blueprint $table) {
    $table->string('slug')->after('title'); // MySQL only
    $table->dropColumn('assignee');
    $table->renameColumn('body', 'content');
});

Schema::rename('posts', 'articles');
Schema::hasTable('tasks');
Schema::hasColumn('tasks', 'title');
Schema::raw('CREATE INDEX idx_tasks_status ON tasks (status)');

Many-to-many (pivot tables)#

php pionia make:pivot posts tags --timestamps

Schema::manyToMany('posts', 'tags', function (Blueprint $table) {
    $table->string('role')->nullable(); // optional pivot data
}, timestamps: true);

Schema::dropManyToMany('posts', 'tags');

Creates post_tag with post_id, tag_id, a composite primary key, and cascade deletes.

Configuration#

environment/settings.ini:

[migrations]
PATH=database/migrations
TABLE=migrations

Drivers: SQLite, MySQL/MariaDB, and PostgreSQL via your [db] connection.

Package / provider migrations#

Composer packages can ship migrations by returning absolute directories from a provider:

public function migrations(): array
{
    return [dirname(__DIR__) . '/database/migrations'];
}

migrate and migrate:status merge the app path with every registered provider path. Duplicate basenames raise MigrationException.

Common mistakes#

• Running migrate:fresh on production MySQL/PostgreSQL without --force — the command refuses; double-check the database name first.
• Forgetting down() — rollbacks need a matching drop or reverse alter.
• Creating products before projects when using a foreign key — migrate parent tables first, or make the FK nullable and add it later.
• Treating Blueprint CHECKs as app validation — still use #[ValidateField] / rules() in services.

What’s next#