SQLite Embedded Database Challenges in Containerized Environments

[senthalan]

Overview

Thunder currently uses SQLite as the default embedded database for simplicity and ease of local development. However, this design choice presents significant operational challenges when deploying to containerized environments (Docker, Kubernetes), particularly when trying to provide a seamless getting started experience for users who prefer containerized deployments.

Problem Statement

Core Issue: Database Persistence in Ephemeral Containers

Background: SQLite database files are created during the Docker image build process and embedded in the image. This works well for single, long-lived containers but breaks down when containers need to be ephemeral or when data needs to be shared between setup and runtime phases.

Why This Matters Now: We need to modify the setup process to:

1. Start the server without security during setup phase (to onboard initial admin role, user, and other bootstrap data)
2. Stop the server after setup completes
3. Start the server with security enabled for normal operation

This architectural requirement exposes fundamental limitations with SQLite in containerized environments:

Problem Scenario: Separate Setup and Runtime Containers

The Challenge: When using the recommended pattern of separate containers for setup (initialization) and runtime (server), the database created during setup is not accessible during runtime because:

• Container filesystems are ephemeral by default
• Each container has its own isolated filesystem
• Data created in the setup container is lost when it exits

Failed Approach:

# Setup creates database and bootstrap data
docker run --rm ghcr.io/asgardeo/thunder:latest ./setup.sh

# Runtime cannot access the database created in setup
docker run -d -p 8090:8090 ghcr.io/asgardeo/thunder:latest
# ❌ Server starts with empty/initial database - all setup data is lost

Complex Workaround:

# Step 1: Extract database files from image
docker run --rm \
  -v "$(pwd)/thunder-db:/tmp/backup" \
  ghcr.io/asgardeo/thunder:latest \
  sh -c "cp -r /opt/thunder/repository/database/* /tmp/backup/"

# Step 2: Run setup with populated volume
docker run -it --rm \
  -v "$(pwd)/thunder-db:/opt/thunder/repository/database" \
  ghcr.io/asgardeo/thunder:latest \
  ./setup.sh

# Step 3: Run server with same volume
docker run -d -p 8090:8090 \
  -v "$(pwd)/thunder-db:/opt/thunder/repository/database" \
  ghcr.io/asgardeo/thunder:latest

Why This is Problematic:

• Complexity: Three-step manual process that's difficult for users to discover and execute correctly
• Poor UX: Counterintuitive workflow that doesn't match Docker best practices
• Documentation burden: Hard to explain clearly without overwhelming new users
• Error-prone: Easy to miss steps or mount paths incorrectly, leading to data loss
• Non-standard: Doesn't follow the typical "docker run and go" experience users expect

This workaround works, but creates significant friction in the getting started experience.

Related Challenges in Kubernetes

Similar issues exist in Kubernetes deployments, which we currently handle with workarounds:

1. Read-Only Root Filesystem Incompatibility

Issue: Modern container security best practices recommend running containers with a read-only root filesystem (readOnlyRootFilesystem: true in Kubernetes SecurityContext). However, SQLite requires write access to its database file and associated journal files (WAL, SHM).

Current Workaround:

# Kubernetes values.yaml
securityContext:
  readOnlyRootFilesystem: true # This must be false if sqlite is used as the database

Impact:

• Forces containers to run with writable root filesystem, violating security best practices
• Increases attack surface for potential exploits
• Fails security compliance checks in enterprise environments

2. Single Pod Limitation

Issue: SQLite is a single-process database. Multiple pods cannot safely share the same SQLite database file, even with network file systems.

Current Workaround:

# Kubernetes values.yaml - WARNING comment
database:
  identity:
    type: "sqlite" # WARNING: Use single pod count if you are using sqlite.

Impact:

• Cannot horizontally scale Thunder deployments
• No high availability - single point of failure
• Cannot utilize Kubernetes features like:
  • HorizontalPodAutoscaler (HPA)
  • Rolling updates without downtime
  • Multi-region deployments

Proposed Solution

Strategic Direction

If Thunder aims to be a cloud-native identity platform, we need to address these containerization challenges head-on. The current SQLite-first approach optimizes for local ZIP-based distributions but creates friction for the majority of users who deploy via containers.

Recommended Approach: Database Strategy by Distribution

1. ZIP Distribution (Local Development & Testing)

• Keep SQLite as default - No changes needed
• Target audience: Developers testing locally, quick demos, CI/CD test environments
• Trade-offs are acceptable: Single instance, file-based, simple setup

2. Docker Image (Containerized Deployments)

• Switch to PostgreSQL as the default (or provide it as the primary documented path)
• Provide docker-compose.yml with PostgreSQL pre-configured for easy setup
• Benefits:
  • Natural data persistence via PostgreSQL volumes
  • Setup and runtime containers can share the same database
  • Follows Docker/container best practices
  • Eliminates complex volume mounting workarounds
  • Better aligns with production deployment patterns

3. Kubernetes/Helm (Production Cloud Deployments)

• Require PostgreSQL
• Simplifies Helm chart (no workarounds for SQLite)
• Enables horizontal scaling, high availability, and security compliance
• Better production readiness out of the box

Expected Impact

Improved User Experience:

• Docker users get a simple, standard workflow: docker-compose up
• No multi-step volume mounting required
• Setup and runtime share database naturally
• Documentation becomes clearer and shorter

Better Cloud-Native Positioning:

• Aligns with how modern cloud platforms work
• Removes artificial scaling limitations
• Enables production-ready deployments by default
• Meets security compliance requirements (read-only root FS, etc.)

Preserves Local Development Simplicity:

• ZIP distribution still uses SQLite - no external dependencies
• Developers can still get started quickly
• CI/CD pipelines can still use SQLite for fast, isolated tests

Trade-offs

Pros:

• ✅ Solves all containerization challenges
• ✅ Better production readiness
• ✅ Standard Docker/K8s patterns
• ✅ Enables horizontal scaling
• ✅ Simpler getting started for Docker users (with docker-compose)

Cons:

• ⚠️ Docker users need to run PostgreSQL (mitigated by docker-compose)
• ⚠️ Slightly more complex for "raw" docker run without compose
• ⚠️ When we support other databases, need to maintain multiple configurations or we have to pick one as the default