Deploying Mathesar

Introduction

Mathesar is an intuitive, open-source interface for PostgreSQL databases that combines the familiarity of spreadsheets with the power of relational databases. It allows users to view, edit, and share database data through a web interface without needing to write SQL queries.

Designed for both technical and non-technical users, Mathesar bridges the gap between raw database access and user-friendly data management. It respects your existing database schema while providing powerful tools for data exploration, filtering, and relationships.

Key highlights of Mathesar:

• Spreadsheet-Like Interface: Familiar grid view for viewing and editing data
• No SQL Required: Perform complex queries through the UI
• Relationship Explorer: Visualize and navigate foreign key relationships
• Data Types: Proper handling of PostgreSQL data types
• Filtering and Sorting: Powerful filtering with a visual query builder
• Data Import: Import CSV files directly into tables
• Access Control: Granular permissions for different users
• Views and Queries: Create saved views of filtered data
• Schema Management: Create and modify tables through the UI
• Non-Destructive: Works with existing databases without modifications
• Open Source: GPLv3 licensed with active development

This guide walks through deploying Mathesar on Klutch.sh using Docker, connecting to your PostgreSQL databases, and managing data effectively.

Why Deploy Mathesar on Klutch.sh

Deploying Mathesar on Klutch.sh provides several advantages:

Central Database Access: Access all your PostgreSQL databases from one interface.

Team Collaboration: Share database access with non-technical team members safely.

Always Available: Your database interface runs 24/7 without managing servers.

HTTPS by Default: Automatic SSL certificates for secure database access.

No Local Installation: Access your data from any device with a browser.

Persistent Configuration: Your connections and settings persist across deployments.

Prerequisites

Before deploying Mathesar on Klutch.sh, ensure you have:

• A Klutch.sh account
• A GitHub account with a repository for your Mathesar configuration
• A PostgreSQL database to connect to (existing or new)
• Basic familiarity with Docker and database concepts

Understanding Mathesar Architecture

Mathesar consists of several components:

Django Backend: Python-based API server handling database operations and business logic.

Svelte Frontend: Modern web interface providing the spreadsheet-like experience.

PostgreSQL (Internal): Mathesar uses its own PostgreSQL database for storing configuration, users, and metadata.

Direct Database Connection: Mathesar connects directly to your target PostgreSQL databases.

SQLAlchemy: Powers the database abstraction layer for multi-database support.

Preparing Your Repository

To deploy Mathesar on Klutch.sh, create a GitHub repository containing your Dockerfile.

Repository Structure

mathesar-deploy/
├── Dockerfile
└── .dockerignore

Creating the Dockerfile

Create a Dockerfile in the root of your repository:

FROM mathesar/mathesar-prod:latest

# Environment configuration
ENV DJANGO_SETTINGS_MODULE=config.settings.production
ENV ALLOWED_HOSTS=${ALLOWED_HOSTS}
ENV SECRET_KEY=${SECRET_KEY}

# Internal database for Mathesar configuration
ENV MATHESAR_DATABASES=${MATHESAR_DATABASES}
ENV DJANGO_DATABASE_URL=${DJANGO_DATABASE_URL}

# Expose the web interface port
EXPOSE 8000

Creating the .dockerignore File

Create a .dockerignore file:

.git
.github
*.md
README.md
LICENSE
.gitignore
*.log
.DS_Store
.env
__pycache__/
*.pyc

Environment Variables Reference

Variable	Required	Default	Description
SECRET_KEY	Yes	-	Django secret key (50+ random characters)
ALLOWED_HOSTS	Yes	-	Comma-separated allowed hostnames
DJANGO_DATABASE_URL	Yes	-	PostgreSQL URL for Mathesar’s internal database
MATHESAR_DATABASES	No	-	JSON config for connecting to user databases

Deploying Mathesar on Klutch.sh

Mathesar requires a PostgreSQL database for its own configuration.

Generate a Secret Key

Generate a secure secret key for Django:

openssl rand -base64 50

Save this key securely for the environment variables configuration.

Set Up PostgreSQL for Mathesar

Mathesar needs its own PostgreSQL database for configuration. Either:

• Use Klutch.sh’s managed PostgreSQL
• Deploy PostgreSQL alongside Mathesar
• Use an external PostgreSQL service

You’ll need a connection string like:

postgresql://user:password@host:5432/mathesar_internal

Push Your Repository to GitHub

Initialize your repository and push to GitHub:

git init
git add Dockerfile .dockerignore
git commit -m "Initial Mathesar deployment configuration"
git remote add origin https://github.com/yourusername/mathesar-deploy.git
git push -u origin main

Create a New Project on Klutch.sh

Navigate to the Klutch.sh dashboard and create a new project. Give it a descriptive name like “mathesar” or “database-ui”.

Create a New App

Within your project, create a new app. Connect your GitHub account if you haven’t already, then select the repository containing your Mathesar Dockerfile.

Configure HTTP Traffic

Mathesar serves its web interface over HTTP. In the deployment settings:

• Select HTTP as the traffic type
• Set the internal port to 8000

Set Environment Variables

In the environment variables section, add:

Variable	Value
SECRET_KEY	Your generated secret key
ALLOWED_HOSTS	your-app-name.klutch.sh
DJANGO_DATABASE_URL	Your PostgreSQL connection string

Deploy Your Application

Click Deploy to start the build process. Klutch.sh will:

• Detect your Dockerfile automatically
• Build the container image
• Start the Mathesar container
• Provision an HTTPS certificate

Run Initial Setup

After deployment, run database migrations if needed:

python manage.py migrate

