Databases

Each installation of Mathesar can connect to multiple PostgreSQL databases. When you begin using Mathesar, your first step will likely be connecting to or creating a database. But what exactly is a database, and how does it differ from tools you might be more familiar with, like spreadsheets?

What is a database

If you’re using Mathesar as a spreadsheet alternative, you might be curious what makes a database different from a spreadsheet and why it matters. A database is a self-contained set of data stored within a rigid structure that maintains data integrity and allows for efficient data operations. There are many different kinds of databases, and Mathesar works specifically with PostgreSQL databases, so that’s what we’ll focus on here.

Within a database, you can have multiple tables — much like you might have multiple sheets within a spreadsheet. And within each table in your database, you’ll have rows and columns, similar to a spreadsheet. But while a spreadsheet gives you a blank canvas to freely enter any data into any cell you choose, a database is more structured. Rows and columns must be explicitly added before you can enter data, and each column must have a name and a data type. In a database, rows are sometimes called “records”.

One superpower of PostgreSQL is that cells from one table can reference records from another table. These references are called foreign key constraints, and Mathesar leverages them so you can model your data with relationships.

If you’ve ever used VLOOKUP in a spreadsheet, you’ll love using relationships in Mathesar!

Connecting a database

Click the Connect Database button from the home page of your Mathesar application and follow the prompts.

Once you’ve connected a database, you can navigate to Mathesar’s page for it where you can browse the database’s schemas and configure various settings for it.

Mathesar will remember the connection even after the application is shut down. Your Mathesar user will be added as a collaborator on the database (along with the PostgreSQL role you entered). The password you entered for that role will be stored in Mathesar’s internal database, encrypted using Mathesar’s SECRET_KEY.

Creating a new database

If you’re starting your database from scratch with Mathesar you can either:

• Use Mathesar to create a new database within Mathesar’s internal server and connect to it. This is a good option to get up and running quickly, but it might require more work later should you decide to set up periodic backups or connect other tools to the same database. Also, this option won’t be possible if Mathesar was installed without an internal server.

  OR

• Use another tool to create your database on an external server and then connect Mathesar to it. You can administer that external server yourself, or choose from a variety of hosted PostgreSQL solutions such as Amazon RDS, Google Cloud SQL, Supabase, and others.

Database Permissions

PostgreSQL databases use a careful system of privileges (colloquially referred to as “permissions”) to control what different users can do. Understanding these permissions is crucial for keeping your data secure while ensuring everyone has the access they need to do their work.

• Owner: In PostgreSQL, every database has a role set as its owner.

  Only the owner can:

  • Drop the database
  • Manage database-level privileges
  • Transfer ownership

• Granted Privileges: Additionally, the following privileges on one database may be granted to specific roles in PostgreSQL:

  • CONNECT- Allows the role to connect to the database.
  • CREATE- Allows the role to create new schemas within the database.
  • TEMPORARY- Allows the role to create temporary tables within the database.

See the PostgreSQL docs for more info.

To manage the owner and granted privileges for a database, navigate to the database page in Mathesar and click on the Database Permissions button at the top right.

See also

To manage the access that Mathesar users have to a database, go to Database Settings > Collaborators. See Access Control for more information.

Disconnecting a database

1. From the Mathesar home page, click on your database to its database page.
2. At the top right, click on the dropdown menu, and select “Disconnect Database”.

Disconnecting a database will not delete the database. It will still be accessible outside Mathesar, and you can reconnect it in the future.

However, disconnecting your database will delete the Mathesar-specific metadata associated with objects in the database. This includes saved explorations, customized column display options, and customized record summary templates.

Dropping a database

If you want to entirely remove all the data in your database by dropping the database from the PostgreSQL server, you’ll need to do so outside of Mathesar via PostgreSQL itself.

We plan to add support for dropping databases in the future. If this is a feature you would like, please comment on this issue to let us know.

Mathesar’s internal database

Mathesar keeps as much of your data as possible inside your connected PostgreSQL database, structured consistently with the way it appears in the Mathesar interface.

Separate from your connected PostgreSQL database, Mathesar also maintains an internal database to store configuration relevant to the Mathesar application itself. While Mathesar does not allow you to work directly with this internal database, you might be interested to understand the distinction between it and your connected database. Below is a comparison of the data stored in each:

Data in your connected database	Data in Mathesar's internal database
The schemas and tables you see from within Mathesar The rows, columns, and cells within those tables Relationships between those tables Roles and their privileges	Users Database connection credentials, including saved role passwords Collaborators Metadata Saved Explorations