Setting Up the Database

Introduction

Laravel migrations are version control for your database schema. This lesson creates the categories and tasks tables with foreign keys, cascade deletes, and indexes using the fluent Schema Blueprint API.

Key Concepts

• Migration: A PHP class describing schema changes in up() and down() methods.
• Schema::create(): Creates a new table with a closure that receives a Blueprint.
• $table->foreignId()->constrained(): Shortcut for declaring a foreign key with a matching primary key.
• cascadeOnDelete() / nullOnDelete(): Referential integrity behaviour on delete.
• Indexes: $table->index([...]) speeds up queries that filter on those columns.

Real World Context

A production database usually has dozens of tables and hundreds of migrations. Because every change goes through a migration, you can rebuild the schema from scratch, roll back a bad change, or deploy the same migrations to every environment with confidence.

Deep Dive

Now let's create the database structure for our task manager. We'll use Laravel migrations to define our tables.

Understanding Migrations

Migrations are like version control for your database. They allow you to:

• Define database schema in PHP code
• Share database changes with your team
• Roll back changes if something goes wrong
• Track all changes over time

Creating the Categories Migration

First, let's create the categories table:

bash
php artisan make:migration create_categories_table

Open database/migrations/xxxx_xx_xx_create_categories_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('categories', function (Blueprint $table) {
            $table->id();  // Auto-incrementing ID
            
            // Foreign key to users table
            $table->foreignId('user_id')
                  ->constrained()  // References users.id
                  ->cascadeOnDelete();  // Delete categories when user is deleted
            
            $table->string('name');  // Category name
            $table->string('color', 7)->default('#6366f1');  // Hex color code
            
            $table->timestamps();  // created_at and updated_at
        });
    }

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

Understanding the Schema

Creating the Tasks Migration

Now create the tasks table:

bash
php artisan make:migration create_tasks_table

Edit database/migrations/xxxx_xx_xx_create_tasks_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('tasks', function (Blueprint $table) {
            $table->id();
            
            // Foreign key to users table
            $table->foreignId('user_id')
                  ->constrained()
                  ->cascadeOnDelete();
            
            // Foreign key to categories (nullable - task may not have category)
            $table->foreignId('category_id')
                  ->nullable()  // Task doesn't require a category
                  ->constrained()
                  ->nullOnDelete();  // Set to null if category is deleted
            
            // Task details
            $table->string('title');
            $table->text('description')->nullable();
            $table->boolean('completed')->default(false);
            $table->date('due_date')->nullable();
            $table->enum('priority', ['low', 'medium', 'high'])->default('medium');
            
            $table->timestamps();
            
            // Index for faster queries
            $table->index(['user_id', 'completed']);
            $table->index(['user_id', 'due_date']);
        });
    }

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

New Concepts Explained

Running the Migrations

Apply the migrations to create the tables:

bash
php artisan migrate

You should see:

   INFO  Running migrations.

  2024_01_15_100000_create_categories_table .......... 5.42ms DONE
  2024_01_15_100001_create_tasks_table ............... 8.31ms DONE

Checking the Database

Use Tinker to verify the tables exist:

bash
php artisan tinker

php
>>> Schema::hasTable('categories')
=> true

>>> Schema::hasTable('tasks')
=> true

>>> Schema::getColumnListing('tasks')
=> [
     "id",
     "user_id",
     "category_id",
     "title",
     "description",
     "completed",
     "due_date",
     "priority",
     "created_at",
     "updated_at",
   ]

Migration Commands Reference

bash
# Run all pending migrations
php artisan migrate

# Rollback the last batch of migrations
php artisan migrate:rollback

# Rollback all migrations
php artisan migrate:reset

# Rollback and re-run all migrations
php artisan migrate:refresh

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

# Check migration status
php artisan migrate:status

Database Diagram

Our complete database structure:

┌─────────────────┐     ┌─────────────────┐     ┌─────────────────┐
│     users       │     │   categories    │     │     tasks       │
├─────────────────┤     ├─────────────────┤     ├─────────────────┤
│ id (PK)         │◄────│ user_id (FK)    │     │ id (PK)         │
│ name            │     │ id (PK)         │◄────│ category_id (FK)│
│ email           │     │ name            │     │ user_id (FK)    │────►
│ password        │     │ color           │     │ title           │
│ created_at      │     │ created_at      │     │ description     │
│ updated_at      │     │ updated_at      │     │ completed       │
└─────────────────┘     └─────────────────┘     │ due_date        │
                                                │ priority        │
                                                │ created_at      │
                                                │ updated_at      │
                                                └─────────────────┘

Next, we'll create Eloquent models to interact with these tables!

Common Pitfalls

1. Editing an old migration after deployment — Colleagues whose databases already ran it will never pick up the change. Always add a new migration.
2. Forgetting indexes on foreign keys — Queries become full-table scans. foreignId(...)->constrained() indexes them for you.

Best Practices

1. Name migrations descriptively — add_priority_to_tasks_table tells a reviewer what the migration does at a glance.
2. Prefer cascadeOnDelete() over manual cleanup — The database enforces it even if your app has bugs.

Summary

• Migrations are version-controlled database schema changes.
• php artisan make:migration scaffolds a new migration file.
• $table->foreignId('user_id')->constrained() declares a foreign key concisely.
• cascadeOnDelete() and nullOnDelete() handle referential integrity.
• $table->index([...]) speeds up queries.