Database Migrations

Database migrations provide a version control system for your database schema, allowing you to track, manage, and apply incremental changes to your database structure over time.

Migrations are essential for team development and deployment workflows. They ensure that database changes are tracked in version control, can be reviewed before application, and can be consistently applied across development, staging, and production environments.

Migration Types

Fuwafuwa Framework supports two types of migrations:

1. SQL Migrations

SQL migrations are raw SQL files that are executed directly against the database. They are the simplest form of migration and work well for straightforward schema changes.

2. PHP Class Migrations

PHP class migrations provide more flexibility, allowing you to use the framework's database abstraction and perform complex logic during migrations.

<?php
// migrations/migration_add_notifications_table.php

class migration_add_notifications_table {
    use \Fuwafuwa\Traits\SQL;

    public function up() {
        $this->SQL("
            CREATE TABLE IF NOT EXISTS notification (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_login TEXT NOT NULL,
                type TEXT NOT NULL DEFAULT 'info',
                title TEXT NOT NULL,
                message TEXT NOT NULL,
                is_read INTEGER DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        ");

        $this->SQL("CREATE INDEX idx_notification_user ON notification(user_login)");
    }

    public function down() {
        $this->SQL("DROP INDEX IF EXISTS idx_notification_user");
        $this->SQL("DROP TABLE IF EXISTS notification");
    }
}

New Project Setup

For new projects, use the complete schema initialization files which contain all tables, indexes, and sample data:

SQLite Setup

# Create new SQLite database
sqlite3 app/db/fuwafuwa.db < sqlite_init.sql

# Or with MySQL
mysql -u username -p database_name < mysql_init.sql

Creating Migrations (For Existing Projects)

If you need to modify an existing database after initial setup, create migration files in the migrations/ directory:

SQL Migration Best Practices

-- migrations/001_add_categories_sqlite.sql

-- Migration: Create categories table
-- Description: Adds product categories for inventory management

CREATE TABLE IF NOT EXISTS categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    slug TEXT UNIQUE NOT NULL,
    parent_id INTEGER DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
);

-- Create indexes for common queries
CREATE INDEX IF NOT EXISTS idx_categories_parent ON categories(parent_id);
CREATE INDEX IF NOT EXISTS idx_categories_slug ON categories(slug);

Running Migrations

Manual Execution

For SQLite databases, run migrations directly:

# Run a single migration (example)
sqlite3 app/db/fuwafuwa.db < migrations/001_your_migration_sqlite.sql

# Run all SQLite migrations in order
for f in migrations/*_sqlite.sql; do
    echo "Running $f..."
    sqlite3 app/db/fuwafuwa.db < "$f"
done

Using PHP Migrations

For PHP class migrations, include and execute them:

<?php
// Include the migration
require_once 'migrations/migration_add_notifications_table.php';

// Run the migration
$migration = new migration_add_notifications_table();
$migration->up();

Database Initialization

For new installations, use the complete schema files:

# Initialize SQLite database
cat sqlite_init.sql | sqlite3 app/db/fuwafuwa.db

# Or with MySQL
mysql -u username -p database_name < mysql_init.sql

Rollback Migrations

SQL migrations should include rollback statements as comments:

-- Migration: Add email verification column
-- Rollback: ALTER TABLE users DROP COLUMN email_verified_at;

ALTER TABLE users ADD COLUMN email_verified_at DATETIME DEFAULT NULL;
CREATE INDEX idx_users_verified ON users(email_verified_at);

PHP class migrations implement the down() method for rollbacks:

public function down() {
    // Reverse the changes made in up()
    $this->SQL("DROP INDEX IF EXISTS idx_users_verified");
    $this->SQL("ALTER TABLE users DROP COLUMN email_verified_at");
}

Schema Files

The framework includes complete schema files for fresh installations:

Best Practices

• Use IF EXISTS / IF NOT EXISTS - Makes migrations idempotent and safer to run
• Create indexes separately - After table creation for better performance
• Test migrations - Run on a copy of production data before applying
• Keep migrations small - One logical change per migration file
• Document rollbacks - Always include how to reverse the change
• Use transactions - When possible, wrap changes in transactions
• Version control - Always commit migrations to version control

Example: Safe Migration Pattern

-- migrations/003_add_user_profile_sqlite.sql

-- Step 1: Create new table
CREATE TABLE IF NOT EXISTS user_profiles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_login TEXT NOT NULL UNIQUE,
    avatar TEXT,
    bio TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_login) REFERENCES user(login) ON DELETE CASCADE
);

-- Step 2: Create indexes
CREATE INDEX IF NOT EXISTS idx_user_profiles_login ON user_profiles(user_login);

-- Step 3: Migrate data (if needed)
-- INSERT INTO user_profiles (user_login) SELECT login FROM user;

-- Rollback:
-- DROP TABLE IF EXISTS user_profiles;

Environment-Specific Setup

Development

# Reset database during development
rm app/db/fuwafuwa.db
sqlite3 app/db/fuwafuwa.db < sqlite_init.sql

# Apply new migrations
sqlite3 app/db/fuwafuwa.db < migrations/00X_your_migration_sqlite.sql

Testing

# Create test database
cp app/db/fuwafuwa.db app/db/test.db

# Or fresh test database
sqlite3 app/db/test.db < sqlite_init.sql

Production

Always backup before applying migrations:

# Create backup first
cp app/db/fuwafuwa.db "app/db/backup_$(date +%Y%m%d_%H%M%S).db"

# Apply migration
sqlite3 app/db/fuwafuwa.db < migrations/00X_migration_sqlite.sql