Laravel Framework

Database Configuration & Migrations

18 min Lesson 6 of 45

Database Configuration & Migrations

Laravel makes database interactions simple and elegant. In this lesson, you'll learn how to configure database connections, create and manage migrations, and use Laravel's powerful Schema Builder to define your database structure.

Database Configuration

Laravel supports multiple database systems out of the box: MySQL, PostgreSQL, SQLite, and SQL Server. Database configuration is stored in config/database.php and uses environment variables from your .env file.

Note: Always keep sensitive database credentials in your .env file, never commit them to version control. The .env file should be listed in your .gitignore.

Setting Up Database Connection

Open your .env file and configure your database connection:

# MySQL Configuration
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laravel_db
DB_USERNAME=root
DB_PASSWORD=your_password

# SQLite Configuration (Alternative)
DB_CONNECTION=sqlite
DB_DATABASE=/absolute/path/to/database.sqlite

After configuring your database, test the connection by running:

php artisan migrate:status

Introduction to Migrations

Migrations are like version control for your database. They allow you to define and share your application's database schema. Each migration file contains instructions to create, modify, or delete database tables.

Tip: Think of migrations as a timeline of your database structure. You can move forward (migrate) or backward (rollback) through this timeline at any time.

Creating Your First Migration

Use the Artisan command to create a new migration:

# Create a migration for a new table
php artisan make:migration create_products_table

# Create a migration to modify an existing table
php artisan make:migration add_price_to_products_table --table=products

# Create a migration with automatic schema generation
php artisan make:migration create_categories_table --create=categories

This creates a new file in database/migrations/ with a timestamp prefix like 2026_02_14_120000_create_products_table.php.

Migration Structure

Every migration file contains two methods: up() and down().

<?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('products', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->text('description')->nullable();
            $table->decimal('price', 8, 2);
            $table->integer('quantity')->default(0);
            $table->boolean('is_available')->default(true);
            $table->timestamps();
        });
    }

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

Schema Builder - Column Types

Laravel's Schema Builder provides many column types for defining your database structure:

// Primary Keys
$table->id();                           // UNSIGNED BIGINT auto-increment
$table->uuid('uuid');                   // UUID column

// String Columns
$table->string('name', 100);           // VARCHAR with optional length
$table->text('description');           // TEXT column
$table->char('code', 10);              // CHAR with fixed length

// Numeric Columns
$table->integer('votes');              // INTEGER
$table->bigInteger('views');           // BIGINT
$table->decimal('price', 8, 2);        // DECIMAL with precision
$table->float('rating', 8, 2);         // FLOAT
$table->double('latitude', 15, 8);     // DOUBLE

// Boolean
$table->boolean('is_active');          // BOOLEAN/TINYINT(1)

// Date and Time
$table->date('birth_date');            // DATE
$table->time('alarm_time');            // TIME
$table->datetime('created_at');        // DATETIME
$table->timestamp('updated_at');       // TIMESTAMP
$table->timestamps();                   // created_at & updated_at
$table->softDeletes();                  // deleted_at for soft deletes

// JSON and Arrays
$table->json('options');               // JSON column
$table->jsonb('settings');             // JSONB (PostgreSQL)

// Binary
$table->binary('photo');               // BLOB

Column Modifiers

Modifiers allow you to add additional properties to columns:

Schema::create('users', function (Blueprint $table) {
    $table->id();

    // Nullable columns
    $table->string('middle_name')->nullable();

    // Default values
    $table->string('status')->default('pending');
    $table->integer('points')->default(0);

    // Unsigned (positive numbers only)
    $table->integer('age')->unsigned();

    // Unique constraint
    $table->string('email')->unique();
    $table->string('username')->unique();

    // Index for faster queries
    $table->string('last_name')->index();

    // Comments
    $table->string('status')->comment('User account status');

    // After specific column (MySQL)
    $table->string('phone')->after('email');

    // First column position (MySQL)
    $table->string('priority')->first();

    // Multiple modifiers
    $table->string('referral_code', 20)
          ->nullable()
          ->unique()
          ->comment('User referral code');

    $table->timestamps();
});

