[mathesar-developers] Opinion on the UX to go with for Database connection string

[Mukesh Murali]

Primarily concerns @Kriti Godey

When reviewing the Database connection PR, pavish found the UX for the database connection to be confusing. We met on a sync call and still couldn’t come to an agreement and would like @Kriti Godey opinion on it from the product perspective.

• The Database Connection page contains all configured databases in a list, some of them are locked (non-editable and cannot be opened/viewed) and some are editable.
• The database connection string can be added
  • Using the UI
  • Using environment variable (MATHESAR_DATABASES)
• The database connections added using the UI are editable.
• The ones configured through the env variable are locked. There is no reason specified in the UI and the user cannot remove the database connection string using the UI. This is confusing and is a problem

Additional Context

• The user won’t have to worry about adding a database connection using the env variable in most cases as they would be using the UI. The connection string added using the env variable is meant to be used by the setup scripts to add the default database credentials which gets created when using Docker or a PaaS.
• Primarily affects Mathesar installations using Docker, Docker Compose, and any PaaS installation in the future.
• Behaviour of Docker and PaaS.
  • Docker - If the user does not set the credentials for the internal database using the DJANGO_DATABASE_URL env variable, we would start the in-built database server which comes with the Docker image. Further, if the user does not set the MATHESAR_DATABASES, we assume the user wants to quickly test out Mathesar and we create a new user database in the in-built database server and set the value of MATHESAR_DATABASES to it.
  • PaaS (planned for future release) - When the user clicks on the “One Click Deploy”, we automatically create a new managed Postgres database server which most PaaS have. This database server will be used for the internal server. Additionally, we create a new user database in that server and set its credentials to MATHESAR_DATABASES, so that the user can instantly try Mathesar out without having to fiddle with setting up a new database server or connecting to an existing database.

Suggested Solutions

Solution A:

• Solution
  • We would rename MATHESAR_DATABASES to INSTALLATION_DB_CONNECTION_STRING (to make the naming less confusing) which will be used for storing the default internal DB credential string upon first start, and any updates after that can only be done via the UI.
• Pros
  • There won’t be any difference between the default database string added using the env variable and the other database connection added using the UI
• Cons
  • Env variables persist across restarts and using it for one-off install is misleading for the user.
  • We cannot differentiate if the user deleted the default database using the UI vs a first-time start. We would end up adding the database connection string again during restarts as long as the environment variable exists. We would need some additional logic to avoid this behavior.

Solution B: (Similar to the current implementation)

• Solution -
  • We use a different env variable called DEFAULT_DB, which will contain one database, and the user cannot modify that specific DB in the UI.
  • We will show a message for this default DB in the UI, which cannot be edited. They can edit it via the env variable.
  • We would modify the UI to indicate this better.
• Pros
  • Standard behavior when using environment variables
• Cons
  • The user can get confused as there are two types of database connection strings.

Solution C:

• Solution
  • Combination of Solution C and Solution B.
  • The user will be able to edit the default DB database credentials using the UI
  • The credentials in the UI will take priority over the environment variable. So if the user makes changes to the database credentials using the UI, the environment variables will still contain the old credentials.
• Pros
  • Since the default db credentials are locked for deletion, we avoid the primary problem with Solution A
    • The user will still be able to edit the connection string just like other database connection string
• Cons
  • The user won’t be able to delete the default database connection string
  • It will be really confusing if the user makes changes to the connection string and ends up looking at the outdated connection string in the environment variable.

The meeting ended with slightly leaning towards going with either Solution A or Solution B, but we would like to get another confirmation on this.

[Sean Colsen]

During the call, I advocated for an entirely separate solution (let’s call it “Solution D”). Perhaps I didn’t articulate it well enough on the call, so I’ll attempt to describe it better here:

• A fresh installation of Mathesar begins with zero database connections. Users need to create one explicitly — but we make it super easy for them to do it!

  Yes, this is an extra step during installation, but I actually think that being explicit is helpful in this case. This is a “teach them to fish” moment. It makes the Mathesar functionality clearer and better aligns the installation process with our stated product direction of connecting to pre-existing databases. I think that if we create the DB for them, that’s a bit too much “magic” to help users understand how Mathesar works and what it’s capable of.

• Database connection details are stored only in the internal database — never within the .env file.

• Connections are identified by auto-incremented numerical IDs instead of by name, allowing the user to modify the name of the connection.

