@libredb/studio

v0.9.13

Published

2 months ago

0High
0Medium
0Low

cevheri

🚀 Live Test

Try LibreDB Studio instantly without installation!

| Test | URL | Credentials | |------|-----|-------------| | Public Test | app.libredb.org | [email protected] / LibreDB.2026 |

The test instance comes with a pre-configured PostgreSQL database via Seed Connections. No setup required!

Overview

LibreDB Studio is a lightweight, high-performance, and secure web-based SQL editor designed to bridge the gap between heavy desktop applications (like DataGrip/DBeaver) and minimal CLI tools. Built with a "Mobile-First, Professional-Always" philosophy, it empowers engineering teams to manage databases anywhere—from a 4K monitor to a mobile screen.

Why LibreDB Studio?

Zero Install: Run a professional SQL IDE in your browser or private network.
Multi-Platform: Native-like experience on both Web and Mobile browsers.
- AI-Native: Multi-model support (Gemini, OpenAI, or Local LLMs) for NL2SQL.
DevOps Ready: Optimized for Kubernetes orchestration and Docker environments.
Enterprise Grade: Built-in RBAC, SSO (OIDC), query auditing, and live health monitoring.

Key Features

Professional SQL IDE

Monaco Engine: Powered by the same core as VS Code.
Smart Autocomplete: Schema-aware suggestions for tables, columns, and SQL keywords.
Multi-Tab Workspace: Handle parallel tasks with independent execution states.
Visual EXPLAIN: Graphical execution plans to identify performance bottlenecks.
Interactive ER Diagrams: Visual schema graph with real foreign key edges, cardinality labels, MiniMap navigation, table search/filter, compact mode, and PNG/SVG export. Automatic hierarchical layout powered by ELK.js.
Schema Diff & Migration: Compare schema snapshots or cross-connection schemas side-by-side. Color-coded diff view (added/removed/modified) with automatic migration SQL generation for PostgreSQL, MySQL, SQLite, Oracle, and SQL Server.
Snapshot Timeline: Visual horizontal timeline of schema snapshots. Click any two points to instantly compare and track schema evolution over time.

Multi-Model AI Copilot

Universal LLM Support: Defaults to Gemini 2.5 Flash, but ready for OpenAI, Claude, or Local LLMs (Ollama/LM Studio).
NL2SQL: Generate complex queries from natural language with schema-aware context.
Query Safety Analysis: AI-powered pre-execution risk assessment for destructive queries (DELETE, DROP, TRUNCATE).
AI Query Explainer: EXPLAIN plans translated into plain language with optimization suggestions.
AI Query Autopilot: Automated slow query analysis with actionable index and rewrite recommendations.
Schema Awareness: AI understands your specific database structure for pinpoint accuracy.
Plug & Play: Works out of the box with zero complex configuration.

Pro Data Management

Universal Data Grid: Virtualized rendering (TanStack) for millions of rows.
Inline Editing: Double-click to update values directly in the grid.
Column Filtering: Per-column text filters on query results for instant data exploration.
Interactive Pivot Table: Client-side pivoting with 5 aggregation functions (COUNT, SUM, AVG, MIN, MAX) and SQL generation.
Expert Exporter: Instant CSV and JSON exports for reporting.

Advanced Data Visualization

8 Chart Types: Bar, Line, Pie, Area, Scatter, Histogram, Stacked Bar, and Stacked Area charts powered by Recharts.
Data Aggregation: Group-by with SUM, AVG, COUNT, MIN, MAX aggregation functions. Date grouping by hour, day, week, month, or year.
Chart Persistence: Save chart configurations and reload them instantly. Manage a library of saved charts.
Chart Dashboard: Grid view of all saved charts for at-a-glance data overview directly in the bottom panel.

Data Privacy & Masking

Automatic Sensitive Column Detection: 10 built-in patterns (email, phone, credit card, SSN, password, IP, date, financial, and more) with regex-based column name matching.
Configurable Masking Rules: Admin panel to add, edit, enable/disable masking patterns. Custom patterns with regex support.
RBAC-Enforced Masking: User role cannot toggle or reveal masked data. Admin role has full control with per-cell temporary reveal (10s auto-hide).
Export Protection: CSV, JSON, and SQL INSERT exports contain masked values when masking is active — no raw data leakage.
Full Coverage: Masking applied across desktop grid, mobile card view, mobile table view, row detail sheet, and clipboard operations.
Persistent Configuration: Masking settings stored in localStorage and survive page reloads.

Analyst & Developer Tools

