Alembic Utils

Warnings

• breaking `PGText` was removed in version `0.8.0`. Users who previously used `PGText` for defining functions or views must migrate their definitions to `PGFunction` or `PGView` respectively. This change provides more specific and structured interfaces for each entity type.
  Fix: Replace `PGText` definitions with `PGFunction` for functions or `PGView` for views, updating arguments as necessary (e.g., adding `returns` to `PGFunction`).
• breaking The `PGFunction` constructor signature changed in version `0.6.0`, making the `returns` argument mandatory. Migrations using `PGFunction` from older versions will fail if `returns` is not provided.
  Fix: Ensure all `PGFunction` definitions include the `returns` argument specifying the return type (e.g., `returns='integer'` or `returns='void'`).
• gotcha Alembic integration for change detection and synchronization varies by version. For Alembic >= 1.10.0 and Alembic-Utils >= 0.8.0, the preferred method to automatically manage entities defined in your application models is `op.sync_alembic_utils()`, typically called in `env.py`. For older versions, `add_alembic_utils_meta(op)` was used in `env.py`.
  Fix: Consult the documentation for your specific Alembic and Alembic-Utils versions. Use `op.sync_alembic_utils()` if on modern versions, otherwise use `add_alembic_utils_meta(op)`.
• gotcha Alembic Utils is primarily designed for PostgreSQL. While some concepts might apply to other SQL dialects, the specific entity classes (e.g., `PGView`, `PGFunction`) and their underlying DDL generation are tailored to PostgreSQL syntax and features. Attempting to use these with other databases may lead to syntax errors or unexpected behavior.
  Fix: Ensure you are using Alembic Utils with a PostgreSQL database. For other databases, seek alternative migration tools for custom DDL.

Install

• pip install alembic-utils Install stable version

Imports

• PGView

  from alembic_utils.pg_view import PGView

• PGFunction

  from alembic_utils.pg_function import PGFunction

• PGMaterializedView

  from alembic_utils.pg_materialized_view import PGMaterializedView

• PGTrigger

  from alembic_utils.pg_trigger import PGTrigger

• PGPolicy

  from alembic_utils.pg_policy import PGPolicy

• add_alembic_utils_meta

  from alembic_utils.utils import add_alembic_utils_meta

  Primarily for older Alembic/Alembic-Utils versions; op.sync_alembic_utils() is preferred for newer setups.

Quickstart

This quickstart demonstrates how to define a PostgreSQL view and function using Alembic Utils, and how to use the `op.create_entity()`, `op.drop_entity()`, and `op.sync_alembic_utils()` methods within an Alembic migration script. The `MockOp` class allows the code to run and display the intended DDL operations without requiring an actual database connection or full Alembic project setup.

import sys
from alembic_utils.pg_view import PGView
from alembic_utils.pg_function import PGFunction

# Mock op object for demonstration purposes to show API usage.
# In a real Alembic migration, `op` is provided by Alembic.
class MockOp:
    def execute(self, statement):
        print(f"Executing DDL: {statement}")

    def create_entity(self, entity):
        print(f"Creating entity: {entity.entity_name}")
        print(f"  Definition: {entity.to_sql_statement()}")

    def drop_entity(self, entity):
        print(f"Dropping entity: {entity.entity_name}")
        print(f"  Definition: {entity.to_sql_drop_statement()}")

    def sync_alembic_utils(self):
        print("Synchronizing Alembic-Utils entities (mocked).")

op = MockOp()

# Define a PostgreSQL view
my_view = PGView(
    schema="public",
    signature="my_first_view",
    definition="SELECT id, name FROM users WHERE active = TRUE;"
)

# Define a PostgreSQL function
my_function = PGFunction(
    schema="public",
    signature="my_first_function(a integer, b integer)",
    definition="RETURNS integer LANGUAGE plpgsql IMMUTABLE AS $$ BEGIN RETURN a + b; END; $$",
    returns="integer" # Required since 0.6.0
)

# Simulate usage in an Alembic migration's upgrade() function
print("--- Simulating 'upgrade' phase ---")
op.create_entity(my_view)
op.create_entity(my_function)

# For Alembic >= 1.10.0 and Alembic-Utils >= 0.8.0, use this in env.py
# to automatically discover and manage entities defined in your model.
# If you define entities directly in migration scripts, this might not be needed.
op.sync_alembic_utils()

print("\n--- Simulating 'downgrade' phase ---")
# Entities should be dropped in reverse order of creation if dependencies exist
op.drop_entity(my_function)
op.drop_entity(my_view)