Database Migrations Guide

This document provides comprehensive guidance for working with database migrations in Neuron CMS.

Cross-Database Compatibility
Core Principles
Migration Workflow
Common Scenarios
Upgrade Path Considerations
Best Practices
Troubleshooting

Cross-Database Compatibility

Neuron CMS supports SQLite, MySQL, and PostgreSQL. When writing migrations, follow these guidelines to ensure compatibility across all platforms:

Avoid Database-Specific Syntax

❌ DON'T use MySQL-specific features:

// MySQL-only: ON UPDATE CURRENT_TIMESTAMP
->addColumn('updated_at', 'timestamp', [
    'default' => 'CURRENT_TIMESTAMP',
    'update' => 'CURRENT_TIMESTAMP'  // ← PostgreSQL/SQLite ignore this
])

✅ DO handle timestamps at application level:

// Cross-database compatible
->addColumn('updated_at', 'timestamp', [
    'default' => 'CURRENT_TIMESTAMP'
])

// In your repository's update() method:
$model->setUpdatedAt(new \DateTimeImmutable());
$model->save();

Use Phinx's Cross-Database Abstractions

✅ DO use Phinx column types that work everywhere:

->addColumn('name', 'string', ['limit' => 255])
->addColumn('count', 'integer', ['default' => 0])
->addColumn('price', 'decimal', ['precision' => 10, 'scale' => 2])
->addColumn('created_at', 'timestamp', ['default' => 'CURRENT_TIMESTAMP'])

❌ DON'T use raw SQL for schema changes:

// Avoid database-specific SQL
$this->execute("ALTER TABLE users ADD COLUMN email VARCHAR(255)");

Foreign Keys Work Everywhere

✅ Foreign keys are properly enforced on all databases (including SQLite):

->addForeignKey('author_id', 'users', 'id', [
    'delete' => 'CASCADE',
    'update' => 'CASCADE'
])

Note: ConnectionFactory automatically enables foreign keys for SQLite via PRAGMA foreign_keys = ON.

Testing on All Databases

Before committing migrations:

Test locally on SQLite (easiest for development)
Test on MySQL if available
Test on PostgreSQL if available
CI tests run on all three - watch for failures

# Test migration on SQLite
TEST_DB_DRIVER=sqlite vendor/bin/phpunit tests/Integration

# Test migration on MySQL (if available)
TEST_DB_DRIVER=mysql vendor/bin/phpunit tests/Integration

# Test migration on PostgreSQL (if available)
TEST_DB_DRIVER=postgres vendor/bin/phpunit tests/Integration

Database-Specific Limitations

Be aware of platform differences:

Feature	SQLite	MySQL	PostgreSQL
ALTER COLUMN	❌ Limited	✅ Full support	✅ Full support
DROP COLUMN	❌ Requires table rebuild	✅ Supported	✅ Supported
ENUM types	❌ Use CHECK	✅ Native ENUM	✅ Native ENUM
Full-text search	Limited	✅ FULLTEXT	✅ ts_vector
JSON columns	✅ TEXT as JSON	✅ JSON type	✅ JSONB type

Recommendation: Design migrations that work on SQLite's limited ALTER TABLE support for maximum compatibility.

Core Principles

Never Modify Existing Migrations

CRITICAL RULE: Once a migration has been committed to the repository, NEVER modify it.

Exception: When adding new columns to a table, use the Dual Migration Pattern (see below).

Why?

Phinx tracks which migrations have been executed using a phinxlog table
Existing installations have already run the original migration
Modifying an existing migration will NOT update those installations
This creates schema drift between installations

Example of What NOT to Do:

// ❌ WRONG: Editing cms/resources/database/migrate/20250111000000_create_users_table.php
// to add a new column after it's already been committed
public function change()
{
    $table = $this->table( 'users' );
    $table->addColumn( 'username', 'string' )
        ->addColumn( 'email', 'string' )
        ->addColumn( 'new_column', 'string' )  // DON'T ADD THIS HERE!
        ->create();
}

