Database Schema Migrations in Go

Database schema migrations are essential for managing changes to your database structure over time, especially in evolving applications. This challenge asks you to implement a simple migration system in Go, allowing you to apply sequential changes to a database schema. This is a common task in backend development and crucial for maintaining data integrity and application functionality.

Problem Description

You are tasked with creating a basic migration system for a database. The system should allow you to define a series of migration functions, each representing a change to the database schema. The system should then be able to apply these migrations in order, ensuring that the database schema evolves correctly.

What needs to be achieved:

• Define a Migration interface with an Up() and Down() method. Up() applies the migration, and Down() reverses it.
• Create a Migrator struct that manages a list of migrations.
• Implement methods on the Migrator struct:
  • ApplyAll(): Applies all migrations in the list in order.
  • RollbackAll(): Reverses all migrations in the list in reverse order.
• Provide a way to register migrations with the Migrator.

Key Requirements:

• The Up() and Down() methods should accept a database connection as an argument (e.g., *sql.DB).
• Migrations should be applied and rolled back sequentially.
• Error handling is crucial. Up() and Down() methods should return errors if they fail. The ApplyAll() and RollbackAll() methods should handle these errors gracefully and return the first error encountered.
• The order of migrations is determined by the order they are registered.

Expected Behavior:

• ApplyAll() should execute each migration's Up() method in order.
• RollbackAll() should execute each migration's Down() method in reverse order.
• If any migration fails during ApplyAll() or RollbackAll(), the process should stop, and the error should be returned.

Edge Cases to Consider:

• Empty migration list: ApplyAll() and RollbackAll() should return gracefully (no error) if there are no migrations.
• Database connection errors: Handle potential errors when connecting to the database.
• Migration errors: Handle errors returned by the Up() and Down() methods of individual migrations.

Examples

Example 1:

Input:
Migrations:
  - Migration 1: Creates a table "users"
  - Migration 2: Adds a column "email" to the "users" table
Database: Initially empty

Output:
Database: Contains tables "users" with column "email"

Explanation: ApplyAll() executes Migration 1 (creates "users" table) and then Migration 2 (adds "email" column).

Example 2:

Input:
Migrations:
  - Migration 1: Creates a table "users"
  - Migration 2: Adds a column "email" to the "users" table
Database: Contains tables "users" with column "email"

Output:
Database: Contains only table "users" (no "email" column)

Explanation: RollbackAll() executes Migration 2's Down() (removes "email" column) and then Migration 1's Down() (drops "users" table).

Example 3: (Error Handling)

Input:
Migrations:
  - Migration 1: Creates a table "users"
  - Migration 2: Adds a column "email" to the "users" table (fails due to database error)
Database: Initially empty

Output:
Error: "database error: permission denied"
Database: Contains table "users"

Explanation: ApplyAll() executes Migration 1 successfully. Migration 2 fails, so ApplyAll() stops and returns the error. The "users" table is created but the "email" column is not added.

Constraints

• The database connection type should be *sql.DB from the standard database/sql package.
• Migrations should be idempotent (applying the same migration multiple times should have the same effect as applying it once). While not strictly enforced in the code, this is a best practice to consider.
• The number of migrations can range from 0 to 100.
• The complexity of each migration (e.g., the SQL statements it executes) is not constrained, but keep the example migrations relatively simple.

Notes

• You don't need to implement a persistent storage mechanism for migrations (e.g., a table to track applied migrations). The migrations are assumed to be defined in code.
• Focus on the core logic of applying and rolling back migrations in the correct order and handling errors.
• Consider using interfaces to make your code more flexible and testable.
• Think about how you would register migrations with the Migrator. A simple slice or map would suffice for this challenge.
• This is a simplified migration system. Real-world migration systems often include features like versioning, dependency management, and more sophisticated error handling.