Database Migrations Guide

⚠️ Documentation Under Review: This documentation is currently being updated and verified against the actual implementation. Some information may be incorrect or incomplete. Please verify all code examples against the actual source code before use.

Overview

The Neuron MVC component provides database migration capabilities powered by Phinx. Migrations enable version control of database schema changes, allowing structured evolution of the database across development, staging, and production environments.

Architecture

Migration System Components

• MigrationManager (Neuron\Mvc\Database\MigrationManager): Orchestrates migration execution
• Phinx Integration: Underlying migration engine
• Migration Files: PHP classes defining schema changes
• Migration Tracking: Database table recording applied migrations

Configuration

Database and migration configuration is defined in config/neuron.yaml.

Database Configuration

database:
  adapter: mysql
  host: localhost
  port: 3306
  name: cms_database
  user: cms_user
  pass: secure_password
  charset: utf8mb4

Migration Configuration

migrations:
  path: db/migrate           # Migration files directory
  seeds_path: db/seed        # Seed files directory
  table: phinx_log           # Migration tracking table
  schema_file: db/schema.yaml  # Schema export file path
  auto_dump_schema: false    # Auto-export schema after migrations

Migration Paths

Migrations are stored in the project root, not in vendor packages:

your-project/
├── db/
│   ├── migrate/           # Migration files
│   ├── seed/              # Seed files
│   └── schema.yaml        # Database schema (auto-generated)

The installer creates this structure automatically. For manual setup:

mkdir -p db/migrate db/seed

CLI Commands

All migration commands are provided by the neuron-php/mvc component.

db:migration:generate

Create a new migration file.

Usage:

./vendor/bin/neuron db:migration:generate <name>

Arguments:

• name (required): Migration name in PascalCase (e.g., CreateUsersTable, AddIndexToPostsTable)

Options:

• --class=<name>: Custom class name
• --template=<path>: Path to custom migration template
• --config=<path>: Path to configuration directory

Examples:

# Create migration for users table
./vendor/bin/neuron db:migration:generate CreateUsersTable

# Create migration for adding column
./vendor/bin/neuron db:migration:generate AddEmailVerifiedToUsers

# Create migration for adding index
./vendor/bin/neuron db:migration:generate AddIndexToPostsSlug

Generated File:

Creates a timestamped migration file in db/migrate/:

db/migrate/20250112143052_create_users_table.php

The timestamp ensures migrations execute in chronological order.

db:migrate

Execute pending migrations.

Usage:

./vendor/bin/neuron db:migrate [options]

Options:

• --target=<version>: Migrate to specific version
• --dry-run: Show migrations without executing
• --fake: Mark migrations as executed without running them
• --force: Force migrations in production environment
• --config=<path>: Path to configuration directory

Examples:

# Run all pending migrations
./vendor/bin/neuron db:migrate

# Migrate to specific version
./vendor/bin/neuron db:migrate --target=20250112143052

# Preview migrations without executing
./vendor/bin/neuron db:migrate --dry-run

db:rollback

Rollback previously executed migrations.

Usage:

./vendor/bin/neuron db:rollback [options]

Options:

• --target=<version>: Rollback to specific version
• --date=<date>: Rollback to specific date
• --force: Force rollback in production environment
• --config=<path>: Path to configuration directory

Examples:

# Rollback last migration batch
./vendor/bin/neuron db:rollback

# Rollback to specific version
./vendor/bin/neuron db:rollback --target=20250112143052

# Rollback to specific date
./vendor/bin/neuron db:rollback --date=20250112

db:migrate:status

Display migration status.

Usage:

./vendor/bin/neuron db:migrate:status [options]

Options:

• --config=<path>: Path to configuration directory

Output:

Displays table showing:

• Migration ID (timestamp)
• Migration name
• Status (up/down)
• Execution date

db:seed

Execute database seeders.

Usage:

./vendor/bin/neuron db:seed [options]

Options:

• --seed=<name>: Specific seeder to run
• --config=<path>: Path to configuration directory

Examples:

# Run all seeders
./vendor/bin/neuron db:seed

# Run specific seeder
./vendor/bin/neuron db:seed --seed=UserSeeder

db:schema:dump

Export database schema to YAML for reference.

Usage:

./vendor/bin/neuron db:schema:dump [options]

Options:

• --output=<path> or -o: Custom output file path
• --config=<path>: Path to configuration directory

Examples:

# Export to default location (db/schema.yaml)
./vendor/bin/neuron db:schema:dump

# Export to custom location
./vendor/bin/neuron db:schema:dump --output=docs/database-schema.yaml

Purpose:

This command exports your current database structure to YAML format, similar to Rails' schema.rb. The schema file provides:

