Migrations and Schema Management

package main

import (
    "fmt"
    "time"

    "gorm.io/driver/sqlite"
    "gorm.io/gorm"
)

// Version 1 of User model
type UserV1 struct {
    ID        uint      `gorm:"primaryKey"`
    Name      string    `gorm:"size:100"`
    Email     string    `gorm:"size:255"`
    CreatedAt time.Time
}

func (UserV1) TableName() string { return "users" }

// Version 2: Added new fields
type UserV2 struct {
    ID        uint      `gorm:"primaryKey"`
    Name      string    `gorm:"size:100"`
    Email     string    `gorm:"size:255;uniqueIndex"`  // Added index
    Phone     string    `gorm:"size:20"`               // New field
    Status    string    `gorm:"size:20;default:'active'"` // New field with default
    CreatedAt time.Time
    UpdatedAt time.Time  // New field
}

func (UserV2) TableName() string { return "users" }

// Version 3: Field renamed (AutoMigrate CANNOT handle this)
type UserV3 struct {
    ID          uint      `gorm:"primaryKey"`
    FullName    string    `gorm:"size:100"` // Renamed from Name
    Email       string    `gorm:"size:255;uniqueIndex"`
    PhoneNumber string    `gorm:"size:20"` // Renamed from Phone
    Status      string    `gorm:"size:20;default:'active'"`
    CreatedAt   time.Time
    UpdatedAt   time.Time
}

func (UserV3) TableName() string { return "users" }

func main() {
    db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
    if err != nil { panic(err) }

    // ===== AutoMigrate Behavior =====

    // Initial migration - creates table
    fmt.Println("=== Initial Migration (V1) ===")
    if err := db.AutoMigrate(&UserV1{}); err != nil { panic(err) }
    printTableInfo(db, "users")

    // Second migration - adds new columns
    fmt.Println("\n=== Adding Fields (V2) ===")
    if err := db.AutoMigrate(&UserV2{}); err != nil { panic(err) }
    printTableInfo(db, "users")

    // Third migration - rename won't work!
    fmt.Println("\n=== Rename Attempt (V3) ===")
    if err := db.AutoMigrate(&UserV3{}); err != nil { panic(err) }
    printTableInfo(db, "users")
    // Note: Name column still exists, FullName is NEW column

    // ===== Safe AutoMigrate Usage =====
    
    // Check what AutoMigrate will do before running
    if !db.Migrator().HasTable(&UserV1{}) {
        fmt.Println("Table doesn't exist, safe to create")
    }

    if !db.Migrator().HasColumn(&UserV1{}, "Phone") {
        fmt.Println("Phone column doesn't exist, will be added")
    }
}

func printTableInfo(db *gorm.DB, tableName string) {
    // Use the migrator; placeholders cannot substitute SQL identifiers in PRAGMA.
    columns, err := db.Migrator().ColumnTypes(&UserV1{}) // all model versions map to "users"
    if err != nil { panic(err) }
    
    fmt.Printf("Table %s columns:\n", tableName)
    for _, col := range columns {
        fmt.Printf("  - %s (%s)\n", col.Name(), col.DatabaseTypeName())
    }
}

package main

import (
    "fmt"
    "time"

    "gorm.io/driver/sqlite"
    "gorm.io/gorm"
)

type User struct {
    ID        uint           `gorm:"primaryKey"`
    Name      string         `gorm:"size:100"`
    Email     string         `gorm:"size:255;uniqueIndex"`
    CreatedAt time.Time
    DeletedAt gorm.DeletedAt `gorm:"index"`
}

type Post struct {
    ID        uint   `gorm:"primaryKey"`
    Title     string `gorm:"size:255"`
    UserID    uint   `gorm:"index"`
    CreatedAt time.Time
}

