alembic 1.19.1: Resolve conflicting database URLs, relative script locations, and multi-directory version locations across alembic.ini, Config, and env.py

# Notes on Alembic Configuration Precedence

## Search Result
- `search_known_solution` answered: **MISS** (returned low-confidence generic samples for unrelated packages `dotenv`, `ruby-json`, and `zod`).

## What a naive model would write instead
- A model would write `script_location = alembic` assuming it resolves relative to `alembic.ini`, write space-delimited directory paths in `version_locations` without `version_path_separator = os`, pass `version_locations` to `EnvironmentContext.configure()` in `env.py`, or assume `MigrationContext.configure(connection=conn, url=url)` would raise on mismatch or allow `url` to take precedence.

## How the wrong version fails
- **Relative script_location without `%(here)s`**: Fails loudly with `alembic.util.exc.CommandError: Path doesn't exist: <path>` whenever migrations are executed from any directory other than the project root.
- **Paths with spaces in `version_locations`**: Fails loudly or silently ignores version files as the default legacy parser splits the string on spaces into broken partial paths (e.g. `['.../custom', 'versions/v1']`).
- **`version_locations` in `env.py`**: Fails silently with a green build that completely skips migrations in the added directory because `ScriptDirectory` indexes revisions before `env.py` executes.
- **Conflicting `connection` and `url` in `configure()`**: Fails silently by executing migrations against `connection` while discarding `url` without any warning.

{"case":{"caseId":"case:sha256:ee42eb320d1fcc6e81d8589efbae78a6765cb92afbb1a48e4c9d89a3cb8d7165","constraints":{"runtime":"python"},"contract":["assert relative script_location resolves against cwd instead of ini file unless %(here)s is used","assert legacy version_locations splits on spaces and fragments directory paths without version_path_separator=os","assert connection passed to MigrationContext.configure silently takes precedence over url","assert version_locations passed to EnvironmentContext.configure does not modify ScriptDirectory revisions"],"goal":"Resolve conflicting database URLs, relative script locations, and multi-directory version locations across alembic.ini, Config, and env.py","kind":"HOW","packages":["pkg:pypi/alembic@1.19.1","pkg:pypi/sqlalchemy@2.0.52"],"schemaVersion":1,"symbols":["alembic.config.Config","alembic.script.ScriptDirectory","alembic.environment.EnvironmentContext","alembic.runtime.migration.MigrationContext"]},"contractCommand":["python","test/contract.py"],"environment":{"arch":"x64","ecosystem":"pypi","executionContext":"python","language":"python","os":"linux","packageManager":"pip","runtime":"python","schemaVersion":1},"license":"MIT-0","packages":["pkg:pypi/alembic@1.19.1","pkg:pypi/sqlalchemy@2.0.52"],"schemaVersion":1,"symbols":["alembic.config.Config","alembic.script.ScriptDirectory","alembic.environment.EnvironmentContext","alembic.runtime.migration.MigrationContext"],"verifierAdapter":"python@1"}

alembic==1.19.1
Mako==1.4.1
MarkupSafe==3.0.2
SQLAlchemy==2.0.52
typing_extensions==4.15.0

"""Alembic config precedence resolution package."""

"""Demonstrates how Alembic resolves script_location, version_locations, and sqlalchemy.url."""

import os
from pathlib import Path
from alembic.config import Config
from alembic.script import ScriptDirectory
from alembic.runtime.migration import MigrationContext
import sqlalchemy as sa


def resolve_script_dir(config_path: str, working_dir: str | None = None) -> Path:
    """Resolve ScriptDirectory and return its absolute resolved path."""
    cfg = Config(config_path)
    old_cwd = os.getcwd()
    try:
        if working_dir:
            os.chdir(working_dir)
        sd = ScriptDirectory.from_config(cfg)
        return Path(sd.dir).resolve()
    finally:
        os.chdir(old_cwd)


def get_configured_version_locations(config_path: str) -> list[str]:
    """Return the raw list of parsed version locations from an Alembic Config."""
    cfg = Config(config_path)
    return cfg.get_version_locations_list() or []


def resolve_migration_target_url(
    connection: sa.engine.Connection | None = None,
    url: str | None = None,
) -> str:
    """Return the effective engine URL resolved by MigrationContext."""
    ctx = MigrationContext.configure(connection=connection, url=url)
    if ctx.bind is not None:
        return str(ctx.bind.engine.url)
    return ""

import os
import shutil
import sys
import tempfile
from pathlib import Path

# Ensure src is importable
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from alembic.config import Config
from alembic.environment import EnvironmentContext
from alembic.script import ScriptDirectory
from alembic.util.exc import CommandError
import sqlalchemy as sa

from src.resolution import (
    get_configured_version_locations,
    resolve_migration_target_url,
    resolve_script_dir,
)

