Laravel – Database & Migrations

Laravel

Database & Migrations

Complete reference for creating and running migrations, all common column types, foreign keys, indexes, rollback commands, database seeders, and model factories with Faker.

Artisan Commands: Use php artisan make:migration to create migration files. The --create flag pre-populates a Schema::create() call, while --table generates an Schema::table() for modifying existing tables.

📄terminal – migration commands
BASH

# Create a new migration (auto-generates table name from migration name)
php artisan make:migration create_posts_table --create=posts
php artisan make:migration create_categories_table --create=categories

# Modify an existing table
php artisan make:migration add_status_to_posts_table --table=posts
php artisan make:migration drop_legacy_column_from_users_table --table=users

# Run all pending migrations
php artisan migrate

# Run with verbose output
php artisan migrate -v

# Migrate a specific path
php artisan migrate --path=/database/migrations/2024_01_01_create_posts_table.php

# Roll back the last batch of migrations
php artisan migrate:rollback

# Roll back a specific number of steps
php artisan migrate:rollback --step=3

# Roll back ALL migrations
php artisan migrate:reset

# Roll back and re-run all migrations (destructive!)
php artisan migrate:refresh

# Drop all tables and re-migrate (destructive!)
php artisan migrate:fresh

# Seed after migrating
php artisan migrate:fresh --seed

# Show migration status
php artisan migrate:status

# Squash all migrations into a single SQL dump
php artisan schema:dump

Migration Structure: A migration file has up() for applying changes and down() for reverting them. Always implement down() so rollbacks work correctly.

📄database/migrations/2024_01_01_000000_create_posts_table.php
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.
     */
    public function up(): void
    {
        Schema::create('posts', function (Blueprint $table) {
            // ── Primary Key ───────────────────────────────────
            $table->id();                          // BIGINT UNSIGNED AUTO_INCREMENT PK
            // $table->uuid('id')->primary();      // UUID primary key
            // $table->ulid('id')->primary();      // ULID primary key

            // ── String / Text ─────────────────────────────────
            $table->string('title');               // VARCHAR(255)
            $table->string('slug', 191)->unique(); // VARCHAR(191) UNIQUE
            $table->string('excerpt')->nullable(); // nullable VARCHAR
            $table->text('body');                  // TEXT
            $table->mediumText('summary');         // MEDIUMTEXT
            $table->longText('content');           // LONGTEXT
            $table->char('code', 6);               // CHAR(6)

            // ── Numbers ───────────────────────────────────────
            $table->tinyInteger('sort_order')->default(0);
            $table->smallInteger('view_count')->unsigned()->default(0);
            $table->integer('likes')->default(0);
            $table->bigInteger('impressions')->default(0);
            $table->unsignedBigInteger('category_id');
            $table->float('rating', 3, 1)->default(0.0);  // FLOAT(3,1)
            $table->double('score', 5, 2)->nullable();
            $table->decimal('price', 10, 2)->default(0.00);

            // ── Boolean ───────────────────────────────────────
            $table->boolean('is_featured')->default(false);
            $table->boolean('is_published')->default(false);

            // ── Enums & Sets ──────────────────────────────────
            $table->enum('status', ['draft', 'published', 'archived'])->default('draft');
            $table->set('tags', ['laravel', 'php', 'vue', 'react']);

            // ── Dates & Times ─────────────────────────────────
            $table->date('published_on')->nullable();
            $table->time('publish_time')->nullable();
            $table->dateTime('scheduled_at')->nullable();
            $table->timestamp('published_at')->nullable();
            $table->year('release_year')->nullable();

            // ── JSON ──────────────────────────────────────────
            $table->json('meta')->nullable();
            $table->jsonb('settings')->nullable();  // PostgreSQL only

            // ── Binary / UUID ─────────────────────────────────
            $table->uuid('external_id')->nullable();
            $table->binary('thumbnail')->nullable();

            // ── IP / MAC ──────────────────────────────────────
            $table->ipAddress('author_ip')->nullable();
            $table->macAddress('device_mac')->nullable();

            // ── Foreign Keys ──────────────────────────────────
            $table->foreignId('user_id')->constrained()->cascadeOnDelete();
            // same as:
            // $table->unsignedBigInteger('user_id');
            // $table->foreign('user_id')->references('id')->on('users')->cascadeOnDelete();

            $table->foreignId('category_id')->nullable()->constrained()->nullOnDelete();

            // Foreign to a differently-named table
            $table->foreignId('author_id')->constrained('users')->cascadeOnDelete();

            // ── Soft Deletes & Timestamps ─────────────────────
            $table->softDeletes();   // adds deleted_at TIMESTAMP NULL
            $table->timestamps();    // adds created_at & updated_at

            // ── Indexes ───────────────────────────────────────
            $table->index('status');
            $table->index(['status', 'published_at'], 'idx_status_published');
            $table->fullText(['title', 'body'], 'ft_title_body'); // MySQL only
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('posts');
    }
};

Modifying Existing Tables: Use Schema::table() to add, change, or drop columns on an existing table. The ->change() method requires the doctrine/dbal package in Laravel 9 and below (built-in from Laravel 10+).

📄database/migrations/2024_06_01_000000_add_status_to_posts_table.php
PHP

<?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::table('posts', function (Blueprint $table) {
            // Add new columns
            $table->string('cover_image')->nullable()->after('slug');
            $table->unsignedInteger('read_time')->default(0)->after('body');

            // Rename a column
            $table->renameColumn('body', 'content');

            // Modify an existing column (no doctrine/dbal needed in L10+)
            $table->string('title', 500)->change();
            $table->text('excerpt')->nullable()->change();

            // Drop a column
            $table->dropColumn('legacy_field');

            // Drop multiple columns
            $table->dropColumn(['old_col_one', 'old_col_two']);

            // Drop a foreign key and its column
            $table->dropConstrainedForeignId('category_id');

            // Add an index after table creation
            $table->index('cover_image');
            $table->dropIndex('posts_status_index');
        });
    }

    public function down(): void
    {
        Schema::table('posts', function (Blueprint $table) {
            $table->dropColumn(['cover_image', 'read_time']);
            $table->renameColumn('content', 'body');
            $table->string('title', 255)->change();
        });
    }
};

