Laravel Database and Migration System

Q: What is the difference between migrate:fresh and migrate:refresh ?

fresh deletes the database and then rebuilds it; it's faster but results in complete data loss. refresh first rolls back the migrations and then applies them, executing down() followed by up() in sequence. Use fresh in the development environment for faster performance, but never use either of these commands in a production environment.

Q: What compatibility issues arise when migrating from SQLite to MySQL?

SQLite does not support certain ALTER TABLE operations (such as changing column types or dropping columns), so using SQLite during development may lead to limitations during migration. We recommend using MySQL or PostgreSQL in production environments.

Q: How can I add an index to an existing large table without locking the table?

MySQL uses ALGORITHM=INPLACE LOCK=NONE (Laravel does not support this directly; you need to use DB::statement ); PostgreSQL uses CONCURRENTLY ( $table->index('col')->concurrently() is supported in Laravel 11+).

Migrations are Laravel's "database version control"—they manage database structures just like Git manages code, so teams no longer have to worry about table structures becoming out of sync.

1. What You'll Learn

Creating and running migration files: make:migration / migrate / rollback
Schema Builder: Column types, indexes, foreign key constraints
Multi-tenant table design: tenants/users/subscriptions/plans/orders
Migration Strategy: Secure Migration of the Production Environment with Zero Downtime
Handling Differences in MySQL and PostgreSQL Migrations

2. A True Story of a DBA

(1) Pain Point: Manually executing SQL causes production incidents

Charlie manually executed a ALTER TABLE orders ADD COLUMN discount DECIMAL(8,2) query in the production environment but forgot to include the default value—as a result, the "discount" column for all 2 million records was set to NULL, causing the reporting module to crash for two hours. To make matters worse, Bob added a field locally without telling Charlie, and when the code was deployed, it immediately triggered an SQL error.

(2) Solutions for Laravel Migrations

Laravel migrations use PHP code to describe database changes; teams share migration files, and the execution order is automatically tracked—after everyone runs php artisan migrate, the database structures are completely identical.

BASH

php artisan make:migration add_discount_to_orders_table
# Creates a timestamped migration file
php artisan migrate
# Runs all pending migrations in order

(3) Revenue

After Charlie replaced manual SQL with migration, new fields must have default values specified (which can be detected during code reviews). When Bob pushes his local changes to Git, Charlie syncs them with a single command, resulting in zero incidents.

3. Fundamentals of Migration

(1) Create a migration file

BASH

# Create a migration
php artisan make:migration create_shops_table

# With table name hint
php artisan make:migration create_shops_table --create=shops

# Add columns to existing table
php artisan make:migration add_status_to_shops_table --table=shops

(2) Migrating the File Structure

PHP

// database/migrations/2024_01_15_000000_create_shops_table.php
return new class extends Migration
{
    public function up(): void
    {
        Schema::create('shops', function (Blueprint $table) {
            $table->id();
            $table->foreignId('tenant_id')->constrained()->cascadeOnDelete();
            $table->string('name');
            $table->string('slug')->unique();
            $table->text('description')->nullable();
            $table->enum('status', ['active', 'suspended', 'closed'])->default('active');
            $table->decimal('revenue', 12, 2)->default(0);
            $table->timestamps();
            $table->softDeletes();
        });
    }

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

(3) Migration Commands

Command	Function
`migrate`	Execute the migration that hasn't been run
`migrate:rollback`	Roll back the previous migration
`migrate:refresh`	Roll Back All + Re-execute
`migrate:fresh`	Delete database + Re-execute
`migrate:status`	View Migration Status
`migrate:reset`	Roll back all migrations

(1) ▶ Example: Creating and Running a Migration

BASH

# Create migration
php artisan make:migration create_shops_table

# Run pending migrations
php artisan migrate
# 2024_01_15_000000_create_shops_table .............. done

# Check migration status
php artisan migrate:status
# Ran?   Migration
# Yes    0001_01_01_000000_create_users_table
# Yes    2024_01_15_000000_create_shops_table
# No     2024_01_16_000000_create_products_table

Output:

TEXT

# Command executed successfully

4. Schema Builder

(1) Common Column Types

Method	Database Type	Description
`id()`	BIGINT UNSIGNED AUTO_INCREMENT	Primary Key
`foreignId('x')`	BIGINT UNSIGNED	Foreign Key
`string('name', 255)`	VARCHAR	String
`text('content')`	TEXT	Long Text
`integer('count')`	INT	Integer
`decimal('price', 8, 2)`	DECIMAL(8,2)	Exact Decimal
`boolean('active')`	TINYINT(1)	Boolean
`enum('status', [...])`	ENUM	Enumeration
`json('metadata')`	JSON	JSON Data
`timestamp('published_at')`	TIMESTAMP	Timestamp
`softDeletes()`	TIMESTAMP NULL	Soft Delete

(2) Indexes and Constraints

PHP

Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->foreignId('tenant_id')->constrained()->cascadeOnDelete();
    $table->foreignId('shop_id')->constrained()->cascadeOnDelete();
    $table->string('order_number')->unique();
    $table->decimal('total', 12, 2);

    // Indexes
    $table->index('shop_id');          // Single index
    $table->index(['shop_id', 'status']); // Composite index
    $table->unique(['tenant_id', 'order_number']); // Unique composite

    // Foreign key constraints
    $table->foreignId('user_id')
        ->constrained('users')         // Custom table name
        ->cascadeOnDelete()            // Delete related on parent delete
        ->cascadeOnUpdate();           // Update related on parent update

    $table->timestamps();
});

