Creating schemas in env.py file - are there better approaches to doing this?

[bmuriithi1]

I'm working on a project which uses alembic for managing postgres database migrations on several schemas in a database, using SQLModel ORM models. The project already has a few migrations. But none of the previous migrations have commands to create schemas, which is a problem when trying to restore a database (eg. after building a new postgres docker container without any data in it).

Our current workaround is to create schemas manually using SQL commands and then apply the database migrations once the schemas are in place.

However I'm thinking that we could add code to our env.py file that loops through our SQLModel models, extracts schemas associated with the model, create schemas if they don't already exist in the database and then run the migration scripts as usual to create tables and perform other actions specified in the migration files.

But before doing this, I was wondering if anyone can think of a better solution?

[CaselIT]

Hi,

Can't you manually add a new migration that creates the schemas and manually set it to be the first one?

[bmuriithi1]

Sorry for the late response - but we ended up doing what you proposed. We were weighing up the pros and cons of manually creating a new first migration vs modifying the code in the env.py py to do this (as we had to modify it for other reasons anyway), and ended up going with the former route.

Happy to close this issue, but now we've got a separate problem we're tackling. I'll raise a separate issue for this as it's unrelated.