func main() {
    db, _ := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
    migrator := db.Migrator()

    // ===== TABLE OPERATIONS =====

    // Check if table exists
    if migrator.HasTable(&User{}) {
        fmt.Println("Users table exists")
    }

    if migrator.HasTable("posts") {
        fmt.Println("Posts table exists")
    }

    // Create table
    if err := migrator.CreateTable(&User{}, &Post{}); err != nil {
        fmt.Printf("Error creating tables: %v\n", err)
    }

    // Drop table (dangerous!)
    // migrator.DropTable(&User{})
    // migrator.DropTable("temp_table")

    // Rename table
    // migrator.RenameTable("users", "app_users")

    // Get all tables
    tables, _ := migrator.GetTables()
    fmt.Printf("Tables: %v\n", tables)

    // ===== COLUMN OPERATIONS =====

    // Check if column exists
    if migrator.HasColumn(&User{}, "Email") {
        fmt.Println("Email column exists")
    }

    // Add column
    type UserPhone struct {
        Phone string `gorm:"size:20"`
    }
    if !migrator.HasColumn(&User{}, "Phone") {
        migrator.AddColumn(&User{}, "Phone")
    }

    // Drop column
    // migrator.DropColumn(&User{}, "Phone")

    // Rename column (not all databases support this)
    // migrator.RenameColumn(&User{}, "Name", "FullName")

    // Alter column (change type, constraints)
    // migrator.AlterColumn(&User{}, "Name")

    // Get column types
    columnTypes, _ := migrator.ColumnTypes(&User{})
    fmt.Println("Column types:")
    for _, col := range columnTypes {
        name := col.Name()
        dataType, _ := col.DatabaseTypeName()
        nullable, _ := col.Nullable()
        fmt.Printf("  %s: %s (nullable: %v)\n", name, dataType, nullable)
    }

    // ===== INDEX OPERATIONS =====

    // Check if index exists
    if migrator.HasIndex(&User{}, "idx_users_email") {
        fmt.Println("Email index exists")
    }

    // Create index
    migrator.CreateIndex(&User{}, "Email")

    // Create composite index
    migrator.CreateIndex(&User{}, "idx_name_created", "Name", "CreatedAt")

    // Drop index
    // migrator.DropIndex(&User{}, "idx_users_email")

    // Rename index
    // migrator.RenameIndex(&User{}, "old_index", "new_index")

    // Get indexes
    indexes, _ := migrator.GetIndexes(&User{})
    fmt.Println("Indexes:")
    for _, idx := range indexes {
        fmt.Printf("  %s: %v\n", idx.Name(), idx.Columns())
    }

    // ===== CONSTRAINT OPERATIONS =====

    // Check if constraint exists
    if migrator.HasConstraint(&Post{}, "fk_posts_user") {
        fmt.Println("Foreign key constraint exists")
    }

    // Create foreign key constraint
    migrator.CreateConstraint(&Post{}, "fk_posts_user")

    // Drop constraint
    // migrator.DropConstraint(&Post{}, "fk_posts_user")

    // ===== SCHEMA INSPECTION =====

    // Current database
    currentDB := migrator.CurrentDatabase()
    fmt.Printf("Current database: %s\n", currentDB)

    // Full table SQL (for debugging)
    // Works differently per database
}

package main

import (
    "database/sql"
    "fmt"
    "sort"
    "time"

    "gorm.io/driver/sqlite"
    "gorm.io/gorm"
)

// Migration represents a database migration
type Migration struct {
    ID        uint      `gorm:"primaryKey"`
    Version   string    `gorm:"size:50;uniqueIndex"`
    Name      string    `gorm:"size:255"`
    AppliedAt time.Time `gorm:"autoCreateTime"`
}

// MigrationFunc defines the signature for migration functions
type MigrationFunc func(tx *gorm.DB) error

// MigrationEntry represents a single migration
type MigrationEntry struct {
    Version string
    Name    string
    Up      MigrationFunc
    Down    MigrationFunc
}

// MigrationManager handles database migrations
type MigrationManager struct {
    db         *gorm.DB
    migrations []MigrationEntry
}

// NewMigrationManager creates a new migration manager
func NewMigrationManager(db *gorm.DB) *MigrationManager {
    mm := &MigrationManager{db: db}
    mm.ensureMigrationTable()
    return mm
}

func (mm *MigrationManager) ensureMigrationTable() {
    mm.db.AutoMigrate(&Migration{})
}

// Register adds a migration to the manager
func (mm *MigrationManager) Register(entry MigrationEntry) {
    mm.migrations = append(mm.migrations, entry)
}

// Migrate runs all pending migrations
func (mm *MigrationManager) Migrate() error {
    // Sort migrations by version
    sort.Slice(mm.migrations, func(i, j int) bool {
        return mm.migrations[i].Version < mm.migrations[j].Version
    })

    for _, migration := range mm.migrations {
        // Check if already applied
        var count int64
        mm.db.Model(&Migration{}).Where("version = ?", migration.Version).Count(&count)
        if count > 0 {
            fmt.Printf("Skipping %s: already applied\n", migration.Version)
            continue
        }

        // Run migration in transaction
        fmt.Printf("Applying %s: %s\n", migration.Version, migration.Name)
        err := mm.db.Transaction(func(tx *gorm.DB) error {
            if err := migration.Up(tx); err != nil {
                return err
            }

            // Record migration
            return tx.Create(&Migration{
                Version: migration.Version,
                Name:    migration.Name,
            }).Error
        })

        if err != nil {
            return fmt.Errorf("migration %s failed: %w", migration.Version, err)
        }

        fmt.Printf("Applied %s successfully\n", migration.Version)
    }

    return nil
}

