Alaa Amer

Database Migrations & Schema: Laravel Database Management

Database Migrations & Schema: Laravel Database Management

Expert Guide by Alaa Amer – Professional Web Developer & Applications Designer

Laravel database migrations provide version control for your database schema. Learn professional techniques for managing database changes in team environments.

2️⃣ Advanced Schema Operations

Complex Table Modifications:

<?php
// database/migrations/2024_01_02_000000_enhance_posts_table.php
return new class extends Migration
{
    public function up(): void
    {
        Schema::table('posts', function (Blueprint $table) {
            // Add new columns
            $table->string('subtitle')->nullable()->after('title');
            $table->decimal('price', 10, 2)->nullable()->after('content');
            $table->json('custom_fields')->nullable()->after('settings');

            // Modify existing columns
            $table->text('excerpt')->nullable()->change();
            $table->string('slug', 300)->change();

            // Add indexes
            $table->index(['status', 'is_featured', 'published_at'], 'posts_featured_published_idx');
            $table->index(['user_id', 'created_at']);

            // Add foreign key constraints
            $table->foreignId('editor_id')->nullable()->constrained('users')->nullOnDelete();

            // Add check constraints (MySQL 8.0.16+)
            $table->check('price >= 0', 'posts_price_positive');
            $table->check('reading_time > 0', 'posts_reading_time_positive');
        });
    }

    public function down(): void
    {
        Schema::table('posts', function (Blueprint $table) {
            // Drop indexes first
            $table->dropIndex('posts_featured_published_idx');
            $table->dropIndex(['user_id', 'created_at']);

            // Drop foreign keys
            $table->dropForeign(['editor_id']);

            // Drop columns
            $table->dropColumn(['subtitle', 'price', 'custom_fields', 'editor_id']);
        });
    }
};

Pivot Table Creation:

<?php
// database/migrations/2024_01_03_000000_create_post_tag_table.php
return new class extends Migration
{
    public function up(): void
    {
        Schema::create('post_tag', function (Blueprint $table) {
            $table->id();

            // Foreign Keys
            $table->foreignId('post_id')->constrained()->cascadeOnDelete();
            $table->foreignId('tag_id')->constrained()->cascadeOnDelete();

            // Additional Pivot Data
            $table->foreignId('added_by')->nullable()->constrained('users')->nullOnDelete();
            $table->integer('sort_order')->default(0);
            $table->json('metadata')->nullable();

            $table->timestamps();

            // Composite unique constraint
            $table->unique(['post_id', 'tag_id']);

            // Indexes
            $table->index('post_id');
            $table->index('tag_id');
            $table->index('added_by');
        });
    }

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

Polymorphic Relationship Tables:

<?php
// database/migrations/2024_01_04_000000_create_images_table.php
return new class extends Migration
{
    public function up(): void
    {
        Schema::create('images', function (Blueprint $table) {
            $table->id();

            // File Information
            $table->string('filename');
            $table->string('original_name');
            $table->string('path', 500);
            $table->string('url', 500)->nullable();
            $table->string('mime_type', 100);
            $table->unsignedBigInteger('size'); // in bytes

            // Image Dimensions
            $table->unsignedInteger('width')->nullable();
            $table->unsignedInteger('height')->nullable();

            // Polymorphic Columns
            $table->morphs('imageable'); // Creates imageable_id and imageable_type

            // Image Metadata
            $table->string('alt_text')->nullable();
            $table->text('caption')->nullable();
            $table->json('metadata')->nullable(); // EXIF data, etc.

            // Organization
            $table->string('collection', 100)->default('default');
            $table->integer('sort_order')->default(0);

            // User tracking
            $table->foreignId('uploaded_by')->nullable()->constrained('users')->nullOnDelete();

            $table->timestamps();

            // Indexes
            $table->index(['imageable_type', 'imageable_id']);
            $table->index('collection');
            $table->index('uploaded_by');
        });
    }

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

4️⃣ Migration Best Practices

Safe Production Migrations:

<?php
// database/migrations/2024_01_05_000000_add_indexes_safely.php
return new class extends Migration
{
    public function up(): void
    {
        // Use transactions for MySQL
        DB::transaction(function () {
            Schema::table('posts', function (Blueprint $table) {
                // Add indexes concurrently (PostgreSQL)
                if (DB::getDriverName() === 'pgsql') {
                    DB::statement('CREATE INDEX CONCURRENTLY posts_user_status_idx ON posts (user_id, status)');
                } else {
                    // MySQL - Add index normally (consider ALGORITHM=INPLACE)
                    $table->index(['user_id', 'status'], 'posts_user_status_idx');
                }
            });
        });
    }

    public function down(): void
    {
        Schema::table('posts', function (Blueprint $table) {
            $table->dropIndex('posts_user_status_idx');
        });
    }
};

// Large data migration with chunking
// database/migrations/2024_01_06_000000_migrate_post_stats.php
return new class extends Migration
{
    public function up(): void
    {
        // Migrate data in chunks to avoid memory issues
        DB::table('posts')->whereNull('reading_time')->chunkById(100, function ($posts) {
            foreach ($posts as $post) {
                $readingTime = $this->calculateReadingTime($post->content);

                DB::table('posts')
                    ->where('id', $post->id)
                    ->update(['reading_time' => $readingTime]);
            }
        });
    }

    public function down(): void
    {
        DB::table('posts')->update(['reading_time' => null]);
    }

    private function calculateReadingTime(string $content): int
    {
        $wordCount = str_word_count(strip_tags($content));
        return ceil($wordCount / 200); // 200 words per minute
    }
};

Team-Friendly Migration Commands:

# Check migration status
php artisan migrate:status

# Run migrations with output
php artisan migrate --verbose

# Rollback specific number of batches
php artisan migrate:rollback --step=3

# Reset and re-run all migrations
php artisan migrate:fresh

# Fresh with seeding (development only)
php artisan migrate:fresh --seed

# Show SQL without executing (Laravel 9+)
php artisan migrate --pretend

# Force run migrations (production)
php artisan migrate --force

Environment-Specific Migrations:

<?php
// database/migrations/2024_01_07_000000_add_development_data.php
return new class extends Migration
{
    public function up(): void
    {
        // Only run in development
        if (!app()->environment('production')) {
            Schema::create('debug_logs', function (Blueprint $table) {
                $table->id();
                $table->string('level');
                $table->text('message');
                $table->json('context')->nullable();
                $table->timestamps();

                $table->index(['level', 'created_at']);
            });
        }
    }

    public function down(): void
    {
        if (!app()->environment('production')) {
            Schema::dropIfExists('debug_logs');
        }
    }
};

💡 Advanced Database Techniques

Custom Schema Builder Macros:

<?php
// app/Providers/AppServiceProvider.php
use Illuminate\Database\Schema\Blueprint;

public function boot()
{
    // Add common column sets
    Blueprint::macro('commonTimestamps', function () {
        $this->timestamps();
        $this->softDeletes();
    });

    Blueprint::macro('userTracking', function () {
        $this->foreignId('created_by')->nullable()->constrained('users');
        $this->foreignId('updated_by')->nullable()->constrained('users');
    });

    Blueprint::macro('seoFields', function () {
        $this->string('meta_title', 60)->nullable();
        $this->string('meta_description', 160)->nullable();
        $this->json('meta_keywords')->nullable();
    });
}

// Usage in migrations
Schema::create('articles', function (Blueprint $table) {
    $table->id();
    $table->string('title');
    $table->text('content');
    $table->seoFields();
    $table->userTracking();
    $table->commonTimestamps();
});

Database Testing Utilities:

<?php
// tests/Feature/MigrationTest.php
namespace Tests\Feature;

use Tests\TestCase;
use Illuminate\Foundation\Testing\RefreshDatabase;

class MigrationTest extends TestCase
{
    use RefreshDatabase;

    public function test_posts_table_has_expected_columns()
    {
        $columns = Schema::getColumnListing('posts');

        $expectedColumns = [
            'id', 'title', 'slug', 'content', 'status',
            'user_id', 'category_id', 'created_at', 'updated_at'
        ];

        foreach ($expectedColumns as $column) {
            $this->assertContains($column, $columns);
        }
    }

    public function test_posts_table_has_proper_indexes()
    {
        $indexes = collect(DB::select("SHOW INDEX FROM posts"));

        $this->assertTrue($indexes->contains('Key_name', 'posts_status_published_at_index'));
        $this->assertTrue($indexes->contains('Key_name', 'posts_user_id_foreign'));
    }
}

Next Steps

Ready to harness the full power of database relationships? Learn Eloquent ORM Advanced Features for sophisticated data management.

📩 Need help with database migrations?