• Any time Mathesar sees that no connections are configured, it prompts the user to create a new connection.

• The “New Database Connection” form also allows the user to create databases in order to connect to them.

• In a later release, we could utilize this explicit “create database” step to offer the user a choice to load sample data into that database.
  

• For Docker installations (which each include a PostgreSQL server), our documentation provides clear steps for the administrator to determine the credentials (i.e. host, port, PostgreSQL username, and password).

  If we could find a way to pre-fill this data into the form, that would be great too, but I don’t understand enough about Docker to understand how the service layer (or even a person, for that matter) would go about determining the connection credentials for the PostgreSQL server within our Docker container. From a UX perspective, I think it would be nice to have a button in the UI (e.g. “Fill connection credentials from PostgreSQL sever within Docker”) that would fill this data in. If it’s not technically feasible to create such a button, then the UI could link to the docs to show the user how to determine the connection credentials for their Docker-based Postgres server.

• For non-Docker-based installations (e.g. Debian), the “New Database Connection” form would link to a different docs page, explaining how the user should determine the PostgreSQL connection credentials. We could use a environment variable for this, i.e. informing the service layer which type of installation is used so that the front end can load the “New Database Connection” form with contextually appropriate help content and docs hyperlinks.

​

[Brent Moran]

I generally like option D the best. It's clearer and more consistent if:

• There's only one place to add/remove/modify DB connections, and
• We don't magically start with one based on some one-off configuration somewhere.
  

[Kriti Godey]

I agree that we should go with Option D for the reasons Brent articulated. I think there are ways in which we can make creating a "default" database quick and easy using the UI, and we should do that instead of using environment variables.

We need to implement this before we release 0.1.4. I don't think there's much value in releasing 0.1.4 until we've ensured that we have a smooth and intuitive installation experience (including docs).

Sean, I'd like you to coordinate the work needed to pull this together (including product and design decisions). Let me know if you have any questions.

[Mukesh Murali]

Thanks for adding your solution. I missed it as we didn’t shortlist it as a solution and it wasn’t recorded in the meeting notes, sorry about that.

I am all in with not having the option to set the internal database credentials using environment variables but I am concerned about not having a default database for the users who want to try out Mathesar quickly. These are the people who are curious about Mathesar and don’t want to use the demo server as they are concerned with sharing their dataset. Their attention span is short and we need to please them as soon as possible. Making someone who is not convinced to set up a database or give an alpha software access to their database on the first start is a pretty big ask. “Why should I learn to fish when I haven’t even tasted it”.

Additionally, if I am trying to advertise Mathesar to someone I would like a method to set up a working instance and have them try out their dataset ASAP instead of having to fiddle with setting up a Postgres server and connecting to it (Docker makes connecting to localhost database even more complicated) on their machine

Also, only the Docker and PaaS installation methods targeted at such users will have a default database.

For Docker installations (which each include a PostgreSQL server), our documentation provides clear steps for the administrator to determine the credentials (i.e. host, port, PostgreSQL username, and password). If we could find a way to pre-fill this data into the form, that would be great too, but I don’t understand enough about Docker to understand how the service layer (or even a person, for that matter) would go about determining the connection credentials for the PostgreSQL server within our Docker container. From a UX perspective, I think it would be nice to have a button in the UI (e.g. “Fill connection credentials from PostgreSQL sever within Docker”) that would fill this data in. If it’s not technically feasible to create such a button, then the UI could link to the docs to show the user how to determine the connection credentials for their Docker-based Postgres server.

I don’t think having the app talk with the environment it is running on is a good idea as it could expose it to a lot of security issues.

Moreover, there are a lot of edge cases

• In the case of PaaS, the database won’t be running on the same server. So figuring out the credentials would be different for each platform
• Some PaaS automatically change the password, so hardcoding the password on the first run won’t fit in such cases

I would like to propose an alternative solution

Copy connection credentials from the internal database

This is similar to what Sean proposed in his “Fill connection credentials from PostgreSQL sever within Docker”. It would look something like

• The database connection UI will check if the internal database is connected to a Postgres server by looking at the internal database credentials
• We would then ask the user if they would like to store their data in the same database server in which Mathesar metadata is stored
• The DB connection form will be filled with the internal database connection string

[Kriti Godey]

This sounds fine to me from a high-level, but I'll defer to Brent and Sean to get into the details and make sure it fits within our general UX and user goals.