Multiple simultaneous database connections

Queries and Resources
,
1

Hi,

I would like my application to connect to several different postgresql databases at the same time. Or at least to be able to switch between databases in a way that is invisible to the user. I've tried setting retoolContext.environment in JS:

retoolContext.environment = "mydatabase";

but although console.log() says it's changed, the change doesn't seem to have any lasting effect.

The broader picture here is that our application will have one database per client. Each database will be identical in terms of table structure, etc.

I tried to choose the database pre-login. I created a new environment. I cannot find the screen in Configure multiple environments, but added the new connection string to my new environment. I then attempted to use a URL parameter, as suggested in Configure URL query parameters, but that did not result in connecting to the new database - it still connected to the one in the Resource field of the QUERY parameter.

Any ideas, please?

Thank You and Best,

Chris

2

Hey @chrismaden!

It's possible to reference the values in {{ currentUser }} and {{ retoolContext }} from your resource setup page. For instance, you could define a database to access based on {{ retoolContext.environment }} and then pass {{ currentUser.email }} as the database user.

Apart from that, there's an experimental feature that allows you to dynamically specify which resource a query uses. With that, you could create a separate resource for each database and switch between them in the app based on user information.

Finally, you might also explore using Row Level Security with a single database as an alternative way to segment accessible information between different users.

Do any of those three options seem like they might work?