Use alembic with sqlalchemy-aurora-data-api

[tomeszmh]

Hey!

Is it possible to use alembic migration with this tool? I have an Aurora serverless cluster with generated secret, but serverless clusters are not public. It would be a huge step to migrate my db using alembic and data api.

Thanks, Tamas

[mikeluby]

Hey @tomeszmh - I was able to get alembic working, below are the steps I followed.

Currently I manage the revisions/migrations from my local machine. This assumes your machine is configured with the proper aws credentials. My goal is to get this into an automated CI/CD pipeline but haven't had an opportunity to tackle that yet.

1. Install & initialize alembic

pip install alembic
alembic init alembic

2. Update alembic.ini and set sqlalchemy.url to the correct connection string (e.g. sqlalchemy.url = postgresql+auroradataapi://:@/DATABASE)
3. env.py, update run_migrations_online to set connectable with create_engine instead of engine_from_config

def run_migrations_online() -> None:
    ...
    connectable = create_engine(f"postgresql+auroradataapi://:@/DATABASE", echo=True,
                                connect_args=dict(aurora_cluster_arn=os.environ.get("AURORA_CLUSTER_ARN"), secret_arn=os.environ.get("SECRET_ARN")))
    ...

6. When running revision or upgrade I pass environment variables of the aurora_cluster_arn and secret_arn

AURORA_CLUSTER_ARN=XXX SECRET_ARN=XXX .env/bin/alembic revision -m "First commit" --autogenerate
AURORA_CLUSTER_ARN=XXX SECRET_ARN=XXX .env/bin/alembic upgrade head