Select postgres schema connection

[rorik302]

Please tell me, how can I switch to another postgres schema when doing queries or making migrations?
In sqlalchemy we can do

async def get_session(schema_name):
    async with async_session() as session:
        await session.execute(f"set search_path to {schema_name}")
        await session.commit()
        yield session

And then use session in api.
How to do that with edgy or saffier?

[tarsil]

Hello. Why would you use sessions with Saffier or Edgy? So, both use an underlying tool that manages the database connections and the sessions like the ones used in SQL Alchemy ORM are managed internally. Now, Saffier and Edgy use SQLAlchemy Core. But if you really want that, then you can use the connection object used in both Saffier and Edgy.

Lets use the following example.

from edgy import Database, Registry

database = Database("postgresql+asyncpg://postgres:postgres@localhost:5432/my_db")
models = Registry(database=database)

...

The objects managing the connections are the database and registry. The registry holds your model information tables and the databases the connections. So switching would be updating both of them and keep using the ORM as normal.

Edgy and Saffier use the underlying technology (https://databasez.tarsild.io/) to manage the DB connections automatically for you.

The SQLAlchemy Session is used in SQLAlchemy ORM and not he Core. As mentioned in the documentation, Edgy and Saffier use the SQLAlchemy Core.

I hope this helps :)

[rorik302]

Thank you for answer. I want to make multitenant app with schema per tenant and need to execute queries with specific schema

[tarsil]

I suspected that from the type of question. I did something similar when I was using Django as well. The thing is that depends of your approach. Multi tenancy you can have in 3 ways.

1. Shared schemas - The data of all users are shared within the same schema and filtered by common IDs or whatever that is unique to the platform. This is not so great for GDPR.
2. Shared database, different Schemas - The user's data is split by different schemas but live on the same database.
3. Different databases - The user's data or any data live on different databases.

I hope I was able to answer your questions? Also, thank you for choosing Edgy :)

[rorik302]

I prefer second option "Shared database, multiple schemas".
Maybe queryset could be like User.with_db_schema(schema_name).query.all(), where with_db_schema function switching to required database schema?

[tarsil]

I actually like the idea but It would be better to see a possible PR with your idea there. Do you have the chance to create one? That would be a good addition 👍🏼

[rorik302]

I'm afraid that I don't have enough experience and skills to do this, but I will try

[tarsil]

Give it a try. Add this idea also to the Discussion section of Edgy as an idea. This is a good one.

[tarsil]

Closing here as this was moved to discussion #14