Self-Hosting Apache Superset with Docker: A Cost-Effective BI Solution for MySQL and PostgreSQL – ITFROMZERO

Table of Contents

The Reality: When Data Sits Idle in Your Database and the Boss Needs Reports ASAP

After running an e-commerce startup for over six months, I truly felt the pain of fragmented data. Transactional info lived in PostgreSQL, while user profiles were in MySQL. Every time the business team needed a growth report, the tech team had to stop coding to manually write SQL queries and export Excel files.

This cycle was incredibly time-consuming. As the demand for real-time data grew, paying over $2,000/year for Tableau or Power BI licenses was a luxury the startup couldn’t afford. I needed an open-source, self-hosted Business Intelligence (BI) platform that was secure and, more importantly, easy to scale.

Why Building Your Own Dashboard is a Nightmare

Many people think: “Just whip up a dashboard in React or Vue, call an API from the DB, and you’re done.” In reality, it’s not that simple. Once your data hits 10-20 million rows, querying the production DB directly will spike the CPU to 90%, causing system-wide lag.

Lack of flexibility: Even a simple chart color change or adding a column requires you to recode, rebuild, and redeploy from scratch.
Permissions nightmare: Manually handling logic like “User A can only see data from Branch X” is a massive undertaking.
Data aggregation: Joining data from two different servers (MySQL and Postgres) into a single chart is an extremely difficult task without a specialized tool.

A Quick Comparison of Popular Solutions

Before choosing Apache Superset, I tried out a few other names:

Metabase: Beautiful UI, easy to install. However, it shows limitations when you need deep customization or complex charts.
Grafana: Unbeatable for server monitoring and logs. But for deep business reporting, the interface is a bit “stiff” and hard for non-tech users.
Redash: Strong for raw SQL. The downside is the older UI, which isn’t as smooth as newer competitors.

Apache Superset: The King of Open Source BI

After six months of production use, I can confidently say Superset is the most balanced choice. It supports almost any database via SQLAlchemy. The drag-and-drop (No-code) interface allows Marketing teams to create their own charts without bothering developers. Most importantly, its Role-Based Access Control (RBAC) is granular down to the row level.

If you need to quickly process data from old reports, try the converter at toolcraft.app/en/tools/data/csv-to-json. This tool helps convert CSV to JSON right in your browser for safe testing imports.

Guide to Installing Apache Superset with Docker

Using Docker Compose is the fastest way to get a stable environment. It manages everything, including Redis (for caching) and PostgreSQL (to store Superset’s metadata).

Step 1: Environment Preparation

You should use a server with at least 4GB of RAM. If your dashboard has many heavy charts, 8GB is a safer bet.

# Clone the official repository
git clone --depth=1 https://github.com/apache/superset.git
cd superset

Step 2: Security Configuration

Never use default configurations in Production. You need to generate a unique Secret Key to encrypt sessions.

# Generate a random secret string
openssl rand -base64 42

Copy this key into the .env-non-dev file. Then, launch the system with the command:

docker-compose -f docker-compose-non-dev.yml up -d

Step 3: Setting Up the Admin Account

Once the containers are running stably, initialize the admin account and internal database:

# Create admin user
docker exec -it superset_app superset fab create-admin \
              --username admin \
              --firstname Superset \
              --lastname Admin \
              --email [email protected] \
              --password admin

# Update database and initialize permissions
docker exec -it superset_app superset db upgrade
docker exec -it superset_app superset init

Connecting to MySQL and PostgreSQL

Access port 8088 and navigate to Settings -> Database Connections.

For PostgreSQL, the connection string typically looks like: postgresql://user:pass@host:5432/db_name.

For MySQL, you should use the mysql+mysqlconnector driver to avoid encoding issues: mysql://user:pass@host:3306/db_name?charset=utf8mb4.

Note: If the DB is on the same server as Docker, use the IP 172.17.0.1 instead of localhost.

Pro-Tips for a Smooth-Running System

Don’t skip configuring Redis as a caching layer. In practice, a dashboard with 10 charts will force the DB to execute 10 simultaneous queries every time the page loads without cache. With Redis, dashboard load times drop from 5 seconds to under 1 second on the second visit.

Secondly, take advantage of Row Level Security (RLS). This feature solved my problem where the North region sales team only sees North region data, even though everyone uses the same dashboard.

Finally, limit resources for your Docker containers. Superset can be RAM-intensive when processing large Heatmap charts. Setting limits ensures other services on the same server don’t crash when someone runs a heavy query.

After deploying Superset, my tech team essentially escaped the “report-on-demand” grind. Data is now directly in the hands of those who need it, helping every business decision be based on real numbers rather than gut feeling.