scripts/apply_migration.py

"""Apply a .sql migration file to the Supabase Postgres DB via DATABASE_URL.

DDL (CREATE FUNCTION, etc.) cannot go through the PostgREST REST API, so this
connects directly with psycopg. Reads DATABASE_URL from the environment or .env.

Usage:
    python scripts/apply_migration.py supabase/migrations/<file>.sql
"""
from __future__ import annotations

import sys
from pathlib import Path

# Allow using the vendored psycopg if it isn't installed in the active env.
_ROOT = Path(__file__).resolve().parent.parent
_DEPS = _ROOT / "python_deps"
if _DEPS.exists():
    sys.path.insert(0, str(_DEPS))

import psycopg  # noqa: E402


def _load_database_url() -> str:
    import os

    url = os.environ.get("DATABASE_URL")
    if url:
        return url
    env_path = _ROOT / ".env"
    if env_path.exists():
        for line in env_path.read_text(encoding="utf-8").splitlines():
            line = line.strip()
            if line.startswith("DATABASE_URL="):
                return line.split("=", 1)[1].strip().strip("'").strip('"')
    raise SystemExit("DATABASE_URL not found in environment or .env")


def main() -> None:
    if len(sys.argv) < 2:
        raise SystemExit("Usage: python scripts/apply_migration.py <file.sql>")
    sql_path = (_ROOT / sys.argv[1]).resolve()
    sql = sql_path.read_text(encoding="utf-8")
    dsn = _load_database_url()

    print(f"Applying {sql_path.name} ...")
    with psycopg.connect(dsn, autocommit=True) as conn:
        with conn.cursor() as cur:
            cur.execute(sql)
    print("OK — migration applied.")


if __name__ == "__main__":
    main()