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 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 [email protected].
>>>>>>> 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&utm_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 [email protected].
>>>>> 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&utm_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 [email protected].
>>> To view this discussion on the web visit
>>> https://groups.google.com/d/msgid/sqlalchemy-alembic/f6dcb50d-368c-4f8d-8e0b-af6f80d10084%40googlegroups.com
>>>
>>> <https://groups.google.com/d/msgid/sqlalchemy-alembic/f6dcb50d-368c-4f8d-8e0b-af6f80d10084%40googlegroups.com?utm_medium=email&utm_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 [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy-alembic/ef68d038-2452-4b29-960d-923df0ae844b%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy-alembic/ef68d038-2452-4b29-960d-923df0ae844b%40googlegroups.com?utm_medium=email&utm_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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy-alembic/cb34834c-54b6-4680-98d1-08f87c4bae15%40www.fastmail.com.
