Database Version Control with Migrations

Database migrations are version control for your database schema, allowing you to evolve your database structure alongside your application versions. Each migration represents a specific version change in your application's database schema, ensuring your database structure stays synchronized with your codebase as your application evolves.

This guide covers how to implement and manage these version-based database changes on Zerops, focusing on PostgreSQL as the recommended database system.

Environment Configuration

Configure your database connection in your environment variables:

zerops:
  - setup: app
    run:
      envVariables:
        DB_CONNECTION: pgsql
        DB_HOST: ${db_hostname}
        DB_PORT: 5432
        DB_DATABASE: myapp
        DB_USERNAME: ${db_user}
        DB_PASSWORD: ${db_password}

Backup your data

Before running migrations in production, it's strongly recommended to back up your database. Zerops provides automated daily backups - see our backup documentation for details.

Running Migrations

Automatic Migrations

The most reliable way to manage migrations in your deployment pipeline is through automatic execution. Configure this in your zerops.yml:

zerops.yml

zerops:
  - setup: app
    run:
      initCommands:
        - php artisan migrate --force --isolated

Caution

When running automatic migrations in production, the --force flag is necessary to bypass Laravel's safety prompt. Without this flag, Laravel asks for confirmation to help prevent accidental data loss.

Migrations in HA mode

The --isolated flag prevents multiple servers from running migrations simultaneously by using a cache lock.

Manual Migrations

For development and troubleshooting purposes, you can execute migrations manually through SSH:

# Connect to your zerops project
zcli vpn up

# SSH into your service using it's hostname (app)
ssh app

# For MacOS users
ssh app.zerops

Migration Commands

Essential migration commands for your workflow:

# Create a new migration file with timestamp
php artisan make:migration create_users_table

# Execute all pending migrations
php artisan migrate

# Revert the most recent migration operation
php artisan migrate:rollback

# Reset and rerun all migrations (warning: destroys existing data)
php artisan migrate:fresh

# Display current migration status
php artisan migrate:status

Best Practices

Migration Structure

database/migrations/create_users_table.php

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     * Creates the users table with essential fields.
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->id();                    // Auto-incrementing primary key
            $table->string('name');          // User's full name
            $table->string('email')->unique(); // Unique email address
            $table->timestamp('email_verified_at')->nullable(); // Email verification timestamp
            $table->string('password');      // Hashed password
            $table->rememberToken();         // Remember me token
            $table->timestamps();            // Created_at and updated_at timestamps
        });
    }

    /**
     * Reverse the migrations.
     * Removes the users table completely.
     */
    public function down()
    {
        Schema::dropIfExists('users');
    }
};

Safe Migration Practices

1. Implement Reversible Changes

public function down()
{
    // Always provide a way to undo migration changes
    Schema::table('users', function (Blueprint $table) {
        $table->dropColumn('new_column');
    });
}

2. Use Foreign Key Contraints

public function up()
{
    Schema::table('posts', function (Blueprint $table) {
        // Create relationship with cascading delete
        $table->foreignId('user_id')
              ->constrained()
              ->onDelete('cascade');
    });
}

3. Handle Large Tables Efficiently

public function up()
{
    // Step 1: Add nullable column to prevent blocking operations
    Schema::table('large_table', function (Blueprint $table) {
        $table->string('new_column')->nullable();
    });

    // Step 2: Update data in manageable chunks
    DB::table('large_table')
        ->orderBy('id')
        ->chunk(1000, function ($records) {
            foreach ($records as $record) {
                DB::table('large_table')
                    ->where('id', $record->id)
                    ->update(['new_column' => 'default_value']);
            }
    });
}

Testing Migrations

Create a Test Database

Add a testing connection to your config/database.php:

'testing' => [
    'driver' => 'pgsql',
    'host' => env('DB_TEST_HOST', '127.0.0.1'),
    'database' => env('DB_TEST_DATABASE', 'testing'),
    'username' => env('DB_TEST_USERNAME', 'postgres'),
    'password' => env('DB_TEST_PASSWORD', ''),
],

Migration Test Example

Create a test file at tests/Unit/MigrationTest.php:

/**
 * Test migration execution and schema verification
 */
public function test_migrations_can_be_run()
{
    // Execute all migrations
    Artisan::call('migrate');

    // Verify table creation
    $this->assertTrue(Schema::hasTable('users'));

    // Verify column structure
    $this->assertTrue(Schema::hasColumns('users', [
        'id', 'name', 'email', 'password'
    ]));
}

Migration Tips

• Always create a backup before running migrations in production
• Use database transactions for complex migrations
• Thoroughly test migrations in development environment
• Implement seeders for initial data population
• Monitor execution time for migrations on large tables

Troubleshooting

Common migration issues and their solutions:

1. Migration Timeout Configure longer timeout in zerops.yml:

zerops:
  - setup: app
    run:
      initCommands:
        - php artisan migrate --force --isolated --timeout=1000

2. Database Lock Timeout Adjust PDO settings in config/database.php:

'pgsql' => [
    // ...
    'options' => [
        PDO::ATTR_LOCK_TIMEOUT => 1000 // Milliseconds
    ]
],

3. Reset Migration State Commands:

# Reset all migrations
php artisan migrate:reset

# Re-run all migrations
php artisan migrate

Additional Resources

• Laravel Migration Documentation