// Rollback reverts the last N migrations
func (mm *MigrationManager) Rollback(steps int) error {
    var applied []Migration
    mm.db.Order("applied_at DESC").Limit(steps).Find(&applied)

    for _, migration := range applied {
        // Find migration entry
        var entry *MigrationEntry
        for _, m := range mm.migrations {
            if m.Version == migration.Version {
                entry = &m
                break
            }
        }

        if entry == nil {
            return fmt.Errorf("migration %s not found", migration.Version)
        }

        if entry.Down == nil {
            return fmt.Errorf("migration %s has no rollback", migration.Version)
        }

        fmt.Printf("Rolling back %s: %s\n", migration.Version, migration.Name)
        err := mm.db.Transaction(func(tx *gorm.DB) error {
            if err := entry.Down(tx); err != nil {
                return err
            }
            return tx.Delete(&Migration{}, "version = ?", migration.Version).Error
        })

        if err != nil {
            return fmt.Errorf("rollback %s failed: %w", migration.Version, err)
        }

        fmt.Printf("Rolled back %s successfully\n", migration.Version)
    }

    return nil
}

// Status shows migration status
func (mm *MigrationManager) Status() {
    var applied []Migration
    mm.db.Order("version ASC").Find(&applied)

    appliedMap := make(map[string]time.Time)
    for _, m := range applied {
        appliedMap[m.Version] = m.AppliedAt
    }

    fmt.Println("Migration Status:")
    fmt.Println("--------------------------------------------------")
    for _, m := range mm.migrations {
        status := "Pending"
        appliedAt := ""
        if t, ok := appliedMap[m.Version]; ok {
            status = "Applied"
            appliedAt = t.Format("2006-01-02 15:04:05")
        }
        fmt.Printf("%-15s %-30s %-10s %s\n", m.Version, m.Name, status, appliedAt)
    }
}

// ===== DEFINE MIGRATIONS =====

func createUsersTable(tx *gorm.DB) error {
    return tx.Exec(`
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name VARCHAR(100) NOT NULL,
            email VARCHAR(255) UNIQUE NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    `).Error
}

func dropUsersTable(tx *gorm.DB) error {
    return tx.Exec(`DROP TABLE IF EXISTS users`).Error
}

func addUserPhoneColumn(tx *gorm.DB) error {
    return tx.Exec(`ALTER TABLE users ADD COLUMN phone VARCHAR(20)`).Error
}

func removeUserPhoneColumn(tx *gorm.DB) error {
    // SQLite doesn't support DROP COLUMN, need workaround
    // For PostgreSQL/MySQL: ALTER TABLE users DROP COLUMN phone
    return nil // Simplified for demo
}

func createPostsTable(tx *gorm.DB) error {
    return tx.Exec(`
        CREATE TABLE IF NOT EXISTS posts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            title VARCHAR(255) NOT NULL,
            content TEXT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id)
        )
    `).Error
}

func dropPostsTable(tx *gorm.DB) error {
    return tx.Exec(`DROP TABLE IF EXISTS posts`).Error
}

func addUserStatusColumn(tx *gorm.DB) error {
    return tx.Exec(`ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active'`).Error
}

func main() {
    db, _ := gorm.Open(sqlite.Open("migration_demo.db"), &gorm.Config{})

    mm := NewMigrationManager(db)

    // Register migrations
    mm.Register(MigrationEntry{
        Version: "20241201_001",
        Name:    "Create users table",
        Up:      createUsersTable,
        Down:    dropUsersTable,
    })

    mm.Register(MigrationEntry{
        Version: "20241201_002",
        Name:    "Add phone to users",
        Up:      addUserPhoneColumn,
        Down:    removeUserPhoneColumn,
    })

    mm.Register(MigrationEntry{
        Version: "20241202_001",
        Name:    "Create posts table",
        Up:      createPostsTable,
        Down:    dropPostsTable,
    })

    mm.Register(MigrationEntry{
        Version: "20241203_001",
        Name:    "Add status to users",
        Up:      addUserStatusColumn,
        Down:    nil, // No rollback for this migration
    })

    // Run migrations
    fmt.Println("=== Running Migrations ===")
    if err := mm.Migrate(); err != nil {
        fmt.Printf("Migration error: %v\n", err)
    }

    // Show status
    fmt.Println("\n=== Migration Status ===")
    mm.Status()

    // Rollback example
    // fmt.Println("\n=== Rollback Last 2 Migrations ===")
    // mm.Rollback(2)
    // mm.Status()
}

