Is it possible to use multiple MySQL databases/schemas on the same connection?

[evgenybf]

In our project we use multiple MySQL schemas/databases. Now, the only way to access the data, is to create a separate 'app' record with their own connection:

class Model1(Model):
  ..
  class Meta:
     app = 'app_for_schema1'
     table = 'table1'

class Model2(Model):
  ..
  class Meta:
     app = 'app_for_schema2'
     table = 'table2'

Tried to find a workaround but without success:

1. If I specify schema name in the table field 'schema1.table1', then the full model's table name becomes schema1.schema1.table1
2. If I don't set db name in the connection string, then the first query fails with "no database selected" error (a lack of USE <schema/database> call.

Question: Is it possible to use multiple db schemas on the same connection?

What if Tortoise ORM started checking the table name and if it contains '.', it wouldn't add db name as a prefix to the table name? Or, alternatively, an extra Meta field could be added, e.g. schema which would override db name specified in the connection string.

[dstlny]

In our project we use multiple MySQL schemas/databases. Now, the only way to access the data, is to create a separate 'app' record with their own connection:

class Model1(Model):
  ..
  class Meta:
     app = 'app_for_schema1'
     table = 'table1'

class Model2(Model):
  ..
  class Meta:
     app = 'app_for_schema2'
     table = 'table2'

Tried to find a workaround but without success:

1. If I specify schema name in the table field 'schema1.table1', then the full model's table name becomes schema1.schema1.table1
2. If I don't set db name in the connection string, then the first query fails with "no database selected" error (a lack of USE <schema/database> call.

Question: Is it possible to use multiple db schemas on the same connection?

What if Tortoise ORM started checking the table name and if it contains '.', it wouldn't add db name as a prefix to the table name? Or, alternatively, an extra Meta field could be added, e.g. schema which would override db name specified in the connection string.

Could you not use Routers? Routers are built specifically for this purpose, pretty much.

https://tortoise-orm.readthedocs.io/en/latest/router.html

And just, in the Router conditionally choose which schema/database is needed depending on the class?

[evgenybf]

@dstlny hm.. But according to the documentation, routers allow the user to switch between connections, not between schemas

Define Router

Define a router is simple, you need just write a class that has db_for_read and db_for_write methods.

class Router:
    def db_for_read(self, model: Type[Model]):
        return "slave"

    def db_for_write(self, model: Type[Model]):
        return "master"

The two methods return a connection string defined in configuration.

...

config = {
    "connections": {"master": "sqlite:///tmp/test.db", "slave": "sqlite:///tmp/test.db"},
    "apps": {
        "models": {
            "models": ["__main__"],
            "default_connection": "master",
        }
    },
    "routers": ["path.Router"],
    "use_tz": False,
    "timezone": "UTC",
}

In MySQL you can execute these queries using a single connection:

USE schema1;
select * from table1;  -- schema1 is used
select * from schema1.table1;  
select * from schema2.table2;  

[evgenybf]

Tried to use a Router. It definitely returns a connection name:

Traceback (most recent call last):
  File "application.py", line 76, in init_tortoise
    await Stores.all().first()
  File "site-packages/tortoise/queryset.py", line 957, in __await__
    self._db = self._choose_db(self._select_for_update)  # type: ignore
  File "site-packages/tortoise/queryset.py", line 108, in _choose_db
    db = router.db_for_read(self.model)
  File "site-packages/tortoise/router.py", line 36, in db_for_read
    return self._db_route(model, "db_for_read")
  File "site-packages/tortoise/router.py", line 31, in _db_route
    return get_connection(self._router_func(model, action))
  File "site-packages/tortoise/transactions.py", line 19, in get_connection
    connection = current_transaction_map[connection_name].get()
KeyError: 'schema2'

[dstlny]

2. If I don't set db name in the connection string, then the first query fails with "no database selected" error (a lack of USE <schema/database> call.

Okay, so according to the below: https://tortoise-orm.readthedocs.io/en/latest/examples/basic.html?highlight=using_db#two-databases

It isn't possible to use Two different databases over the same connection. Instead, you need to have two different connections, using "app" in Meta - and a second database connection specified in the Tortoise config, along with a second "app" specified in the Tortoise config.

From what i can tell, you literally aren't able to use multiple DB's on a single connection... so you might be shit out of luck for now

[evgenybf]

@dstlny

From what i can tell, you literally aren't able to use multiple DB's on a single connection... so you might be shit out of luck for now

I'm talking about using multiple schemas, not multiple DB's. In MySQL schema is a synonym for database on the same instance. For example, we can call raw queries such as these even in Tortoise now:

conn = get_connection()
await conn.execute_query("select * from shema1.table1")
await conn.execute_query("select * from shema2.table2")

. In PostgreSQL they are different things - https://www.postgresql.org/docs/9.1/ddl-schemas.html And, actually, it's quite a common practice to use schemas like namespaces.

OK, I got your point. It's not possible for now. As a last attempt tried this:

TORTOISE_ORM = {
    "connections": {
        "default": {
            "engine": "tortoise.backends.mysql",
            "credentials": {
                "database": "",   # if database name is set, tortoise adds it as a prefix to each table name
                "host": config("DB_HOST"),
                "port": config("DB_PORT", cast=int, default=3306),
                "user": config("DB_USER"),
                "password": config("DB_PASSWORD"),
                "init_command": """\
USE schema1;
""",
            },
        },
    },
    "apps": {
        "models": {
            "models": ["models.models"],
            "default_connection": "default",
        },
    },
}

class Table2(Model):
  ...
  class Meta:
     table = 'schema2.table2'

Unfortunately, the following query fails

await Table2.all().first()

as in the generated query the name of the table wrapped in '`':

select * from `schema2.table2`;

Here is a workaround:

class Table2(Model):
  ...
  class Meta:
     table = 'schema2`.`table2'

but, it's a bit hackish.

Anyway, I still think that it would be great to be able to specify table's schema name in the Meta section such as:

class Table2(Model):
  ...
  class Meta:
     table = 'table2'
     schema = 'schema1'

If I'm not mistaken, without the workaround, the total number of connections to the database = number of schemas * connection Pool size * number server instances.

Unfortunately, Django models also neglect the issue, but there is a similar workaround: https://stackoverflow.com/a/49633224 Here are some explanations why schema support is not implemented in Django: https://code.djangoproject.com/ticket/6148#no2

[apan-git]

I have an idea, but I don't know whether it is feasible. The database is not linked when the project is started. The database can be connected and operated during access. In this way, different databases can be connected, but I don't know what consequences will be caused

[andreymal]

Not directly related, but I find it similar enough to mention here — I want to attach multiple files to a single SQLite connection:

conn = await aiosqlite.connect("main.sqlite3")
await conn.execute("ATTACH DATABASE ? AS schema2", ("schema2.sqlite3",))
# I can even use joins between two different database files
await conn.execute("SELECT * FROM main.table1 INNER JOIN schema2.table2 USING (id);")

I would like to have a generic approach that works for both MySQL and SQLite (and PostgreSQL?)