Database Switching

[ghislaineguerin]

The specs related to issue #173 are ready for review. Please take a look at the attached document and follow the review guidelines.

Document Link

https://wiki.mathesar.org/en/design/specs/database-switching

@mathemancer @kgodey @pavish, your feedback is required.
The deadline for feedback is at the end of this week. Please plan your review so that at least one round of changes can be made and reviewed if needed.

[kgodey]

Overall, the design looks good. I like that the user is only working on one schema at a time, it simplifies things.

Feedback:

1. Do we want the user to have a human readable name for their databases? Currently we're just using the database connection key to identify a database, such as mathesar_tables, which is not very user friendly. If we want a human readable name, we need the user to be able to set and edit that name.
2. We won't have the ability for users to set up favorites until we actually have user accounts, which is currently scheduled for milestone 16.
3. We also won't have the ability to store the most recent tables a user accessed until we actually have user accounts.
4. How is the sidebar going to work if there are hundreds of tables? e.g. if there are 500 tables, and the user expands out the "Tables (500)" under "All", they'll need to scroll past 500 tables to get to "Views".
5. Similarly, we need to be able to handle hundreds of schemas if they exist. If there are 200 schemas, the dropdown will be too long.
6. I'm not sure what the element to the far left of the screen is.
   
7. re: default database being the one that was most recently open, we'll also need to defer that until we have user accounts.

[ghislaineguerin]

1. On database display name: If users add a 'connection' to a database, they can give a name to that connection. I also think that connections, even if the database is deleted, should remain until they remove them.
2. Favorites: I included favorites as they are part of the strategy to handle a large number of objects. I will keep them as a future consideration and come up with other ways to improve the table retrieval experience.
3. Recent tables: same as above, I will add notes for this, but focus on making table retrieval better.
4. Sidebar: It might make sense to split it into scrollable independent sections. I will create an example. Otherwise, the user can just collapse tables to get to views. However, this might impact the discoverability of views.
5. Dropdown and schema length: is should be a scrollable panel. I will add more schemas to demonstrate.
6. It's something I haven't decided on yet, a placeholder for the component we'd need if we wanted to minimize the sidebar and still show the current DB and other high-level navigation elements. I can also remove it.
7. If we can't have a default DB then I need to create a state where nothing is selected.

[kgodey]

@ghislaineguerin it's fine to keep favorites, recent tables, and default DB. We'll just store them only in the browser's local storage for now, and figure out backend storage later.

On #6, maybe just remove it for now? You can always add it in later if it's useful.

[pavish]

@ghislaineguerin For #2, showing Favorites is not a good idea at the moment. Because if a user marks something as a favorite, they will expect it on all their sessions, but we can only store it at the browser local storage at the moment. To start with, maybe we can only show Recent instead of Favorite. @kgodey Thoughts?

[kgodey]

That makes sense, just recent is fine.

[ghislaineguerin]

@kgodey @pavish I'm fine with keeping favorites for later. However, sorting by recent tables would be useful so that they are visible after creation.

[pavish]

@kgodey has covered the concerns I had about the sidebar and large number of schemas/tables. One minor point to add:

• There is an image on the database/schema selector. We do not have any configuration for setting images as such. We could show the first letter of the database connection key, similar to how Element shows it. But I do not see any value in it.

[kgodey]

I missed this one, I agree, I don't see any point in having an image.

[mathemancer]

I see a value if the elements to the left @kgodey asked about are mini DB-selectors. I actually like Element's way of letting me minimize the room-selection panel to where it only shows the icons (first letter) so that I can maximize the screen real-estate that's used for the actual conversation. Maximizing real estate for the actual table in every place where it's possible would be beneficial I think.

[ghislaineguerin]

The image, if we provide a simple way to assign icon/color to a connection, can help users to identify their different DB's much faster and allows us to minimize them in other contexts.
@mathemancer That was the intention for that bar, I still haven't designed it, but I do think using visuals to reference certain high-level objects is helpful.