Constraint Method	Function
`unique()`	Unique Index
`index()`	Regular Index
`constrained()`	Automatically Infer Foreign Key Relationships
`cascadeOnDelete()`	Cascading Deletion
`restrictOnDelete()`	Deletion restricted (an error occurs if there are child records)
`nullOnDelete()`	Set to NULL when deleting a parent record

(1) ▶ Example: ShopMetrics Multi-Tenant Outdoor Key Design

PHP

// database/migrations/create_tenants_table.php
Schema::create('tenants', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('slug')->unique();
    $table->string('domain')->unique();
    $table->enum('status', ['active', 'suspended', 'cancelled'])->default('active');
    $table->timestamps();
    $table->softDeletes();
});

// database/migrations/create_subscriptions_table.php
Schema::create('subscriptions', function (Blueprint $table) {
    $table->id();
    $table->foreignId('tenant_id')->constrained()->cascadeOnDelete();
    $table->foreignId('plan_id')->constrained()->cascadeOnDelete();
    $table->enum('status', ['active', 'past_due', 'cancelled'])->default('active');
    $table->timestamp('trial_ends_at')->nullable();
    $table->timestamp('ends_at')->nullable();
    $table->timestamps();

    $table->index(['tenant_id', 'status']);
});

Output:

TEXT

// Execution successful

5. Multitenant Table Design

(1) ShopMetrics Core Tables

timeline
    title ShopMetrics Migration Timeline
    Create tenants : tenants table
    Create plans : plans table
    Create users : users table with tenant_id
    Create subscriptions : subscriptions table
    Create shops : shops table with tenant_id
    Create products : products table with shop_id
    Create categories : categories table
    Create category_product : pivot table
    Create orders : orders table with tenant_id + shop_id
    Create order_items : order_items table with order_id + product_id

(2) Complete Migration Sequence

Order	Table Name	Key Fields
1	tenants	id, name, slug, domain, status
2	plans	id, name, price, features (JSON)
3	users	id, tenant_id (FK), name, email, role
4	subscriptions	id, tenant_id (FK), plan_id (FK), status
5	shops	id, tenant_id (FK), name, slug, revenue
6	products	id, shop_id (FK), name, price, sku
7	categories	id, name, slug
8	category_product	category_id (FK), product_id (FK)
9	orders	id, tenant_id (FK), shop_id (FK), total, status
10	order_items	id, order_id (FK), product_id (FK), qty, price

(1) ▶ Example: Complete User Table Migration for ShopMetrics

PHP

// database/migrations/create_users_table.php
Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->foreignId('tenant_id')->nullable()->constrained()->nullOnDelete();
    $table->string('name');
    $table->string('email')->unique();
    $table->timestamp('email_verified_at')->nullable();
    $table->string('password');
    $table->enum('role', ['super_admin', 'tenant_owner', 'analyst', 'viewer'])
        ->default('viewer');
    $table->rememberToken();
    $table->timestamps();
    $table->softDeletes();

    $table->index(['tenant_id', 'role']);
    $table->index('email');
});

Output:

TEXT

// Execution successful

6. Production Migration Strategy

(1) Principles of Secure Migration

Principle	Description	Consequences of Violation
New entries must have a default value	`->default(0)` or `->nullable()`	Error for existing records
Don't delete the column; delete the code first	Stop using the column first, then delete it in the next version	Code references a column that doesn't exist
Add columns to the table using `after()`	Reduce table rebuild by specifying column positions	Table lock duration is too long
Migration package within a transaction	`withinTransaction` property	Partially successful, partially failed