AI Data Profiler: One-click table profiling with column statistics (null %, cardinality, min/max, sample values) and AI-powered narrative summaries.
ORM Code Generator: Generate TypeScript interfaces, Zod schemas, Prisma models, Go structs, Python dataclasses, and Java POJOs from live table schemas.
Test Data Generator: Schema-aware fake data generation with 30+ semantic column inferences (email, phone, name, address, etc.). Produces INSERT statements or MongoDB insertMany JSON.
Database Documentation: Auto-generated searchable data dictionary from live schema with AI-powered documentation and Markdown export.

Authentication & SSO

Dual Auth Modes: Local email/password login or OpenID Connect (OIDC) Single Sign-On — switchable via environment variable.
Vendor-Agnostic OIDC: Works with any OIDC-compliant provider — Auth0, Keycloak, Okta, Azure AD, Zitadel, Google, and more.
PKCE Security: Authorization Code Flow with Proof Key for Code Exchange (S256) for secure authentication.
Auto Role Mapping: Configurable claim-based role mapping with dot-notation for nested claims (e.g., realm_access.roles).
Provider Logout: Logout clears both local JWT session and identity provider session.

DBA Maintenance Toolkit (Admin Only)

Live Monitoring Dashboard: 7-tab monitoring with Overview, Performance, Queries, Sessions, Tables, Storage, and Connection Pool views.
Time-Series Trend Charts: Real-time metric trends (connections, cache hit ratio, buffer pool, deadlocks) with auto-refreshing ring buffer history.
Configurable Auto-Refresh: Polling intervals from 5s to 60s with play/pause control.
Threshold Alerting: Color-coded health indicators (healthy/warning/critical) for cache hit ratio, connection usage, deadlocks, and buffer pool utilization.
Connection Pool Stats: Live total/active/idle/waiting pool metrics with utilization progress bars.
One-Click Maintenance: Trigger VACUUM, ANALYZE, REINDEX, UPDATE STATISTICS, DBCC CHECKDB, and ALTER INDEX REBUILD per database engine.
Audit Trail: Full history of every query executed across the organization.

Supported Databases

All SQL databases share: schema explorer, ER diagrams, schema diff & migration, data masking, monitoring dashboard, and connection string import.

Tech Stack

Getting Started

Quick Start (Docker)

Run LibreDB Studio with a single command — no clone, no install, no build:

docker run -d \
  --name libredb-studio \
  -p 3000:3000 \
  -e [email protected] \
  -e ADMIN_PASSWORD=LibreDB.2026 \
  -e [email protected] \
  -e USER_PASSWORD=LibreDB.2026 \
  -e JWT_SECRET=change-me-to-a-random-32-char-string \
  ghcr.io/libredb/libredb-studio:latest

Open http://localhost:3000 and login with [email protected] / LibreDB.2026.

Tip: Add -e LLM_PROVIDER=gemini -e LLM_API_KEY=your_key -e LLM_MODEL=gemini-2.5-flash to enable AI features.

Prerequisites

Bun (Recommended) or Node.js 20+
A target database to query (PostgreSQL, MySQL, Oracle, SQL Server, SQLite, MongoDB, or Redis)

Quick Start (Local)

Clone & Install

git clone https://github.com/libredb/libredb-studio.git
cd libredb-studio
bun install

2. **Configure Environment**
   Create a `.env.local` file:
   ```env
   # Authentication (email/password)
   [email protected]
   ADMIN_PASSWORD=your_admin_password
   [email protected]
   USER_PASSWORD=your_user_password
   JWT_SECRET=your_32_character_random_string

   # Optional: OIDC Single Sign-On (Auth0, Keycloak, Okta, Azure AD, etc.)
   # NEXT_PUBLIC_AUTH_PROVIDER=oidc
   # OIDC_ISSUER=https://your-provider.com
   # OIDC_CLIENT_ID=your_client_id
   # OIDC_CLIENT_SECRET=your_client_secret

   # LLM Configuration
   LLM_PROVIDER=gemini # options: gemini, openai, ollama, custom
   LLM_API_KEY=your_api_key
   LLM_MODEL=gemini-2.5-flash
   LLM_API_URL=http://localhost:11434/v1 # optional for local LLMs (Ollama)
   ```

Launch
```
bun dev
```
Open http://localhost:3000

🗄️ Development Databases

Need databases to test with? We provide ready-to-use containers for all supported engines:

# Start all development databases (PostgreSQL, MySQL, MongoDB, SQL Server, Oracle)
docker compose -f database-compose.yml up -d

# Or start a specific database
docker compose -f database-compose.yml up -d postgres
docker compose -f database-compose.yml up -d mssql
docker compose -f database-compose.yml up -d oracle

