QueryConsole

A Rails engine that provides a secure, mountable web interface for running SQL queries against your application's database. Read-only by default with optional DML support.

Modern, responsive SQL query interface with schema explorer, query management, and real-time execution

• Features
• Screenshots
• Security Features
• Installation
• Configuration
• Usage
• Security Considerations
• Development
• Troubleshooting
• Contributing
• Changelog

• 🔒 Security First: Read-only by default with multi-layer enforcement
• 🚦 Environment Gating: Disabled by default in production
• 🔑 Flexible Authorization: Integrate with your existing auth system
• ⚡ Resource Protection: Configurable row limits and query timeouts
• 📝 Comprehensive Audit Logging: All queries logged with actor information and metadata
• 🔐 Optional DML Support: Enable INSERT/UPDATE/DELETE with confirmation dialogs

• 📊 EXPLAIN Query Plans: Analyze query execution plans for performance debugging
• ✅ Smart Validation: SQL validation with keyword blocking and statement isolation
• 🎯 Accurate Results: Proper row counts for both SELECT and DML operations
• ⏱️ Query Timeout: Configurable timeout to prevent long-running queries

• 📊 Modern UI: Clean, responsive interface with real-time updates
• 🗂️ Schema Explorer: Browse tables, columns, types with quick actions
• 💾 Query Management: Save, organize, import/export queries (client-side)
• 📜 Query History: Client-side history stored in browser localStorage
• 🎨 Tabbed Navigation: Switch between History, Schema, and Saved Queries seamlessly
• 🔍 Quick Actions: Generate queries from schema, copy names, insert WHERE clauses
• ⚡ Hotwire-Powered: Turbo Frames and Stimulus for smooth, SPA-like experience
• 🎨 Zero Build Step: CDN-hosted dependencies, no asset compilation needed

Execute SQL queries with real-time results, execution time, and row counts displayed in a clean, scrollable table

Browse database tables, columns with data types, nullable status, and quick-action buttons (Insert, WHERE, Copy Table Name)

DML operations show "Data Modified" banner, accurate "Rows Affected" count, and permanent change confirmation. A browser confirmation dialog appears before execution (not shown - browser native UI).

Access recent queries with timestamps - click any query to load it into the editor instantly

Save important queries with names and tags, then load, export, or import them with one click

QueryConsole implements multiple layers of security:

1. Environment Gating: Only enabled in configured environments (development by default)
2. Authorization Hook: Requires explicit authorization configuration
3. Read-Only by Default: Only SELECT and WITH (CTE) queries allowed by default
4. Optional DML with Safeguards: INSERT/UPDATE/DELETE available when explicitly enabled, with mandatory user confirmation dialogs
5. Keyword Blocking: Always blocks DDL operations (DROP, ALTER, CREATE, TRUNCATE, etc.)
6. Statement Isolation: Prevents multiple statement execution
7. Row Limiting: Automatic result limiting to prevent resource exhaustion
8. Query Timeout: Configurable timeout to prevent long-running queries
9. Comprehensive Audit Trail: All queries logged with actor, query type, and execution metadata

Add this line to your application's Gemfile:

gem 'query_console'

And then execute:

bundle install
rails generate query_console:install

Requirements:

• Ruby 3.1+
• Rails 7.0+
• Works with Rails 8+
• Hotwire (Turbo Rails + Stimulus) - automatically included

The generator creates config/initializers/query_console.rb. You MUST configure the authorization hook:

QueryConsole.configure do |config|
  # Required: Set up authorization
  config.authorize = ->(controller) {
    controller.current_user&.admin?
  }

  # Track who runs queries
  config.current_actor = ->(controller) {
    controller.current_user&.email || "anonymous"
  }

  # Optional: Enable in additional environments (use with caution!)
  # config.enabled_environments = %w[development staging]

  # Optional: Adjust limits
  # config.max_rows = 1000
  # config.timeout_ms = 5000
  
  # Advanced Features
  # EXPLAIN feature (default: enabled)
  # config.enable_explain = true
  # config.enable_explain_analyze = false  # Disabled by default for safety
  
  # Schema Explorer (default: enabled)
  # config.schema_explorer = true
  # config.schema_cache_seconds = 60
  # config.schema_table_denylist = ["schema_migrations", "ar_internal_metadata"]
  # config.schema_allowlist = []  # Optional: whitelist specific tables
end

config.authorize = ->(controller) {
  controller.current_user&.admin?
}

config.authorize = ->(controller) {
  controller.authenticate_or_request_with_http_basic do |username, password|
    username == "admin" && password == Rails.application.credentials.query_console_password
  end
}

