Tortoise-orm: Selecting schema

[je111ena]

To include in db config (or elsewhere) which existing schema should be used.
Currently, there is no option that enables specifying schema.

For example:

{
    'connections': {
        # Dict format for connection
        'default': {
            'engine': 'tortoise.backends.asyncpg',
            'credentials': {
                'host': 'localhost',
                'port': '5432',
                'user': 'tortoise',
                'password': 'qwerty123',
                'database': 'test',

            'schema': 'test_schema'

            }
        },
        # Using a DB_URL string
        'default': 'postgres://postgres:qwerty123@localhost:5432/events'
    },
    'apps': {
        'models': {
            'models': ['__main__'],
            # If no default_connection specified, defaults to 'default'
            'default_connection': 'default',
        }
    }
}

[grigi]

I looked at asyncpg, and it doesn't have a way to set the schema during a connection.

Reading up on PostgreSQL docs: https://www.postgresql.org/docs/11/ddl-schemas.html
It seems that your current schema can be selected like so:

SET search_path TO myschema;

Which would prefix all unqualified table names with myschema..
Which would probably be OK as a first version of schema support?

Is this what you are thinking of?

[je111ena]

Yes, that's what I thought.

[grigi]

Possibly implemented as a special parameter schema that can be specified as a query-string parameter. If it exists, we run Set search_path TO {schema}.
Now how does one test that this does what we expect? :thinking:

[je111ena]

Maybe by creating several schemes in the DB, and checking if the query executes over the right one?

[grigi]

Yeah, I don't see any other way. This does mean we will have to introduce DB-specific setup in the tests though. So far have only used DB's in default config for testing.

Possibly can do so just by manual testing for now, and add a TODO that we want to automate it at a later stage (When we have more such cases)

[grigi]

@je111ena Could you please see if this resolves your issue?

[grigi]

Released v0.13.1