The Dual Migration Pattern

When adding columns to an existing table, you must update TWO migration files:

1. Update the CREATE migration (for NEW installations):

// cms/resources/database/migrate/20250111000000_create_users_table.php
$usersTable->addColumn( 'username', 'string', [ 'limit' => 255 ] )
    ->addColumn( 'email', 'string', [ 'limit' => 255 ] )
    ->addColumn( 'new_column', 'string', [ 'limit' => 255, 'null' => true ] )  // ← ADD HERE
    ->create();

2. Create an ALTER migration (for EXISTING installations):

// cms/resources/database/migrate/20251205000000_add_new_column_to_users.php
use Phinx\Migration\AbstractMigration;

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

        // Check if column exists (handles both new and existing installations)
        if( !$table->hasColumn( 'new_column' ) )
        {
            $table->addColumn( 'new_column', 'string', [ 'limit' => 255, 'null' => true ] )
                ->update();
        }
    }
}

Why both?

CREATE migration: Ensures new installations get the complete, up-to-date schema from day one
ALTER migration: Upgrades existing installations that already ran the original CREATE migration
hasColumn() check: Makes the ALTER migration safe to run on both new and existing installations

Testing:

# ALWAYS run tests after updating migrations
./vendor/bin/phpunit tests

# If integration tests fail with "table already exists", clean test DB:
rm -f /tmp/cms_test_*.db && ./vendor/bin/phpunit tests

Migration Workflow

Creating a New Migration

Generate migration file with timestamp:

# Format: YYYYMMDDHHMMSS_description_of_change.php
# Example: 20251205143000_add_timezone_to_users.php

Use descriptive names:
- add_[column]_to_[table].php - Adding columns
- remove_[column]_from_[table].php - Removing columns
- create_[table]_table.php - Creating new tables
- rename_[old]_to_[new]_in_[table].php - Renaming columns
Place migrations in the correct location:
- CMS component: cms/resources/database/migrate/
- Test installations: testing/*/db/migrate/

Implementing the Migration

<?php

use Phinx\Migration\AbstractMigration;

/**
 * Brief description of what this migration does
 */
class AddTimezoneToUsers extends AbstractMigration
{
    /**
     * Change Method.
     *
     * Write your reversible migrations using this method.
     *
     * For more information see documentation:
     * https://book.cakephp.org/phinx/0/en/migrations.html
     */
    public function change()
    {
        $table = $this->table( 'users' );

        $table->addColumn( 'timezone', 'string', [
                'limit' => 50,
                'default' => 'UTC',
                'null' => false,
                'after' => 'last_login_at'  // Optional: specify column position
            ])
            ->update();
    }
}

Testing the Migration

Test in development environment:
```
php neuron db:migrate
```
Test rollback (if applicable):
```
php neuron db:rollback
```

Verify schema changes:

# SQLite
sqlite3 storage/database.sqlite3 "PRAGMA table_info(users);"

# MySQL
mysql -u user -p -e "DESCRIBE users;" database_name

Common Scenarios

Adding a Column to an Existing Table

class AddRecoveryCodeToUsers extends AbstractMigration
{
    public function change()
    {
        $table = $this->table( 'users' );
        $table->addColumn( 'two_factor_recovery_codes', 'text', [
                'null' => true,
                'comment' => 'JSON-encoded recovery codes for 2FA'
            ])
            ->update();
    }
}

Adding Multiple Columns

class AddUserPreferences extends AbstractMigration
{
    public function change()
    {
        $table = $this->table( 'users' );
        $table->addColumn( 'timezone', 'string', [ 'limit' => 50, 'default' => 'UTC' ] )
            ->addColumn( 'language', 'string', [ 'limit' => 10, 'default' => 'en' ] )
            ->addColumn( 'theme', 'string', [ 'limit' => 20, 'default' => 'light' ] )
            ->update();
    }
}

Renaming a Column

