🗃️ Migrations in Squehub

Migrations in Squehub provide a structured and version-controlled way to manage your database schema. With simple PHP migration files, you can easily create, modify, and rollback database tables using CLI commands.

📁 Migration Directory Structure

All migration files are stored in the /database/migrations/ directory. Each file should define a class with an up() method (to apply the migration) and an optional down() method (to rollback).

project/
├── database/
│   ├── migrations/
│   │   ├── 2024_01_01_000000_create_users_table.php
│   │   └── 2024_01_02_000001_add_email_verification_to_users.php
│   └── sql/
│       └── create_initial_schema.sql

⚙️ Example Migration File

This is a basic migration to create a users table:

<?php

use App\Core\Database;

class CreateUsersTable
{
    public function up()
    {
        $sql = "CREATE TABLE users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            username VARCHAR(100) NOT NULL UNIQUE,
            email VARCHAR(100) NOT NULL UNIQUE,
            password VARCHAR(255) NOT NULL,
            country VARCHAR(50),
            status VARCHAR(20) DEFAULT 'active',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )";

        Database::connect()->exec($sql);
    }

    public function down()
    {
        Database::connect()->exec("DROP TABLE IF EXISTS users");
    }
}

🚀 CLI Commands for Migrations

You can manage your migrations using the php squehub CLI tool:

• php squehub migrate – Run all pending migrations.
• php squehub migrate:rollback – Rollback the last batch of migrations.
• php squehub make:migration CreatePostsTable – Create a new migration file.

📌 Example

$ php squehub make:migration CreateProductsTable

This will generate a timestamped file like 2025_07_02_123000_create_products_table.php inside /database/migrations/.

📖 Migration Class Rules

• The class name must match the filename (PascalCase).
• Each migration must have an up() method for applying changes.
• The optional down() method should reverse the changes made in up().
• Use the Database::connect() method from App\Core\Database for executing raw SQL.

💾 Alternative: Using Raw SQL Files

If you prefer not to use Squehub’s migration system, you can manually create SQL schema files inside project/database/sql/ and import them into your database directly (e.g., using phpMyAdmin or the MySQL CLI).

Example file: project/database/sql/create_initial_schema.sql

-- create_initial_schema.sql

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    password VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    title VARCHAR(255),
    body TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

🧠 Best Practices

• Run migrations on fresh setups using php squehub migrate.
• For team-based work or production environments, prefer structured PHP migrations over raw SQL.
• Use the SQL option only when working with legacy databases or rapid prototyping.

✅ Summary

• Migrations are stored in /database/migrations/ as PHP classes.
• Optional raw SQL files can be stored in /database/sql/.
• Use CLI commands to generate and run migrations.
• Each migration should contain an up() method (and optionally down()).