# Start PostgreSQL with sample e-commerce data
docker compose -f docker/postgres.yml up -d

# Stop (keeps data)
docker compose -f database-compose.yml down

# Stop and remove all data
docker compose -f database-compose.yml down -v

Connection Details

| Database | Host | Port | User | Password | Database/Service | |----------|------|------|------|----------|-----------------| | PostgreSQL | localhost | 5432 | postgres | postgres | postgres | | MySQL | localhost | 3306 | root | root | mysql | | SQL Server | localhost | 1433 | sa | Password123! | master | | Oracle | localhost | 1521 | system | Password123! | freepdb1 | | MongoDB | localhost | 27017 | admin | admin | — |

PostgreSQL Sample Data

The docker/postgres.yml setup includes a pre-loaded e-commerce schema:

| Feature | Description | |---------|-------------| | PostgreSQL 17 | Latest Alpine image | | pg_stat_statements | Pre-enabled for query monitoring | | Sample Schema | E-commerce database (app schema) | | Sample Data | 25 customers, 30 products, 100 orders | | Views | Order summary, product sales, customer LTV |

Sample tables: app.customers, app.products, app.orders, app.order_items, app.product_reviews, app.categories, app.coupons, app.audit_log

This setup is ideal for testing the Monitoring Dashboard features with real pg_stat_statements data.

Testing

LibreDB Studio has a comprehensive test suite with 2,500+ unit/integration tests and 35 E2E tests across 6 layers, achieving 96%+ line coverage.

Quick Commands

# Run all tests (unit + API + integration + hooks + components)
bun run test

# Run by layer
bun run test:unit          # Pure function tests (800 cases)
bun run test:api           # API route handler tests (205 cases)
bun run test:integration   # Database provider tests (294 cases)
bun run test:hooks         # React hook tests (178 cases)
bun run test:components    # Component tests with mock isolation (194 cases)

# E2E tests (requires build)
bun run test:e2e           # Playwright browser tests (35 cases)

# Coverage report (lcov)
bun run test:coverage

Test Architecture

| Layer | Directory | Runner | Tests | What it covers | |-------|-----------|--------|-------|----------------| | Unit | tests/unit/ | bun:test | ~800 | Pure functions: SQL parser, connection strings, data masking, query limiter, schema diff, error classes, DB icons, showcase queries | | API | tests/api/ | bun:test | ~205 | Route handlers: auth, query, transaction, maintenance, AI endpoints, middleware | | Integration | tests/integration/ | bun:test | ~294 | Database providers: PG, MySQL, SQLite, MongoDB, Redis, Oracle, MSSQL| | Hooks | tests/hooks/ | bun:test | ~178 | React hooks: auth, connections, tabs, query execution, transactions, inline editing, AI chat, monitoring | | Components | tests/components/ | bun:test + happy-dom | ~194 | UI components: Studio, Sidebar, QueryEditor, ResultsGrid, Admin Dashboard, Charts, ERD | | E2E | e2e/ | Playwright | ~35 | Full browser flows: login, connections, query execution, tabs, export, admin |

Key Details

Test runner: bun:test (built-in, Jest-compatible API) with happy-dom for DOM environment
Component isolation: Component tests run in 6 isolated groups via tests/run-components.sh to prevent mock.module() cross-contamination
E2E: Playwright with Chromium, runs against a production build (bun run build && bun start)
CI: GitHub Actions runs lint + typecheck + build, unit/integration tests with coverage, E2E tests, and SonarCloud analysis
Coverage: bun test --coverage generates lcov reports for SonarCloud integration

Important: Always use bun run test instead of bare bun test. The test script handles proper isolation between test groups.

⚡ One-Click Deploy

Deploy your own instance of LibreDB Studio with a single click and a free account on Koyeb or Render:

Environment Variables

