Laravel Mastery: Mastering Database Migrations from JSON

JSON to Laravel Migration: Eloquent Casting, Indexes, and Zero-Downtime Deployment

Laravel's Schema Builder is a fluent PHP DSL that generates portable SQL across MySQL, PostgreSQL, and SQLite. But migration files are only the starting point — the decisions that matter most are how to cast JSON fields in the Eloquent model (so PHP works with typed values, not raw strings), which columns need composite indexes for your query patterns, how to safely modify a production table without downtime using ->after() and deferred constraint creation, and when to use foreignId()->constrained() vs a manual foreign() declaration.

Live Example: E-commerce Product Migration

// Input JSON
{
  "sku": "WIDGET-PRO-L",
  "name": "Widget Pro (Large)",
  "price": "149.99",
  "stock": 250,
  "category": "hardware",
  "is_available": true,
  "attributes": { "color": "black", "weight_kg": 1.2 },
  "published_at": "2024-01-15T08:30:00Z"
}

// Generated Migration — database/migrations/2024_01_01_create_products_table.php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration {
    public function up(): void {
        Schema::create('products', function (Blueprint $table) {
            $table->id();                                     // bigint unsigned auto-increment
            $table->string('sku', 64)->unique();
            $table->string('name', 200);
            $table->decimal('price', 10, 2);                  // precise money: 10 digits, 2 decimal
            $table->unsignedInteger('stock')->default(0);
            $table->string('category', 30);
            $table->boolean('is_available')->default(true);
            $table->json('attributes')->nullable();           // structured sub-object
            $table->timestamp('published_at')->nullable();
            $table->foreignId('brand_id')                     // FK to brands.id
                  ->nullable()
                  ->constrained()                             // adds FK constraint
                  ->nullOnDelete();                           // SET NULL on parent delete
            $table->timestamps();                             // created_at, updated_at
            $table->softDeletes();                            // deleted_at for soft delete

            // Composite indexes for common query patterns
            $table->index(['category', 'is_available'], 'idx_products_category_available');
            $table->index('published_at');
        });
    }

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

Eloquent Model: Casting JSON and Types

// app/Models/Product.php
namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;
use Illuminate\Database\Eloquent\Casts\AsCollection;

class Product extends Model {
    use SoftDeletes;

    protected $fillable = [
        'sku', 'name', 'price', 'stock', 'category',
        'is_available', 'attributes', 'published_at', 'brand_id',
    ];

    // Casting: tells Eloquent how to deserialize DB values into PHP types
    protected $casts = [
        'price'        => 'decimal:2',          // returns string with 2 decimal places
        'stock'        => 'integer',
        'is_available' => 'boolean',
        'attributes'   => AsCollection::class,  // JSON → Laravel Collection
        'published_at' => 'datetime',           // JSON string → Carbon instance
        'deleted_at'   => 'datetime',
    ];

    // Accessor for formatted price (read-only computed)
    public function getPriceFormattedAttribute(): string {
        return '$' . number_format($this->price, 2);
    }

    // Scope for common query filter
    public function scopeAvailable($query) {
        return $query->where('is_available', true)->whereNull('deleted_at');
    }

    public function scopeInCategory($query, string $category) {
        return $query->where('category', $category);
    }

    // Relationship
    public function brand() {
        return $this->belongsTo(Brand::class);
    }

    public function orderLines() {
        return $this->hasMany(OrderLine::class);
    }
}

// Usage — casting makes JSON work like a Collection
$product = Product::find(1);
$color   = $product->attributes->get('color');        // "black"
$product->attributes->put('color', 'white');           // modify in memory
$product->save();                                      // re-serializes to JSON

Adding Columns to an Existing Table

// New migration to add columns — never modify existing migration files
// database/migrations/2024_03_01_add_tags_to_products.php

return new class extends Migration {
    public function up(): void {
        Schema::table('products', function (Blueprint $table) {
            // ->after() controls column position (MySQL only — ignored by Postgres/SQLite)
            $table->json('tags')->nullable()->default(null)->after('attributes');
            $table->string('meta_title', 100)->nullable()->after('name');
            $table->text('description')->nullable()->after('meta_title');

            // Add index to existing column
            $table->index('category');
        });
    }

    public function down(): void {
        Schema::table('products', function (Blueprint $table) {
            $table->dropColumn(['tags', 'meta_title', 'description']);
            $table->dropIndex('products_category_index');
        });
    }
};

