What I want to know is if we can switch (programmatically) the database that a particular user sees, while still making use of anvil.tables.app_tables orm?
I know I can do it via something like psychopg2 with manual sql, but I really do like anvil’s table access approach.
For context: We are self-hosting an anvil application and we currently have an instance per customer. Basically, it is a VM per customer with an instance of the anvil application and its own database.
With your setup, you have databases – and Anvil instances – that are physically distinct, and physically isolated from each other. One might do this for load-balancing, security, or both.
Are you looking for a way for one running Anvil instance to access the database of another running Anvil instance, as if that database was its own? I’d be very surprised if there was an easy, built-in way.
If you’re looking for an easy way to move a user (individual) from one customer (company) to another, without them manually logging out of one and into the other, that sounds more feasible. It’s not built in, but there are ways to automate log-outs and log-ins so that it appears somewhat seamless.
If you clarified the distinction between “user” and “customer”, that would probably help us understand what you want to achieve.
From the user’s perspective, what would the series of steps look like?
I should add, Anvil offers other approaches, that could give you some or all of what you want.
In Anvil, the original way to handle this uses the following approach:
Because Views are constructed at run-time, and can be filtered on any column(s) you choose, you could use the same mechanism to give users the appearance of switching databases, programmatically, at run-time.
The second way this effect is handled is by giving a single App multiple Deployment Environments. Each run-time Environment can have its own branch of the App, and its own Database, within the same Anvil+PG instance. You’d typically assign one Environment to each user or user-organization.
If an Environment needs to switch databases, that can be done manually. I don’t know of any programmatic way to do it.
And I think you’d want to have every user of that Environment logged off, at the time of the switch. But then that probably applies to any such switch, programmatic or otherwise.
I am pretty sure this is not possible with the out of the box open-source anvil-runtime.
It would be possible to write new portable classes, then an extension of the app tables class that overwrites the behavior of some bits used to serialize and then transmit to an anvil uplink on the other ‘app machine’.
You would have to know how to write in python pretty well and adapt the anvil code.
…and then it would also be pretty slow probably, serializing and deserializing all of your data.
So… yes it would probably actually be easier if you do it with:
…if all you are really looking for is having your data returned in a list of dicts that is totally doable with psycopg2 by modifying the behavior of the cursor.
It is also going to be magnitudes faster because the underlying code for the database connection in psycopg2 is mostly written in C .
OK, thanks everyone, I was not clear in my original post (although I think I have my answer anyway)
What I was looking for is a single app instance, but with a database per customer. I would look up what company a user was from and then switch to that company’s database for the remainder of the user session.
I do know that I could use a column that ID’d the company and always check that for all tables and all calls), but I wanted the additional safety of physically separate data.
Thanks.
The programmatical way to do it with anvil is using client views so it is impossible for others to see the mixed together data.
All other ways involve not using anvil afaik.
You would save yourself from bugs in the queries that return values from other companies, but you wouldn’t save yourself from bugs that query the wrong tables.
The rule of thumb is that having two tables (or two databases) with same structure in the same app is often a bad idea.
Adding the company ID and maybe using views as @p.colbert suggested is usually the right way.