multiple databases with dependency libs containing tables

[scott2b]

I am curious to know what established patterns there are for configuring multiple databases in piccolo. In particular, I want to be able to configure a distinct database that will be primarily used by tables in an app library.

So, thinking about this from a hard-coded perspective, I am looking for a structure like this:

Inside myapp -- a pip-installed dependency that is a piccolo app

DB = SomeSupportedEngine(....)

class AppTable(Table, db=DB):
    ....

Inside myproject -- The main project which uses myapp and has its own tables as well

DB = SomeSupportedEngine(....)
MYAPP_DB = myapp.whatever.DB

class ProjTableOne(Table):
    # this uses the discovered piccolo conf db, aka DB above

class ProjTableTwo(Table, db=MYAPP_DB):
    # While defined here, the data actually goes into the app specific db and maybe even has foreign keys to AppTable

I think I could do something like this using the db parameter and a hard-coded approach to the depency database like above. In this sense, the app database is treated as if it were "embedded" in the application. This approach is not absolutely terrible for e.g. just a simple sqlite db that does not have to be super flexible or robust.

But the hardcoded approach is less than ideal for a number of obvious reasons, so I am wondering about other approaches. Perhaps subclassing? I could do something like this maybe?

In the project:

from myapp.whatever.tables import AppTable as _AppTable

MYAPP_DB = # ... now I define this in the project instead of the installed app

class AppTable(_AppTable, db=MYAPP_DB)
    ....

Would this even work? I have not tried subclassing piccolo tables at all, and I'm somewhat wary that this might cause all kinds of unforeseen problems. Maybe there is a better approach to what I am trying to do?

[scott2b]

The subclassing approach will not work in my case because I have foreign keys in the app library .. this would result in different versions of the table being used from the library than from the project itself.

Here is what I have come up with, for what it's worth: I now have a concept of using an "embedded" database in the app. There is an env variable configured flag that determines whether or not to embed the database, which is hard-coded to use sqlite. In the main project then I can toggle this env variable to set it either to use the "embedded" db or the primary project db. I can also import the db reference from the library and use that as a way to put individual project tables into the embedded app db.

It's not perfect and not ideal for general multi-db situations, but for my current use case I think this is going to work.

[dantownsend]

Yes, I think that using an environment variable is the best approach.

[dantownsend]

Subclassing isn't ideal in this situation if you have foreign keys.