(2) Differences Between MySQL and PostgreSQL

Feature	MySQL	PostgreSQL
Column Default Value	Instant Add	Requires Table Overwrite
JSON Column	`json()`	`json()` + `jsonb()`
Enumeration	`enum()`	Suggestion `string` + CHECK
Full-Text Index	`fullText()`	`fullText()` + GIN
Foreign Key Check	Can Be Temporarily Disabled	Strict Check

(1) ▶ Example: Safely Adding a Column to a Large Production Table

PHP

// Safe: Add column with default value
Schema::table('orders', function (Blueprint $table) {
    $table->decimal('discount', 8, 2)
        ->default(0)
        ->after('total');
});

// Safe: Make column nullable first
Schema::table('shops', function (Blueprint $table) {
    $table->string('phone')->nullable()->after('email');
});

// DANGEROUS: Removing column — do in two steps
// Step 1: This release — stop using the column in code
// Schema::table('shops', function (Blueprint $table) {
//     $table->dropColumn('legacy_field');
// });

Output:

TEXT

// Execution successful

7. Modifying Table Structures

(1) Modify Column

BASH

composer require doctrine/dbal
# Required for modifying existing columns

PHP

Schema::table('shops', function (Blueprint $table) {
    $table->string('name', 100)->change();     // Change length
    $table->renameColumn('desc', 'description'); // Rename column
    $table->dropColumn('legacy_field');          // Drop column
});

(2) Modify the index

PHP

Schema::table('orders', function (Blueprint $table) {
    $table->dropUnique('orders_order_number_unique');
    $table->unique(['tenant_id', 'order_number'], 'orders_tenant_order_unique');
});

(1) ▶ Example: Hands-On Guide to ShopMetrics Migration and Customization

PHP

// database/migrations/2024_02_01_add_stripe_to_subscriptions.php
return new class extends Migration
{
    public function up(): void
    {
        Schema::table('subscriptions', function (Blueprint $table) {
            $table->string('stripe_id')->nullable()->unique()->after('id');
            $table->string('stripe_status')->nullable()->after('status');
            $table->timestamp('current_period_start')->nullable()->after('trial_ends_at');
            $table->timestamp('current_period_end')->nullable()->after('current_period_start');
        });
    }

    public function down(): void
    {
        Schema::table('subscriptions', function (Blueprint $table) {
            $table->dropColumn([
                'stripe_id', 'stripe_status',
                'current_period_start', 'current_period_end',
            ]);
        });
    }
};

Output:

TEXT

// Execution successful

8. Comprehensive Example: The Complete ShopMetrics Migration Set

PHP

// ============================================
// Comprehensive: ShopMetrics Core Migrations
// Covers: tables, foreign keys, indexes, polymorphic
// ============================================

// Migration 1: Create plans table
Schema::create('plans', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('slug')->unique();
    $table->decimal('price', 8, 2);
    $table->integer('shop_limit')->default(5);
    $table->integer('order_limit')->default(1000);
    $table->json('features')->nullable();
    $table->boolean('is_active')->default(true);
    $table->timestamps();
});

// Migration 2: Create tenants table
Schema::create('tenants', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('slug')->unique();
    $table->string('domain')->unique();
    $table->foreignId('plan_id')->nullable()->constrained()->nullOnDelete();
    $table->enum('status', ['active', 'suspended', 'cancelled'])->default('active');
    $table->timestamps();
    $table->softDeletes();
    $table->index(['status', 'created_at']);
});

// Migration 3: Create shops table
Schema::create('shops', function (Blueprint $table) {
    $table->id();
    $table->foreignId('tenant_id')->constrained()->cascadeOnDelete();
    $table->string('name');
    $table->string('slug');
    $table->text('description')->nullable();
    $table->decimal('revenue', 12, 2)->default(0);
    $table->enum('status', ['active', 'suspended', 'closed'])->default('active');
    $table->timestamps();
    $table->softDeletes();
    $table->unique(['tenant_id', 'slug']);
    $table->index(['tenant_id', 'status']);
});

// Migration 4: Create products table
Schema::create('products', function (Blueprint $table) {
    $table->id();
    $table->foreignId('shop_id')->constrained()->cascadeOnDelete();
    $table->string('name');
    $table->string('sku')->unique();
    $table->decimal('price', 10, 2);
    $table->integer('stock')->default(0);
    $table->boolean('is_active')->default(true);
    $table->timestamps();
    $table->softDeletes();
    $table->index(['shop_id', 'is_active']);
});

