QueryLens

querylens.ai

A mountable Rails engine that lets users write natural language questions and get SQL queries generated by AI, executed against their database, with results displayed — all in one interface. Think "Blazer meets ChatGPT."

Powered by RubyLLM, QueryLens works with any major AI provider: OpenAI, Anthropic (Claude), Google Gemini, DeepSeek, Mistral, Ollama (local models), and more.

Watch the demo (90 seconds)

Want to try it without touching your own database? The QueryLens Testbed is a ready-to-go Rails app with sample data — clone, run, and start querying in under 2 minutes.

• Natural language to SQL conversion powered by any LLM
• Works with OpenAI, Anthropic, Gemini, Ollama, and 10+ other providers
• Saved queries organized into projects (like Blazer) — save, edit, move, and reuse known-good queries
• Automatic database schema introspection with caching
• Smart schema handling for large databases (two-stage table selection)
• Conversation history — auto-saved conversations persist across page refreshes, with a Claude.ai-style sidebar for browsing recent chats
• Interactive conversation with context (follow-up questions refine queries)
• Read-only query execution (safety enforced at transaction level)
• Editable SQL editor with syntax highlighting
• Results displayed as sortable tables
• Configurable authentication, timeouts, and row limits
• Zero frontend dependencies (self-contained CSS, vanilla JS)

Add to your Gemfile:

gem "query_lens"

Then run:

bundle install
rails generate query_lens:install
rails db:migrate

This will:

1. Create config/initializers/query_lens.rb with RubyLLM and QueryLens configuration
2. Copy the migration to create the query_lens_projects, query_lens_saved_queries, and query_lens_conversations tables
3. Add the engine route to your config/routes.rb

Upgrading from a previous version? If you already ran migrations from an earlier release (before the install generator included them), your tables are already in place. Just run rails generate query_lens:install and skip or discard the generated migration file — no need to migrate again.

Configure your AI provider in config/initializers/query_lens.rb:

# Configure your AI provider (you only need one)
RubyLLM.configure do |config|
  config.openai_api_key = ENV["OPENAI_API_KEY"]
  # config.anthropic_api_key = ENV["ANTHROPIC_API_KEY"]
  # config.gemini_api_key = ENV["GEMINI_API_KEY"]
  # config.ollama_api_base = "http://localhost:11434"
end

QueryLens.configure do |config|
  # Pick any model supported by your provider
  config.model = "gpt-4o"                          # OpenAI
  # config.model = "claude-sonnet-4-5-20250929"     # Anthropic
  # config.model = "gemini-2.0-flash"               # Google
  # config.model = "llama3.2"                       # Ollama (local)

  config.max_rows = 1000                            # Max rows returned
  config.query_timeout = 30                         # Seconds
  config.excluded_tables = %w[api_keys secrets]     # Hide from AI
  config.authentication = ->(controller) {          # Auth check
    controller.current_user&.admin?
  }

  # Schema cache TTL in seconds (default: 300 / 5 minutes)
  # config.schema_cache_ttl = 300

  # Table selection threshold (default: 50)
  # Schemas larger than this use two-stage AI generation
  # config.table_selection_threshold = 50
end

Visit /query_lens in your browser and start asking questions:

• "How many users signed up this month?"
• "What's the total revenue by plan?"
• "Show me the top 10 accounts by transaction volume"
• "Break that down by month" (follow-up questions work!)

QueryLens supports saving queries for reuse, organized into projects (e.g., "Rewards", "User Analytics"). Saved queries are shared across all admins.

1. Run a query (via AI or manually)
2. Click Save in the sidebar's Saved Queries section
3. Enter a name, optional description, and optionally assign to a project

Projects are folders for organizing saved queries. Create them from the Saved Queries toolbar. When a project is deleted, its queries are moved to "Unorganized" rather than being destroyed.

Click any saved query in the sidebar to load its SQL into the editor and auto-run it.

Use the kebab menu (three dots) on any project or query to rename, edit, move between projects, or delete.

Conversations auto-save as you chat — no save button needed. A page refresh preserves your full conversation history, including the SQL editor state.

