Recipe for applying migrations in production?

[pawamoy]

I'm developing a FastAPI app and need to apply locally working migrations on a production database. I cannot manually run custom commands in production. So I thought about including the Alembic data (config, env.py, migrations) into my source code, so I can programmatically apply migrations every time my app starts (in the FastAPI's startup event), but I' really not sure if I'm using the right approach / the correct file/folder layout and related Python instructions.

I think I got something working (not tested in production):

from alembic.config import Config
from alembic.command import upgrade
from fastapi import FastAPI

app = FastAPI()
app.state.database = db.database  # imported from a submodule


@app.on_event("startup")
async def startup() -> None:
    """Startup application."""
    alembic_cfg = Config()
    # including the config file in the source code and passing it to Config
    # seems to complicate things as the config itself references the "script location",
    # relative to the current working directory and not the config file itself:
    # my app is "installed" (non-editable) when in prod, so its path
    # is different than on a local machine. I've seen %(here)s variables used
    # in the docs, but not sure what other variables I could use

    alembic_cfg.set_main_option("script_location", "my_namespace.my_package.my_subpackage:migrations")
    # couldn't find docs explaining the python_dotted_path:folder format

    alembic_cfg.set_main_option("sqlalchemy.url", "sqlite:///db.sqlite")
    # seems required, but will be overwritten by env.py anyway.
    # should I remove the logic from env.py and keep it here only?

    upgrade(alembic_cfg, "head")

    database = app.state.database
    if not database.is_connected:
        await database.connect()

Is this the way to go? Is there a better approach? Has anybody set such a thing up?

[zzzeek]

hi and sorry for the late reply.

We of course never need to require that commands are run "manually" on production. The usual way this use case is met without a lot of hassle is that the running of the "alembic upgrade" command would be added to the deploy process for your application. Deploy processes vary, from shell scripts to things like Ansible to web-based administration systems, but I can't imagine a production-quality deployment system that does not include the option to run arbitrary scripts or commands as part of the process by which new files are deployed to servers; after all, the database had to be created in the first place somehow.

so given the recommendation is, "the deploy process should support addition of arbitrary commands", and that's not an option right now, yes you could also make use of FastAPI's startup hooks to run alembic before FastAPI starts the whole application for receiving requests. You could make use of the recipe at https://alembic.sqlalchemy.org/en/latest/cookbook.html#test-current-database-revision-is-at-head-s as a quick check for the database's version before running the upgrade command.

[pawamoy]

I see, thank you for your answer. I could indeed run arbitrary commands using initContainers or jobs (kubernetes), but it brings even more complexity than just doing the thing in the app directly. Thanks again!