{"id":9943,"library":"migra","title":"migra: PostgreSQL Schema Migration","description":"migra is a Python library that functions like `diff` for PostgreSQL schemas. It compares two PostgreSQL databases (or SQLAlchemy metadata objects) and generates the SQL statements necessary to migrate one to the other, making schema management and deployment safer and more explicit. The current version is 3.0.1663481299, with releases following active development.","status":"active","version":"3.0.1663481299","language":"en","source_language":"en","source_url":"https://github.com/djrobstep/migra","tags":["postgresql","database","migration","schema","ddl","sqlalchemy"],"install":[{"cmd":"pip install migra","lang":"bash","label":"Install latest version"}],"dependencies":[{"reason":"Required for PostgreSQL database connectivity.","package":"psycopg2-binary","optional":false}],"imports":[{"symbol":"Migration","correct":"from migra import Migration"},{"note":"Used for programmatic creation of migration objects, often not needed for simple diffs.","symbol":"create","correct":"from migra import create"}],"quickstart":{"code":"import os\nfrom migra import Migration\nfrom sqlalchemy import create_engine, MetaData, Table, Column, Integer, String\n\n# Use environment variables for PostgreSQL connection strings for safety\n# Ensure POSTGRES_SOURCE_URL and POSTGRES_TARGET_URL are set to real PostgreSQL databases.\n# For a demo, you can point them to two different databases on the same host,\n# or create a temporary 'source' schema and an empty 'target' schema.\nsource_url = os.environ.get('POSTGRES_SOURCE_URL', 'postgresql+psycopg2://user:pass@localhost:5432/source_db')\ntarget_url = os.environ.get('POSTGRES_TARGET_URL', 'postgresql+psycopg2://user:pass@localhost:5432/target_db')\n\n# Establish SQLAlchemy engines\nsource_engine = create_engine(source_url)\ntarget_engine = create_engine(target_url)\n\n# Define a simple schema for the \"source\" database (what we want the target to look like)\nsource_metadata = MetaData()\nTable('users', source_metadata,\n      Column('id', Integer, primary_key=True),\n      Column('name', String(50), nullable=False),\n      Column('email', String(100), unique=True))\n\n# Apply the source schema to the source database (if not already there)\nprint(\"Ensuring source schema exists in source_db...\")\nwith source_engine.connect() as conn:\n    source_metadata.create_all(conn)\n    conn.commit()\n\n# The target database is assumed to be empty or have an older schema.\n# migra will generate SQL to make target look like source.\n\n# Create a Migration object to compare the live schemas\nm = Migration(source=source_engine, target=target_engine)\n\n# Get the DDL statements to transform target to source\nsql_statements = m.statements\n\nprint(\"\\nGenerated SQL statements to migrate target_db to source_db schema:\")\nif sql_statements:\n    for stmt in sql_statements:\n        print(stmt)\n    # To apply the migration to the target database (UNCOMMENT WITH EXTREME CAUTION!)\n    # print(\"\\nApplying migration to target database...\")\n    # with target_engine.connect() as conn:\n    #    m.apply(conn)\n    #    conn.commit()\n    # print(\"Migration applied successfully.\")\nelse:\n    print(\"No migration statements needed (schemas are identical or target is already ahead).\")","lang":"python","description":"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."},"warnings":[{"fix":"Always inspect `m.statements` output before calling `m.apply()`. Manually adjust complex migrations if needed.","message":"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.","severity":"gotcha","affected_versions":"All"},{"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.","message":"`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.","severity":"gotcha","affected_versions":"All"},{"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.","message":"`migra` depends on `psycopg2-binary` for PostgreSQL connectivity. Incompatible versions or a missing installation can lead to runtime errors or failed connections.","severity":"gotcha","affected_versions":"All"}],"env_vars":null,"last_verified":"2026-04-17T00:00:00.000Z","next_check":"2026-07-16T00:00:00.000Z","problems":[{"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.","cause":"Incorrect database connection credentials (username, password) or network issues prevent connection to the PostgreSQL server.","error":"psycopg2.OperationalError: connection to server at \"...\" failed: FATAL: password authentication failed for user \"...\""},{"fix":"Install the dependency: `pip install psycopg2-binary`. If using a virtual environment, ensure it's activated.","cause":"The required PostgreSQL adapter `psycopg2-binary` (or `psycopg2`) is not installed in your Python environment. `migra` depends on this for database interaction.","error":"ImportError: No module named 'psycopg2'"},{"fix":"Correct the import statement to `from migra import Migration`.","cause":"You are attempting to access `Migration` incorrectly. It is directly available from the top-level `migra` module.","error":"AttributeError: module 'migra' has no attribute 'Migration'"},{"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()`.","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.","error":"sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) relation \"your_table_name\" does not exist"}]}