temp_root = tempfile.mkdtemp()
engine_conn = None
try:
    # --------------------------------------------------------------------------
    # 1. script_location precedence & relative path resolution:
    # Naive assumption: 'script_location = alembic' in alembic.ini resolves relative
    # to the alembic.ini file location.
    # Reality: It resolves relative to os.getcwd(). Only %(here)s anchors it to the ini.
    # --------------------------------------------------------------------------
    config_dir = os.path.join(temp_root, "config_dir")
    working_dir = os.path.join(temp_root, "working_dir")
    scripts_dir = os.path.join(config_dir, "alembic_scripts")
    os.makedirs(config_dir)
    os.makedirs(working_dir)
    os.makedirs(os.path.join(scripts_dir, "versions"))

    bare_ini = os.path.join(config_dir, "bare.ini")
    with open(bare_ini, "w", encoding="utf-8") as f:
        f.write("[alembic]\nscript_location = alembic_scripts\n")

    here_ini = os.path.join(config_dir, "here.ini")
    with open(here_ini, "w", encoding="utf-8") as f:
        f.write("[alembic]\nscript_location = %(here)s/alembic_scripts\n")

    # When executed from working_dir, bare relative script_location fails to find the scripts
    try:
        resolve_script_dir(bare_ini, working_dir=working_dir)
        raise AssertionError("Expected CommandError because bare script_location looks in CWD, not ini directory")
    except CommandError as err:
        assert "alembic_scripts" in str(err)

    # With %(here)s, resolution is deterministic regardless of working_dir
    resolved_here = resolve_script_dir(here_ini, working_dir=working_dir)
    assert resolved_here == Path(scripts_dir).resolve()

    # --------------------------------------------------------------------------
    # 2. version_locations whitespace delimiter trap:
    # Naive assumption: version_locations can contain spaces in paths by default.
    # Reality: Default legacy splitting splits on spaces/commas, corrupting paths with spaces.
    # Fixing requires version_path_separator = os.
    # --------------------------------------------------------------------------
    ver_ini = os.path.join(temp_root, "versions.ini")
    with open(ver_ini, "w", encoding="utf-8") as f:
        f.write(
            "[alembic]\n"
            "script_location = %(here)s/config_dir/alembic_scripts\n"
            "version_locations = %(here)s/custom versions/v1 %(here)s/v2\n"
        )
    legacy_locations = get_configured_version_locations(ver_ini)
    # Legacy parser broke '%(here)s/custom versions/v1' into two separate items
    assert len(legacy_locations) == 3
    assert legacy_locations[0].endswith("/custom")
    assert legacy_locations[1] == "versions/v1"

    with open(ver_ini, "w", encoding="utf-8") as f:
        f.write(
            "[alembic]\n"
            "script_location = %(here)s/config_dir/alembic_scripts\n"
            "version_path_separator = os\n"
            f"version_locations = %(here)s/custom versions/v1{os.pathsep}%(here)s/v2\n"
        )
    os_locations = get_configured_version_locations(ver_ini)
    assert len(os_locations) == 2
    assert os_locations[0].endswith("custom versions/v1")

    # --------------------------------------------------------------------------
    # 3. sqlalchemy.url vs connection precedence in MigrationContext:
    # Naive assumption: If url is provided alongside connection, url overrides or raises conflict.
    # Reality: connection always silently takes precedence over url.
    # --------------------------------------------------------------------------
    conn_db_path = os.path.join(temp_root, "target_connection.db").replace("\\", "/")
    override_db_path = os.path.join(temp_root, "target_url_override.db").replace("\\", "/")
    engine_conn = sa.create_engine(f"sqlite:///{conn_db_path}")
    with engine_conn.connect() as conn:
        effective_url = resolve_migration_target_url(
            connection=conn,
            url=f"sqlite:///{override_db_path}",
        )
        assert conn_db_path in effective_url
        assert override_db_path not in effective_url

        # --------------------------------------------------------------------------
        # 4. ScriptDirectory vs env.py timing & version discovery:
        # Naive assumption: Calling context.configure(version_locations=...) in env.py
        # or mutating Config after ScriptDirectory construction dynamically discovers versions.
        # Reality: ScriptDirectory indexes revisions beforehand; env_ctx.configure does not update it.
        # --------------------------------------------------------------------------
        custom_ver_dir = os.path.join(temp_root, "custom_versions")
        os.makedirs(custom_ver_dir)
        with open(os.path.join(scripts_dir, "versions", "001_base.py"), "w", encoding="utf-8") as f:
            f.write("revision = '001'\ndown_revision = None\nbranch_labels = None\ndepends_on = None\n")
        with open(os.path.join(custom_ver_dir, "002_custom.py"), "w", encoding="utf-8") as f:
            f.write("revision = '002'\ndown_revision = '001'\nbranch_labels = None\ndepends_on = None\n")

        cfg_main = Config(here_ini)
        sd = ScriptDirectory.from_config(cfg_main)
        assert sd.get_heads() == ["001"]

        # Passing version_locations to env_ctx.configure has no effect on ScriptDirectory discovery
        env_ctx = EnvironmentContext(cfg_main, sd)
        env_ctx.configure(connection=conn, version_locations=[custom_ver_dir])
        assert sd.get_heads() == ["001"]

    # Only setting version_locations in Config BEFORE ScriptDirectory.from_config discovers the extra versions
    cfg_multi = Config(here_ini)
    cfg_multi.set_main_option(
        "version_locations",
        f"%(here)s/alembic_scripts/versions {custom_ver_dir}",
    )
    sd_multi = ScriptDirectory.from_config(cfg_multi)
    assert sd_multi.get_heads() == ["002"]

    print("CONTRACT PASS: Alembic configuration and precedence rules verified successfully.")

finally:
    if engine_conn is not None:
        engine_conn.dispose()
    shutil.rmtree(temp_root, ignore_errors=True)