| Variable | Required | Description | |----------|----------|-------------| | ADMIN_EMAIL | ✅ | Admin email (default: [email protected]) | | ADMIN_PASSWORD | ✅ | Admin password | | USER_EMAIL | ✅ | User email (default: [email protected]) | | USER_PASSWORD | ✅ | User password | | JWT_SECRET | ✅ | Secret for JWT tokens (min 32 chars) | | NEXT_PUBLIC_AUTH_PROVIDER | ❌ | local (default) or oidc for SSO | | OIDC_ISSUER | ❌ | OIDC issuer URL (required when oidc) | | OIDC_CLIENT_ID | ❌ | OIDC client ID (required when oidc) | | OIDC_CLIENT_SECRET | ❌ | OIDC client secret (required when oidc) | | OIDC_ADMIN_ROLES | ❌ | Comma-separated admin role values (default: admin) | | OIDC_ROLE_CLAIM | ❌ | Claim path for role (e.g. realm_access.roles) | | OIDC_SCOPE | ❌ | OIDC scope (default: openid profile email) | | LLM_PROVIDER | ❌ | AI provider: gemini, openai, ollama | | LLM_API_KEY | ❌ | API key for AI features | | LLM_MODEL | ❌ | Model name (e.g., gemini-2.5-flash) | | STORAGE_PROVIDER | ❌ | Storage provider: local (default), sqlite, or postgres | | STORAGE_POSTGRES_URL | ❌ | PostgreSQL connection URL (required when STORAGE_PROVIDER=postgres) | | SEED_CONFIG_PATH | ❌ | Path to seed connections YAML config (see Seed Connections) | | SEED_CACHE_TTL_MS | ❌ | Seed config cache TTL in ms (default: 60000) |

Tip: Copy .env.example to .env.local for local development.

Deployment (DevOps)

Koyeb (Recommended for cloud deployment)

Fork this repository
Connect to Koyeb: app.koyeb.com → New → Blueprint
Select your forked repo and Koyeb will auto-detect koyeb.yaml
Set Environment Variables in Koyeb Dashboard:
Deploy! 🎉

Render (Recommended for cloud deployment)

LibreDB Studio includes a render.yaml Blueprint for one-click deployment:

Fork this repository
Connect to Render: dashboard.render.com → New → Blueprint
Select your forked repo and Render will auto-detect render.yaml
Set Environment Variables in Render Dashboard:
Deploy! 🎉

Docker Compose (Self-Hosted)

docker-compose up -d

Kubernetes (Helm Chart)

helm repo add libredb https://libredb.org/libredb-studio/
helm install libredb libredb/libredb-studio \
  --set secrets.jwtSecret=$(openssl rand -base64 32) \
  --set secrets.adminPassword=MyAdmin123 \
  --set secrets.userPassword=MyUser123

Or via OCI registry:

helm install libredb oci://ghcr.io/libredb/charts/libredb-studio --version 0.1.0 \
  --set secrets.jwtSecret=$(openssl rand -base64 32) \
  --set secrets.adminPassword=MyAdmin123 \
  --set secrets.userPassword=MyUser123

Features: PostgreSQL subchart, Ingress/TLS, HPA, PDB, NetworkPolicy, ExternalSecrets support. See charts/libredb-studio/README.md for full documentation.

Seed Connections (Pre-Configured Databases)

Pre-configure database connections via a YAML config file so users see them immediately after login. Ideal for Platform/SaaS deployments where admins provision databases for teams.

Features:

Role-based access control (admin, user, * wildcard)
Hybrid model: managed: true (read-only, admin-controlled) or managed: false (editable copy for user)
Credentials injected via ${ENV_VAR} syntax — never stored in config file
Hot-reload: config changes apply within 60s without restart
Works with Docker, docker-compose, and Kubernetes (Helm)

1. Create a config file (seed-connections.yaml):

version: "1"

defaults:
  managed: true
  environment: production

connections:
  - id: "prod-analytics"
    name: "Production Analytics"
    type: postgres
    host: analytics-db.internal
    port: 5432
    database: analytics
    user: "readonly_user"
    password: "${ANALYTICS_DB_PASSWORD}"
    roles: ["admin"]
    color: "#10B981"

  - id: "dev-sandbox"
    name: "Dev Sandbox"
    type: mysql
    host: dev-mysql.internal
    port: 3306
    database: sandbox
    user: "dev_user"
    password: "${DEV_DB_PASSWORD}"
    roles: ["*"]
    managed: false

2. Mount and configure:

docker run -v ./seed-connections.yaml:/app/config/seed-connections.yaml:ro \
  -e SEED_CONFIG_PATH=/app/config/seed-connections.yaml \
  -e ANALYTICS_DB_PASSWORD=secret \
  -e DEV_DB_PASSWORD=devsecret \
  ghcr.io/libredb/libredb-studio:latest

services:
  app:
    image: ghcr.io/libredb/libredb-studio:latest
    volumes:
      - ./seed-connections.yaml:/app/config/seed-connections.yaml:ro
    environment:
      SEED_CONFIG_PATH: /app/config/seed-connections.yaml
      ANALYTICS_DB_PASSWORD: ${ANALYTICS_DB_PASSWORD}
      DEV_DB_PASSWORD: ${DEV_DB_PASSWORD}

