# Use Alembic with GeoAlchemy 2

The Alembic package is a lightweight database migration tool which is able to automatically detect the table column types.

## Interactions between Alembic and GeoAlchemy 2

Interactions between some features of Alembic and GeoAlchemy 2 may lead to errors in migration scripts, especially when using the `--autogenerate` feature of Alembic with the `spatial_index=True` feature of GeoAlchemy 2. In this case, the following errors occur:

1. the migration script misses the relevant imports from `geoalchemy2`.

2. the migration script will create the indexes of the spatial columns after the table is created, but these indexes are already automatically created during table creation, which will lead to an error.

For example, suppose the following table is defined:

    class Lake(Base):
        __tablename__ = 'lake'
        id = Column(Integer, primary_key=True)
        geom = Column(
            Geometry(
                geometry_type='LINESTRING',
                srid=4326,
                spatial_index=True,
            )
        )

Then the command `alembic revision --autogenerate -m "Create new table"` will create the following migration script:

    """Create new table

    Revision ID: <rev_id>
    Revises: <down_rev_id>
    Create Date: <date>

    """
    from alembic import op
    import sqlalchemy as sa

    # revision identifiers, used by Alembic.
    revision = "<rev_id>"
    down_revision = "<down_rev_id>"
    branch_labels = None
    depends_on = None

    def upgrade():
        # ### commands auto generated by Alembic - please adjust! ###
        op.create_table(
            "lake",
            sa.Column("id", sa.Integer(), nullable=False),
            sa.Column(
                "geom",
                geoalchemy2.types.Geometry(
                    geometry_type="LINESTRING",
                    srid=4326,
                    from_text="ST_GeomFromEWKT",
                    name="geometry",
                ),
                nullable=True,
            ),
            sa.PrimaryKeyConstraint("id"),
        )
        op.create_index(
            "idx_lake_geom",
            "lake",
            ["geom"],
            unique=False,
            postgresql_using="gist",
            postgresql_ops={},
        )
        # ### end Alembic commands ###

    def downgrade():
        # ### commands auto generated by Alembic - please adjust! ###
        op.drop_index(
            "idx_lake_geom",
            table_name="lake",
            postgresql_using="gist",
            postgresql_ops={},
        )
        op.drop_table("lake")
        # ### end Alembic commands ###

In this case, we have to do the following changes to make it work:

1. add the missing import `from geoalchemy2 import Geometry`.

2. remove the `create_index` statement in the `upgrade()` function.

3. remove the `drop_index` statement in the `downgrade()` function.

## Helpers

In order to make the use of Alembic easier, a few helpers are provided in geoalchemy2.alembic_helpers. These helpers can be used in the `env.py` file used by Alembic to auto-generate the migration scripts, like in the following example:

    # ...
    from geoalchemy2 import alembic_helpers
    # ...

    def run_migrations_offline():
        # ...
        context.configure(
            # ...
            include_object=alembic_helpers.include_object,
            process_revision_directives=alembic_helpers.writer,
            render_item=alembic_helpers.render_item,
        )
        # ...

    def run_migrations_online():
        # ...
        context.configure(
            # ...
            include_object=alembic_helpers.include_object,
            process_revision_directives=alembic_helpers.writer,
            render_item=alembic_helpers.render_item,
        )
        # ...

As one can see, there are 3 specific functions to pass to the context:

1. `geoalchemy2.alembic_helpers.include_object()` ignores the internal tables managed by the spatial extensions (note that in some cases this function might need some customization, see the details in the doc of this function).

2. `geoalchemy2.alembic_helpers.writer` adds specific spatial operations to Alembic.

3. `geoalchemy2.alembic_helpers.render_item()` automatically adds GeoAlchemy2 imports into the migration scripts.

After running the `alembic revision --autogenerate -m <msg>` command, the migration script should be properly generated and should not need to be manually edited.

In this migration script you will notice specific spatial operation like create_geospatial_table, drop_geospatial_table, add_geospatial_column, drop_geospatial_column, etc. These operations can of course be edited manually in the migration scripts if you don’t want to use auto-generation. All specific operations can be found in geoalchemy2.alembic_helpers.

## Dealing with custom types

With `SQLAlchemy`, users are able to define custom types, as shown in Automatically use a function at insert or select. In this case, you can refer to the dedicated page of Alembic’s documentation for the details.

A simple solution for this case is to create a new `render_item` function to add specific imports for these custom types. For example, if your custom type is called `TheCustomType` and is defined in `my_package.custom_types`, you just have to edit the `env.py` file like the following:

    # ...
    from geoalchemy2 import alembic_helpers
    from my_package.custom_types import TheCustomType
    # ...

    def render_item(obj_type, obj, autogen_context):
        """Apply custom rendering for selected items."""
        spatial_type = alembic_helpers.render_item(obj_type, obj, autogen_context)
        if spatial_type:
            return spatial_type

        # For the custom type
        if obj_type == 'type' and isinstance(obj, TheCustomType):
            import_name = obj.__class__.__name__
            autogen_context.imports.add(f"from my_package.custom_types import {import_name}")
            return "%r" % obj

        # default rendering for other objects
        return False

    def run_migrations_offline():
        # ...
        context.configure(
            # ...
            include_object=alembic_helpers.include_object,
            process_revision_directives=alembic_helpers.writer,
            render_item=render_item,
        )
        # ...

    def run_migrations_online():
        # ...
        context.configure(
            # ...
            include_object=alembic_helpers.include_object,
            process_revision_directives=alembic_helpers.writer,
            render_item=render_item,
        )
        # ...

Then the proper imports will be automatically added in the migration scripts.

## Dialects

Some dialects (like SQLite) require some specific management to alter columns or tables. In this case, other dedicated helpers are provided to handle this. For example, if one wants to add and drop columns in a SQLite database, the SpatiaLite extension should be loaded when the engine connects, thus the `env.py` file should look like the following:

    from geoalchemy2 import alembic_helpers
    from geoalchemy2 import load_spatialite

    def run_migrations_offline():
        # ...
        context.configure(
            # ...
            include_object=alembic_helpers.include_object,
            process_revision_directives=alembic_helpers.writer,
            render_item=alembic_helpers.render_item,
        )
        # ...

    def run_migrations_online():
        # ...
        if connectable.dialect.name == "sqlite":
            # Load the SpatiaLite extension when the engine connects to the DB
            listen(connectable, 'connect', load_spatialite)

        with connectable.connect() as connection:
            # ...
            context.configure(
                # ...
                include_object=alembic_helpers.include_object,
                process_revision_directives=alembic_helpers.writer,
                render_item=alembic_helpers.render_item,
            )
            # ...