// Changing column type or size — requires doctrine/dbal:
// composer require doctrine/dbal
Schema::table('products', function (Blueprint $table) {
    $table->string('sku', 128)->change();   // resize VARCHAR
    $table->decimal('price', 12, 2)->change();  // increase precision
});

Zero-Downtime Deployment: Deferred Constraints

// Problem: adding NOT NULL column to a 10M-row table locks it for minutes
// Solution: add nullable → backfill → add NOT NULL constraint → drop nullable

// Step 1: add nullable (fast, no lock)
Schema::table('products', function (Blueprint $table) {
    $table->string('slug')->nullable()->after('name');
});

// Step 2: backfill in a separate release/job
Product::whereNull('slug')->chunkById(1000, function ($products) {
    foreach ($products as $product) {
        $product->update(['slug' => Str::slug($product->name . '-' . $product->sku)]);
    }
});

// Step 3: add unique constraint (separate deployment)
Schema::table('products', function (Blueprint $table) {
    $table->string('slug', 255)->nullable(false)->change();
    $table->unique('slug');
});

// Preview what SQL will run before touching production
php artisan migrate --pretend
// → alter table `products` add `slug` varchar(255) null after `name`

Polymorphic Relationships

// A Comment can belong to either a Post or a Product
Schema::create('comments', function (Blueprint $table) {
    $table->id();
    $table->morphs('commentable');   // adds commentable_id (bigint) + commentable_type (string)
    $table->foreignId('user_id')->constrained()->cascadeOnDelete();
    $table->text('body');
    $table->timestamps();
});

// Equivalent to:
// $table->unsignedBigInteger('commentable_id');
// $table->string('commentable_type');
// $table->index(['commentable_type', 'commentable_id']);

// Model relationship
class Comment extends Model {
    public function commentable() {
        return $this->morphTo();
    }
}

class Post extends Model {
    public function comments() {
        return $this->morphMany(Comment::class, 'commentable');
    }
}

// Query
$post->comments()->where('created_at', '>=', now()->subDays(7))->get();

JSON Column Querying

// Query into JSON fields using -> operator (MySQL/Postgres)
// attributes = {"color": "black", "weight_kg": 1.2}

// Filter by JSON key
Product::where('attributes->color', 'black')->get();

// JSON contains (Laravel 9+, Postgres JSONB)
Product::whereJsonContains('attributes->tags', 'featured')->get();

// JSON length
Product::whereJsonLength('attributes->tags', '>', 2)->get();

// Update a single JSON key (MySQL json_set)
Product::where('id', 1)->update([
    'attributes->color' => 'white',
]);

Best Practices for Production

• Use decimal for all monetary amounts: $table->decimal('price', 10, 2) maps to NUMERIC in Postgres and DECIMAL in MySQL — exact decimal arithmetic. Never use $table->float() for money; floating-point imprecision will cause rounding errors in financial calculations.
• Always write down() methods: Even if you think you'll never roll back, down() is required for php artisan migrate:rollback in development and staging. A migration without down() becomes untestable.
• Never modify existing migration files after deployment: Once a migration has run on any environment (staging, production), its file must never change. Create a new migration to make further changes. Laravel tracks executed migrations by filename in the migrations table.
• Use $casts for all non-string fields: Without casts, $product->is_available may return "1" (string) instead of true (bool) depending on the DB driver. Always declare casts so your model behaves correctly regardless of database.

FAQ

Q: What is the difference between json and jsonb column types?
A: $table->json() maps to JSON in MySQL and TEXT in SQLite — stored as text, no indexing. $table->jsonb() is PostgreSQL-specific: stores in binary format, supports GIN indexes for @> containment queries, and is faster for reads. Use jsonb when you need to query or index JSON keys; use json for portability.

Q: How do I add a full-text search index?
A: Use $table->fullText(['title', 'body']) (Laravel 8.x+). MySQL and Postgres support full-text; the generated SQL differs per driver. For MySQL: FULLTEXT INDEX. For Postgres: uses tsvector. Query with Model::whereFullText(['title', 'body'], 'search term').

Q: How do I handle enum columns?
A: Use $table->enum('status', ['draft', 'published', 'archived']). Note: adding a new enum value in MySQL requires a full table rebuild. For frequently-changing enums, use a string column with application-layer validation instead, or a check constraint on Postgres.

Q: How do I check which migrations have run?
A: Run php artisan migrate:status to see which migrations have run and which are pending. The migrations table in your database records the filename and batch number of every executed migration.