Lotus

Lotus

Hex VersionHexDocsCI Status

Lotus is a lightweight SQL query runner and storage library for Elixir applications with Ecto. It provides a safe, read-only environment for executing analytical queries while offering organized storage and management of saved queries.

🚧 While this library already ships a lot of features and the public API is mostly set, it’s still evolving. We’ll make a best effort to announce breaking changes, but we can’t guarantee backwards compatibility yet — especially as we generalize the Source abstraction to support more than SQL-backed data sources.

Production Use and UUID Caveats

Lotus is generally safe to run in production: it executes queries in read‑only transactions and restores session state (timeouts, isolation, etc.) to avoid pool pollution. 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 across databases, there are current limitations to be aware of:

We plan to improve this with column‑aware binding (using schema metadata per adapter to deterministically cast/shape values for the true column type). Until then, keep the above in mind when authoring queries that filter on UUID columns.

Lotus Web UI

While Lotus can be used standalone, it pairs naturally with Lotus Web (v0.4+ for Lotus 0.9), which provides a beautiful web interface you can mount directly in your Phoenix application:

Learn more about setting up Lotus Web in the installation guide.

Current Features

Production-Safe Connection Pooling

Lotus automatically preserves your database session state to prevent connection pool pollution. When a query completes, all session settings (read-only mode, timeouts, isolation levels) are restored to their original values, ensuring Lotus doesn't interfere with other parts of your application. Learn more about session management →

What's planned?

Installation

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

def deps do

  [

    {:lotus, "~> 0.9.0"}

  ]

end

Lotus requires Elixir 1.16 or later, and OTP 25 or later. It may work with earlier versions, but it wasn't tested against them.

Follow the installation instructions to set up Lotus in your application.

Migration Guide

Upgrading from versions < 0.9.0

If you're upgrading from a version prior to 0.9.0 and have stored queries with static_options, you'll need to migrate your data. The static_options field format has changed from simple string arrays to structured maps.

Old format:

"static_options" => ["Bob", "Alice", "Charlie"]

New format:

"static_options" => [

  %{"value" => "Bob", "label" => "Bob"},

  %{"value" => "Alice", "label" => "Alice"},

  %{"value" => "Charlie", "label" => "Charlie"}

]

Migration script:

# Run this in your application console (iex -S mix)

import Ecto.Query



# Use the same repo that Lotus is configured to store queries in

repo = Lotus.repo()  # Returns the configured ecto_repo



# Get all queries (raw data to bypass Ecto schema loading)

{:ok, result} = repo.query("SELECT id, name, variables FROM lotus_queries")



# Process each row

for [id, name, variables] <- result.rows do

  needs_migration =

    Enum.any?(variables, fn var ->

      case var["static_options"] do

        [first | _] when is_binary(first) -> true

        _ -> false

      end

    end)



  if needs_migration do

    IO.puts("Migrating query: #{name}")



    updated_variables =

      Enum.map(variables, fn var ->

        case var["static_options"] do

          options when is_list(options) ->

            migrated_options =

              Enum.map(options, fn

                opt when is_binary(opt) -> %{"value" => opt, "label" => opt}

                opt -> opt  # Already migrated or other format

              end)

            Map.put(var, "static_options", migrated_options)



          _ -> var

        end

      end)



    {:ok, _} = repo.query("""

      UPDATE lotus_queries

      SET variables = $1, updated_at = NOW()

      WHERE id = $2

    """, [updated_variables, id])



    IO.puts("✓ Updated query #{name}")

  end

end

Getting Started

Take a look at the overview guide for a quick introduction to Lotus.

Configuration

View all the configuration options in the configuration guide.

Basic Usage

Configuration

Add to your config:

config :lotus,

  ecto_repo: MyApp.Repo,    # Repo where Lotus stores saved queries

  default_repo: "main",     # Default repo for queries (required with multiple repos)

  data_repos: %{            # Repos where queries run against actual data

    "main" => MyApp.Repo,

    "analytics" => MyApp.AnalyticsRepo,

    "mysql" => MyApp.MySQLRepo

  }



# Optional: Configure caching (ETS adapter included)

config :lotus,

  cache: [

    adapter: Lotus.Cache.ETS,

    profiles: %{

      results: [ttl: 60_000],      # Cache query results for 1 minute

      schema: [ttl: 3_600_000],    # Cache table schemas for 1 hour

      options: [ttl: 300_000]      # Cache query options for 5 minutes

    }

  ]

Creating and Running Queries

# Create and save a query

{:ok, query} = Lotus.create_query(%{

  name: "Active Users",

  statement: "SELECT * FROM users WHERE active = true"

})



# Execute a saved query

{:ok, results} = Lotus.run_query(query)



# Execute SQL directly (read-only)

{:ok, results} = Lotus.run_sql("SELECT * FROM products WHERE price > $1", [100])



# Execute against a specific data repository

{:ok, results} = Lotus.run_sql("SELECT COUNT(*) FROM events", [], repo: "analytics")

Development Setup

Prerequisites

Setting up the development environment

  1. Clone the repository and install dependencies:
git clone https://github.com/typhoonworks/lotus.git

cd lotus

mix deps.get
  1. Set up the development databases:
# Start MySQL with Docker Compose (optional)

docker compose up -d mysql



# Create and migrate databases

mix ecto.create

mix ecto.migrate

This creates:

Running tests

# Run all tests

mix test



# Run specific test files

mix test test/lotus_test.exs



# Run with coverage

mix test --cover

The test suite uses separate databases:

Contributing

See the contribution guide for details on how to contribute to Lotus.

License

This project is licensed under the MIT License - see the LICENSE file for details.