config.authorize = ->(_controller) { true }

By default, Query Console is read-only. To enable DML operations (INSERT, UPDATE, DELETE):

QueryConsole.configure do |config|
  config.enable_dml = true
  
  # Recommended: Restrict to specific environments
  config.enabled_environments = ["development", "staging"]
end

• DML is disabled by default for safety
• When enabled, INSERT, UPDATE, DELETE, and MERGE queries are permitted
• All DML operations are logged with actor information and query type
• No transaction support - queries auto-commit immediately
• Consider additional application-level authorization for production use

Even with DML enabled, these operations remain forbidden:

• DROP, ALTER, CREATE (schema changes)
• TRUNCATE (bulk deletion)
• GRANT, REVOKE (permission changes)
• EXECUTE, EXEC (stored procedures)
• TRANSACTION, COMMIT, ROLLBACK (manual transaction control)
• System procedures (sp_, xp_)

When DML is enabled and a DML query is detected:

• Before execution: A confirmation dialog appears with a clear warning about permanent data modifications
• User must explicitly confirm to proceed (can click "Cancel" to abort)
• After execution: An informational banner shows: "ℹ️ Data Modified: This query has modified the database"
• Rows Affected count is displayed (e.g., "3 row(s) affected") showing how many rows were inserted/updated/deleted
• The security banner reflects DML status
• All changes are permanent and logged

DML operations work on all supported databases:

• SQLite: INSERT, UPDATE, DELETE
• PostgreSQL: INSERT, UPDATE, DELETE, MERGE (via INSERT ... ON CONFLICT)
• MySQL: INSERT, UPDATE, DELETE, REPLACE

DML queries are logged with additional metadata:

{
  component: "query_console",
  actor: "user@example.com",
  sql: "UPDATE users SET active = true WHERE id = 123",
  duration_ms: 12.5,
  rows: 1,
  status: "ok",
  query_type: "UPDATE",     # NEW: Query type classification
  is_dml: true              # NEW: DML flag
}

-- Insert a new record
INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');

-- Update existing records
UPDATE users SET active = true WHERE id = 123;

-- Delete specific records
DELETE FROM sessions WHERE expires_at < NOW();

-- PostgreSQL upsert
INSERT INTO settings (key, value) VALUES ('theme', 'dark')
ON CONFLICT (key) DO UPDATE SET value = 'dark';

Add to your config/routes.rb:

Rails.application.routes.draw do
  mount QueryConsole::Engine, at: "/query_console"
end

Then visit: http://localhost:3000/query_console

1. Enter your SELECT query in the editor
2. Click "Run Query" or press Ctrl/Cmd+Enter
3. View results in the table below
4. Query is automatically saved to history

• Stored locally in your browser (not on server)
• Click any history item to load it into the editor
• Stores up to 20 recent queries
• Clear history with the "Clear" button

✅ Allowed:

• SELECT * FROM users
• SELECT id, name FROM users WHERE active = true
• WITH active_users AS (SELECT * FROM users WHERE active = true) SELECT * FROM active_users
• Queries with JOINs, ORDER BY, GROUP BY, etc.

❌ Blocked (by default):

• UPDATE users SET name = 'test' (unless enable_dml = true)
• DELETE FROM users (unless enable_dml = true)
• INSERT INTO users VALUES (...) (unless enable_dml = true)
• DROP TABLE users (always blocked)
• TRUNCATE TABLE users (always blocked)
• SELECT * FROM users; DELETE FROM users (multiple statements always blocked)
• Any query containing forbidden keywords

Note: With config.enable_dml = true, INSERT, UPDATE, DELETE, and MERGE queries become allowed.

-- List recent users
SELECT id, email, created_at 
FROM users 
ORDER BY created_at DESC 
LIMIT 10;

-- Count by status
SELECT status, COUNT(*) as count 
FROM orders 
GROUP BY status;

-- Join with aggregation
SELECT u.email, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.email
ORDER BY order_count DESC
LIMIT 20;

-- Common Table Expression (CTE)
WITH active_users AS (
  SELECT * FROM users WHERE active = true
)
SELECT * FROM active_users WHERE created_at > DATE('now', '-30 days');

⚠️ Important: QueryConsole is disabled by default in production. To enable in non-development environments:

config.enabled_environments = %w[development staging]

Never enable in production without:

1. Strong authentication
2. Network restrictions (VPN, IP whitelist)
3. Audit monitoring
4. Database read-only user (recommended)

The authorization hook is called on every request. Ensure it:

• Returns false or nil to deny access
• Is performant (avoid N+1 queries)
• Handles edge cases (logged out users, etc.)

All queries are logged to Rails.logger.info with:

{
  component: "query_console",
  actor: "user@example.com",
  sql: "SELECT * FROM users LIMIT 10",
  duration_ms: 45.2,
  rows: 10,
  status: "ok",  # or "error"
  truncated: false
}

Monitor these logs for:

• Unusual query patterns
• Unauthorized access attempts
• Performance issues
• Data access patterns

For production environments, consider using a dedicated read-only database user:

# config/database.yml
production_readonly:
  <<: *production
  username: readonly_user
  password: <%= ENV['READONLY_DB_PASSWORD'] %>

# In your initializer
QueryConsole.configure do |config|
  config.database_config = :production_readonly
end

You can test the gem in isolation without needing a full Rails application:

Option 1: Run automated tests

cd query_console
bundle install
bundle exec rspec

Option 2: Start the test server

cd query_console
bundle install
./bin/test_server

Then visit: http://localhost:9292/query_console

The test server includes sample data and is pre-configured for easy testing.

See TESTING.md for detailed testing instructions.

The test suite includes:

• SQL validator specs (security rules)
• SQL limiter specs (result limiting)
• Runner specs (integration tests)
• Controller specs (authorization & routing)

QueryConsole uses Hotwire (Turbo + Stimulus), the modern Rails-native frontend framework:

• Turbo Frames: Query results update without page reloads (SPA-like experience)
• Stimulus Controllers: Organized JavaScript for collapsible sections, history, and editor
• CDN Delivery: Hotwire loaded from CDN (no asset compilation needed)
• Zero Build Step: No webpack, esbuild, or other bundlers required

Frontend Stack
├── HTML: ERB Templates
├── CSS: Vanilla CSS (inline)
├── JavaScript: 
│   ├── Turbo Frames (results updates)
│   └── Stimulus Controllers
│       ├── collapsible_controller (section toggling)
│       ├── history_controller (localStorage management)
│       └── editor_controller (query execution)
└── Storage: localStorage API

✅ No Build Step: Works out of the box, no compilation needed
✅ Rails-Native: Standard Rails 7+ approach
✅ Lightweight: ~50KB total (vs React's 200KB+)
✅ Fast: No page reloads, instant interactions
✅ Progressive: Degrades gracefully without JavaScript

1. Rails Standard: Default frontend stack for Rails 7+
2. Simple: Fewer moving parts than SPA frameworks
3. Productive: Write less JavaScript, more HTML
4. Modern: All the benefits of SPAs without the complexity
5. Maintainable: Standard Rails patterns throughout

Possible causes:

1. Environment not in enabled_environments
2. Authorization hook returns false
3. Authorization hook not configured (defaults to deny)

Solution: Check your initializer configuration.

Causes:

• Query is too complex
• Database is slow
• Timeout setting too aggressive

Solutions:

• Increase timeout_ms
• Optimize query
• Add indexes to database

Cause: Query contains semicolon (;) in the middle

Solution: Remove extra semicolons. Only one trailing semicolon is allowed.

1. Fork the repository
2. Create your feature branch (git checkout -b feature/my-feature)
3. Write tests for your changes
4. Ensure tests pass (bundle exec rspec)
5. Commit your changes (git commit -am 'Add feature')
6. Push to the branch (git push origin feature/my-feature)
7. Create a Pull Request

The gem is available as open source under the terms of the MIT License.

Created by Johnson Gnanasekar

See CHANGELOG.md for detailed version history.

• ✨ Optional DML Support: INSERT/UPDATE/DELETE with mandatory confirmation dialogs
• 🎯 Accurate Row Counts: Proper affected rows tracking for DML operations
• 🔒 Enhanced Security: Pre-execution confirmation with detailed warnings
• 📝 Enhanced Audit Logging: Query type classification and DML flags
• 🗃️ Multi-Database Support: SQLite, PostgreSQL, MySQL compatibility

• 📊 EXPLAIN Plans: Query execution plan analysis
• 🗂️ Schema Explorer: Interactive table/column browser with quick actions
• 💾 Saved Queries: Client-side query management with import/export
• 🎨 Modern UI: Tabbed navigation and collapsible sections
• 🔍 Quick Actions: Generate queries from schema explorer

• 🔒 Read-only query console with security enforcement
• 🚦 Environment gating and authorization hooks
• ✅ SQL validation and row limiting
• ⏱️ Query timeout protection
• 📜 Client-side history with localStorage
• ✅ Comprehensive test suite and audit logging