[pavish]

This would be useful if we had a way to minimize the sidebar and only have the icons on the left. But based on our current UX, that's not possible. The icons only represent the databases, whereas we will need to be able to show the schemas & tables for selection. If we always have the sidebar, there's no point in having this additionally, it only takes more space.

If we were to provide an option to minimize the sidebar completely, we would have to update the UX to accommodate schemas and tables somewhere.

[ghislaineguerin]

@pavish I wasn't thinking we'd list the DB's but once we have user settings or apps it might be a nice place to keep navigation items. I will remove it for now to avoid confusion. Thanks

[mathemancer]

Overall, I think it looks good and makes sense. To provide another angle to what @kgodey and @pavish mentioned w.r.t. the problems with many schemata or tables, I think we should try to avoid the need to click through to the schema after choosing the DB in the case where there's only one schema. Having a single schema (public) is common in PostgreSQL, and there's no reason to make the user "choose" that from a list of 1.

[ghislaineguerin]

This makes sense. I will include an example.

[ghislaineguerin]

@kgodey @mathemancer @pavish The prototype is ready for re-review:
Additions

• Example for sidebar with overflowing tables
• Example for dropdown with overflowing schemas
• Example of database icon with color and letter rather than image

What to look for

• From the first page try navigating to 'int_film_database' and a warning should show up given the errors present in the current DB
• Try navigating to the database with lost connection to see the error message

[kgodey]

FYI the spec document has been moved to https://wiki.mathesar.org/en/design/specs/database-switching. I updated the link in the first post as well.

[kgodey]

@ghislaineguerin this looks good and addresses most of my concerns. Thoughts:

• I think the recent tables idea was good, I see it's gone now. as @pavish mentioned upthread, we could store the most recently used tables in the browser's local storage for now.
• I don't think our current solution for overflowing tables/schemas will work with hundreds of them. If there are hundreds, we don't want to even load their details into the frontend since it will involve a lot of data, a lot of rendering, and generally slow the page down. However, it probably doesn't make sense to spend time optimizing for hundreds of tables/schemas at this point, so my inclination is to make a GitHub issue to track the work and schedule it for the future (maybe post-MVP).
• I assume the minimized placeholder element does not need to be implemented, it would be great if you could add a note explaining that in the spec.

On an implementation note, @pavish, do you need to store color associated with a database in the backend? Should the backend generate the color?

[pavish]

@kgodey The color can be decided on the frontend based on a hash of the name. I'm sure there are libraries, or we could write our own. I do not think this should be stored on the backend, as these are specific to clients, and they can decide it however they want to.

Regarding overflowing tables/schemas we could consider it for post-MVP, but if we get a window, maybe we could try to accommodate it for the MVP itself.

[ghislaineguerin]

@kgodey I've included the recent section again. As for managing hundreds of tables, I do agree we need a separate issue. Thanks

[kgodey]

Looks good, thank you!

[ghislaineguerin]

@kgodey @pavish @mathemancer
Review notes have been added to the document https://wiki.mathesar.org/en/design/specs/database-switching
Listed here for reference:

Database Names

Mathesar will use the database connection key to identify databases within Mathesar. If we want a human-readable name in the future, we will need a way to capture that preference.

Recent Tables

Showing a list of the most recently updated tables can help users manage their work progress, and access recently created objects. As part of the implementation of this spec, we will store user-session information locally until we have the structure for user accounts in place.

Loading of a Large Number of Tables

Some concerns exist about fully loading a list of tables that exceeds what can be managed without disrupting the users' experience in terms of performance. However, we don't think that our current use case will be affected by this. A task has been added to optimize the loading of tables after the MVP is released.

Using Icons to Identify Databases

Using visual identifiers to differentiate between databases can help users quickly find and access their databases. As part of implementing this spec, we will add the necessary functionality to display and generate the database icons.