Laravel Migrations: Schema Management

Hello! Let's get started with today's lesson.

In our last session, we meticulously designed a normalized database schema for a simple e-commerce application. We moved from raw requirements to a logical blueprint, ensuring our data structure was efficient and free from redundancy by applying the principles of normalization up to 3NF.

Today, we transition from blueprint to construction. You will learn how to create and manage that database schema using Laravel migrations. Migrations are one of Laravel's most powerful features, acting as version control for your database. They allow you and your team to define, modify, and share the application's database schema programmatically. By the end of this lesson, you will have translated the schema we designed into actual tables within your database.

1. What Are Migrations and Why Use Them?

Before we write any code, it's crucial to understand the "why" behind migrations. You could, of course, open a database management tool and create your tables manually. However, this approach quickly becomes unmanageable, especially in a team environment.

Migrations solve this by codifying your schema changes in PHP files. This means:

• Collaboration is seamless: A teammate can pull your latest code, run one command, and have their database schema perfectly match yours.
• History is tracked: Just like Git tracks code changes, migrations track your database's evolution.
• Deployment is reliable: You can run migrations as part of an automated deployment process to update your production database schema consistently.

To get a feel for how this works in practice, let's watch a short introduction.

The key takeaway is that migrations provide a robust and shareable way to manage your database structure. Not using them is a common mistake for developers new to the framework.

2. Setting Up Your MSSQL Connection

Before we can run a migration, Laravel needs to know how to connect to your database. Since your goal is to master Laravel with MSSQL, we'll configure that connection now.

First, you'll need to update your .env file with the correct credentials for your local MSSQL server. The following article shows exactly which variables to change.

As the article mentions, running php artisan migrate after this change might result in a could not find driver error. This happens if the required PHP extensions for SQL Server (pdo_sqlsrv and sqlsrv) are not enabled in your php.ini file. The same article guides you through enabling them. Ensure your environment is correctly configured before proceeding.

3. Creating Your First Migrations

With the connection configured, let's start building the tables from our e-commerce schema.

Generating a Migration File

We use an Artisan command to create a new migration file. Laravel's command-line tool is smart; if you follow its naming conventions, it will generate boilerplate code for you.

Open your terminal in the project root and run the following command to create a migration for our products table:

php artisan make:migration create_products_table

Laravel uses the name create_products_table to infer that we want to create a table named products. Now, open the new file located in the database/migrations/ directory. It will have a timestamp in its name, like YYYY_MM_DD_HHMMSS_create_products_table.php.

You'll see a structure similar to this:

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('products', function (Blueprint $table) {
            $table->id();
            $table->timestamps();
        });
    }

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

• up() method: This is executed when you run php artisan migrate. Its job is to add new tables, columns, or indexes to your database.
• down() method: This is executed when you roll back a migration. It should do the exact opposite of the up() method. Here, it drops the table that up() created.

Defining the Schema

Inside the up() method's closure, we use the $table object (an instance of Illuminate\Database\Schema\Blueprint) to define the table's columns. Laravel's Schema Builder provides a rich, database-agnostic API for this.

Let's flesh out the products table migration based on the schema we designed in the last lesson.

// In database/migrations/YYYY_MM_DD_HHMMSS_create_products_table.php

public function up(): void
{
    Schema::create('products', function (Blueprint $table) {
        $table->id(); // BigInt, Unsigned, Auto-incrementing Primary Key
        $table->string('title');
        $table->text('description')->nullable(); // Good practice to allow nullable descriptions
        $table->decimal('price', 8, 2); // 8 total digits, 2 after the decimal
        $table->unsignedInteger('stock_quantity')->default(0);
        $table->timestamps(); // Creates created_at and updated_at columns
    });
}

The official Laravel documentation is your best friend for discovering all available column types and modifiers.

Your Turn: Create the Other Tables

Now it's your turn to apply what you've learned. Create the migrations for the users, categories, and orders tables based on our schema design.

1. Generate the migration files:

   php artisan make:migration create_users_table
   php artisan make:migration create_categories_table
   php artisan make:migration create_orders_table
   php artisan make:migration create_order_product_table
   php artisan make:migration create_category_product_table
   

2. Define the schema in the up() method of each new migration file. Use the schema from the previous lesson as your guide. I've provided the code for users and orders below to help you. Try to complete categories and the two pivot tables (order_product, category_product) on your own.

   create_users_table.php:

   Schema::create('users', function (Blueprint $table) {
       $table->id();
       $table->string('name');
       $table->string('email')->unique();
       $table->string('password');
       $table->rememberToken(); // A helper for "remember me" functionality
       $table->timestamps();
   });
   

   create_orders_table.php:

   Schema::create('orders', function (Blueprint $table) {
       $table->id();
       $table->unsignedBigInteger('user_id'); // We'll add the foreign key constraint in the next lesson
       $table->decimal('total_amount', 10, 2);
       $table->string('status')->default('pending');
       $table->timestamps();
   });
   

   Challenge: Fill in the up() methods for create_categories_table, create_order_product_table, and create_category_product_table.

4. Running and Managing Migrations

Once you've defined your migrations, you need to execute them. Artisan provides several commands for this.

Here are the most common commands:

• php artisan migrate
  This command runs the up() method on all migrations that haven't been run yet. Run this now to create your tables in the MSSQL database.

• php artisan migrate:status
  Shows a list of all migrations and whether they have been run.

• php artisan migrate:rollback
  This rolls back the last batch of migrations that were run. It executes the down() method for each of them.

• php artisan migrate:fresh
  This command is extremely useful during development. It drops all tables from your database and then runs all migrations from the beginning. Warning: Never run this in a production environment with live data!

5. The Golden Rule: Don't Modify Pushed Migrations

A migration file is a record of a change at a point in time. Once a migration has been committed to version control and shared with your team (or deployed), you must not edit it.

Why? If you change an existing migration that your teammate has already run, their migrate command will report "nothing to migrate," but their schema will be out of sync with yours, leading to bugs.

The correct approach is to create a new migration to make the change.

• Wrong: Edit create_products_table to add a sku column.
• Right: Run php artisan make:migration add_sku_to_products_table and add the new column in that new file.

// In database/migrations/YYYY_MM_DD_HHMMSS_add_sku_to_products_table.php
public function up(): void
{
    Schema::table('products', function (Blueprint $table) {
        $table->string('sku')->unique()->after('id');
    });
}

public function down(): void
{
    Schema::table('products', function (Blueprint $table) {
        $table->dropColumn('sku');
    });
}

Notice we use Schema::table() to modify an existing table.

This workflow is one of the most important professional practices when working with migrations.

Conclusion

Congratulations! You've taken the logical schema you designed and turned it into a physical database structure using Laravel migrations. You now have a repeatable, version-controlled process for managing your database schema.

Key Takeaways:

• Migrations act as version control for your database, making team collaboration and deployments safe and predictable.
• The up() method applies schema changes, while the down() method reverses them.
• The php artisan make:migration command is used to generate migration files.
• Laravel's Schema Builder provides a fluent, database-agnostic API for defining table structures.
• The commands migrate, rollback, and fresh are your primary tools for managing the schema state during development.
• The golden rule: Once a migration is shared, create a new migration to make further changes rather than editing the old one.

Next Up:

Our tables exist, but they are just isolated islands of data. They don't know about each other yet. In the next lesson, we will complete our schema implementation by learning how to define foreign key constraints and indexes within migrations. This will enforce the relationships we designed (like orders belonging to users) at the database level and prepare our tables for efficient querying.