Lotus - Lightweight, SQL query runner and storage for Phoenix apps

Lotus and Lotus Web v1 are finally out.

I meant to release this earlier, but for the last 5 months I didn’t have time for the project. I finally managed to push it across the line.

This is not a drop-in upgrade. Lotus is no longer only SQL on Ecto repos. Supporting non-SQL data sources needed some big changes to the core: config keys, function names, middleware payloads and a couple of DB columns all moved. There is an upgrading to v1 guide that goes step by step. Please read it before you bump the version.

Every data source now sits behind one adapter behaviour. Postgres, MySQL and SQLite are built in, and there are two new adapters in separate packages:

• lotus_clickhouse
• lotus_elasticsearch (works with OpenSearch too)

Other features included in the release worth mentioning:

1. The editor follows the source: JSON mode with autocomplete for Elasticsearch, and the correct syntax for each SQL dialect.
2. You can write your own adapter for any other data source. The source adapters guide explains how.
3. Lotus Web can pass the current user to Lotus, so middleware and visibility rules know who is asking (tenant scoping, date range limits, per-action permissions).
4. Dashboards got cascading filters and relative dates (“Last 30 days” stays relative in a shared link).
   Full changelogs: lotus · lotus_web

Lotus also has a new home at lotusbi.com, and a new GitHub org with all the repos.

Questions and feedback are welcome here.

Hey everyone! Really excited about this one! Our latest versions of Lotus and Lotus Web make up the biggest Lotus release yet.

AI Assistant

We added a bunch of new AI capabilities on top of the multi-turn conversations from the previous release:

• Explain query / Explain fragment — select a piece of SQL you don’t understand and the AI breaks it down in plain English. Really useful when you’re looking at someone else’s query or a complex JOIN.
• Optimize query — runs EXPLAIN under the hood and gives you suggestions like missing indexes, query rewrites, etc.
• Variable-aware — the AI now understands the {{variable}} and [[optional clause]] syntax, so it can generate parameterized queries with the right variable types and input widgets out of the box.

On the provider side, we now support way more LLMs — OpenAI, Anthropic, Google, Groq, Mistral, and anything else ReqLLM supports. BYOK as always.

16 chart types

We expanded the visualization options significantly: bar, horizontal bar, line, area, combo (dual-axis), scatter, bubble, histogram, heatmap, pie, donut, funnel, waterfall, KPI card, trend, gauge, progress bar,
and sparkline. The picker is now organized by category (Charts, Distribution, Part of Whole, Single Value) which makes it easier to find what you need.

Interactive results

Query results are no longer just a static table:

• Right-click any cell to quick-filter results (equals, not equals, greater than, LIKE, IS NULL, etc.)
• Right-click column headers to sort ascending/descending
• Hover on a column header to see statistics — min, max, avg, median, distinct count, and a histogram
• Active filters and sorts show as dismissible chips above the results

These are applied as CTEs on top of your query, so the original SQL stays untouched.

Dashboard filters

Dashboards now support interactive filter widgets — dropdowns, date pickers, text inputs, date ranges. You map them to query variables per card, and they reflect in URL query parameters so filtered views are bookmarkable and shareable. Works on both the editor and public dashboards.

We also redesigned the website to better showcase all the new features — check it out at lotus.typhoon.works with demo videos of the main capabilities.

And we now have a Discord if you want to chat, ask questions, or share what you’re building: discord.gg/qnvcdjvw

We’ve been getting issues and PRs from people using Lotus in production and it’s been incredibly motivating. We’d love more of that — whether it’s bug reports, feature ideas, or contributions. Come help us shape where this goes.

https://github.com/typhoonworks/lotus

https://github.com/typhoonworks/lotus_web

Just playing with the Lotus AI Assistant, with typos and all, and it unlocks such power moves for when you want to do visualizations!

image1200×668 38.2 KB

image1200×671 33.2 KB

The AI Assistant understands Lotus Queries and Lotus Variables (for easy query and inputs+widget generation) but it has no awareness of the visualizations. But it’s still helpful to give a rough description of the kind of viz we want to see and it will give you a usable query

Thanks for the suggestion! While Lotus is primarily focused on read-only analytics and reporting, this actually aligns with something we already had in the codebase but wasn’t properly exposed.

Lotus already had a read_only option that flowed through to the database adapters, but the application-level SQL validation was ignoring it — so it was effectively broken. We’ve now fixed that and promoted it to a first-class config option.

Starting from main (will be in the next release), you can set it globally:

config :lotus,
       read_only: false

This disables the application-level deny list for INSERT, UPDATE, DELETE, DDL, etc. — both for Lotus.run_sql/Lotus.run_query calls and through the Lotus Web UI.

Single-statement validation and table visibility rules still apply.

You can also override it per-call (on the lotus lib) without changing the global config:

Lotus.run_sql(
  “INSERT INTO notes (body) VALUES ($1) RETURNING id”,
  [“hello”],
  read_only: false
)

The AI query generator also respects this — when read_only: false is configured, it will generate write queries when asked.

Maybe this is out of scope for your project, but I was wondering if you could add an option to allow write queries to work in lotus.

Basically the idea would be to have it only enabled in dev so it can also help development in general instead of the user having to use psql, or some other third party sql ui.

I’ve added support for multiple values in variables via multi select dropdown or a new freeform input that adds selected values as chip tags

Tag input

CleanShot 2026-02-16 at 07.53.49@2x1200×667 28.1 KB

Multi select dropdown

CleanShot 2026-02-16 at 07.54.41@2x1200×670 30 KB

Released in:

Lotus v0.14.0 GitHub - elixir-lotus/lotus: Embeddable business intelligence engine for Elixir — SQL editor, dashboards, charts, and AI query generation that mount directly in your Phoenix app. · GitHub

Lotus Web v0.13.0 GitHub - elixir-lotus/lotus_web: LiveView-powered BI interface for Phoenix — SQL editor, dashboards, charts, and AI query assistant. No separate deployment needed. · GitHub

Wow that’s amazing. Thanks for the feedback

I wonder if there is something that can be done to get right in one shot

I had a tough query to write today that used JSON aggregates and multiple subqueries, so I got to test out the new AI chat flow. It worked out great, had a working query after about 3 error corrections!

Currently Lotus doesn’t have built-in variable constraints or per-user variable injection. By design, Lotus doesn’t manage its own users or roles — it relies on the host application for authentication, and assumes that anyone with access to the dashboard is a trusted user. So there’s no built-in mechanism to restrict what different users can do once they’re in.

That said, I can see the value in both of these:

1. Date range constraints — preventing expensive queries by limiting how wide a date range can be
2. Automatic filtering by user context — e.g., forcing a tenant_id based on who’s logged in, so users only see their own data

Neither is on the roadmap right now, but they’re both interesting ideas. If you’d like to see either of these, please open a feature request at Issues · elixir-lotus/lotus · GitHub

It helps me understand what people actually need and prioritize accordingly.

Can we restrict the values of variables the normal users can set? for example usually you do not want to allow long running queries on certain databases through the date range period? Another example , if we have filtering per some columnon user profile such as the institution or tenant he/she belong to ?