package main

import (
    "fmt"
    "time"

    "gorm.io/driver/postgres" // Using PostgreSQL for advanced features
    "gorm.io/gorm"
)

// ===== ZERO-DOWNTIME MIGRATION PATTERNS =====

// Pattern 1: Expand-Contract Pattern for Column Rename
// Instead of renaming, add new column, migrate data, then remove old

func renameColumnSafely(db *gorm.DB) error {
    // Step 1: Add new column
    if err := db.Exec(`ALTER TABLE users ADD COLUMN full_name VARCHAR(100)`).Error; err != nil {
        return err
    }

    // Step 2: Copy data from old to new
    if err := db.Exec(`UPDATE users SET full_name = name WHERE full_name IS NULL`).Error; err != nil {
        return err
    }

    // Step 3: Deploy application code that uses both columns
    // (Application writes to both, reads from new)

    // Step 4: After verification, drop old column (separate deployment)
    // db.Exec(`ALTER TABLE users DROP COLUMN name`)

    return nil
}

// Pattern 2: Adding NOT NULL column with default
func addNotNullColumnSafely(db *gorm.DB) error {
    // Step 1: Add nullable column
    if err := db.Exec(`ALTER TABLE users ADD COLUMN status VARCHAR(20)`).Error; err != nil {
        return err
    }

    // Step 2: Set default for existing rows (batch to avoid locking)
    batchSize := 1000
    for {
        result := db.Exec(`
            UPDATE users 
            SET status = 'active' 
            WHERE id IN (
                SELECT id FROM users 
                WHERE status IS NULL 
                LIMIT ?
            )
        `, batchSize)
        
        if result.Error != nil {
            return result.Error
        }
        if result.RowsAffected == 0 {
            break
        }
        
        fmt.Printf("Updated %d rows\n", result.RowsAffected)
        time.Sleep(100 * time.Millisecond) // Give database breathing room
    }

    // Step 3: Add NOT NULL constraint
    if err := db.Exec(`ALTER TABLE users ALTER COLUMN status SET NOT NULL`).Error; err != nil {
        return err
    }

    // Step 4: Add default for new rows
    if err := db.Exec(`ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active'`).Error; err != nil {
        return err
    }

    return nil
}

// Pattern 3: Adding index without locking (PostgreSQL)
func addIndexConcurrently(db *gorm.DB) error {
    // PostgreSQL: run outside db.Transaction; CONCURRENTLY cannot run inside a transaction block.
    // It reduces blocking but still takes locks and can fail, leaving an invalid index.
    return db.Exec(`CREATE INDEX CONCURRENTLY idx_users_email ON users(email)`).Error
}

// Pattern 4: Table migration with temporary table
func migrateTableStructure(db *gorm.DB) error {
    return db.Transaction(func(tx *gorm.DB) error {
        // Step 1: Create new table with desired structure
        if err := tx.Exec(`
            CREATE TABLE users_new (
                id SERIAL PRIMARY KEY,
                full_name VARCHAR(100) NOT NULL,
                email VARCHAR(255) UNIQUE NOT NULL,
                phone VARCHAR(20),
                status VARCHAR(20) DEFAULT 'active',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        `).Error; err != nil {
            return err
        }

        // Step 2: Copy data with transformation
        if err := tx.Exec(`
            INSERT INTO users_new (id, full_name, email, phone, status, created_at)
            SELECT id, COALESCE(name, 'Unknown'), email, phone, 
                   COALESCE(status, 'active'), created_at
            FROM users
        `).Error; err != nil {
            return err
        }

        // Step 3: Rename tables
        if err := tx.Exec(`ALTER TABLE users RENAME TO users_old`).Error; err != nil {
            return err
        }
        if err := tx.Exec(`ALTER TABLE users_new RENAME TO users`).Error; err != nil {
            return err
        }

        // Step 4: Drop old table (or keep for backup)
        // tx.Exec(`DROP TABLE users_old`)

        return nil
    })
}