class RenamePasswordHashInUsers extends AbstractMigration
{
    public function change()
    {
        $table = $this->table( 'users' );
        $table->renameColumn( 'password_hash', 'hashed_password' )
            ->update();
    }
}

Adding an Index

class AddTimezoneIndexToUsers extends AbstractMigration
{
    public function change()
    {
        $table = $this->table( 'users' );
        $table->addIndex( [ 'timezone' ], [ 'name' => 'idx_users_timezone' ] )
            ->update();
    }
}

Modifying a Column (Breaking Change)

When you need to change a column's type or constraints:

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

        // Phinx doesn't directly support changeColumn in all cases
        // You may need to use raw SQL for complex changes
        $table->changeColumn( 'email', 'string', [
                'limit' => 320,  // Changed from 255 to support longer emails
                'null' => false
            ])
            ->update();
    }
}

Creating a New Table (with Foreign Keys)

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

        $table->addColumn( 'user_id', 'integer', [ 'null' => false ] )
            ->addColumn( 'token', 'string', [ 'limit' => 64 ] )
            ->addColumn( 'ip_address', 'string', [ 'limit' => 45, 'null' => true ] )
            ->addColumn( 'user_agent', 'string', [ 'limit' => 255, 'null' => true ] )
            ->addColumn( 'expires_at', 'timestamp', [ 'null' => false ] )
            ->addColumn( 'created_at', 'timestamp', [ 'default' => 'CURRENT_TIMESTAMP' ] )
            ->addIndex( [ 'token' ], [ 'unique' => true ] )
            ->addIndex( [ 'user_id' ] )
            ->addForeignKey( 'user_id', 'users', 'id', [
                'delete' => 'CASCADE',
                'update' => 'CASCADE'
            ])
            ->create();
    }
}

Upgrade Path Considerations

Problem: Schema Drift Between Installations

When you update the CMS code via composer update, the code changes (like Model classes expecting new columns) but the database schema doesn't automatically update.

Symptoms:

SQLSTATE[HY000]: General error: 1 no such column: column_name
Model methods reference columns that don't exist in older installations

Solution: Migration-Based Upgrades

Update the initial migration for NEW installations:
- Edit the create_*_table.php migration in development
- This ensures new installations get the complete schema
Create an ALTER migration for EXISTING installations:
- Create add_*_to_*.php migration with the same changes
- This updates installations that already ran the original migration

Example Workflow:

# Step 1: User model now needs 'timezone' column
# Don't edit: 20250111000000_create_users_table.php (old installations already ran this)

# Step 2: Create new migration
touch cms/resources/database/migrate/20251205000000_add_timezone_to_users.php

# Step 3: Implement the migration
# (see examples above)

# Step 4: Document in versionlog.md
# Version X.Y.Z
# - Added timezone column to users table (Migration: 20251205000000)

# Step 5: Users upgrade via composer and run:
php neuron db:migrate

cms:install Command Behavior

The cms:install command (src/Cms/Cli/Commands/Install/InstallCommand.php):

Copies ALL migration files from cms/resources/database/migrate/ to project
Skips migrations that already exist (by filename)
Optionally runs migrations

Limitation: When you run composer update, new migrations in the CMS package don't automatically copy to existing installations.

Workaround: Manually copy new migrations or run cms:install with reinstall option (will overwrite files).

Future Enhancement: Create cms:upgrade command to:

Detect new migrations in CMS package
Copy them to installation
Optionally run them

Best Practices

1. Use Descriptive Migration Names

✅ 20251205120000_add_two_factor_recovery_codes_to_users.php
❌ 20251205120000_update_users.php

2. Include Comments in Migration Code

/**
 * Add two-factor authentication recovery codes to users table
 *
 * This migration adds support for 2FA recovery codes, allowing users
 * to regain access if they lose their authenticator device.
 */
class AddTwoFactorRecoveryCodesToUsers extends AbstractMigration

3. Always Test Rollbacks

