Database Migrations - ServITech Backend

This guide covers how to work with database migrations and seeders to manage your database schema and populate test data.

Understanding Migrations

Migrations are version control for your database schema. Each migration file represents a change to the database structure.

Migration File Structure

Migrations are located in database/migrations/ and follow this naming pattern:

YYYY_MM_DD_HHMMSS_description.php

The timestamp ensures migrations run in the correct order.

Available Migrations

The ServITech Backend includes these migrations (in execution order):

0001_01_01_000000_create_users_table.php

Creates the core user authentication tables:Tables Created:

users - User accounts with name, email, phone, password
password_reset_tokens - Password reset tokens
sessions - User session storage

Key Fields:

id - Primary key
name - User’s full name
email - Unique email address
phone - Optional phone number
password - Hashed password
email_verified_at - Email verification timestamp
deleted_at - Soft delete support

0001_01_01_000001_create_cache_table.php

Creates tables for Laravel’s database cache driver:Tables Created:

cache - Cached data storage
cache_locks - Distributed lock mechanism

0001_01_01_000002_create_jobs_table.php

Creates queue and job tracking tables:Tables Created:

jobs - Pending queue jobs
job_batches - Batch job tracking
failed_jobs - Failed job records

2025_03_21_072704_create_permission_tables.php

Creates role and permission system tables (Spatie Laravel Permission):Tables Created:

permissions - Available permissions
roles - User roles
model_has_permissions - Direct permission assignments
model_has_roles - User-role assignments
role_has_permissions - Role-permission assignments

2025_03_21_243245_create_categories_table.php

Creates the product categories table:Fields:

id - Primary key
name - Category name
deleted_at - Soft delete support
timestamps - created_at, updated_at

2025_03_23_223703_create_subcategories_table.php

Creates the product subcategories table:Fields:

id - Primary key
category_id - Foreign key to categories (cascade delete)
name - Subcategory name
deleted_at - Soft delete support
timestamps - created_at, updated_at

2025_03_23_235144_create_images_table.php

Creates the images table for storing product images:Fields:

id - Primary key
imageable_id - Polymorphic relation ID
imageable_type - Polymorphic relation type
url - Image URL or path
deleted_at - Soft delete support
timestamps - created_at, updated_at

2025_03_23_235722_create_repair_requests_table.php

Creates repair request tracking for technical support:Fields:

id - Primary key
user_id - Customer who requested repair
receipt_number - Unique receipt identifier
device_type - Type of device
brand - Device brand
model - Device model
issue_description - Problem description
status - Current repair status
deleted_at - Soft delete support
timestamps - created_at, updated_at

2025_03_25_005226_create_articles_table.php

Creates the articles (products) table:Fields:

id - Primary key
category_id - Foreign key to categories (cascade delete)
subcategory_id - Foreign key to subcategories (cascade delete)
name - Product name
description - Product description
price - Product price (float)
deleted_at - Soft delete support
timestamps - created_at, updated_at

2025_03_26_165534_create_support_requests_table.php

Creates technical support request tracking:Fields:

id - Primary key
user_id - Customer requesting support
subject - Support request subject
description - Detailed problem description
status - Request status (open, in_progress, closed)
priority - Priority level
deleted_at - Soft delete support
timestamps - created_at, updated_at

Running Migrations

First Time Setup

Run all pending migrations:

php artisan migrate

This creates all tables in your database according to the migration files.

Migrations are tracked in the migrations table. Laravel only runs migrations that haven’t been executed yet.

Check Migration Status

View which migrations have been run:

php artisan migrate:status

Output shows:

✓ Ran migrations (green)
✗ Pending migrations (yellow)

Rolling Back Migrations

Undo the last batch of migrations:

php artisan migrate:rollback

Rollback a specific number of migration batches:

php artisan migrate:rollback --step=2

Rollback all migrations:

php artisan migrate:reset

Rollback commands will delete data. Only use in development or with proper backups.

Fresh Migration

Drop all tables and re-run all migrations:

php artisan migrate:fresh

Combine with seeding:

php artisan migrate:fresh --seed

migrate:fresh destroys all data in the database. Never run this in production.

Refresh Migration

Rollback all migrations and run them again:

php artisan migrate:refresh

With seeding:

php artisan migrate:refresh --seed

Creating New Migrations

Basic Migration

Create a new migration file:

php artisan make:migration create_table_name_table

Migration with Model

Create a model with migration:

php artisan make:model ModelName -m

This creates:

app/Models/ModelName.php
database/migrations/YYYY_MM_DD_HHMMSS_create_model_names_table.php

Example Migration Structure

database/migrations/YYYY_MM_DD_create_example_table.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::create('examples', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->text('description')->nullable();
            $table->foreignId('user_id')->constrained()->onDelete('cascade');
            $table->softDeletes();
            $table->timestamps();
        });
    }

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

Database Seeders

Seeders populate your database with test or initial data.

Available Seeders

Located in database/seeders/:

DatabaseSeeder.php - Main seeder that calls all others
UserSeeder.php - Creates test users with roles
CategorySeeder.php - Adds product categories
SubcategorySeeder.php - Adds subcategories for each category
ArticleSeeder.php - Generates sample products
PermissionSeeder.php - Creates permissions
RoleSeeder.php - Creates user roles

Running Seeders

Run all seeders:

php artisan db:seed

Run a specific seeder:

php artisan db:seed --class=UserSeeder

Seeder Execution Order

The DatabaseSeeder runs seeders in this order:

