LotusWeb

Try the demo here

Lotus

Hex VersionHexDocsCI Status

A beautiful, lightweight web interface for Lotus - the SQL query runner and storage library for Elixir applications.

LotusWeb provides a free, easy-to-setup BI dashboard that you can mount directly in your Phoenix application. Perfect for technical and non-technical users who need to run SQL queries, create reports, and explore data without the complexity of a full BI solution.

🚧 While LotusWeb already has a solid feature set and its API surface is stabilizing, it’s still evolving. We’ll make a best effort to announce breaking changes, but we can’t guarantee backwards compatibility yet — especially as Lotus broadens its Source abstraction to support more than SQL-backed data sources.

Production Use and UUID Caveats

LotusWeb is generally safe to use in production. It relies on Lotus’s read-only execution and session safety. We are running it in Accomplish successfully in production today, notwithanding being affected by the limitation described below.

If your application uses UUIDs or mixed ID formats, there are current limitations that affect how variables work in the LotusWeb UI:

We plan to improve this with column‑aware binding (Lotus will use schema metadata to deterministically cast/shape values). Once available, LotusWeb will take advantage of it automatically.

Why LotusWeb?

🎯 Lightweight Alternative to Complex BI Tools

🔐 Secure by Default

🏗️ Built for Phoenix

⚡ Developer & User Friendly

Current Features

What's planned?

Installation

Add lotus_web to your list of dependencies in mix.exs:

def deps do
  [
    {:lotus_web, "~> 0.5.0"}
  ]
end

Requirements

Version Compatibility Matrix

LotusWeb Version Required Lotus Version Notes
0.4.x 0.9.0+ Latest stable release
0.3.x 0.6.0+ Legacy version

The dependency constraint in mix.exs automatically ensures compatible versions are installed together.

Quick Setup

1. Configure Lotus (if not already done)

Add Lotus configuration to your config/config.exs:

config :lotus,
  ecto_repo: MyApp.Repo,        # Where Lotus stores queries
  default_repo: "main",         # Default repository for query execution
  data_repos: %{                # Where queries execute
    "main" => MyApp.Repo,
    "analytics" => MyApp.AnalyticsRepo
  }

2. Add Lotus migration (if not already done)

mix ecto.gen.migration create_lotus_tables

Add the Lotus migration to your generated migration file:

defmodule MyApp.Repo.Migrations.CreateLotusTables do
  use Ecto.Migration

  def up do
    Lotus.Migrations.up()
  end

  def down do
    Lotus.Migrations.down()
  end
end

Run the migration:

mix ecto.migrate

3. Configure Caching (Optional but Recommended)

Lotus supports result caching to improve query performance. To enable caching:

Add Lotus to your supervision tree:

# lib/my_app/application.ex
def start(_type, _args) do
  children = [
    MyApp.Repo,
    # Add Lotus for caching support
    Lotus,
    MyAppWeb.Endpoint
  ]

  opts = [strategy: :one_for_one, name: MyApp.Supervisor]
  Supervisor.start_link(children, opts)
end

Configure cache settings:

# config/config.exs
config :lotus,
  cache: [
    adapter: Lotus.Cache.ETS,
    namespace: "my_app_cache",
    profiles: %{
      results: [ttl_ms: 60_000],      # Cache query results for 1 minute
      schema: [ttl_ms: 3_600_000],    # Cache schemas for 1 hour
      options: [ttl_ms: 300_000]      # Cache dropdown options for 5 minutes
    }
  ]

Note: Without adding Lotus to your supervision tree, all query functions will work normally but caching will be disabled.

4. Mount LotusWeb in your router

defmodule MyAppWeb.Router do
  use MyAppWeb, :router
  import Lotus.Web.Router

  # ... other routes

  scope "/", MyAppWeb do
    pipe_through [:browser, :require_authenticated_user] # 🔒 Important: Add authentication!

    lotus_dashboard "/lotus"
  end
end

⚠️ Security Notice: Always mount LotusWeb behind authentication in production. The dashboard provides powerful query capabilities and should only be accessible to authorized users.

Usage

Once mounted, visit /lotus in your application to access the dashboard:

Query Editor

Schema Explorer

Query Management

Configuration Options

Basic Configuration

# Mount with default options
lotus_dashboard "/lotus"

Custom Route Name

# Use a custom route name (default is :lotus_dashboard)
lotus_dashboard "/admin/queries", as: :admin_queries

WebSocket Configuration

# Customize WebSocket settings
lotus_dashboard "/lotus",
  socket_path: "/live",
  transport: "websocket"

Additional Mount Callbacks

# Add authentication or other mount logic
lotus_dashboard "/lotus",
  on_mount: [MyAppWeb.RequireAdmin, MyAppWeb.LogDashboardAccess]

Security Best Practices

1. Always Require Authentication

# ✅ Good - requires authentication
scope "/", MyAppWeb do
  pipe_through [:browser, :require_authenticated_user]
  lotus_dashboard "/lotus"
end

# ❌ Bad - no authentication required
scope "/", MyAppWeb do
  pipe_through [:browser]
  lotus_dashboard "/lotus"  # Anyone can access!
end

2. Use Table Visibility Controls

Configure Lotus to control access to database tables:

config :lotus,
  table_visibility: %{
    default: [
      allow: [
        "reports_users",
        "analytics_events",
        {"reporting", ~r/^daily_/}  # Allow reporting.daily_* tables
      ],
      deny: [
        "users",           # Block sensitive user data
        "admin_logs",      # Block admin tables
        {"public", ~r/^schema_/}  # Block schema tables
      ]
    ]
  }

Comparison with Alternatives

vs. Livebook

vs. Full BI Solutions (Metabase, Grafana, etc.)

vs. Ruby's Blazer Gem

Development

Prerequisites

Running Tests

mix test

Development Server

For initial setup when cloning the repository:

# Create required asset files and install dependencies
mkdir -p priv/static
touch priv/static/app.css && touch priv/static/app.js
npm install --prefix assets
mix dev

For subsequent runs:

mix dev

Contributing

We welcome contributions!

Common ways to help:

Acknowledgments

LotusWeb owes significant inspiration to:

License

This project is licensed under the MIT License.

Portions of the code are adapted from Oban Web, © 2025 The Oban Team, licensed under the Apache License 2.0.