Database Migrations
Relevant source files
The following files were used as context for generating this wiki page:
Database Migrations in Meltano are used to manage the lifecycle of the system database schema. The system database stores critical project metadata, including job history and plugin settings. As the project evolves, migrations ensure that the underlying database structure remains compatible with the current version of the Meltano core application.
The migration system is built upon Alembic and SQLAlchemy, providing a declarative way to define schema changes through revision scripts. The MigrationService acts as the primary interface for triggering upgrades and ensuring the system database is up-to-date before project operations proceed.
Sources: src/meltano/core/migration_service.py:1-40, integration/example-library/meltano-migrations/index.md:1-5
Architecture and Migration Logic
The migration logic is centralized in the MigrationService class, which interacts with Alembic's ScriptDirectory and MigrationContext to determine the state of the database relative to available migration scripts.
Migration Service Components
The migration process relies on several key components to ensure safety and accuracy:
- Engine: The SQLAlchemy engine used to connect to the system database.
- Lock Path: A path to a revision lock file that identifies the target version the database should reach.
- Migration Directory: The root directory containing the Alembic environment and version scripts.
Sources: src/meltano/core/migration_service.py:43-60
Upgrade Workflow
When an upgrade is triggered, the service determines if a migration is actually needed by comparing the current database revision against the target revision specified in the lock file. If the database is already at the target revision or a newer one, the process is skipped.
The diagram shows the logic flow from initiating an upgrade to verifying the current schema version and finally applying changes if necessary.
Sources: src/meltano/core/migration_service.py:79-130, tests/meltano/core/test_migration_service.py:73-83
Migration Revision Structure
Migration revisions are Python scripts that define upgrade() and downgrade() functions. These scripts are uniquely identified by a revision ID and linked to their predecessor via a down_revision attribute.
Base Table Definitions
The initial migrations establish the core schema required for Meltano functionality, specifically for tracking job states and plugin configurations.
| Table Name | Description | Key Columns |
|---|---|---|
job | Tracks execution details and states of Meltano jobs. | id, job_id, state, started_at, payload |
plugin_settings | Stores project-specific settings for various plugins. | name, namespace, value, enabled |
Sources: src/meltano/migrations/versions/b4c05e463b53_.py:13-64
Job States
Job states are defined as an enumeration within the migration context to represent the lifecycle of a task:
IDLE: Initial state.RUNNING: Job is currently executing.SUCCESS: Job completed without errors.FAIL: Job encountered an error.DEAD: Job was terminated or lost.
Sources: src/meltano/migrations/versions/b4c05e463b53_.py:29-35
Error Handling and Exceptions
The migration system defines specific exceptions to handle various failure scenarios during the schema update process.
- MigrationError: A generic exception raised when an upgrade fails, often due to a missing revision lock or a corrupted database.
- MigrationUnneededException: An internal exception used to signal that the database is already at the correct revision.
Common Failure Scenarios
If the lock_path is not found, the service raises a MigrationError stating "Cannot upgrade the system database, revision lock not found." If an unexpected error occurs, it suggests the database might have been created before version 0.34.0 (when migrations were introduced) or is corrupted.
Sources: src/meltano/core/migration_service.py:27-35, src/meltano/core/migration_service.py:116-130
Integration with State Management
Meltano has extended migration concepts to specialized data stores. Version 4.2.0 introduced the StateStoreManager.migrate() interface, allowing state backend implementations (like S3 or GCS) to provide their own migration mechanisms for persistent state data.
Sources: CHANGELOG.md:19-20
Testing Migrations
Meltano uses a robust testing suite to verify migration reliability. The tests use in-memory SQLite databases to simulate schema upgrades and verify error handling.
Migration Test Utility
Tests typically generate a sequence of mock migration scripts to verify that the MigrationService correctly navigates the revision history.
Sources: tests/meltano/core/test_migration_service.py:41-65
# Example of a migration script template used in tests
MIGRATION_TEMPLATE = """
revision = {revision}
down_revision = {down_revision}
def upgrade() -> None: ...
def downgrade() -> None: ...
"""
Sources: tests/meltano/core/test_migration_service.py:22-28
Conclusion
Database migrations are a fundamental part of the Meltano core, ensuring system schema stability across versions. By leveraging Alembic and a dedicated MigrationService, Meltano provides a predictable and safe way to update project metadata stores, protecting the integrity of job history and configurations. sources: src/meltano/core/migration_service.py