database/seeders/DatabaseSeeder.php

public function run(): void
{
    $this->call([
        UserSeeder::class,
        CategorySeeder::class,
        SubcategorySeeder::class,
        ArticleSeeder::class,
    ]);
}

Order matters! Seeders with foreign key dependencies must run after their parent tables are seeded.

Creating a New Seeder

Generate a seeder class:

php artisan make:seeder ProductSeeder

Example seeder structure:

database/seeders/ProductSeeder.php

<?php

namespace Database\Seeders;

use App\Models\Product;
use Illuminate\Database\Seeder;

class ProductSeeder extends Seeder
{
    public function run(): void
    {
        Product::create([
            'name' => 'Sample Product',
            'description' => 'A sample product for testing',
            'price' => 99.99,
            'category_id' => 1,
        ]);

        // Or use factories for bulk data
        Product::factory()->count(50)->create();
    }
}

Add to DatabaseSeeder.php:

$this->call([
    UserSeeder::class,
    CategorySeeder::class,
    ProductSeeder::class, // Add your new seeder
]);

Common Workflows

Initial Setup

Run Migrations

php artisan migrate

Seed Database

php artisan db:seed

Verify

Check your database to ensure tables and data were created.

Reset and Reseed

When you need fresh data:

php artisan migrate:fresh --seed

This:

Drops all tables
Runs all migrations
Runs all seeders

Adding a New Feature

Create Migration

php artisan make:migration create_features_table

Define Schema

Edit the generated migration file in database/migrations/

Run Migration

php artisan migrate

Create Seeder (Optional)

php artisan make:seeder FeatureSeeder

Seed Data

php artisan db:seed --class=FeatureSeeder

Troubleshooting

Foreign Key Constraint Fails

Error: SQLSTATE[23000]: Integrity constraint violation Cause: Trying to insert data with foreign keys that don’t exist. Solution: Ensure parent records exist first or run seeders in the correct order.

Migration Already Exists

Error: Migration already exists Solution:

Check database/migrations/ for duplicate files

Or rollback and re-run:

php artisan migrate:rollback
php artisan migrate

Table Already Exists

Error: SQLSTATE[42S01]: Base table or view already exists Solution:

# Option 1: Drop the table manually
php artisan tinker
>>> Schema::dropIfExists('table_name');

# Option 2: Fresh migration
php artisan migrate:fresh

Seeder Class Not Found

Error: Target class [SeederName] does not exist Solution: Ensure proper namespace and autoload:

composer dump-autoload

SQLite Database Locked

Error: database is locked Cause: Another process is using the SQLite database. Solution:

Stop all running PHP processes
Restart the development server
Or switch to MySQL for better concurrency

Database Maintenance Commands

View Database Schema

# List all tables
php artisan db:show

# Show table structure
php artisan db:table users

Interactive Database Shell

php artisan tinker

Example queries in Tinker:

// Count users
App\Models\User::count();

// Get all categories
App\Models\Category::all();

// Create a user
App\Models\User::create([
    'name' => 'Test User',
    'email' => '[email protected]',
    'password' => bcrypt('password')
]);

Wipe Database

Drop all tables without running migrations:

php artisan db:wipe

This permanently deletes all tables and data.

Best Practices

Never Edit Existing Migrations

Once a migration has been committed and shared, create a new migration to modify the schema.

Always Use Soft Deletes

Include $table->softDeletes() for data recovery. Already implemented in all ServITech tables.

Use Descriptive Names

Migration names should clearly describe the change: add_status_to_orders_table

Test Rollbacks

Ensure your down() method properly reverses the up() method changes.

Next Steps

Testing

Learn how to write and run tests for your API

API Reference

Explore the complete API endpoint documentation

Development

Deployment

Best Practices

​Understanding Migrations

​Migration File Structure

​Available Migrations

​Running Migrations

​First Time Setup

​Check Migration Status

​Rolling Back Migrations

​Fresh Migration

​Refresh Migration

​Creating New Migrations

​Basic Migration

​Migration with Model

​Example Migration Structure

​Database Seeders

​Available Seeders

​Running Seeders

​Seeder Execution Order

​Creating a New Seeder

​Common Workflows

​Initial Setup

​Reset and Reseed

​Adding a New Feature

​Troubleshooting

​Foreign Key Constraint Fails

​Migration Already Exists

​Table Already Exists

​Seeder Class Not Found

​SQLite Database Locked

​Database Maintenance Commands

​View Database Schema

​Interactive Database Shell

​Wipe Database

​Best Practices

Never Edit Existing Migrations

Always Use Soft Deletes

Use Descriptive Names

Test Rollbacks

​Next Steps

Testing

API Reference

​Additional Resources

Build docs developers (and LLMs) love

Understanding Migrations

Migration File Structure

Available Migrations

Running Migrations

First Time Setup

Check Migration Status

Rolling Back Migrations

Fresh Migration

Refresh Migration

Creating New Migrations

Basic Migration

Migration with Model

Example Migration Structure

Database Seeders

Available Seeders

Running Seeders

Seeder Execution Order

Creating a New Seeder

Common Workflows

Initial Setup

Reset and Reseed

Adding a New Feature

Troubleshooting

Foreign Key Constraint Fails

Migration Already Exists

Table Already Exists

Seeder Class Not Found

SQLite Database Locked

Database Maintenance Commands

View Database Schema

Interactive Database Shell

Wipe Database

Best Practices

Next Steps

Additional Resources