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:
- the migration script miss the relevant imports from
geoalchemy2
. - 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:
- add the missing import
from geoalchemy2 import Geometry
. - remove the
create_index
statement in theupgrade()
function. - remove the
drop_index
statement in thedowngrade()
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, like in the following example:
# ...
from geoalchemy2.alembic_helpers import include_object
from geoalchemy2.alembic_helpers import render_item
# ...
def run_migrations_offline():
# ...
context.configure(
# ...
process_revision_directives=alembic_helpers.writer,
render_item=alembic_helpers.render_item,
)
# ...
def run_migrations_online():
# ...
context.configure(
# ...
process_revision_directives=alembic_helpers.writer,
render_item=alembic_helpers.render_item,
)
# ...
After running the alembic
command, the migration script should be properly generated and should
not need to be manually edited.
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.alembic_helpers import include_object
from geoalchemy2.alembic_helpers import render_item as spatial_render_item
from my_package.custom_types import TheCustomType
# ...
def render_item(obj_type, obj, autogen_context):
"""Apply custom rendering for selected items."""
spatial_type = spatial_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(
# ...
process_revision_directives=alembic_helpers.writer,
render_item=render_item,
)
# ...
def run_migrations_online():
# ...
context.configure(
# ...
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.alembic_helpers import load_spatialite
from geoalchemy2.alembic_helpers import writer
def run_migrations_offline():
# ...
context.configure(
# ...
process_revision_directives=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(
# ...
process_revision_directives=writer,
render_item=alembic_helpers.render_item,
)
# ...