# values.yaml
seedConnections:
  enabled: true
  config:
    version: "1"
    connections:
      - id: "prod-analytics"
        name: "Production Analytics"
        type: postgres
        host: analytics-db.internal
        password: "${ANALYTICS_DB_PASSWORD}"
        roles: ["admin"]

# Credentials via K8s Secret:
extraEnvFrom:
  - secretRef:
      name: seed-db-credentials

Config Reference:

| Field | Required | Description | |-------|----------|-------------| | version | Yes | Must be "1" | | defaults | No | Default values merged into all connections | | connections[].id | Yes | Unique slug ([a-z0-9-]+, max 64 chars) | | connections[].name | Yes | Display name in UI | | connections[].type | Yes | postgres, mysql, sqlite, mongodb, redis, oracle, mssql | | connections[].roles | Yes | ["*"] (everyone), ["admin"], ["user"], or ["admin", "user"] | | connections[].managed | No | true = read-only (default), false = editable copy for user | | connections[].password | No | Use ${ENV_VAR} syntax for secrets | | connections[].environment | No | production, staging, development, local, other | | connections[].group | No | Group label in sidebar | | connections[].color | No | Hex color for badge (e.g., #10B981) |

Environment Variables:

| Variable | Default | Description | |----------|---------|-------------| | SEED_CONFIG_PATH | /app/config/seed-connections.yaml | Path to config file | | SEED_CACHE_TTL_MS | 60000 | Cache TTL in ms (hot-reload interval) |

Roadmap

[x] Phase 1: Monaco SQL IDE & Multi-Tab Support.
[x] Phase 2: Multi-Model AI (Gemini, OpenAI, Ollama, Custom) Integration.
[x] Phase 3: Pro Data Grid & Virtualization.
[x] Phase 4: Multi-Database Support (PostgreSQL, MySQL, SQLite, MongoDB, Redis).
[x] Phase 5: Interactive ER Diagrams (Visual Schema Graph).
[x] Phase 6: Enterprise Foundation (Connection Testing, SSL/TLS, SSH Tunnel, Transaction Control, Query Cancellation).
[x] Phase 7: AI Intelligence (NL2SQL, Query Safety Analysis, AI Index Advisor, Multi-Turn Chat, Query Autopilot).
[x] Phase 8: Analyst & Developer Tools (Data Profiler, Code Generator, Test Data Generator, Pivot Table, Column Filtering, Database Docs).
[x] Phase 9: Data Privacy (Automatic Sensitive Column Detection, Configurable Masking Patterns, RBAC-Enforced Masking, Export Protection).
[x] Phase 10: Advanced ERD (Real FK Edges, ELK.js Auto-Layout, MiniMap, PNG/SVG Export, Compact Mode, Table Search).
[x] Phase 11: Schema Diff & Migration (Snapshot Timeline, Cross-Connection Diff, Migration SQL Generation for PG/MySQL/SQLite).
[x] Phase 12: Advanced Charting (Scatter, Histogram, Stacked Charts, Aggregation, Date Grouping, Chart Save/Load, Chart Dashboard).
[x] Phase 13: Monitoring Enhancement (Time-Series Trends, Threshold Alerting, Connection Pool Stats, Configurable Polling).
[x] Phase 14: Enterprise Database Support (Oracle Database via oracledb Thin mode, Microsoft SQL Server via mssql/tedious).
[x] Phase 15: SSO Integration — Vendor-agnostic OIDC authentication (Auth0, Keycloak, Okta, Azure AD, Zitadel) with PKCE, role mapping, and provider logout.
[ ] Phase 16: DBA & Monitoring (Lock Dependency Graph, Vacuum Scheduler, Prometheus Export).
[ ] Phase 17: Enterprise Collaboration (User Identity, Shared Workspaces, SAML 2.0).

Community & Quality

| Resource | Description | |----------|-------------| | DeepWiki | AI-powered documentation — always up-to-date with the codebase | | SonarCloud | Code quality, security analysis, and technical debt tracking | | API Docs | Complete REST API reference | | OIDC Setup Guide | SSO configuration for Auth0, Keycloak, Okta, Azure AD, Zitadel | | OIDC Architecture | OIDC subsystem internals, security model, extension points | | Theming Guide | CSS theming, dark mode, and styling customization | | Architecture | System architecture and design patterns |

Contributing

We welcome contributions from the community! Whether it's a bug fix, a new feature, or documentation improvements:

Fork the Project.
Create your Feature Branch (git checkout -b feature/AmazingFeature).
Commit your Changes (git commit -m 'Add some AmazingFeature').
Push to the Branch (git push origin feature/AmazingFeature).
Open a Pull Request.

License

Distributed under the MIT License. See LICENSE for more information.