Database Schema Migration Tool in Python

Database schema migrations are essential for managing changes to your database structure over time, especially in evolving applications. This challenge asks you to build a simplified schema migration tool in Python that allows you to define and apply changes to a SQLite database. This tool will help you manage database evolution safely and predictably.

Problem Description

You are tasked with creating a Python-based tool to manage schema migrations for a SQLite database. The tool should allow you to define migration scripts, each representing a specific change to the database schema (e.g., adding a column, dropping a table, changing a data type). The tool should then be able to apply these migrations sequentially to bring the database to the desired state.

What needs to be achieved:

1. Migration Script Definition: Define a way to represent a migration script. Each script should contain a unique name (string) and a function that performs the actual schema change.
2. Migration Application: Implement a function that takes a list of migration scripts and applies them to a SQLite database. The function should execute each migration function in the order they appear in the list.
3. Migration Tracking: Maintain a record of applied migrations in a table named migrations within the database itself. This table should have a single column name (TEXT) to store the names of applied migrations.
4. Idempotency: The migration tool should handle the case where a migration has already been applied. It should check the migrations table before applying each migration and skip it if it's already present.

Key Requirements:

• The tool must work with SQLite databases.
• Migration functions should accept a database connection as an argument.
• Error handling: If a migration fails, the tool should log the error and stop applying further migrations.
• The migrations table should be created automatically if it doesn't exist.

Expected Behavior:

• When the tool is run for the first time on a new database, it should create the migrations table.
• Each migration function should modify the database schema as intended.
• The names of successfully applied migrations should be recorded in the migrations table.
• If a migration fails, an error message should be printed, and no further migrations should be applied.
• If a migration has already been applied, it should be skipped.

Edge Cases to Consider:

• Database connection errors.
• Migration functions raising exceptions.
• Duplicate migration names.
• Empty list of migrations.
• The migrations table already exists with some migrations applied.

Examples

Example 1:

Input:
migrations = [
    ("migration_1", lambda conn: conn.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)")),
    ("migration_2", lambda conn: conn.execute("ALTER TABLE users ADD COLUMN email TEXT"))
]
database_file = "test.db"

Output:
The database 'test.db' is updated with migrations: migration_1, migration_2.

Explanation: The tool creates the users table (if it doesn't exist) and then adds the email column to the users table. The names "migration_1" and "migration_2" are added to the migrations table.

Example 2:

Input:
migrations = [
    ("migration_1", lambda conn: conn.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)")),
    ("migration_2", lambda conn: conn.execute("ALTER TABLE users ADD COLUMN email TEXT"))
]
database_file = "test.db" # Database already exists with migrations: migration_1

Output:
The database 'test.db' is updated with migrations: migration_2.

Explanation: The migration_1 is skipped because it's already in the migrations table. Only migration_2 is applied.

Example 3: (Error Handling)

Input:
migrations = [
    ("migration_1", lambda conn: conn.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)")),
    ("migration_2", lambda conn: conn.execute("ALTER TABLE users ADD COLUMN email TEXT"))
]
database_file = "test.db"

Output:
Error applying migration_2:  sqlite3.OperationalError: no such column: email

Explanation: The migration_2 fails because the email column cannot be added (perhaps due to a constraint). The error is logged, and no further migrations are applied.

Constraints

• The database must be a SQLite database.
• Migration names must be unique strings.
• Migration functions must accept a database connection object as their only argument.
• The tool should handle potential errors gracefully and provide informative error messages.
• The solution should be reasonably efficient for a small number of migrations (e.g., up to 100). Performance is not the primary focus.

Notes

• You can use the sqlite3 module for interacting with the SQLite database.
• Consider using a try-except block to handle potential errors within the migration functions.
• Think about how to structure your code to make it modular and easy to extend with new migration scripts.
• The migration functions should be pure functions; they should not have any side effects other than modifying the database schema.
• Focus on correctness and clarity over extreme optimization. The goal is to demonstrate a functional schema migration tool.