• First message in a new chat creates a conversation (titled from your first question)
• Subsequent messages update the conversation automatically after each AI response
• Conversation sidebar shows recent chats in the left sidebar under "Recents"
• Click any conversation to restore it — messages, SQL editor, and all
• Click New Chat to start fresh
• Delete conversations with the × button (appears on hover)

Conversations are shared across all admins (no per-user scoping), matching the pattern of saved queries. The most recent 50 conversations are shown in the sidebar.

QueryLens needs to tell the AI about your database structure so it can write accurate SQL. Naively sending your entire schema on every request would be slow and expensive for large databases. Here's how QueryLens handles this:

The database schema is introspected once and cached in memory. Subsequent AI requests reuse the cached schema instead of re-querying every table, column, and row count from the database. The cache expires after 5 minutes by default (configurable via schema_cache_ttl).

For most applications, the full schema is compact enough to send directly to the AI in a single request. Every table with its columns, types, foreign keys, and approximate row counts is included in the system prompt. This gives the AI complete context to write accurate queries.

For large schemas — hundreds of tables, thousands of columns — sending everything would burn excessive tokens, increase latency, and potentially exceed context windows. QueryLens uses a two-stage approach instead:

Stage 1 — Table Selection: A compact index is sent to the AI — one line per table listing just the table name, column names, and row count. The AI identifies which tables (typically 3-10) are relevant to the user's question.

Stage 2 — Query Generation: The full schema (columns, types, foreign keys, constraints) for only the selected tables is sent to the AI, which generates the SQL query.

This mirrors how a human DBA works: scan the table list, zero in on the relevant ones, then examine their structure. It also mirrors how tools like Claude Code work — they don't load an entire codebase into context, they search for and read only the relevant files.

The threshold is configurable via table_selection_threshold (default: 50 tables). For databases right around the threshold, you can tune this based on your preference for completeness vs. speed.

QueryLens enforces multiple layers of safety:

1. Read-only transactions: All queries run inside SET TRANSACTION READ ONLY (PostgreSQL)
2. SQL parsing: Rejects any statement that isn't a SELECT or WITH (CTE)
3. Statement blocklist: Blocks INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, TRUNCATE, GRANT, REVOKE, EXECUTE, CALL
4. Semicolon blocking: Prevents multi-statement injection
5. Function blocklist: Blocks dangerous PostgreSQL functions (pg_sleep, pg_terminate_backend, etc.)
6. Query timeout: Configurable per-query timeout enforced at the database level
7. Row limits: Configurable max rows (default 1000)
8. Authentication: Configurable auth lambda to restrict access
9. Table enforcement: excluded_tables blocks both AI context and query execution — queries referencing restricted tables are rejected with a clear error
10. Audit logging: Configurable logging of all query executions, blocked attempts, and AI generations

Important: Always restrict access to QueryLens in production using the authentication config option. Even with read-only enforcement, database access should be limited to authorized users.

Tables listed in excluded_tables are enforced at two levels:

• AI context: The AI never sees these tables, so it won't suggest queries against them
• Execution: Even if a user manually types a query referencing a restricted table, it's blocked with a clear error

Restricted tables are shown in a collapsible banner in the UI so admins know which tables are off-limits.

QueryLens.configure do |config|
  config.excluded_tables = %w[api_keys admin_users payment_methods ssn_records]
end

Every query execution, blocked attempt, and AI generation can be logged. The audit logger receives a hash with :user, :action, :sql, :row_count, :error, :timestamp, and :ip.

QueryLens.configure do |config|
  # Simple: log to Rails logger
  config.audit_logger = ->(entry) {
    Rails.logger.info("[QueryLens] #{entry[:action]} by #{entry[:user]} — #{entry[:sql]}")
  }

  # Production: log to a database table
  config.audit_logger = ->(entry) {
    QueryAuditLog.create!(
      user_identifier: entry[:user],
      action: entry[:action],
      sql_query: entry[:sql],
      row_count: entry[:row_count],
      error_message: entry[:error],
      ip_address: entry[:ip]
    )
  }

  # The method called on the controller to identify the user (default: :current_user)
  # For Active Admin: :current_admin_user
  config.current_user_method = :current_admin_user