// Make migrations reversible when possible
public function change()
{
    // Phinx can automatically reverse addColumn, addIndex, etc.
    $table = $this->table( 'users' );
    $table->addColumn( 'timezone', 'string' )->update();
}

// For complex migrations, implement up/down explicitly
public function up()
{
    // Migration code
}

public function down()
{
    // Rollback code
}

4. Handle NULL Values Appropriately

When adding columns to tables with existing data:

// Good: Allow NULL or provide default value
$table->addColumn( 'timezone', 'string', [
    'default' => 'UTC',
    'null' => false
]);

// Alternative: Allow NULL, update later
$table->addColumn( 'timezone', 'string', [ 'null' => true ] );

5. Document Breaking Changes

If a migration requires manual intervention:

/**
 * BREAKING CHANGE: Removes legacy authentication method
 *
 * BEFORE RUNNING:
 * 1. Ensure all users have migrated to new auth system
 * 2. Backup the database
 * 3. Review docs at: docs/auth-migration.md
 */
class RemoveLegacyAuthColumns extends AbstractMigration

6. Version Documentation

Update versionlog.md with migration information:

## Version 2.1.0 - 2025-12-05

### Database Changes
- Added `two_factor_recovery_codes` column to users table
- Added `timezone` column to users table with default 'UTC'
- Migration files: 20251205000000_add_two_factor_and_timezone_to_users.php

### Upgrade Notes
Run `php neuron db:migrate` to apply schema changes.

Troubleshooting

Migration Already Exists Error

Problem: Migration file exists in both CMS package and installation, but with different content.

Solution:

Check which version ran (look at installation's file modification date)
Create a new migration to reconcile differences
Never overwrite the existing migration

Column Already Exists

Problem: Migration tries to add a column that already exists.

SQLSTATE[HY000]: General error: 1 duplicate column name

Solution:

public function change()
{
    $table = $this->table( 'users' );

    // Check if column exists before adding
    if( !$table->hasColumn( 'timezone' ) )
    {
        $table->addColumn( 'timezone', 'string', [ 'default' => 'UTC' ] )
            ->update();
    }
}

Migration Tracking Out of Sync

Problem: Phinx thinks a migration ran, but the schema change isn't present.

Solution:

# Check migration status
php neuron db:status

# If needed, manually fix phinxlog table
sqlite3 storage/database.sqlite3
> DELETE FROM phinxlog WHERE version = '20251205000000';
> .quit

# Re-run migration
php neuron db:migrate

Data Loss Prevention

Always backup before:

Dropping columns
Renaming columns
Changing column types
Dropping tables

# SQLite backup
cp storage/database.sqlite3 storage/database.sqlite3.backup

# MySQL backup
mysqldump -u user -p database_name > backup.sql

FilesExpand file tree

MIGRATIONS.md

Latest commit

History

MIGRATIONS.md

File metadata and controls

Database Migrations Guide

Table of Contents

Cross-Database Compatibility

Avoid Database-Specific Syntax

Use Phinx's Cross-Database Abstractions

Foreign Keys Work Everywhere

Testing on All Databases

Database-Specific Limitations

Core Principles

Never Modify Existing Migrations

The Dual Migration Pattern

Migration Workflow

Creating a New Migration

Implementing the Migration

Testing the Migration

Common Scenarios

Adding a Column to an Existing Table

Adding Multiple Columns

Renaming a Column

Adding an Index

Modifying a Column (Breaking Change)

Creating a New Table (with Foreign Keys)

Upgrade Path Considerations

Problem: Schema Drift Between Installations

Solution: Migration-Based Upgrades

cms:install Command Behavior

Best Practices

1. Use Descriptive Migration Names

2. Include Comments in Migration Code

3. Always Test Rollbacks

4. Handle NULL Values Appropriately

5. Document Breaking Changes

6. Version Documentation

Troubleshooting

Migration Already Exists Error

Column Already Exists

Migration Tracking Out of Sync

Data Loss Prevention

Additional Resources