Seeders: Seeders populate the database with test or initial data. The DatabaseSeeder is the entry point — it calls individual seeders via call(). Run with php artisan db:seed or php artisan migrate:fresh --seed.

📄database/seeders/DatabaseSeeder.php
PHP

<?php

namespace Database\Seeders;

use App\Models\User;
use App\Models\Post;
use App\Models\Category;
use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\Hash;

class DatabaseSeeder extends Seeder
{
    public function run(): void
    {
        // Run specific seeders in order
        $this->call([
            CategorySeeder::class,
            UserSeeder::class,
            PostSeeder::class,
        ]);
    }
}

// ─────────────────────────────────────────────
// database/seeders/UserSeeder.php
// ─────────────────────────────────────────────
class UserSeeder extends Seeder
{
    public function run(): void
    {
        // Create a fixed admin user
        User::firstOrCreate(
            ['email' => 'admin@example.com'],
            [
                'name'              => 'Admin User',
                'password'          => Hash::make('password'),
                'role'              => 'admin',
                'email_verified_at' => now(),
            ]
        );

        // Generate 20 random users via factory
        User::factory(20)->create();

        // Generate 5 editors using a named state
        User::factory(5)->editor()->create();
    }
}

// ─────────────────────────────────────────────
// database/seeders/PostSeeder.php
// ─────────────────────────────────────────────
class PostSeeder extends Seeder
{
    public function run(): void
    {
        // Assign posts to existing users
        User::all()->each(function (User $user) {
            Post::factory(rand(2, 8))
                ->for($user)
                ->create();
        });

        // Create published posts with a specific state
        Post::factory(10)->published()->create();
    }
}

// Artisan commands:
// php artisan make:seeder UserSeeder
// php artisan db:seed                          -- run DatabaseSeeder
// php artisan db:seed --class=UserSeeder       -- run specific seeder
// php artisan migrate:fresh --seed             -- fresh + seed

Model Factories: Factories use Faker to generate realistic fake data for testing and seeding. Define states to represent different model configurations. Generate with php artisan make:factory PostFactory --model=Post.

📄database/factories/PostFactory.php
PHP

<?php

namespace Database\Factories;

use App\Models\User;
use App\Models\Category;
use Illuminate\Database\Eloquent\Factories\Factory;
use Illuminate\Support\Str;

/**
 * @extends \Illuminate\Database\Eloquent\Factories\Factory<\App\Models\Post>
 */
class PostFactory extends Factory
{
    /**
     * Define the model's default state.
     */
    public function definition(): array
    {
        $title = fake()->sentence(rand(5, 10));

        return [
            'user_id'      => User::factory(),          // creates a related user
            'category_id'  => Category::factory(),
            'title'        => $title,
            'slug'         => Str::slug($title),
            'excerpt'      => fake()->paragraph(2),
            'body'         => fake()->paragraphs(5, true),
            'status'       => fake()->randomElement(['draft', 'published']),
            'is_featured'  => fake()->boolean(20),       // 20% chance true
            'rating'       => fake()->randomFloat(1, 1, 5),
            'published_at' => null,
            'meta'         => [
                'seo_title'       => $title,
                'seo_description' => fake()->sentence(15),
                'og_image'        => fake()->imageUrl(1200, 630),
            ],
        ];
    }

    /**
     * State: Published post with a published_at timestamp.
     */
    public function published(): static
    {
        return $this->state(fn (array $attributes) => [
            'status'       => 'published',
            'published_at' => fake()->dateTimeBetween('-6 months', 'now'),
        ]);
    }

    /**
     * State: Archived post.
     */
    public function archived(): static
    {
        return $this->state(fn (array $attributes) => [
            'status'       => 'archived',
            'published_at' => fake()->dateTimeBetween('-1 year', '-6 months'),
        ]);
    }

    /**
     * State: Featured post.
     */
    public function featured(): static
    {
        return $this->state(fn (array $attributes) => [
            'is_featured' => true,
        ]);
    }
}

// ─────────────────────────────────────────────
// UserFactory – with editor state
// ─────────────────────────────────────────────
// class UserFactory extends Factory {
//     public function definition(): array {
//         return [
//             'name'              => fake()->name(),
//             'email'             => fake()->unique()->safeEmail(),
//             'password'          => Hash::make('password'),
//             'role'              => 'user',
//             'email_verified_at' => now(),
//             'remember_token'    => Str::random(10),
//         ];
//     }
//     public function editor(): static {
//         return $this->state(fn () => ['role' => 'editor']);
//     }
// }

// ─────────────────────────────────────────────
// Using factories in tests / tinker
// ─────────────────────────────────────────────
// Post::factory()->create();                     -- persists one
// Post::factory(5)->create();                    -- persists five
// Post::factory()->make();                       -- in-memory only
// Post::factory()->published()->featured()->create();
// Post::factory()->for(User::find(1))->create(); -- attach to existing user
// Post::factory()->has(Comment::factory(3))->create(); -- with hasMany