// Migration 5: Create orders table
Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->foreignId('tenant_id')->constrained()->cascadeOnDelete();
    $table->foreignId('shop_id')->constrained()->cascadeOnDelete();
    $table->foreignId('user_id')->constrained()->cascadeOnDelete();
    $table->string('order_number')->unique();
    $table->decimal('subtotal', 12, 2);
    $table->decimal('discount', 8, 2)->default(0);
    $table->decimal('total', 12, 2);
    $table->enum('status', ['pending', 'processing', 'completed', 'cancelled'])->default('pending');
    $table->json('metadata')->nullable();
    $table->timestamps();
    $table->index(['tenant_id', 'status']);
    $table->index(['shop_id', 'created_at']);
});

❓ FAQ

Q What is the difference between migrate:fresh and migrate:refresh?

A fresh deletes the database and then rebuilds it; it's faster but results in complete data loss. refresh first rolls back the migrations and then applies them, executing down() followed by up() in sequence. Use fresh in the development environment for faster performance, but never use either of these commands in a production environment.

Q Do foreign key constraints affect performance?

A Yes. Foreign key constraints must be checked during every INSERT, UPDATE, or DELETE operation. In high-concurrency scenarios, you may want to consider omitting foreign key constraints and instead ensure data consistency at the application layer.

Q How can I safely perform a migration in a production environment?

A First, test the migration in the staging environment; new columns must have a default value or be nullable; deleting columns should be done in two steps (first remove code references, then delete the column in the next version); migrate large tables during off-peak hours.

Q Can migration files be modified?

A Migrations that have not been executed (i.e., not yet migrated) can be modified freely; do not modify migrations that have already been executed. Instead, create a new migration to implement the changes. Otherwise, the migrate command in other environments will return an error.

Q What compatibility issues arise when migrating from SQLite to MySQL?

A SQLite does not support certain ALTER TABLE operations (such as changing column types or dropping columns), so using SQLite during development may lead to limitations during migration. We recommend using MySQL or PostgreSQL in production environments.

Q How can I add an index to an existing large table without locking the table?

A MySQL uses ALGORITHM=INPLACE LOCK=NONE (Laravel does not support this directly; you need to use DB::statement); PostgreSQL uses CONCURRENTLY ($table->index('col')->concurrently() is supported in Laravel 11+).

📖 Summary

Migrations are a form of version control for databases; teams share migration files to ensure structural consistency.
Schema Builder describes table structures using PHP code and supports column types, indexes, and foreign keys
Foreign key constraints ensure referential integrity, but in high-concurrency scenarios, you may want to rely on the application layer to ensure it.
The multi-tenant table design achieves data isolation through the tenant_id foreign key
Production migrations must be safe: Add new columns with default values, and delete columns in two steps
migrate:fresh: For development; use only "migrate" in production.

📝 Exercises

Basic Exercise (⭐): Create migration files for the three tables—tenants, plans, and subscriptions—for ShopMetrics, including appropriate foreign keys and indexes, and run migrate to verify that everything is correct.
Advanced Exercise (⭐⭐): Based on the existing orders table, create a migration to add a discount column (default value 0) and a composite index (tenant_id + status), and write the corresponding down() method to ensure the migration is rollbackable.
Challenge (⭐⭐⭐): Design a polymorphic association migration for ShopMetrics—allow both shops and products to have addresses (using morphTo for the addresses table)—and implement a foreign key design using morphable_type and morphable_id.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

404 Not Found

Laravel Database and Migration System

1. What You'll Learn

2. A True Story of a DBA

(1) Pain Point: Manually executing SQL causes production incidents

(2) Solutions for Laravel Migrations

(3) Revenue

3. Fundamentals of Migration

(1) Create a migration file

(2) Migrating the File Structure

(3) Migration Commands

(1) ▶ Example: Creating and Running a Migration

4. Schema Builder

(1) Common Column Types

(2) Indexes and Constraints

(1) ▶ Example: ShopMetrics Multi-Tenant Outdoor Key Design

5. Multitenant Table Design

(1) ShopMetrics Core Tables

(2) Complete Migration Sequence

(1) ▶ Example: Complete User Table Migration for ShopMetrics

6. Production Migration Strategy

(1) Principles of Secure Migration

(2) Differences Between MySQL and PostgreSQL

(1) ▶ Example: Safely Adding a Column to a Large Production Table

7. Modifying Table Structures

(1) Modify Column

(2) Modify the index

(1) ▶ Example: Hands-On Guide to ShopMetrics Migration and Customization

8. Comprehensive Example: The Complete ShopMetrics Migration Set

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好