Support multiple databases & schemas?

[bgentry]

A user asked for our UI to be capable of letting a single UI instance serve multiple databases so they don't require a single UI deployment per database. I've run into the frustration of having to have many different UI deployments at my day job, so I agree this would be nice.

We might also be able to tackle multiple schemas using the same mechanism here. Imagine if the UI is able to somehow take a config of DB names to URLs. There could be a switcher in the menu bar to allow you to change the "context" of which one you're working on.

The big question I have is how this would be reflected in URLs for either the frontend or the API backend. Does the DB name become a required part of the path or a query param in either case? On the frontend, you could keep it as a local storage setting (like switching from light to dark mode) and keep it out of the URL, but this would have some downsides (URLs not as easily shareable between users). Instead of that, we may want to structure our URLs to include the DB name as a prefix.

cc @brandur

[brandur]

Yeah good question. IMO we should really try get everything in the URL as nicely as possible because it's going to be so much more convenient for things like bookmarks or sending links to people.

One possibility is that when having the user configure the UI, we ask them to name each database URL they want to use like:

{
    "prod": "postgres://.../prod",
    "staging": "postgres://.../staging"
}

We'd encourage the names to be URL friendly and they could go in the URL's path.

Schemas should be in there too. They might be easier in that what we could do is maybe not put anything in the path if it's the default schema, but otherwise include them, and since they're just a simple name like my_schema, they're inherently more friendly for inclusion in a path already.