Alembic: version_locations wildcard for a monorepo

[am17torres]

I have a repo that's structured like so:

db
└ schema
  ├ schema1
  ├ schema2
  └ schema3

Where all schemas are colocated on the same database cluster and each schema has its own model and versions.

I expect the list of schemas to grow over time where there will be n schemas managed in this monorepo. To further complicate things, some schemas may have dependencies on another.

I've been looking at the working with multiple bases documenation. This seems right inline with what I'm trying to do.

_alembic.ini_

[schema1]
script_location = db/schema/schema1
# override the database name if different than section name
database = my_schema1

[schema2]
script_location = db/schema/schema2

[schema3]
script_location = db/schema/schema3

[alembic]
script_location = %(here)s/db
version_locations = %(here)s/db/schema/db1/versions/ %(here)s/db/schema/db2/versions/ %(here)s/db/schema/db3/versions/ 

I am currently having a few issues:

1. version_locations - Is there a way I could use a wildcard so I didn't have to list out every schema?

1. As of now I have to use alembic --name for all my commands. I'm wondering if there is a way simply use alembic (without the --name) and treat the collection of schemas as components of a single, larger, cluster of schema all within one "database". I see that I can use the depends_on attribute if revisions cross boundaries, but I'm not sure how to go about orchestrating the correct upgrade sequence if say someone checks out this project for the first time and tries to setup their local development environment to match production.

If i'm way off base here please let me know.

Thanks in advance!

[zzzeek]

there's no wildcard support for version_locations however if someone had the interest in building out such a feature it could be included if the implementation and tests are of sufficient quality.

there's also an issue #570 that would allow version_locations to be writeable from env.py. This can work for you right now as long as you pop out the memoized version of it, e.g.:

# env.py
script.version_locations = [<locate all the version directories using os.walk or similar>]
script.__dict__.pop('_version_locations', None)

then in config make sure you set revision_environment = True.

another way to do this is to make a custom front end to alembic, so that instead of running the "alembic" runner you use the command api directly, and you can programmatically create a config.

as for the single environment, definitely. you can have multiple bases and depends_on as needed, and someone starting from scratch would run "alembic upgrade heads". in theory as long as you've ensured the correct dependencies between the different revision streams, it should all.....hopefully work!

[am17torres]

I'm a bit confused on how to set up alembic.ini for multiple bases. Do you have a single script location or multiple? Do you have a single env.py or an env.py specific to each schema? How does the migration framework know under which schema to create the models?

Is there an example repo where this has been implemented successfully?

[zzzeek]

I'm a bit confused on how to set up alembic.ini for multiple bases. Do you have a single script location or multiple? Do you have a single env.py or an env.py specific to each schema?

for multiple bases as discussed at https://alembic.sqlalchemy.org/en/latest/branches.html#working-with-multiple-bases , there's a single "env.py" that alembic itself cares about.

How does the migration framework know under which schema to create the models?

All the alembic operation directives accept a "schema" argument, so assuming you have versioning scripts that are specific to a particular schema, you'd see this name present inside the migration file inside of the op directives like op.add_column("sometable", Column(...), schema="some_schema").

if OTOH this is more like a "multitenancy" kind of thing where the same versioning scripts are used for multiple schemas, we'd have to use a different approach but that does not seem to be what you described above.

Is there an example repo where this has been implemented successfully?

I don't have example repos handy on my end...but when I wrote the docs at the link above I was running those commands for real to make sure they worked so if you just walked through that you'd mostly have an example repo. if you are using autogenerate you'd want to make sure "include_schemas=True" in your env.py.

[am17torres]

All the alembic operation directives accept a "schema" argument, so assuming you have versioning scripts that are specific to a particular schema, you'd see this name present inside the migration file inside of the op directives like op.add_column("sometable", Column(...), schema="some_schema").

This is good to know! Is there a way to declare the schema in the model so --autogenerate picks that up?

[zzzeek]

yeah you should be setting up "schema" throughout your model: https://docs.sqlalchemy.org/en/13/core/metadata.html#specifying-the-schema-name note with SQLAlchemy ORM declarative this goes in __table_args__

[am17torres]

Big thanks @zzzeek! I was able to accomplish exactly what I needed by using a single database deployment and by including the schema in __table_args__

[zzzeek]

ok great!