Re: Autogenerate with Multi-Tenant
That works!
Thank you.
Brian
On Friday, March 20, 2020 at 12:11:21 PM UTC-4, Mike Bayer wrote:
>
> OK one more addition to the recipe, please do this:
>
> DB_SCHEMA = "my_foo_schema"
>
> with connectable.connect() as connection:
> connection.execute(f'create schema if not exists {DB_SCHEMA}')
> connection.execute(f'set search_path to {DB_SCHEMA}')
>
> connection.dialect.default_schema_name = DB_SCHEMA
>
>
> will work completely
>
> alternatively you can use an on connect event on the engine to do the same
> thing.
>
>
> On Fri, Mar 20, 2020, at 10:48 AM, Brian Hill wrote:
>
> this is what i tried but it generated the whole schema:
> .
> with connectable.connect() as connection:
>
> connection.execute(f'create schema if not exists {DB_SCHEMA}')
> connection.execute(f'set search_path to {DB_SCHEMA}')
>
> context.configure(
> connection=connection,
> target_metadata=metadata,
> )
>
> with context.begin_transaction():
> context.run_migrations()
>
> it works when my schema is 'public', when it matches metadata. my solution
> will be to set my schema to 'public' and use this simple env.py.
>
> thanks,
>
> brian
>
> On Friday, March 20, 2020 at 9:49:52 AM UTC-4, Mike Bayer wrote:
>
> I just realized that you really dont need to even use schema_translate_map
> at all here. If you use a completely straight env.py, and simply set
> search_path=SCHEMA, you should be able to run Alembic in entirely
> "schemaless" mode; don't include schemas anywhere nor would you need to set
> it for the alembic version table.Postgresql will also CREATE/ ALTER in
> that schema as well.As long as your environment runs completely in just
> one schema at a time, this can be set up entirely at the connection level.
>
>
> On Thu, Mar 19, 2020, at 9:15 PM, Brian Hill wrote:
>
>
>
> On Thursday, March 19, 2020 at 8:20:06 PM UTC-4, Mike Bayer wrote:
>
>
>
> On Thu, Mar 19, 2020, at 7:41 PM, Brian Hill wrote:
>
>
>
> On Thursday, March 19, 2020 at 7:19:08 PM UTC-4, Mike Bayer wrote:
>
> so let me get this straight:
>
> 1. you have many schemas
>
>
> yes
>
>
>
> 2. you want to run autogenerate only once
>
>
> yes
>
>
> 3. you want your mirations genrated with None for schema
>
>
> yes
>
>
> 4. *HOWEVER*, when you run autogenerate, you are picking *one* (random?
> arbitrary?) schema to use as the target, is that right?
>
>
> yes one, but it won't be arbitrary, it will be a development schema
> generated from the latest version (prior to the change we want to
> autogenerate).
>
>
>
>
> As it stands, your autogenerate is not going to look in any schema except
> "public", assuming default PG search path, because you did not set
> "include_schemas=True".
>
>
> i had it set to True and it behaves the same. i just took it out based on
> your previous suggestion, but again, it behaves the same.
>
>
> If you truly want autogenerate to look at one schema and only that schema,
> and generate everything as non-schema qualified, then I would simply set
> the default schema in PG to that schema name using search_path:
>
> connection.execute("SET search_path TO my_schema")
>
> that way everything Alembic reflects will be from "my_schema" and it will
> see the schema as blank, and it should generate as such. you might need to
> disable the schema translate map when autogenerate runs but try it without
> doing that first.
>
>
> it's still generating the entire schema in the revision file.
>
> here's the updated section and it behaves the same with/without schema
> translate map:
>
> with connectable.connect() as connection:
>
> # create DB_SCHEMA if it doesn't exist
> connection.execute(f'create schema if not exists {DB_SCHEMA}')
>
> # map metadata schema (None) to DB_SCHEMA
> # connection =
> connection.execution_options(schema_translate_map={None:DB_SCHEMA})
>
> connection.execute(f"set search_path to {DB_SCHEMA}")
>
> # set alembic version table location in DB_SCHEMA
> context.configure(
> connection=connection,
> include_schemas=True,
> target_metadata=metadata,
> version_table_schema=DB_SCHEMA,
> )
>
> with context.begin_transaction():
> context.run_migrations()
>
>
>
>
> no, don't use "include_schemas". you want to run alembic in a mode where
> it has no idea there are other schemas to use. i think
> version_table_schema is OK here but don't use include_schemas. Also turn
> on INFO or DEBUG logging for the sqlalchemy logger to see exactly w
Re: Autogenerate with Multi-Tenant
this is what i tried but it generated the whole schema:
.
with connectable.connect() as connection:
connection.execute(f'create schema if not exists {DB_SCHEMA}')
connection.execute(f'set search_path to {DB_SCHEMA}')
context.configure(
connection=connection,
target_metadata=metadata,
)
with context.begin_transaction():
context.run_migrations()
it works when my schema is 'public', when it matches metadata. my solution
will be to set my schema to 'public' and use this simple env.py.
thanks,
brian
On Friday, March 20, 2020 at 9:49:52 AM UTC-4, Mike Bayer wrote:
>
> I just realized that you really dont need to even use schema_translate_map
> at all here. If you use a completely straight env.py, and simply set
> search_path=SCHEMA, you should be able to run Alembic in entirely
> "schemaless" mode; don't include schemas anywhere nor would you need to set
> it for the alembic version table.Postgresql will also CREATE/ ALTER in
> that schema as well.As long as your environment runs completely in just
> one schema at a time, this can be set up entirely at the connection level.
>
>
> On Thu, Mar 19, 2020, at 9:15 PM, Brian Hill wrote:
>
>
>
> On Thursday, March 19, 2020 at 8:20:06 PM UTC-4, Mike Bayer wrote:
>
>
>
> On Thu, Mar 19, 2020, at 7:41 PM, Brian Hill wrote:
>
>
>
> On Thursday, March 19, 2020 at 7:19:08 PM UTC-4, Mike Bayer wrote:
>
> so let me get this straight:
>
> 1. you have many schemas
>
>
> yes
>
>
>
> 2. you want to run autogenerate only once
>
>
> yes
>
>
> 3. you want your mirations genrated with None for schema
>
>
> yes
>
>
> 4. *HOWEVER*, when you run autogenerate, you are picking *one* (random?
> arbitrary?) schema to use as the target, is that right?
>
>
> yes one, but it won't be arbitrary, it will be a development schema
> generated from the latest version (prior to the change we want to
> autogenerate).
>
>
>
>
> As it stands, your autogenerate is not going to look in any schema except
> "public", assuming default PG search path, because you did not set
> "include_schemas=True".
>
>
> i had it set to True and it behaves the same. i just took it out based on
> your previous suggestion, but again, it behaves the same.
>
>
> If you truly want autogenerate to look at one schema and only that schema,
> and generate everything as non-schema qualified, then I would simply set
> the default schema in PG to that schema name using search_path:
>
> connection.execute("SET search_path TO my_schema")
>
> that way everything Alembic reflects will be from "my_schema" and it will
> see the schema as blank, and it should generate as such. you might need to
> disable the schema translate map when autogenerate runs but try it without
> doing that first.
>
>
> it's still generating the entire schema in the revision file.
>
> here's the updated section and it behaves the same with/without schema
> translate map:
>
> with connectable.connect() as connection:
>
> # create DB_SCHEMA if it doesn't exist
> connection.execute(f'create schema if not exists {DB_SCHEMA}')
>
> # map metadata schema (None) to DB_SCHEMA
> # connection =
> connection.execution_options(schema_translate_map={None:DB_SCHEMA})
>
> connection.execute(f"set search_path to {DB_SCHEMA}")
>
> # set alembic version table location in DB_SCHEMA
> context.configure(
> connection=connection,
> include_schemas=True,
> target_metadata=metadata,
> version_table_schema=DB_SCHEMA,
> )
>
> with context.begin_transaction():
> context.run_migrations()
>
>
>
>
> no, don't use "include_schemas". you want to run alembic in a mode where
> it has no idea there are other schemas to use. i think
> version_table_schema is OK here but don't use include_schemas. Also turn
> on INFO or DEBUG logging for the sqlalchemy logger to see exactly what
> tables it is reflecting.
>
>
> same behavior, entire schema generated. i'll look at using INFO and DEBUG
> tomorrow to get a better idea of what's going on, but using public as my
> dev schema to autogenerate against works. i just have to exclude the
> alembic tables.
>
> again thanks for your help in understanding what i was trying to do.
>
> brian
>
>
>
>
>
>
>
>
>
>
>
>
>
> On Thu, Mar 19, 2020, at 7:09 PM, Brian Hill wrote:
>
> Here's my env.py. Thanks for the help.
> Brian
>
> On Thursday, March 19, 2020 at 5:37:38 PM UTC-4, Mike Bayer wrote:
>
>
>
> On Thu, Mar 19, 2020, at 5:30 PM, Brian Hill wrote:
>
> Are there known issues with using autogenerate with
Re: Autogenerate with Multi-Tenant
On Thursday, March 19, 2020 at 8:20:06 PM UTC-4, Mike Bayer wrote:
>
>
>
> On Thu, Mar 19, 2020, at 7:41 PM, Brian Hill wrote:
>
>
>
> On Thursday, March 19, 2020 at 7:19:08 PM UTC-4, Mike Bayer wrote:
>
> so let me get this straight:
>
> 1. you have many schemas
>
>
> yes
>
>
>
> 2. you want to run autogenerate only once
>
>
> yes
>
>
> 3. you want your mirations genrated with None for schema
>
>
> yes
>
>
> 4. *HOWEVER*, when you run autogenerate, you are picking *one* (random?
> arbitrary?) schema to use as the target, is that right?
>
>
> yes one, but it won't be arbitrary, it will be a development schema
> generated from the latest version (prior to the change we want to
> autogenerate).
>
>
>
>
> As it stands, your autogenerate is not going to look in any schema except
> "public", assuming default PG search path, because you did not set
> "include_schemas=True".
>
>
> i had it set to True and it behaves the same. i just took it out based on
> your previous suggestion, but again, it behaves the same.
>
>
> If you truly want autogenerate to look at one schema and only that schema,
> and generate everything as non-schema qualified, then I would simply set
> the default schema in PG to that schema name using search_path:
>
> connection.execute("SET search_path TO my_schema")
>
> that way everything Alembic reflects will be from "my_schema" and it will
> see the schema as blank, and it should generate as such. you might need to
> disable the schema translate map when autogenerate runs but try it without
> doing that first.
>
>
> it's still generating the entire schema in the revision file.
>
> here's the updated section and it behaves the same with/without schema
> translate map:
>
> with connectable.connect() as connection:
>
> # create DB_SCHEMA if it doesn't exist
> connection.execute(f'create schema if not exists {DB_SCHEMA}')
>
> # map metadata schema (None) to DB_SCHEMA
> # connection =
> connection.execution_options(schema_translate_map={None:DB_SCHEMA})
>
> connection.execute(f"set search_path to {DB_SCHEMA}")
>
> # set alembic version table location in DB_SCHEMA
> context.configure(
> connection=connection,
> include_schemas=True,
> target_metadata=metadata,
> version_table_schema=DB_SCHEMA,
> )
>
> with context.begin_transaction():
> context.run_migrations()
>
>
>
>
> no, don't use "include_schemas". you want to run alembic in a mode where
> it has no idea there are other schemas to use. i think
> version_table_schema is OK here but don't use include_schemas. Also turn
> on INFO or DEBUG logging for the sqlalchemy logger to see exactly what
> tables it is reflecting.
>
same behavior, entire schema generated. i'll look at using INFO and DEBUG
tomorrow to get a better idea of what's going on, but using public as my
dev schema to autogenerate against works. i just have to exclude the
alembic tables.
again thanks for your help in understanding what i was trying to do.
brian
>
>
>
>
>
>
>
>
>
>
> On Thu, Mar 19, 2020, at 7:09 PM, Brian Hill wrote:
>
> Here's my env.py. Thanks for the help.
> Brian
>
> On Thursday, March 19, 2020 at 5:37:38 PM UTC-4, Mike Bayer wrote:
>
>
>
> On Thu, Mar 19, 2020, at 5:30 PM, Brian Hill wrote:
>
> Are there known issues with using autogenerate with multi-tenant
> (schema_translate_map)?
>
>
> it's complicated and not one-size-fits-all, if you consider that to be an
> issue
>
>
>
> My metadata doesn't have a schema and I my
> schema_translate_map={None:'my_schema'}.
>
> This works for migrations but when I use autogenerate the generated
> revision file is the full schema and not the diff.
>
> It's detecting the alembic version table in the tenant schema. If I run
> the autogenerate a second time with the new revision file it fails saying
> Taget database is not up to date.
>
>
> are you setting include_schemas=True in your environment? I'd probably
> not do this. Can't help much more without a complete and concise working
> example of your env.py, please remove all extraneous details.
>
>
>
> Thanks,
>
> Brian
>
>
> --
> 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-alembic+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy-alembic/5011240c-ba09-4423-9
Re: Autogenerate with Multi-Tenant
i think i just figured out that what i'm trying to do is unreasonable and
unecessary. alembic shouldn't have to know how to reverse map the schemas
and i don't need to do it that way. i can create my dev schema in public
and autogenerate against that, which maintains the same schema in metadata
and the db.
thanks,
brian
On Thursday, March 19, 2020 at 7:41:54 PM UTC-4, Brian Hill wrote:
>
>
>
> On Thursday, March 19, 2020 at 7:19:08 PM UTC-4, Mike Bayer wrote:
>>
>> so let me get this straight:
>>
>> 1. you have many schemas
>>
>>
> yes
>
>
>> 2. you want to run autogenerate only once
>>
>
> yes
>
>
>> 3. you want your mirations genrated with None for schema
>>
>
> yes
>
>
>> 4. *HOWEVER*, when you run autogenerate, you are picking *one* (random?
>> arbitrary?) schema to use as the target, is that right?
>>
>
> yes one, but it won't be arbitrary, it will be a development schema
> generated from the latest version (prior to the change we want to
> autogenerate).
>
>
>>
>> As it stands, your autogenerate is not going to look in any schema except
>> "public", assuming default PG search path, because you did not set
>> "include_schemas=True".
>>
>>
> i had it set to True and it behaves the same. i just took it out based on
> your previous suggestion, but again, it behaves the same.
>
> If you truly want autogenerate to look at one schema and only that schema,
>> and generate everything as non-schema qualified, then I would simply set
>> the default schema in PG to that schema name using search_path:
>>
>> connection.execute("SET search_path TO my_schema")
>>
>> that way everything Alembic reflects will be from "my_schema" and it will
>> see the schema as blank, and it should generate as such. you might need to
>> disable the schema translate map when autogenerate runs but try it without
>> doing that first.
>>
>>
> it's still generating the entire schema in the revision file.
>
> here's the updated section and it behaves the same with/without schema
> translate map:
>
> with connectable.connect() as connection:
>
> # create DB_SCHEMA if it doesn't exist
> connection.execute(f'create schema if not exists {DB_SCHEMA}')
>
> # map metadata schema (None) to DB_SCHEMA
> # connection =
> connection.execution_options(schema_translate_map={None:DB_SCHEMA})
>
> connection.execute(f"set search_path to {DB_SCHEMA}")
>
> # set alembic version table location in DB_SCHEMA
> context.configure(
> connection=connection,
> include_schemas=True,
> target_metadata=metadata,
> version_table_schema=DB_SCHEMA,
> )
>
> with context.begin_transaction():
> context.run_migrations()
>
>
>
>>
>>
>> On Thu, Mar 19, 2020, at 7:09 PM, Brian Hill wrote:
>>
>> Here's my env.py. Thanks for the help.
>> Brian
>>
>> On Thursday, March 19, 2020 at 5:37:38 PM UTC-4, Mike Bayer wrote:
>>
>>
>>
>> On Thu, Mar 19, 2020, at 5:30 PM, Brian Hill wrote:
>>
>> Are there known issues with using autogenerate with multi-tenant
>> (schema_translate_map)?
>>
>>
>> it's complicated and not one-size-fits-all, if you consider that to be an
>> issue
>>
>>
>>
>> My metadata doesn't have a schema and I my
>> schema_translate_map={None:'my_schema'}.
>>
>> This works for migrations but when I use autogenerate the generated
>> revision file is the full schema and not the diff.
>>
>> It's detecting the alembic version table in the tenant schema. If I run
>> the autogenerate a second time with the new revision file it fails saying
>> Taget database is not up to date.
>>
>>
>> are you setting include_schemas=True in your environment? I'd probably
>> not do this. Can't help much more without a complete and concise working
>> example of your env.py, please remove all extraneous details.
>>
>>
>>
>> Thanks,
>>
>> Brian
>>
>>
>> --
>> 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-alembic+unsubscr...@googlegroups.com.
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/sqlalchemy-alembic/5011240c-ba09-4423-9a05-0e6d2481dadf%40googlegroups.com
>>
>> <https://groups.google.com/d/msgid/sqlalchemy-alembic/5011240c-ba09-4423-9a05-0e6d24
Re: Autogenerate with Multi-Tenant
On Thursday, March 19, 2020 at 7:19:08 PM UTC-4, Mike Bayer wrote:
>
> so let me get this straight:
>
> 1. you have many schemas
>
>
yes
> 2. you want to run autogenerate only once
>
yes
> 3. you want your mirations genrated with None for schema
>
yes
> 4. *HOWEVER*, when you run autogenerate, you are picking *one* (random?
> arbitrary?) schema to use as the target, is that right?
>
yes one, but it won't be arbitrary, it will be a development schema
generated from the latest version (prior to the change we want to
autogenerate).
>
> As it stands, your autogenerate is not going to look in any schema except
> "public", assuming default PG search path, because you did not set
> "include_schemas=True".
>
>
i had it set to True and it behaves the same. i just took it out based on
your previous suggestion, but again, it behaves the same.
If you truly want autogenerate to look at one schema and only that schema,
> and generate everything as non-schema qualified, then I would simply set
> the default schema in PG to that schema name using search_path:
>
> connection.execute("SET search_path TO my_schema")
>
> that way everything Alembic reflects will be from "my_schema" and it will
> see the schema as blank, and it should generate as such. you might need to
> disable the schema translate map when autogenerate runs but try it without
> doing that first.
>
>
it's still generating the entire schema in the revision file.
here's the updated section and it behaves the same with/without schema
translate map:
with connectable.connect() as connection:
# create DB_SCHEMA if it doesn't exist
connection.execute(f'create schema if not exists {DB_SCHEMA}')
# map metadata schema (None) to DB_SCHEMA
# connection =
connection.execution_options(schema_translate_map={None:DB_SCHEMA})
connection.execute(f"set search_path to {DB_SCHEMA}")
# set alembic version table location in DB_SCHEMA
context.configure(
connection=connection,
include_schemas=True,
target_metadata=metadata,
version_table_schema=DB_SCHEMA,
)
with context.begin_transaction():
context.run_migrations()
>
>
> On Thu, Mar 19, 2020, at 7:09 PM, Brian Hill wrote:
>
> Here's my env.py. Thanks for the help.
> Brian
>
> On Thursday, March 19, 2020 at 5:37:38 PM UTC-4, Mike Bayer wrote:
>
>
>
> On Thu, Mar 19, 2020, at 5:30 PM, Brian Hill wrote:
>
> Are there known issues with using autogenerate with multi-tenant
> (schema_translate_map)?
>
>
> it's complicated and not one-size-fits-all, if you consider that to be an
> issue
>
>
>
> My metadata doesn't have a schema and I my
> schema_translate_map={None:'my_schema'}.
>
> This works for migrations but when I use autogenerate the generated
> revision file is the full schema and not the diff.
>
> It's detecting the alembic version table in the tenant schema. If I run
> the autogenerate a second time with the new revision file it fails saying
> Taget database is not up to date.
>
>
> are you setting include_schemas=True in your environment? I'd probably
> not do this. Can't help much more without a complete and concise working
> example of your env.py, please remove all extraneous details.
>
>
>
> Thanks,
>
> Brian
>
>
> --
> 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-alembic+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy-alembic/5011240c-ba09-4423-9a05-0e6d2481dadf%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy-alembic/5011240c-ba09-4423-9a05-0e6d2481dadf%40googlegroups.com?utm_medium=email_source=footer>
> .
>
>
>
> --
> 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-alembic+unsubscr...@googlegroups.com .
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy-alembic/fe6fddb4-25d6-4dd1-be3a-c1a174548bb4%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy-alembic/fe6fddb4-25d6-4dd1-be3a-c1a174548bb4%40googlegroups.com?utm_medium=email_source=footer>
> .
>
>
> *Attachments:*
>
>- env.py
>
>
>
--
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-alembic+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy-alembic/f6dcb50d-368c-4f8d-8e0b-af6f80d10084%40googlegroups.com.
Re: Autogenerate with Multi-Tenant
Here's my env.py. Thanks for the help.
Brian
On Thursday, March 19, 2020 at 5:37:38 PM UTC-4, Mike Bayer wrote:
>
>
>
> On Thu, Mar 19, 2020, at 5:30 PM, Brian Hill wrote:
>
> Are there known issues with using autogenerate with multi-tenant
> (schema_translate_map)?
>
>
> it's complicated and not one-size-fits-all, if you consider that to be an
> issue
>
>
>
> My metadata doesn't have a schema and I my
> schema_translate_map={None:'my_schema'}.
>
> This works for migrations but when I use autogenerate the generated
> revision file is the full schema and not the diff.
>
> It's detecting the alembic version table in the tenant schema. If I run
> the autogenerate a second time with the new revision file it fails saying
> Taget database is not up to date.
>
>
> are you setting include_schemas=True in your environment? I'd probably
> not do this. Can't help much more without a complete and concise working
> example of your env.py, please remove all extraneous details.
>
>
>
> Thanks,
>
> Brian
>
>
> --
> 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-alembic+unsubscr...@googlegroups.com .
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy-alembic/5011240c-ba09-4423-9a05-0e6d2481dadf%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy-alembic/5011240c-ba09-4423-9a05-0e6d2481dadf%40googlegroups.com?utm_medium=email_source=footer>
> .
>
>
>
--
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-alembic+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy-alembic/fe6fddb4-25d6-4dd1-be3a-c1a174548bb4%40googlegroups.com.
import os
from alembic import context
from sqlalchemy import engine_from_config, pool
# add parent/parent to path so we can import metadata
dir_path = os.path.dirname(os.path.realpath(__file__))
repo_path = os.path.dirname(dir_path)
os.sys.path.append(repo_path)
# required import when autogenerating revisions at the command line:
from yms_db.db.db_schema import metadata
# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config
# get db envs and set config sqlalchemy.url
DB_SCHEMA = os.environ['DB_SCHEMA']
PG_URL = 'postgresql://{DB_USER}:{DB_PASS}@{DB_HOST}/{DB_NAME}'.format(**os.environ)
config.set_main_option('sqlalchemy.url', PG_URL)
def run_migrations_offline():
"""Run migrations in 'offline' mode."""
url = config.get_main_option("sqlalchemy.url")
context.configure(
url=url, target_metadata=metadata, literal_binds=True)
with context.begin_transaction():
context.run_migrations()
def run_migrations_online():
"""Run migrations in 'online' mode."""
connectable = engine_from_config(
config.get_section(config.config_ini_section),
prefix='sqlalchemy.',
poolclass=pool.NullPool)
with connectable.connect() as connection:
# create DB_SCHEMA if it doesn't exist
connection.execute(f'create schema if not exists {DB_SCHEMA}')
# map metadata schema (None) to DB_SCHEMA
connection = connection.execution_options(schema_translate_map={None:DB_SCHEMA})
# set alembic version table location in DB_SCHEMA
context.configure(
connection=connection,
target_metadata=metadata,
version_table_schema=DB_SCHEMA,
)
with context.begin_transaction():
context.run_migrations()
if context.is_offline_mode():
run_migrations_offline()
else:
run_migrations_online()
Autogenerate with Multi-Tenant
Are there known issues with using autogenerate with multi-tenant
(schema_translate_map)?
My metadata doesn't have a schema and I my
schema_translate_map={None:'my_schema'}.
This works for migrations but when I use autogenerate the generated
revision file is the full schema and not the diff.
It's detecting the alembic version table in the tenant schema. If I run the
autogenerate a second time with the new revision file it fails saying Taget
database is not up to date.
Thanks,
Brian
--
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-alembic+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy-alembic/5011240c-ba09-4423-9a05-0e6d2481dadf%40googlegroups.com.
Re: Migrate alembic_version table from Public to tenant schema
That works. I check if the version table exists in the schema and set
version_table_schema if it does. Then after run_migrations if the version
table didn't exist in the schema I move it to the schema.
Thanks!
Brian
On Tuesday, February 18, 2020 at 1:30:54 PM UTC-5, Mike Bayer wrote:
>
>
>
> On Tue, Feb 18, 2020, at 1:17 PM, Brian Hill wrote:
>
> Is there a way to move the alembic_version table in the Public schema
> (postgres) to a specific schema (mult-tenant) as part of a migration?
>
>
> I highly doubt this is possible in the general case without the raw SQL in
> your env.py, because as the migration runs, the environment needs to
> update the table. if it moves schemas, then it won't be updating the
> correct table anymore.You would have to manipulate the internal state
> of the MigrationContext within the migration script so that this doesnt
> happen, however, the migration is also occurring inside of a transaction so
> it's very likely that you wouldn't actually be able to fully drop the old
> table in every case and there could be other side effects of this as well,
> and I cant guarantee this internal manipulation will always work for new
> Alembic releases.
>
>
>
> I want alembic to read the inital version from Public.alembic_version and
> then write the new upgraded version to the specific schema.alembic_version.
>
> The only way I can get it to work is executing raw sql after the
> migration, and this won't work for future migrations.
>
> with context.begin_transaction():
> context.run_migrations()
> context.execute(f'alter table Public.alembic_version set schema {DB_SCHEMA
> }')
>
>
>
> I think what you can do here is check for alembic_version in the old
> schema and in the new one and then move the table after the migrations but
> only if it needs to be moved. Basically have the migrations always
> run with the schema whereever it is to start with, then move it after the
> fact.
>
> you'd have to detect which schema to use before you configure the context,
> however, since you need to pass it the schema for the alembic_version table.
>
>
>
>
> Do I have to flatten/rebase my versions after this?
>
> Thanks,
>
> Brian
>
>
> --
> 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-alembic+unsubscr...@googlegroups.com .
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy-alembic/9ceb8556-e2c3-4631-84cf-8ba636c31a24%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy-alembic/9ceb8556-e2c3-4631-84cf-8ba636c31a24%40googlegroups.com?utm_medium=email_source=footer>
> .
>
>
>
--
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-alembic+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy-alembic/7a4e0542-0ae8-4148-a802-9c34c485144b%40googlegroups.com.
Migrate alembic_version table from Public to tenant schema
Is there a way to move the alembic_version table in the Public schema
(postgres) to a specific schema (mult-tenant) as part of a migration?
I want alembic to read the inital version from Public.alembic_version and
then write the new upgraded version to the specific schema.alembic_version.
The only way I can get it to work is executing raw sql after the migration,
and this won't work for future migrations.
with context.begin_transaction():
context.run_migrations()
context.execute(f'alter table Public.alembic_version set schema {DB_SCHEMA}'
)
Do I have to flatten/rebase my versions after this?
Thanks,
Brian
--
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-alembic+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy-alembic/9ceb8556-e2c3-4631-84cf-8ba636c31a24%40googlegroups.com.
Re: Using enums with schema_translate_map
Thanks for figuring this out.
On Monday, February 17, 2020 at 3:05:30 PM UTC-5, Mike Bayer wrote:
>
> Here is your SQL output, enum is created correctly:
>
> CREATE TYPE test_schema.enum1 AS ENUM ('One', 'Two')
>
>
> however table does not refer to the correct enum:
>
> CREATE TABLE test_schema.table1 (
> id SERIAL NOT NULL,
> type1 enum1,
> PRIMARY KEY (id)
> )
>
>
> this is SQLAlchemy bug
> https://github.com/sqlalchemy/sqlalchemy/issues/5158 will be fixed in
> 1.3.14
>
>
> On Mon, Feb 17, 2020, at 2:51 PM, Mike Bayer wrote:
>
> mm nope we have plenty of tests for this here:
>
>
> https://github.com/sqlalchemy/sqlalchemy/blob/master/test/dialect/postgresql/test_compiler.py#L223
>
>
>
> On Mon, Feb 17, 2020, at 2:49 PM, Mike Bayer wrote:
>
> it's possible also that PG Enum CREATE TYPE doesn't work with
> schema_translate_map, would need to evaluate that on the SQLAlchemy side.
>
> On Mon, Feb 17, 2020, at 2:45 PM, Mike Bayer wrote:
>
> schema_translate_map is not yet supported with all Alembic ops:
>
> https://github.com/sqlalchemy/alembic/issues/555
>
> you will need to fill in the "schema" parameter explicitly when you call
> upon op.create_table()
>
>
>
> On Mon, Feb 17, 2020, at 12:47 PM, Brian Hill wrote:
>
> I'm having trouble using enums in conjunction with schema_translate_map
> for postgres migrations.
>
> My model, single table, single enum.
>
> import enum
> from sqlalchemy import MetaData, Enum, Column, Integer
> from sqlalchemy.ext.declarative import declarative_base
>
>
> metadata = MetaData()
> Base = declarative_base(metadata=metadata)
>
>
>
>
> class Enum1(enum.Enum):
> One = 1
> Two = 2
>
>
>
>
> class Table1(Base):
> __tablename__ = 'table1'
> id = Column(Integer, primary_key=True)
> type1 = Column(Enum(Enum1))
>
> Version file.
>
>
> """initial revision
>
>
> Revision ID: 844dd0269c1b
> Revises:
> Create Date: 2020-02-17 12:23:31.125308
>
>
> """
> from alembic import op
> import sqlalchemy as sa
>
>
>
>
> # revision identifiers, used by Alembic.
> revision = '844dd0269c1b'
> down_revision = None
> branch_labels = None
> depends_on = None
>
>
>
>
> def upgrade():
> # ### commands auto generated by Alembic - please adjust! ###
> op.create_table('table1',
> sa.Column('id', sa.Integer(), nullable=False),
> sa.Column('type1', sa.Enum('One', 'Two', name='enum1'), nullable=True
> ),
> sa.PrimaryKeyConstraint('id')
> )
> # ### end Alembic commands ###
>
>
>
>
> def downgrade():
> # ### commands auto generated by Alembic - please adjust! ###
> op.drop_table('table1')
> # ### end Alembic commands ###
>
>
> Run migration funtion from env.py (creating foo schema):
>
>
>
> def run_migrations_online():
> connectable = engine_from_config(
> config.get_section(config.config_ini_section),
> prefix="sqlalchemy.",
> poolclass=pool.NullPool,
> )
>
>
> with connectable.connect() as connection:
>
>
> # use different schema
> connection.execute(f'create schema if not exists foo')
> connection = connection.execution_options(
> schema_translate_map={None: 'foo'}
> )
>
>
> context.configure(
> connection=connection,
> target_metadata=target_metadata,
> include_schema=True,
> )
>
>
> with context.begin_transaction():
> context.run_migrations()
>
>
> I get the following error when i run *alembic upgrade head*:
>
>
> sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedObject) type
> "enum1" does not exist
> LINE 4: type1 enum1,
>^
>
>
> [SQL:
> CREATE TABLE foo.table1 (
> id SERIAL NOT NULL,
> type1 enum1,
> PRIMARY KEY (id)
> )
>
>
> ]
> (Background on this error at: http://sqlalche.me/e/f405)
>
> It works when I comment out schema_translate_map.
>
> I feel like I'm missing something fundamental about using
> schema_translate_map for multi-tenant/schema.
>
>
> --
> 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-alembic+unsubscr...@googlegroups.com .
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy-alembic/f8513719-4dd0-4bb2-b76
Using enums with schema_translate_map
I'm having trouble using enums in conjunction with schema_translate_map for
postgres migrations.
My model, single table, single enum.
import enum
from sqlalchemy import MetaData, Enum, Column, Integer
from sqlalchemy.ext.declarative import declarative_base
metadata = MetaData()
Base = declarative_base(metadata=metadata)
class Enum1(enum.Enum):
One = 1
Two = 2
class Table1(Base):
__tablename__ = 'table1'
id = Column(Integer, primary_key=True)
type1 = Column(Enum(Enum1))
Version file.
"""initial revision
Revision ID: 844dd0269c1b
Revises:
Create Date: 2020-02-17 12:23:31.125308
"""
from alembic import op
import sqlalchemy as sa
# revision identifiers, used by Alembic.
revision = '844dd0269c1b'
down_revision = None
branch_labels = None
depends_on = None
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('table1',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('type1', sa.Enum('One', 'Two', name='enum1'), nullable=True),
sa.PrimaryKeyConstraint('id')
)
# ### end Alembic commands ###
def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.drop_table('table1')
# ### end Alembic commands ###
Run migration funtion from env.py (creating foo schema):
def run_migrations_online():
connectable = engine_from_config(
config.get_section(config.config_ini_section),
prefix="sqlalchemy.",
poolclass=pool.NullPool,
)
with connectable.connect() as connection:
# use different schema
connection.execute(f'create schema if not exists foo')
connection = connection.execution_options(
schema_translate_map={None: 'foo'}
)
context.configure(
connection=connection,
target_metadata=target_metadata,
include_schema=True,
)
with context.begin_transaction():
context.run_migrations()
I get the following error when i run *alembic upgrade head*:
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedObject) type
"enum1" does not exist
LINE 4: type1 enum1,
^
[SQL:
CREATE TABLE foo.table1 (
id SERIAL NOT NULL,
type1 enum1,
PRIMARY KEY (id)
)
]
(Background on this error at: http://sqlalche.me/e/f405)
It works when I comment out schema_translate_map.
I feel like I'm missing something fundamental about using
schema_translate_map for multi-tenant/schema.
--
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-alembic+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy-alembic/f8513719-4dd0-4bb2-b769-ff874e2017f2%40googlegroups.com.
