Skip to main content

Database Migration

OPS Intermediate

Manage database schema changes and migrations in EZ-Console.

Overview​

EZ-Console uses GORM's AutoMigrate feature to automatically handle database schema migrations. When the server starts, it automatically creates or updates database tables based on your model definitions.

Automatic Migrations​

How It Works​

When the server starts:

  1. Framework connects to database
  2. Compares current schema with model definitions
  3. Automatically creates missing tables
  4. Adds missing columns
  5. Updates indexes

Migration on Startup​

Migrations run automatically when the server starts:

./server --global.encrypt-key=your-key

Log Output:

[INFO] Auto-migrating database...
[INFO] Created table: t_user
[INFO] Created table: t_role
[INFO] Created table: t_permission

Model Definitions​

Basic Model​

type Product struct {
    ID        string    `gorm:"primaryKey;type:varchar(36)"`
    Name      string    `gorm:"type:varchar(255);not null"`
    Price     float64   `gorm:"type:decimal(10,2)"`
    CreatedAt time.Time
    UpdatedAt time.Time
}

With Indexes​

type Product struct {
    ID        string    `gorm:"primaryKey"`
    Name      string    `gorm:"type:varchar(255);index"`
    Category  string    `gorm:"type:varchar(100);index"`
    Status    string    `gorm:"type:varchar(50);index"`
    CreatedAt time.Time
}

With Foreign Keys​

type Order struct {
    ID        string    `gorm:"primaryKey"`
    UserID    string    `gorm:"type:varchar(36);index"`
    User      User      `gorm:"foreignKey:UserID"`
    Items     []OrderItem `gorm:"foreignKey:OrderID"`
    CreatedAt time.Time
}

Manual Migrations​

Custom Migration Scripts​

For complex migrations, create custom scripts:

package migration

import (
    "gorm.io/gorm"
)

func MigrateV1ToV2(db *gorm.DB) error {
    // Add new column
    if err := db.Exec("ALTER TABLE t_product ADD COLUMN description TEXT").Error; err != nil {
        return err
    }
    
    // Update existing data
    if err := db.Exec("UPDATE t_product SET description = '' WHERE description IS NULL").Error; err != nil {
        return err
    }
    
    return nil
}

Running Manual Migrations​

func main() {
    // ... setup database ...
    
    // Run custom migrations
    if err := migration.MigrateV1ToV2(db); err != nil {
        log.Fatal("Migration failed:", err)
    }
    
    // Start server
    // ...
}

Migration Best Practices​

1. Backup Before Migration​

Always backup your database before running migrations:

# MySQL
mysqldump -u user -p database > backup_$(date +%Y%m%d).sql

# PostgreSQL
pg_dump -U user database > backup_$(date +%Y%m%d).sql

# SQLite
cp database.db database.db.backup

2. Test in Staging First​

Always test migrations in staging before production:

  1. Restore production backup to staging
  2. Run migration
  3. Verify data integrity
  4. Test application functionality

3. Non-Breaking Changes​

When possible, make non-breaking changes:

  • Add new columns (nullable)
  • Add new tables
  • Add indexes

4. Breaking Changes​

For breaking changes, plan carefully:

  • Remove columns in multiple steps
  • Migrate data before schema changes
  • Coordinate with application updates

Migration Strategies​

Add Column​

// Model change
type Product struct {
    // ... existing fields ...
    Description string `gorm:"type:text"` // New field
}

Migration runs automatically on next startup.

Remove Column​

Step 1: Remove from model (but keep in database temporarily)

Step 2: Migrate data if needed

Step 3: Remove column manually:

ALTER TABLE t_product DROP COLUMN old_field;

Rename Column​

-- MySQL
ALTER TABLE t_product CHANGE old_name new_name VARCHAR(255);

-- PostgreSQL
ALTER TABLE t_product RENAME COLUMN old_name TO new_name;

Rollback Migrations​

Manual Rollback​

If a migration fails:

  1. Stop the server
  2. Restore from backup: 
    mysql -u user -p database < backup.sql
  3. Fix the issue
  4. Re-run migration

Version Control​

Track migration versions:

type MigrationVersion struct {
    Version   int       `gorm:"primaryKey"`
    AppliedAt time.Time
}

func RecordMigration(db *gorm.DB, version int) error {
    return db.Create(&MigrationVersion{
        Version:   version,
        AppliedAt: time.Now(),
    }).Error
}

Troubleshooting​

Migration Fails​

Symptom: Server fails to start with migration error

Solutions:

  1. Check database permissions
  2. Verify model definitions
  3. Check for conflicting changes
  4. Review error logs

Missing Tables​

Symptom: Tables not created

Solutions:

  1. Verify models are registered
  2. Check database connection
  3. Review migration logs
  4. Manually create tables if needed

Data Loss Risk​

Symptom: Migration might cause data loss

Solutions:

  1. Backup database first
  2. Test in staging
  3. Use transactions for data migrations
  4. Have rollback plan ready

Need help? Ask in GitHub Discussions.