Access Mathesar

Once deployment completes, access your instance at https://your-app-name.klutch.sh.

Initial Setup and Configuration

Creating Your Admin Account

When you first access Mathesar:

1. Navigate to the setup wizard
2. Create your administrator account
3. This account has full access to manage databases and users

Connecting Your First Database

1. Log in with your admin account
2. Click Add Database or Connect Database
3. Enter your PostgreSQL connection details:
   • Host
   • Port (default 5432)
   • Database name
   • Username
   • Password
4. Test the connection
5. Click Connect

Database Connection String Format

postgresql://username:password@hostname:port/database_name

Example:

postgresql://myuser:mypassword@db.example.com:5432/myapp

Using Mathesar

Exploring Data

Table View:

1. Select a database from the sidebar
2. Click on a table
3. View data in a spreadsheet-like grid
4. Click cells to edit values
5. Use the toolbar for actions

Relationships:

1. Click on a foreign key cell
2. View linked records in the related table
3. Navigate through relationships

Filtering and Sorting

Quick Filters:

1. Click the filter icon on any column
2. Select filter type (equals, contains, greater than, etc.)
3. Enter filter value
4. Combine multiple filters

Sorting:

1. Click column headers to sort
2. Shift+click for multi-column sort

Saved Views:

1. Configure filters and sort order
2. Click Save View
3. Name your view
4. Access it from the sidebar

Editing Data

Single Cell:

1. Click on a cell
2. Enter new value
3. Press Enter to save

Bulk Edit:

1. Select multiple rows
2. Use bulk actions menu
3. Apply changes to all selected

Adding Rows:

1. Scroll to bottom of table
2. Click the empty row
3. Enter values and save

Data Import

Import CSV files:

1. Click Import or the import icon
2. Select your CSV file
3. Map columns to table fields
4. Preview the import
5. Confirm to import

Schema Management

Creating Tables:

1. Click New Table
2. Define columns with types
3. Set primary key
4. Define relationships (foreign keys)
5. Create the table

Modifying Tables:

1. Access table settings
2. Add, rename, or delete columns
3. Modify data types
4. Changes are applied to the actual database

User Management

Adding Users

1. Go to Settings > Users
2. Click Add User
3. Enter email and set password
4. Assign role and permissions

Roles and Permissions

Role	Capabilities
Admin	Full access, user management, database connections
Editor	View and edit data in assigned databases
Viewer	View-only access to assigned databases

Database-Level Access

Control which databases each user can access:

1. Edit user permissions
2. Select allowed databases
3. Set read/write or read-only access

Advanced Features

Explorations

Create data explorations that span multiple tables:

1. Click New Exploration
2. Select starting table
3. Add related tables via joins
4. Select columns to include
5. Apply filters
6. Save and share

Views

Create database views through Mathesar:

1. Configure an exploration
2. Save as a view
3. View appears as a virtual table
4. Useful for frequently needed queries

API Access

Mathesar provides a REST API:

curl -X GET https://your-app-name.klutch.sh/api/v0/tables/ \
  -H "Authorization: Token YOUR_API_TOKEN"

Production Best Practices

Security Recommendations

• Secure Connections: Use SSL for database connections
• Strong Passwords: Enforce strong passwords for all users
• Principle of Least Privilege: Grant minimum necessary permissions
• Audit Access: Review user access regularly
• Network Security: Limit database access to Mathesar only

Database Best Practices

• Read Replicas: Point Mathesar at read replicas for heavy workloads
• Connection Limits: Be aware of connection pool limits
• Backup Strategy: Mathesar edits live data - ensure backups exist

Performance Tips

• Index Important Columns: Add indexes for frequently filtered columns
• Limit Result Sets: Use pagination for large tables
• Optimize Queries: Monitor query performance

Troubleshooting Common Issues

Cannot Connect to Database

Symptoms: Connection errors when adding database.

Solutions:

• Verify connection string format
• Check network connectivity
• Verify firewall allows connection
• Confirm PostgreSQL is accepting remote connections
• Check pg_hba.conf configuration

Slow Performance

Symptoms: Tables take long to load.

Solutions:

• Add indexes to frequently accessed columns
• Use pagination for large tables
• Check database server resources
• Optimize query patterns

Permission Errors

Symptoms: Cannot view or edit certain tables.

Solutions:

• Check Mathesar user permissions
• Verify PostgreSQL user has required grants
• Check table ownership

Changes Not Saving

Symptoms: Edits don’t persist.

Solutions:

• Check PostgreSQL user has write permissions
• Verify no constraint violations
• Check for triggers blocking changes
• Review error messages

Alternative Database Tools

If Mathesar doesn’t fit your needs, consider:

• Adminer: Lightweight database management
• pgAdmin: Full-featured PostgreSQL administration
• DBeaver: Universal database tool
• Metabase: BI and analytics focus

Additional Resources

• Mathesar Official Website
• Mathesar GitHub Repository
• Mathesar Documentation
• Mathesar Wiki
• Klutch.sh Deployments

Conclusion

Deploying Mathesar on Klutch.sh gives you a powerful, user-friendly interface for managing PostgreSQL databases. By combining spreadsheet familiarity with database power, Mathesar enables both technical and non-technical users to work with data effectively.

Whether you’re managing application data, analyzing business information, or collaborating with a team, Mathesar provides the tools to view, edit, and explore your databases without writing SQL. With Klutch.sh handling the hosting, your database interface is always available, secure, and accessible from anywhere.

Democratize data access in your organization with a self-hosted solution that respects your existing database structure while making it accessible to everyone who needs it.