BI Tool: how to accept database credentials from users

Hi all,

I’m trying to create a business intelligence tool in Elixir. It’s a multi-tenant app, and users can submit their database credentials so that queries are executed on their database. I will also persist these credentials in my own database (by encrypting them)

This might seem a very n00b question - however, I haven’t been able to figure out how to go about doing it.

I did try to see how livebook does it, it seem to be using a library called kino_db which does work for a session. However, I’m not sure if these credentials are persisted and are even used in the background for offline data reporting.

Any pointers on how to go about doing it will be great. To start with I’m willing to restrict the scope to Postgres and BigQuery.

Thanks,
Abhinav

Hey @abhinavs can you elaborate as to where you are stuck? Is it the encryption and storage in postgres that has you stuck? Are you unclear on how to use these credentials to start new database connections? What are you stuck on?

Thanks for asking. I’m stuck on how to start new database connections.

I’m used to creating the usual Phoenix app in which database configuration settings are provided in the config file. I’m not sure how to create these db connections dynamically (that are user supplied) and maintain them.

I might be overthinking too, however, I have questions about how to manage these separate pools of DB connections, and security practices.

Gotcha! For starting repos dynamically you can do Replicas and dynamic repositories — Ecto v3.10.1. Depending on what you are doing with those, it may be easier to just use Postgrex directly Postgrex — Postgrex v0.17.1 at that point as well.

BigQuery doesn’t do connections in that same way at all. There you’d be making HTTP requests.

1 Like

Thanks a lot, Ben. Let me explore Postgrex.