end

Actions logged:

• execute — successful query execution (includes SQL and row count)
• execute_blocked — query rejected by safety checks (includes reason)
• execute_error — query failed at database level (includes error message)
• generate — AI generated SQL
• generate_error — AI generation failed

Audit logging is fail-safe: if your logger raises an error, the query still executes normally and the failure is logged to Rails.logger.error.

For production use, point QueryLens at a read-only database replica or a connection using a read-only PostgreSQL user:

QueryLens.configure do |config|
  config.read_only_connection = ActiveRecord::Base.connected_to(role: :reading) {
    ActiveRecord::Base.connection
  }
end

Before deploying QueryLens to a production environment, especially one with sensitive data:

1. Restrict access with authentication. Never run QueryLens without an authentication lambda. Limit access to specific admin roles — not everyone who can log in should be able to query your database.

2. Point it at a read-only replica. A runaway query (big joins, full table scans) hitting your primary database can affect production performance. A replica isolates that blast radius. See Read-Only Connection above.

3. Use a read-only database user. Belt and suspenders. Even with transaction-level read-only enforcement, connecting via a PostgreSQL user with only SELECT grants means the database itself won't allow writes regardless of what happens at the application level.

4. Exclude sensitive tables. Any table containing PII, credentials, financial secrets, or data that shouldn't be queryable — add it to excluded_tables. The AI will never see these tables, and manual queries against them are blocked at execution time.

5. Enable audit logging. Log every query, every blocked attempt, with the user and IP address. If someone queries something they shouldn't, you want to know. See Audit Logging above.

6. Review your schema exposure. QueryLens sends your database schema (table names, column names, types, foreign keys) to your configured LLM provider. If your schema itself is sensitive, consider using a local model via Ollama instead of a cloud provider.

If you use Active Admin, you can mount QueryLens under your admin path and reuse Active Admin's authentication:

# config/routes.rb
Rails.application.routes.draw do
  devise_for :admin_users, ActiveAdmin::Devise.config
  ActiveAdmin.routes(self)

  # Mount QueryLens under /admin/query_lens
  mount QueryLens::Engine, at: "/admin/query_lens"

  # ...
end

Then configure QueryLens to require an authenticated admin user:

# config/initializers/query_lens.rb
QueryLens.configure do |config|
  config.authentication = ->(controller) {
    # Warden is available because Devise is middleware
    controller.request.env["warden"].authenticated?(:admin_user)
  }
end

QueryLens will be available at /admin/query_lens. Unauthenticated requests get a 401. Active Admin's navigation won't show a link automatically — you can add one with a custom menu item:

# app/admin/query_lens.rb
ActiveAdmin.register_page "QueryLens" do
  menu label: "QueryLens", url: "/admin/query_lens", priority: 99
end

• Rails 7.1+
• Ruby 3.2+
• An API key for any RubyLLM-supported provider (or a local Ollama instance)
• PostgreSQL recommended (SQLite works but without transaction-level read-only enforcement)

Want to see QueryLens in action before integrating it into your own app? The QueryLens Testbed is a standalone Rails app with a realistic SaaS dataset — users, teams, posts, comments, tags, and invoices (~1,450 records) — ready to query.

git clone https://github.com/bryanbeshore/query_lens_testbed.git
git clone https://github.com/bryanbeshore/query_lens.git
cd query_lens_testbed
bundle install
bin/rails db:prepare

Start the server with your API key (any RubyLLM-supported provider works):

ANTHROPIC_API_KEY=sk-ant-your-key bin/dev
# or: OPENAI_API_KEY=sk-your-key bin/dev

Then visit localhost:3000/query_lens and start asking questions.

git clone https://github.com/bryanbeshore/query_lens.git
cd query_lens
bundle install
bundle exec rake test

1. Fork the repo
2. Create your feature branch (git checkout -b my-feature)
3. Commit your changes (git commit -am 'Add feature')
4. Push to the branch (git push origin my-feature)
5. Create a Pull Request

MIT License. See MIT-LICENSE.