migra: PostgreSQL Schema Migration

Common errors

• psycopg2.OperationalError: connection to server at "..." failed: FATAL: password authentication failed for user "..."

  cause Incorrect database connection credentials (username, password) or network issues prevent connection to the PostgreSQL server.
  fix Verify the username, password, host, port, and database name in your PostgreSQL connection URL. Ensure the server is reachable and listening on the specified port.

• ImportError: No module named 'psycopg2'

  cause The required PostgreSQL adapter `psycopg2-binary` (or `psycopg2`) is not installed in your Python environment. `migra` depends on this for database interaction.
  fix Install the dependency: `pip install psycopg2-binary`. If using a virtual environment, ensure it's activated.

• AttributeError: module 'migra' has no attribute 'Migration'

  cause You are attempting to access `Migration` incorrectly. It is directly available from the top-level `migra` module.
  fix Correct the import statement to `from migra import Migration`.

• sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) relation "your_table_name" does not exist

  cause This typically occurs when `migra` expects a table to exist in the target database but it's missing, or if you're trying to query a table before `migra` has applied the `CREATE TABLE` statement.
  fix Ensure your `target_engine` points to the intended database. If the table is supposed to be created by `migra`, review the generated statements and apply them (carefully) using `m.apply()`.

Warnings

• gotcha Migra performs destructive operations (e.g., `DROP` and `CREATE`) for schema changes it cannot intelligently alter directly (like column type changes, renames without explicit hints, or complex constraint modifications). Always review generated SQL statements carefully before applying.
  Fix: Always inspect `m.statements` output before calling `m.apply()`. Manually adjust complex migrations if needed.
• gotcha `migra` directly modifies your PostgreSQL database when `m.apply()` is called. This can lead to data loss or schema corruption if used improperly. It operates on schema, not data integrity during evolution.
  Fix: Always use `m.statements` to perform a dry run. Back up your target database before applying any migration, especially in production environments. Consider wrapping `m.apply()` in a transaction for rollback capabilities.
• gotcha `migra` depends on `psycopg2-binary` for PostgreSQL connectivity. Incompatible versions or a missing installation can lead to runtime errors or failed connections.
  Fix: Ensure `psycopg2-binary` is correctly installed and compatible with your Python version. If `pip install migra` doesn't resolve it, try `pip install psycopg2-binary` explicitly.

Install

• pip install migra Install latest version

Imports

• Migration

  from migra import Migration

• create

  from migra import create

  Used for programmatic creation of migration objects, often not needed for simple diffs.

Quickstart

This quickstart compares a defined SQLAlchemy `MetaData` schema with a live (potentially empty) target PostgreSQL database. It generates the DDL needed to make the target schema match the source. Remember to set `POSTGRES_SOURCE_URL` and `POSTGRES_TARGET_URL` environment variables to your actual PostgreSQL connection strings. Always review the generated SQL before applying it to a production database.

import os
from migra import Migration
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String

# Use environment variables for PostgreSQL connection strings for safety
# Ensure POSTGRES_SOURCE_URL and POSTGRES_TARGET_URL are set to real PostgreSQL databases.
# For a demo, you can point them to two different databases on the same host,
# or create a temporary 'source' schema and an empty 'target' schema.
source_url = os.environ.get('POSTGRES_SOURCE_URL', 'postgresql+psycopg2://user:pass@localhost:5432/source_db')
target_url = os.environ.get('POSTGRES_TARGET_URL', 'postgresql+psycopg2://user:pass@localhost:5432/target_db')

# Establish SQLAlchemy engines
source_engine = create_engine(source_url)
target_engine = create_engine(target_url)

# Define a simple schema for the "source" database (what we want the target to look like)
source_metadata = MetaData()
Table('users', source_metadata,
      Column('id', Integer, primary_key=True),
      Column('name', String(50), nullable=False),
      Column('email', String(100), unique=True))

# Apply the source schema to the source database (if not already there)
print("Ensuring source schema exists in source_db...")
with source_engine.connect() as conn:
    source_metadata.create_all(conn)
    conn.commit()

# The target database is assumed to be empty or have an older schema.
# migra will generate SQL to make target look like source.

# Create a Migration object to compare the live schemas
m = Migration(source=source_engine, target=target_engine)

# Get the DDL statements to transform target to source
sql_statements = m.statements

print("\nGenerated SQL statements to migrate target_db to source_db schema:")
if sql_statements:
    for stmt in sql_statements:
        print(stmt)
    # To apply the migration to the target database (UNCOMMENT WITH EXTREME CAUTION!)
    # print("\nApplying migration to target database...")
    # with target_engine.connect() as conn:
    #    m.apply(conn)
    #    conn.commit()
    # print("Migration applied successfully.")
else:
    print("No migration statements needed (schemas are identical or target is already ahead).")