Req: concurrent use of multiple databases

[davidmcnabnz]

Is your feature request related to a problem? Please describe.

I need to be able to access multiple databases simultaneously, on on local machine, and 2 or more on other remote servers. But Tortoise's usage pattern suggests it's only meant for accessing one database within the one Python environment.

Describe the solution you'd like

Here's the OLD way - singleton Tortoise context:

# access spec for a single database
db1 = {'user': 'db1user', 'passwd': 'db1pass', 'host': 'db1.myhost.com', 'db': 'db1', 'models': 'db1.models'}
db1_url = f"postgres://{db1['user']}:{db1['pass']}@{db1['host']}:5432/{db1['db']}"

# set up a singleton Tortoise context for accessing this
await Tortoise.init(db_url=dbUrl, modules={'models': db1['models']})
await Tortoise.generate_schemas()

# other databases seem to require access outside of Tortoise
import asyncpg
db2_conn = await asyncpg.connect(
    host='db2.myhost.com',
    user=db2_user,
    database=db2_db,
    password=db2_password,
    )
...

NEW preferred way - multple distinct Tortoise contexts:

databases = {
    'db1': {
        'user': 'db1user',
        'passwd': 'db1pass',
        'host': 'db1.myhost.com',
        'db': 'db1',
        'models': 'db1.models'
    },
    'db2': {
        'user': 'db2user',
        'passwd': 'db2pass',
        'host': 'db2.myhost.com',
        'db': 'db2',
        'models': 'db2.models'
    },
   ...
}
contexts = {}
for name, db in databases.items:
    url = db['url'] = f"postgres://{db['user']}:{db['pass']}@{db['host']}:5432/{db['db']}"
    # create distinct Tortoise context for this db, and run the generate schemas
    ctx = contexts[name] = await Tortoise.init(db_url=dbUrl, modules={'models': db['models']})
    await ctx.generate_schemas()

This lets me then make arbitrary access to multiple databases at once:

# bad use case, but illustrates the concept
# populate db1 with someone living at an address on db2

# get the person's address on db2
address = await (db2.models.Address.filter(firstname="John", lastname="Doe").first())

# create small record on db1, noting simply the id of the address on db2
person = await db1.models.Foo.create(firstname="John", lastname="Doe", db2_address=address.id)

# note that no attempt is made (and none required) to automatically link between rows on db1 and db2

Describe alternatives you've considered In production use, I am limited to using Tortoise for the local PostgreSQL database, and low-level asyncpg pooled connections to PostgreSQL databases on other servers. But I would greatly prefer to be able to wrap all databases, local and remote, within Tortoise's excellent ORM layers.

Additional context Add any other context about the feature request here.

[abondar]

Hi

There is example how you can use two databases https://github.com/tortoise/tortoise-orm/blob/develop/examples/two_databases.py

Is not what you are looking for?

[abondar]

Please write if I misunderstood your request

[davidmcnabnz]

Thanks, but the example you've given uses .execute_query(), instead of being able to use the ORM methods such as Event.filter( ... ).

Can you recommend how to have one models module for a database on one server, and another models module for a database on a different server, and to access both through Tortoise ORM patterns?

[abondar]

@davidmcnabnz All filter(...) method and other method should work fine. You can try to copy this example locally, modify it and see that it works. Only real limitation here - is that, obviously, you can't cross-reference models from different databases

Explicit . execute_query calls in these example is only needed to verify that Event model is running on second connection, but failing on first connection