Mathesar: DB Client, or more integrated?

[mathemancer]

I think we should try to pin down whether Mathesar is a DB client, or more integrated into the underlying user DB than that.

As a DB client:

• Always assume other clients might be simultaneously connected to the same DB, working with the same tables in parallel.
• Other Mathesar clients might be simultaneously connected.
• Multiple users connected to the same webapp might be an over-complication; couldn't it instead be multiple Mathesar webapp installations? (this would be analogous to the way multiple users would connect to a DB using a more traditional client).
• Mathesar wouldn't add special mathesar_id primary keys to tables (A DB client wouldn't need to do that to function; it would just use the tables as they are).
• The webapp should be kept as thin as possible. Focus should be on real-time reflection of the state of the DB (which is assumed to be changing ad hoc).

A more integrated Mathesar:

• Would consider other clients connected to "its" database to be abnormal. It could be handled, but some pain / slowness / inconsistency would be acceptable.
• If many users want to use Mathesar on a given DB, they'd all have accounts on the same installation of the webapp.
• The webapp should cache info about the DB state for efficiency. It should maintain its own version of that state, and update that version of the state only when some inconsistency is encountered.

I'm inclined towards the thinner Mathesar, since we've already identified the use case of connecting to previously-existing DBs. It seems that this would imply an assumption that Mathesar is "just another client". I fear that trying to do both is trying to have Mathesar be Jack of too many trades. Alternatively, for a more integrated Mathesar, we could provide an import functionality that would simply give the user the ability to actually import data from other DBs into Mathesar; since we intend to do this anyway via pg_dump and similar, maybe it's not necessary to have Mathesar work with a DB that's already up, running, and full of data.

[pavish]

In my opinion, I think it should be more integrated.

The primary use case for Mathesar is to help users who are not technical, and the chances of them using a pre-existing db is low. Also, not having multiple users access one Mathesar client doesn't seem optimal for most of our use cases.

The ideal scenario would be that Mathesar behaves similar to monitoring tools (eg., Grafana), one instance connecting to one or more DBs, and can have multiple users, provides a single pane of access to their data.

[mathemancer]

I think we need to think about what we mean by "not technical" still. My current model is that a user will have the skills to host something, and may have used a DB before, but will lack technical DBA skills (or that they have access to such a person). So not "my aunt Mae", but more like a startup with a single web dev but no DBA, but who wants to maintain customer records.

[mathemancer]

Bouncing off of what @pavish said: I don't think it's completely unreasonable to have both multiple clients hitting the DB, and multiple users per client, but we'd need to make sure to have a very clear line between what the webapp "owns", and what the DB does. This will make consistency challenging (I think), since we'd want to maintain enough state in the webapp for users to collaborate without needing to call the DB, but other clients might modify the DB in the meantime.

[kgodey]

This seems like a false dichotomy to me. Mathesar should function as a DB client, but it shouldn't just be a DB client.

It seems like you're framing the issue as DB client vs. integrated to answer a few questions, which I'll address individually.

Should we assume other clients are going to be modifying the tables that Mathesar is also modifying?
Yes. Mathesar is meant to make it easy for people to edit, restructure, and collaborate on data, so even if there are no other clients using the database, other users may be modifying or restructuring data.

Should Mathesar edit tables to add mathesar_id, or edit databases in other ways in order to be able to work with them?
No, we shouldn't edit the database in order to make Mathesar functional, because we don't need to. I don't think we're actually using mathesar_id for anything. We may want to edit the database for additional features (for example, if our types schema isn't installed, users will not be able to use an email type), but it shouldn't be a prerequisite.

Should the web application duplicate data for efficiency?
We should only have one source of truth for all data:

• The database is the source of truth for schemas, tables, and records.
• The web application is the source of truth for the REST API.

We need a unique ID to represent each database object in the REST API, and this is owned by the web application. This involves some duplication and resyncing of data (table and schema names), but it's not for efficiency or caching, it's because the database shouldn't need to know anything about the API or its concerns.

---

There's also some discussion about non-technical vs. technical users. Our goal is to be usable by both technical and non-technical users. When we release our MVP, the person setting up an installation of Mathesar will need to be technical, but the users using Mathesar once it is set up will not need to be technical, and could be "my aunt Mae".

[mathemancer]

Updating rows will be kind of an issue if a user doesn't have any indexes on their table. It may even be that they attach to a DB with non-unique rows. Not that we can't come up with something, but it'll be a bit tricky. Maybe we could give users a way to add an index (or mark a column as an indexed column) before editing?

If mathesar_id shouldn't be added to preexisting tables, shouldn't we show it in the UI so that users aren't confused about when it exists and when it doesn't?

[kgodey]

I think it's fine to disable editing entirely if there are no primary key columns, and ask the user to add one if they want to edit rows.

Yes, we should show all columns in the UI. My previous conception of Mathesar involved hiding or simplifying some database concepts for non-technical users (hence the "collection" and "application" names that are now abandoned). I think that's going to be more confusing in the long run, though, and we should use database terminology and show all columns. That would make us a better DB client for technical users and also help non-technical users build database skills.