How to Set Up a Database for Apache Superset Locally: Complete SQLite Setup Guide

The superset-sh/superset repository uses a SQLite file (local.db) as the default development database, which is automatically seeded from $HOME/.superset/local.db when you run the .superset/setup.sh script.

Setting up a local database for Apache Superset development requires understanding the automated seeding process built into the superset-sh/superset codebase. The project provides a streamlined setup pipeline that handles database initialization, permissions, and write-ahead log (WAL) management without manual configuration.

Understanding the Local Database Architecture

The Seed Database Location

The source of truth for your local development database resides outside the repository in your home directory. The setup script expects to find the seed database at:

$HOME/.superset/local.db

This location serves as the template that gets copied into your workspace. The repository also checks for associated WAL files (local.db-shm and local.db-wal) to ensure database consistency during the copy operation.

The Workspace Database Structure

After running the setup script, your local database lives in the workspace-specific directory:

superset-dev-data/local.db

This self-contained approach keeps all development data isolated per workspace. The generated .env file automatically sets the connection string:

DATABASE_URL=sqlite:///$(pwd)/superset-dev-data/local.db

Running the Automated Database Setup

The entire database initialization process is handled by the step_seed_local_db function inside .superset/lib/setup/steps.sh (lines 601-653). This function orchestrates the copy, permission setting, and WAL checkpoint operations.

Standard Setup (Preserves Existing Data)

To set up your local Superset database for the first time or maintain existing workspace data:

# From the repository root

./.superset/setup.sh

The script executes the following database-specific steps:

1. Detects the seed database at $HOME/.superset/local.db
2. Creates the superset-dev-data/ directory if missing
3. Copies the main database file and any WAL sidecars
4. Sets file permissions to 600 (read/write for owner only)
5. Runs PRAGMA wal_checkpoint(TRUNCATE) if sqlite3 CLI is available

Force Fresh Database Setup

If you need to reset your local database to the original seed state—useful when the schema changes or you want to discard local changes—use the force flag:

./.superset/setup.sh --force

Passing -f or --force sets FORCE_OVERWRITE_DATA=1, which triggers the removal of the existing superset-dev-data/ directory before the seeding process begins. This ensures a completely clean state without manual file deletion.

Manual Database Seeding (Alternative Approach)

If you prefer to set up the database without running the full setup pipeline, you can manually replicate the step_seed_local_db logic:

# Ensure source seed exists

if [[ ! -f "$HOME/.superset/local.db" ]]; then
  echo "Error: Seed database not found at $HOME/.superset/local.db"
  exit 1
fi

# Create target directory

mkdir -p superset-dev-data

# Copy database and WAL files

cp "$HOME/.superset/local.db" superset-dev-data/
for ext in -shm -wal; do
  if [[ -f "$HOME/.superset/local.db$ext" ]]; then
    cp "$HOME/.superset/local.db$ext" superset-dev-data/
  fi
done

# Set secure permissions

chmod 600 superset-dev-data/local.db*

# Optional: Checkpoint WAL to truncate log

if command -v sqlite3 &> /dev/null; then
  sqlite3 superset-dev-data/local.db "PRAGMA wal_checkpoint(TRUNCATE);"
fi

echo "Database seeded successfully to superset-dev-data/local.db"

After manual seeding, configure your environment:

export DATABASE_URL="sqlite:///$(pwd)/superset-dev-data/local.db"

Verifying Your Local Superset Database

To confirm the database is properly configured and accessible:

# Check database schema

sqlite3 superset-dev-data/local.db ".schema"

# Verify tables exist

sqlite3 superset-dev-data/local.db "SELECT name FROM sqlite_master WHERE type='table' LIMIT 10;"

# Test connection via Python (if Superset Python environment is active)

python -c "import sqlalchemy; engine = sqlalchemy.create_engine('sqlite:///superset-dev-data/local.db'); print(engine.connect().execute(sqlalchemy.text('SELECT 1')).fetchone())"

Successful execution confirms that your local database for Apache Superset is ready for development work.

Key Implementation Details

The database seeding logic in step_seed_local_db (.superset/lib/setup/steps.sh, lines 601-653) handles several critical edge cases:

• WAL File Preservation: SQLite uses write-ahead logging for concurrency. The script explicitly copies -shm and -wal sidecar files to prevent database corruption.
• Security Permissions: Files are chmodded to 600 immediately after copying to prevent unauthorized access to the development database.
• WAL Checkpointing: If the sqlite3 binary is available, the script runs PRAGMA wal_checkpoint(TRUNCATE) to merge WAL contents into the main database file and truncate the log, preventing lock contention when multiple processes access the database.
• Force Overwrite Logic: The --force flag triggers recursive removal of the existing superset-dev-data/ directory before seeding, ensuring a pristine state.

The setup entry point at .superset/setup.sh sources the common library (.superset/lib/common.sh), argument parser (.superset/lib/setup/args.sh), and step definitions before executing setup_main, which orchestrates the full pipeline including dependency checks, database seeding, and environment generation.

Summary

• Apache Superset local development uses a SQLite file (local.db) stored in superset-dev-data/ as the default database.
• The seed database originates from $HOME/.superset/local.db and is copied automatically by the step_seed_local_db function in .superset/lib/setup/steps.sh.
• Run ./.superset/setup.sh to execute the full setup pipeline, or use ./.superset/setup.sh --force to reset to a clean database state.
• Manual seeding requires copying the main DB file plus -shm and -wal sidecars, setting 600 permissions, and optionally running a WAL checkpoint.
• Verification involves checking file existence, querying the schema with sqlite3, or testing the connection via SQLAlchemy.

Frequently Asked Questions

What is the default database for local Superset development?

The superset-sh/superset repository uses SQLite as the default development database. Specifically, it expects a seed file at $HOME/.superset/local.db that gets copied to superset-dev-data/local.db in your workspace during the setup process.

Where does Superset store the local SQLite database?

After running the setup script, the active database resides in your workspace at superset-dev-data/local.db. This path is relative to the repository root and is referenced by the DATABASE_URL environment variable (e.g., sqlite:///superset-dev-data/local.db) generated by the setup pipeline.

How do I reset my local Superset database to a clean state?

To completely reset your local database, run the setup script with the force flag:

./.superset/setup.sh --force

This triggers the step_seed_local_db function to remove the existing superset-dev-data/ directory before copying a fresh seed from $HOME/.superset/local.db, effectively restoring the original schema and data.

Can I use a different database instead of SQLite for local development?

While the automated setup script (step_seed_local_db in .superset/lib/setup/steps.sh) is specifically designed for SQLite seeding, you can manually configure a different database by modifying the DATABASE_URL in your .env file. However, you would need to handle schema creation and migrations manually, as the seeding logic assumes SQLite file operations and WAL file handling.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}