// Pattern 5: Foreign Key addition without blocking
func addForeignKeySafely(db *gorm.DB) error {
    // Step 1: Add column without constraint
    if err := db.Exec(`ALTER TABLE posts ADD COLUMN category_id INTEGER`).Error; err != nil {
        return err
    }

    // Step 2: Add index
    if err := db.Exec(`CREATE INDEX idx_posts_category ON posts(category_id)`).Error; err != nil {
        return err
    }

    // Step 3: Validate existing data
    var invalidCount int64
    db.Raw(`
        SELECT COUNT(*) FROM posts p 
        WHERE p.category_id IS NOT NULL 
        AND NOT EXISTS (SELECT 1 FROM categories c WHERE c.id = p.category_id)
    `).Scan(&invalidCount)

    if invalidCount > 0 {
        return fmt.Errorf("found %d posts with invalid category_id", invalidCount)
    }

    // Step 4: Add constraint (NOT VALID for PostgreSQL to avoid scanning existing rows)
    if err := db.Exec(`
        ALTER TABLE posts 
        ADD CONSTRAINT fk_posts_category 
        FOREIGN KEY (category_id) REFERENCES categories(id) 
        NOT VALID
    `).Error; err != nil {
        return err
    }

    // Step 5: Validate constraint in background
    if err := db.Exec(`ALTER TABLE posts VALIDATE CONSTRAINT fk_posts_category`).Error; err != nil {
        return err
    }

    return nil
}

func main() {
    dsn := "host=localhost user=postgres password=secret dbname=test port=5432"
    db, _ := gorm.Open(postgres.Open(dsn), &gorm.Config{})

    // Run safe migrations
    fmt.Println("Running safe migrations...")
    
    // These would be called as part of deployment
    // renameColumnSafely(db)
    // addNotNullColumnSafely(db)
    // addIndexConcurrently(db)
    
    _ = db // Use db
}

package main

import (
    "database/sql"
    "fmt"
    "log"

    "github.com/golang-migrate/migrate/v4"
    "github.com/golang-migrate/migrate/v4/database/postgres"
    _ "github.com/golang-migrate/migrate/v4/source/file"
    _ "github.com/lib/pq"
)

/*
Directory structure for migrations:
migrations/
├── 000001_create_users_table.up.sql
├── 000001_create_users_table.down.sql
├── 000002_add_user_phone.up.sql
├── 000002_add_user_phone.down.sql
├── 000003_create_posts_table.up.sql
└── 000003_create_posts_table.down.sql

Example migration files:

-- 000001_create_users_table.up.sql
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 000001_create_users_table.down.sql
DROP TABLE IF EXISTS users;

-- 000002_add_user_phone.up.sql
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 000002_add_user_phone.down.sql
ALTER TABLE users DROP COLUMN phone;
*/

func runMigrations(databaseURL, migrationsPath string) error {
    db, err := sql.Open("postgres", databaseURL)
    if err != nil {
        return err
    }
    defer db.Close()

    driver, err := postgres.WithInstance(db, &postgres.Config{})
    if err != nil {
        return err
    }

    m, err := migrate.NewWithDatabaseInstance(
        "file://"+migrationsPath,
        "postgres",
        driver,
    )
    if err != nil {
        return err
    }

    // Migrate to latest version
    if err := m.Up(); err != nil && err != migrate.ErrNoChange {
        return err
    }

    version, dirty, _ := m.Version()
    fmt.Printf("Current version: %d, Dirty: %v\n", version, dirty)

    return nil
}

func main() {
    databaseURL := "postgres://user:password@localhost:5432/mydb?sslmode=disable"
    migrationsPath := "./migrations"

    if err := runMigrations(databaseURL, migrationsPath); err != nil {
        log.Fatal(err)
    }

    fmt.Println("Migrations completed successfully!")
}

package main

import (
    "fmt"
    "time"

    "gorm.io/driver/sqlite"
    "gorm.io/gorm"
)

type SchemaMigration struct {
    ID        uint      `gorm:"primaryKey"`
    Version   string    `gorm:"size:50;uniqueIndex"`
    AppliedAt time.Time `gorm:"autoCreateTime"`
}

type SimpleMigrator struct {
    db *gorm.DB
}

func NewSimpleMigrator(db *gorm.DB) *SimpleMigrator {
    db.AutoMigrate(&SchemaMigration{})
    return &SimpleMigrator{db: db}
}

func (m *SimpleMigrator) Run(version string, migration func(*gorm.DB) error) error {
    var count int64
    m.db.Model(&SchemaMigration{}).Where("version = ?", version).Count(&count)
    if count > 0 {
        return nil // Already applied
    }

    return m.db.Transaction(func(tx *gorm.DB) error {
        if err := migration(tx); err != nil {
            return err
        }
        return tx.Create(&SchemaMigration{Version: version}).Error
    })
}

func main() {
    db, _ := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
    m := NewSimpleMigrator(db)

    m.Run("001_create_users", func(tx *gorm.DB) error {
        return tx.Exec(`CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)`).Error
    })

    m.Run("002_add_email", func(tx *gorm.DB) error {
        return tx.Exec(`ALTER TABLE users ADD COLUMN email TEXT`).Error
    })

    fmt.Println("Migrations complete!")
}