I’ve started working on a new library to run SQL queries and do basic business intelligence.
Think “Blazer for Elixir.”
Currently it features a SQL explorer + saved queries + schema browser — mounted right inside your Phoenix app. Supports both Posgresql and SQLite (multiple data source repos).
It can be used as standalone or with the web UI (separate repos):
I think I get what is this about and I love the idea but a few screenshots and a video would be great.
Thanks @dimitarvp . You’re absolutely right. I added a screenshot to the original post.
I’ll have to record a demo video to share!
In the age of the LLMs this expression gives me PTSD. 
Thanks for adding the screenshot. Would love seeing a video later if you ping this thread in the future.
hahaha… is GPT/Claude lingo seeping into my speech patterns? 
I’ll share a video demo here later on.
@dimitarvp Here’s a video demo of Lotus
This is so neat. I love it. Thank you for your contribution to the ecosystem!
Thank you for the kind words. I’m glad you like it and hope others will find it useful 
Nice work on this, it looks great.
[Previous job catharsis]: My prior end users would have asked for a drag and drop graphical query editor and wonder why it couldn’t predict their queries and graph them in twelve dimensions. However, Lotus seems like nifty tool to have in the toolbox, and they aren’t my end users anymore. Haha!
Please note, that was not a feature request!
Thanks for this.
This is really fantastic. Thanks for sharing this!
This looks great!!
How do you manage to make it read only? Do I have to create a read only account for it or you just check the statement if it only a select statement? If you check the statement I can do some deletes or inserts inside a CTE will this be caught as well?
Hi @A7mad . Creating a read only account is the bullet-proof way to ensure there won’t be any writes and if that’s available I would recommend it.
That being said, Lotus uses a two-layer defense approach:
Rejects known write operations like INSERT, UPDATE, DELETE, etc. This check happens BEFORE the query is executed and will catch write operations even if they’re inside CTEs
Database-level read-only transaction guard. In PostgreSQL it uses
SET LOCAL transaction_read_only = on which prevents ANY write operations at the database level, regardless of how they’re structured. In SQLite this layer does nothing.
Update: I should clarify that in the latest 0.5.0 release Lotus actually has enhanced SQLite support and now also supports MySQL!
SQLite: While my previous reply mentioned that the database-level read-only layer “does nothing” in SQLite, that’s not entirely accurate anymore. Lotus now uses PRAGMA query_only = ON for SQLite 3.8.0+ (released in 2013), which provides database-level write prevention similar to PostgreSQL’s transaction_read_only. For older SQLite versions, it falls back to the regex-based validation layer only.
MySQL: Lotus now also supports MySQL with the same two-layer defense approach:
SET SESSION TRANSACTION READ ONLY for database-level write preventionSo the complete picture is:
This looks fantastic! I love the autocomplete and table explorer thingy for building queries. Perhaps a more fine-grained column visibility could be added to table_visibility, for example in the demo I could imagine wanting the hashed_password field to be silently omitted (or its contents elided, which seems safe since it’s all read-only).
Such an excellent tool for admin/debugging/analysis.
Thanks for the feedback!
Yes, I have it in the roadmap to be able to configure which fields you want omitted. Currently you can only deny entire tables in the config, but I want to have more fine-grained visibility control like you suggested. I have some ideas on also giving the ability to decide what to do with those columns, wether to hide them entirely, error if they are in a query or hide their content.
I’m currently working on bringing variables configuration and variable substitution in the web editor and will likely tackle caching next and then column visibility.
Very promising tool, nice work! Lotus definitely could solve some data visibility issues at my workplace.
I gave it a spin, and I have a few thoughts:
ctrl+enter is typical)Hi @frankdugan3 ,
Thank you for your feedback.
The keyboard shortcut for running the query will be available in the next version of Lotus web. I already have it working in my current branch
You are right about the schemas. The Lotus backend supports Posgres schemas work but the Web has no way to cycle through them. If you only have a handful of schemas, you could do declare them as data_repos in config:
config :my_app, MyApp.Repo,
username: "postgres",
password: "postgres",
hostname: "localhost",
port: 2346,
database: "my_app"
)
config :my_app, MyApp.Tenant1,
username: "postgres",
password: "postgres",
hostname: "localhost",
port: 2346,
database: "my_app",
parameters: [search_path: "tenant1"]
)
config :lotus,
ecto_repo: MyApp.Repo,
data_repos: %{
"main" => MyApp.Repo,
"tenant1" => MyApp.Tenant1
}
But I suppose this is not very convenient. I will see if I can find a better way to expose the various schemas in the UI.
Other items, like custom functions, views, etc. aren’t surfaced in the explorer
Do you have examples of thing you’d like to do?
In the next release autocompletion will give suggestions based on the current schema (the active/selected data source).
Can’t promise to have dark mode soon as I want to prioritise other features first. But I’m certainly not against implementing it.
Realase: Lotus 0.7 and LotusWeb 3.1:
Variables system: Full {{variable}} support in SQL editor with CodeMirror highlighting and automatic widgets in the toolbal
Caching system: new adapter behaviour + ETS backend with TTLs, cache profiles (:results, :schema, :options) and tag-based invalidation.
OTP: runs as an OTP application (Lotus.Application, Lotus.Supervisor), with child_spec/1 + start_link/1 for supervision tree integration.
Performance: schema queries and metadata resolution are now cached → much faster introspection
Cache models: cache modes (default, :bypass, :refresh) and built-in profiles with runtime overrides give you control per query
Looking forward to checking this out, I recently ported a bunch of queries from Blazer into an internal Elixir app, and created a basic interface for new queries, but this looks much better!
© Copyright Elixir Forum | Terms | Privacy & Cookies
