Alembic: Varying database names per environment?

[Scott]

Looking to use Alembic to manage migrations.

We currently have different database names in each environment, so for dev, test and prod we have db_dev, db_test and db_prod respectively.

Is this database naming scheme going to be compatible with Alembic or am I better off looking to drop the environment suffix?

Thanks, Scott

[Mike Bayer]

these are three different URLs and if the concern is putting them into one alembic.ini file, easy enough using separate sections:   https://alembic.sqlalchemy.org/en/latest/cookbook.html#run-multiple-alembic-environments-from-one-ini-file

though usually staging and production DBs have a password you're looking to keep private, and you'd have a separate alembic.ini on your staging and prod servers. but either way it's all doable

Thanks, Scott

--

You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alem...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy-alembic/e61e70a9-c2dc-4880-a839-f36272cbfad7%40googlegroups.com.

[Scott]

On Monday, August 19, 2019 at 9:58:19 AM UTC+10, Mike Bayer wrote:

On Sun, Aug 18, 2019, at 6:50 PM, Scott wrote:

Looking to use Alembic to manage migrations.

We currently have different database names in each environment, so for dev, test and prod we have db_dev, db_test and db_prod respectively.

Is this database naming scheme going to be compatible with Alembic or am I better off looking to drop the environment suffix?

these are three different URLs and if the concern is putting them into one alembic.ini file, easy enough using separate sections:   https://alembic.sqlalchemy.org/en/latest/cookbook.html#run-multiple-alembic-environments-from-one-ini-file

though usually staging and production DBs have a password you're looking to keep private, and you'd have a separate alembic.ini on your staging and prod servers. but either way it's all doable

Thanks for your reply.

The databases in question will in fact contain the same schema, table, view, etc. objects. We develop in dev, then promote code and database changes to test and then on to prod. This seems like a pretty straightforward use case for Alembic; each DB will have its own version and when we promote code from dev to test and then on to prod the relevant head would be retrieved from git (along with application code) and can be applied to the target database in order to bring it up to the correct version.

In our case however, with manual deployment we included a variable in the database name and change this per environment. So when we promote code we need the changes we made to db_dev.schema1.table1 to be made to db_test.schema1.table1. I think this is different concept to the what "sections" provides.

If I was going to manually create the upgrade/downgrade scripts every time I could continue to use a variable to compute the database name, but I could never use autogenerate as this would bring in a specific database name and I would no longer be able to move my code between environments.

I suspect the safest approach will be if we drop the environment suffix from our table names. This will be easier all around.

Happy to receive any further advice others may have to offer. Wondering for example if render_item can be used.

Cheers, Scott

[Mike Bayer]

On Mon, Aug 19, 2019, at 7:24 AM, Scott wrote:

On Monday, August 19, 2019 at 9:58:19 AM UTC+10, Mike Bayer wrote:

On Sun, Aug 18, 2019, at 6:50 PM, Scott wrote:

Looking to use Alembic to manage migrations.

We currently have different database names in each environment, so for dev, test and prod we have db_dev, db_test and db_prod respectively.

Is this database naming scheme going to be compatible with Alembic or am I better off looking to drop the environment suffix?

these are three different URLs and if the concern is putting them into one alembic.ini file, easy enough using separate sections:   https://alembic.sqlalchemy.org/en/latest/cookbook.html#run-multiple-alembic-environments-from-one-ini-file

though usually staging and production DBs have a password you're looking to keep private, and you'd have a separate alembic.ini on your staging and prod servers. but either way it's all doable

Thanks for your reply.

The databases in question will in fact contain the same schema, table, view, etc. objects. We develop in dev, then promote code and database changes to test and then on to prod. This seems like a pretty straightforward use case for Alembic; each DB will have its own version and when we promote code from dev to test and then on to prod the relevant head would be retrieved from git (along with application code) and can be applied to the target database in order to bring it up to the correct version.

In our case however, with manual deployment we included a variable in the database name and change this per environment. So when we promote code we need the changes we made to db_dev.schema1.table1 to be made to db_test.schema1.table1. I think this is different concept to the what "sections" provides.

So when we talk about "db_dev.schema.table", the first element in that path is usually the database name that is part of what you put in the database URL, like:

dbtype://user:pass@hostname/db_dev

I don't recall if you said you were using Microsoft SQL Server, where we do support a lesser used case where the "schema" given in the Table / Metadata definition has the dot within it, e.g.:

Table('foo', metadata, <columns>, schema="db_dev.schema1")

so you'd need to confirm that this is the pattern you're doing.

If it were me, I'd try to avoid having the dotted db/schema in the Table/Metadata itself and opt for the database URL to instead establish a connection that provides the correct database name default to DDL operations.    If this is SQL server, you'd have different logins which provide for this (see https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-login-transact-sql?view=sql-server-2017 ).

However SQLAlchemy supports compound "database.schema" tokens, and if you want these to be dynamic you would use the schema translation feature: https://docs.sqlalchemy.org/en/13/core/connections.html#translation-of-schema-names  in conjunction with some additional directives in your env.py to set this up, and the env.py would then interact either with an -x option (https://alembic.sqlalchemy.org/en/latest/api/runtime.html?#alembic.runtime.environment.EnvironmentContext.get_x_argument ) or you would read the config directly, defining a variable name of your choice within separate alembic.ini sections.

With schema translation, your Python code refers to a fixed name inside of the "schema" tokens for tables- the alternate db name / schema name is substituted in at runtime using the execution_options(translate_map) feature.   

If you have an arbitrary number of "schema" names inside each database, like tenant schemas or something where you can't easily code the "translate_map" to have explicit entries for each schema, there are still more techniques where the functionality of "translate_map" can be implemented using events that would use your own regular-expression scheme against the DDL and SQL statements in order to affect the database.schemaname rendering.

If I was going to manually create the upgrade/downgrade scripts every time I could continue to use a variable to compute the database name, but I could never use autogenerate as this would bring in a specific database name and I would no longer be able to move my code between environments.

I suspect the safest approach will be if we drop the environment suffix from our table names. This will be easier all around.

I would consider alternately that the database connection would set up the default database name "db_dev" / "db_test" as implicitly used when you refer to a table as "schema1.table1", however the schema translation feature would otherwise allow you to use the three-token scheme explicitly in your DDL / SQL scripts.

Happy to receive any further advice others may have to offer. Wondering for example if render_item can be used.

Cheers, Scott

--

You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alem...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy-alembic/878a46b2-9e3c-4f40-8369-dd05f45e872f%40googlegroups.com.