Warning: Not all modifiers work with all database systems. For example, after() and first() are MySQL-specific and won't work with PostgreSQL or SQLite.

Foreign Keys and Relationships

Define relationships between tables using foreign keys:

Schema::create('orders', function (Blueprint $table) {
    $table->id();

    // Foreign key - long form
    $table->unsignedBigInteger('user_id');
    $table->foreign('user_id')
          ->references('id')
          ->on('users')
          ->onDelete('cascade')
          ->onUpdate('cascade');

    // Foreign key - short form
    $table->foreignId('product_id')
          ->constrained()
          ->onDelete('cascade');

    // Foreign key with custom table name
    $table->foreignId('category_id')
          ->constrained('product_categories')
          ->onDelete('set null');

    $table->integer('quantity');
    $table->decimal('total', 10, 2);
    $table->timestamps();
});

Running Migrations

Execute your migrations to create the database structure:

# Run all pending migrations
php artisan migrate

# Run migrations with seed data
php artisan migrate --seed

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

# Rollback specific number of batches
php artisan migrate:rollback --step=3

# Rollback all migrations
php artisan migrate:reset

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

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

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

# Show migration status
php artisan migrate:status

Note: In production, use php artisan migrate carefully. Always backup your database before running migrations that modify existing tables.

Modifying Existing Tables

Create a migration to modify an existing 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::table('products', function (Blueprint $table) {
            // Add new columns
            $table->string('sku')->unique()->after('name');
            $table->integer('discount')->default(0)->after('price');

            // Modify existing column
            $table->text('description')->nullable()->change();

            // Rename column
            $table->renameColumn('quantity', 'stock_quantity');

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

            // Add index
            $table->index('sku');

            // Add foreign key
            $table->foreignId('category_id')
                  ->nullable()
                  ->constrained()
                  ->onDelete('set null');
        });
    }

    public function down(): void
    {
        Schema::table('products', function (Blueprint $table) {
            $table->dropForeign(['category_id']);
            $table->dropColumn(['sku', 'discount', 'category_id']);
            $table->renameColumn('stock_quantity', 'quantity');
            $table->boolean('is_available')->default(true);
        });
    }
};

Warning: To modify columns, you need to install the Doctrine DBAL package: composer require doctrine/dbal

Exercise 1: Create a Blog Migration

Create a migration for a posts table with the following structure:

id (primary key)
user_id (foreign key to users table)
category_id (foreign key to categories table, nullable)
title (string, max 200 characters)
slug (string, unique)
excerpt (text, nullable)
content (text)
featured_image (string, nullable)
views (integer, default 0)
is_published (boolean, default false)
published_at (timestamp, nullable)
timestamps (created_at, updated_at)
soft deletes (deleted_at)

Use appropriate column types, modifiers, and foreign key constraints.

Exercise 2: Database Indexes

Create a migration to add indexes to the posts table:

Single index on slug
Composite index on user_id and is_published
Full-text index on title and content (MySQL only)

Remember to include the down() method to drop these indexes.

Exercise 3: Pivot Table Migration

Create a migration for a many-to-many relationship between posts and tags:

Table name: post_tag
Foreign keys: post_id and tag_id
Both foreign keys should cascade on delete
Add a unique composite index on both columns
Add timestamps

Best Practices

Atomic Migrations: Each migration should focus on a single change. Don't combine multiple unrelated changes.
Reversible Migrations: Always implement the down() method so migrations can be rolled back.
Test Locally: Always test migrations on your local database before running them in production.
Backup Production: Always backup your production database before running migrations.
Use Transactions: Laravel wraps migrations in transactions automatically (on supported databases).
Naming Conventions: Use descriptive migration names: create_posts_table, add_featured_to_posts.

Summary

In this lesson, you've learned:

How to configure database connections in Laravel
Creating and structuring migration files
Using the Schema Builder with various column types
Applying column modifiers for constraints and properties
Defining foreign key relationships
Running, rolling back, and managing migrations
Modifying existing tables safely

Migrations are the foundation of your database structure in Laravel. Master them, and you'll have complete control over your application's data layer.