query_lens

KO | EN

gitlite — search

#python #java #dsh-plugin #ai-agents #deepseek-harness #game #ai #typescript #android #c-plus-plus #demo #cli

★ 71 Open GitHub ↗

Natural language SQL query builder for Rails, powered by AI

// repository documentation

Was this content helpful?

★ 0 (0 ratings)

# QueryLens

**[querylens.ai](https://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](https://rubyllm.com), QueryLens works with any major AI provider: OpenAI, Anthropic (Claude), Google Gemini, DeepSeek, Mistral, Ollama (local models), and more.

![QueryLens Screenshot](https://raw.githubusercontent.com/bryanbeshore/query_lens/main/docs/screenshot.png)

[Watch the demo (90 seconds)](https://www.loom.com/share/595ed0ea3c1f42b28152a345db586c85)

**Want to try it without touching your own database?** The [QueryLens Testbed](https://github.com/bryanbeshore/query_lens_testbed) is a ready-to-go Rails app with sample data — clone, run, and start querying in under 2 minutes.

## Features

- 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 with **Chart.js charting** (bar, line, pie, scatter) and auto-detected chart types
- Configurable authentication, timeouts, and row limits
- Zero frontend dependencies (self-contained CSS, vanilla JS)

## Installation

Add to your Gemfile:

```ruby
gem "query_lens"
```

Then run:

```bash
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.

## Configuration

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

```ruby
# 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
```

## Usage

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!)

## Saved Queries

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

### Saving a Query

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

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.

### Loading a Saved Query

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

### Managing Queries

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

## Conversation History

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

### How It Works

- **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.

## How Schema Handling Works

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:

### Schema Caching

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`).

### Small Databases (< 50 tables)

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.

### Large Databases (50+ tables)

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.

## Security

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.

### Excluded Tables

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.

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

### Audit Logging

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`.

```ruby
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`.

### Read-Only Connection (Recommended for Production)

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

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

## Production Checklist

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](#read-only-connection-recommended-for-production) 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](#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.

## Mounting with Active Admin

If you use [Active Admin](https://activeadmin.info), you can mount QueryLens under your admin path and reuse Active Admin's authentication:

```ruby
# 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:

```ruby
# 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:

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

## Requirements

- Rails 7.1+
- Ruby 3.2+
- An API key for any [RubyLLM-supported provider](https://rubyllm.com) (or a local Ollama instance)
- PostgreSQL recommended (SQLite works but without transaction-level read-only enforcement)

## Try It Out

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

```bash
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](https://rubyllm.com) works):

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

Then visit [localhost:3000/query_lens](http://localhost:3000/query_lens) and start asking questions.

## Development

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

## Contributing

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

## License

MIT License. See [MIT-LICENSE](MIT-LICENSE).

query_lens

Explore Similar Repositories

Was this content helpful?

Recent Feedback

Download README