• Current migration version
• All tables with columns, types, and constraints
• Indexes and unique constraints
• Foreign key relationships

Note: The schema file is for reference only. Always use migrations for actual database changes.

Migration File Structure

Basic Migration

<?php

use Phinx\Migration\AbstractMigration;

class CreateUsersTable extends AbstractMigration
{
    public function change()
    {
        $table = $this->table( 'users' );

        $table->addColumn( 'username', 'string', ['limit' => 255] )
              ->addColumn( 'email', 'string', ['limit' => 255] )
              ->addColumn( 'password_hash', 'string', ['limit' => 255] )
              ->addColumn( 'created_at', 'timestamp', ['default' => 'CURRENT_TIMESTAMP'] )
              ->addColumn( 'updated_at', 'timestamp', [
                  'default' => 'CURRENT_TIMESTAMP',
                  'update' => 'CURRENT_TIMESTAMP'
              ] )
              ->addIndex( ['email'], ['unique' => true] )
              ->addIndex( ['username'], ['unique' => true] )
              ->create();
    }
}

Migration with Up/Down Methods

For migrations requiring different logic for forward and backward execution:

<?php

use Phinx\Migration\AbstractMigration;

class AddRoleToUsers extends AbstractMigration
{
    public function up()
    {
        $table = $this->table( 'users' );
        $table->addColumn( 'role', 'string', [
                  'limit' => 50,
                  'default' => 'subscriber',
                  'after' => 'email'
              ] )
              ->update();
    }

    public function down()
    {
        $table = $this->table( 'users' );
        $table->removeColumn( 'role' )
              ->update();
    }
}

Column Types

Phinx supports the following column types:

• string: VARCHAR
• text: TEXT
• integer: INT
• biginteger: BIGINT
• float: FLOAT
• decimal: DECIMAL
• datetime: DATETIME
• timestamp: TIMESTAMP
• time: TIME
• date: DATE
• binary: BLOB
• boolean: TINYINT(1)
• json: JSON (MySQL 5.7+)
• uuid: CHAR(36)

Column Options

$table->addColumn( 'column_name', 'type', [
    'limit' => 255,              // Column length
    'null' => false,             // Allow NULL
    'default' => 'value',        // Default value
    'after' => 'other_column',   // Column position (MySQL )
    'comment' => 'Description',  // Column comment
    'signed' => false,           // Unsigned integer (MySQL)
    'precision' => 10,           // Decimal precision
    'scale' => 2,                // Decimal scale
]);

Index Management

Adding Indexes

// Simple index
$table->addIndex( ['column_name'] );

// Unique index
$table->addIndex( ['email'], ['unique' => true] );

// Composite index
$table->addIndex( ['user_id', 'post_id'] );

// Named index
$table->addIndex( ['slug'], ['name' => 'idx_posts_slug'] );

Removing Indexes

// Remove by column
$table->removeIndex( ['email'] );

// Remove by name
$table->removeIndexByName( 'idx_posts_slug' );

Foreign Keys

Adding Foreign Keys

$table->addForeignKey( 'user_id',                    // Local column
    'users',                      // Referenced table
    'id',                         // Referenced column
    [
        'delete' => 'CASCADE',    // ON DELETE action
        'update' => 'NO_ACTION'   // ON UPDATE action
    ] );

Removing Foreign Keys

$table->dropForeignKey( 'user_id' );

Seeder Files

Seeders populate database with test or default data.

Creating a Seeder

Create file in db/seed/:

<?php

use Phinx\Seed\AbstractSeed;

class UserSeeder extends AbstractSeed
{
    public function run()
    {
        $data = [
            [
                'username' => 'admin',
                'email' => '[email protected]',
                'password_hash' => password_hash( 'admin123', PASSWORD_ARGON2ID ),
                'role' => 'admin',
                'created_at' => date( 'Y-m-d H:i:s' ),
                'updated_at' => date( 'Y-m-d H:i:s' ),
            ],
        ];

        $this->table( 'users' )->insert( $data )->save();
    }
}

Schema Export

The migration system can automatically export your database schema to YAML format for reference purposes. This provides a readable snapshot of your database structure that's easy to review and version control.

Automatic Schema Export

Enable automatic schema export in config/neuron.yaml:

migrations:
  schema_file: db/schema.yaml
  auto_dump_schema: true

With this enabled, the schema automatically exports after each successful:

• ./vendor/bin/neuron db:migrate
• ./vendor/bin/neuron db:rollback

Manual Schema Export

Export schema on demand:

# Export to default location
./vendor/bin/neuron db:schema:dump

# Export to custom path
./vendor/bin/neuron db:schema:dump --output=docs/schema.yaml

Schema File Format

The exported schema includes:

• Migration version (latest applied migration)
• All tables with column definitions
• Indexes (including unique constraints)
• Foreign key relationships

Example Output:

version: '20250112143052'
tables:
  users:
    columns:
      id:
        type: integer
        null: false
        primary: true
        auto_increment: true
      username:
        type: string
        limit: 255
        null: false
      email:
        type: string
        limit: 255
        null: false
      password_hash:
        type: string
        limit: 255
        null: false
      role:
        type: string
        limit: 50
        null: false
        default: subscriber
      created_at:
        type: timestamp
        null: false
        default: CURRENT_TIMESTAMP
      updated_at:
        type: timestamp
        null: false
        default: CURRENT_TIMESTAMP
    indexes:
      - name: users_username_unique
        columns:
          - username
        unique: true
      - name: users_email_unique
        columns:
          - email
        unique: true
  posts:
    columns:
      id:
        type: integer
        null: false
        primary: true
        auto_increment: true
      user_id:
        type: integer
        null: false
      title:
        type: string
        limit: 255
        null: false
      slug:
        type: string
        limit: 255
        null: false
      body:
        type: text
        null: false
      created_at:
        type: timestamp
        null: false
    indexes:
      - name: posts_slug_unique
        columns:
          - slug
        unique: true
    foreign_keys:
      - name: posts_user_id_fk
        columns:
          - user_id
        referenced_table: users
        referenced_columns:
          - id
        on_delete: CASCADE

Use Cases

Documentation: Provides readable reference of database structure without connecting to database.

Version Control: Track schema changes alongside code changes. Review database structure modifications in pull requests.

Team Collaboration: Everyone has access to current database schema. New developers can quickly understand database structure.

Code Reviews: Easier to review and discuss database changes. Schema changes are visible in git diffs.

Quick Reference: Check table structures, column types, and relationships instantly.

Important Notes

• Schema file is for reference only
• Does not replace migrations
• Always use migrations for actual database changes
• Auto-export only runs on successful (non-fake) migrations
• Commit schema file to version control

Best Practices

Migration Naming

Use descriptive names following conventions:

• Create table: CreateUsersTable, CreatePostsTable
• Add column: AddEmailVerifiedToUsers, AddSlugToPosts
• Remove column: RemoveOldColumnFromUsers
• Add index: AddIndexToPostsSlug
• Add foreign key: AddUserIdForeignKeyToPosts

Schema Changes

1. Always test migrations: Test both up and down methods
2. Backup before production: Backup database before running migrations
3. Use transactions: Migrations run in transactions by default
4. Avoid data migrations: Separate data migrations from schema migrations
5. Document complex changes: Add comments for non-obvious migrations

Version Control

1. Commit migrations: Include migration files in version control
2. Commit schema file: Include db/schema.yaml in version control for reference
3. Never modify executed migrations: Create new migration instead
4. Coordinate with team: Ensure migrations don't conflict
5. Use schema export: Enable auto_dump_schema to maintain up-to-date schema reference

Common Patterns

Renaming Columns

public function change()
{
    $table = $this->table( 'users' );
    $table->renameColumn( 'old_name', 'new_name' )
          ->update();
}

Changing Column Type

public function change()
{
    $table = $this->table( 'users' );
    $table->changeColumn( 'age', 'integer', ['null' => false] )
          ->update();
}

Multiple Tables

public function change()
{
    // Create first table
    $users = $this->table( 'users' );
    $users->addColumn( 'username', 'string' )
          ->create();

    // Create second table with foreign key
    $posts = $this->table( 'posts' );
    $posts->addColumn( 'user_id', 'integer' )
          ->addColumn( 'title', 'string' )
          ->addForeignKey( 'user_id', 'users', 'id', ['delete' => 'CASCADE'] )
          ->create();
}

Troubleshooting

Migration Fails to Execute

Check migration syntax: Ensure PHP syntax is valid

php -l db/migrate/20250112143052_create_users_table.php

Check database connection: Verify configuration in config/neuron.yaml

Check permissions: Ensure database user has schema modification privileges

Migration Already Executed

If attempting to re-run a migration:

# Check status
./vendor/bin/neuron db:migrate:status

# Rollback if needed
./vendor/bin/neuron db:rollback

Seeder Issues

Seeder not found: Ensure seeder file exists in db/seed/ directory

Duplicate data: Seeders may insert duplicate data on multiple runs. Add duplicate checking:

public function run()
{
    // Check if data exists
    $exists = $this->fetchRow( "SELECT id FROM users WHERE email = '[email protected]'" );

    if( !$exists )
{
        // Insert data
    }
}

Additional Resources

• Phinx Documentation
• Configuration Reference
• CLI Commands Reference