👁️ DBViewer

DBViewer is a powerful Rails engine that provides a comprehensive interface to view and explore database tables, records, and schema. It's designed for development, debugging, and database analysis, offering a clean and intuitive way to interact with your application's database.

image

✨ Features

  • Dashboard: View a comprehensive dashboard with database analytics, largest tables, most complex tables, and recent SQL queries
  • Table Overview: View a list of all tables with record count, column count, and quick access links
  • Detailed Schema Information:
    • View columns with their types, nullability, defaults, and primary key indicators
    • Examine table indexes and their uniqueness constraints
    • Explore foreign key relationships between tables
  • Entity Relationship Diagram (ERD):
    • Interactive visualization of database schema and table relationships
    • Zoomable and pannable diagram to explore complex database structures
    • Full table details including all columns and their data types
    • Visual representation of foreign key relationships between tables
  • Data Browsing:
    • Browse table records with customizable pagination (10, 20, 50, or 100 records per page)
    • Sort data by any column in ascending or descending order
    • Navigate through large datasets with an intuitive pagination interface
    • Scrollable table with fixed headers for improved navigation
    • Single-line cell display with ellipsis for wide content (tooltips on hover)
  • SQL Queries:
    • Run custom SELECT queries against your database in a secure, read-only environment
    • View table structure reference while writing queries
    • Protection against potentially harmful SQL operations
    • Query execution statistics and timing
  • Enhanced UI Features:
    • Responsive, Bootstrap-based interface that works on desktop and mobile
    • Fixed header navigation with quick access to all features
    • Modern sidebar layout with improved filtering and scrollable table list
    • Clean tabbed interface for exploring different aspects of table structure
    • Advanced table filtering with keyboard navigation support
    • Proper formatting for various data types (dates, JSON, arrays, etc.)
    • Enhanced data presentation with appropriate styling

📸 Screenshots

Click to see more screenshots #### Dashboard Overview image #### Table Details image #### Query Editor image #### Query Logs image #### ERD image

📥 Installation

Add this line to your application's Gemfile:

gem "dbviewer", group: :development

And then execute:

$ bundle

🔧 Usage

Mount the engine in your application's config/routes.rb file:

Rails.application.routes.draw do
  # Your application routes...

  # Mount the DBViewer engine
  if Rails.env.development?
    mount Dbviewer::Engine, at: "/dbviewer"
  end
end

Then, visit /dbviewer in your browser to access the database viewer.

Rails API-only Applications

If you're using a Rails API-only application (created with --api flag), you'll need to enable the Flash middleware for DBViewer to work properly. Add the following to your config/application.rb:

module YourApp
  class Application < Rails::Application
    # ... existing configuration

    # Required for DBViewer flash messages
    config.middleware.use ActionDispatch::Flash
  end
end

This is necessary because API-only Rails applications don't include the Flash middleware by default, which DBViewer uses for displaying notifications.

Available Pages

  • Dashboard (/dbviewer): Comprehensive overview with database statistics and analytics
  • Tables Index (/dbviewer/tables): Shows all tables in your database with column counts and quick access
  • Table Details (/dbviewer/tables/:table_name): Shows table structure and records with pagination
  • SQL Query (/dbviewer/tables/:table_name/query): Allows running custom SQL queries
  • ERD View (/dbviewer/entity_relationship_diagrams): Interactive Entity Relationship Diagram of your database
  • SQL Query Logs (/dbviewer/logs): View and analyze logged SQL queries with performance metrics

🤝🏻 Extending DBViewer

Adding Custom Functionality

You can extend the database manager with custom methods:

# config/initializers/dbviewer_extensions.rb
Rails.application.config.to_prepare do
  Dbviewer::DatabaseManager.class_eval do
    def table_statistics(table_name)
      # Your custom code to generate table statistics
      {
        avg_row_size: calculate_avg_row_size(table_name),
        last_updated: last_updated_timestamp(table_name)
      }
    end

    private

    def calculate_avg_row_size(table_name)
      # Implementation...
    end

    def last_updated_timestamp(table_name)
      # Implementation...
    end
  end
end

⚙️ Configuration Options

You can configure DBViewer by creating an initializer in your application:

# config/initializers/dbviewer.rb
Dbviewer.configure do |config|
  config.per_page_options = [10, 20, 50, 100, 250]  # Default pagination options
  config.default_per_page = 20                       # Default records per page
  config.max_query_length = 10000                    # Maximum SQL query length
  config.cache_expiry = 300                          # Cache expiration in seconds
  config.max_records = 10000                         # Maximum records to return in any query
  config.enable_data_export = false                  # Whether to allow data exporting
  config.query_timeout = 30                          # SQL query timeout in seconds

  # Query logging options
  config.query_logging_mode = :memory                # Storage mode for SQL queries (:memory or :file)
  config.query_log_path = "log/dbviewer.log"         # Path for query log file when in :file mode
  config.max_memory_queries = 1000                   # Maximum number of queries to store in memory
end

The configuration is accessed through Dbviewer.configuration throughout the codebase. You can also access it via Dbviewer.config which is an alias for backward compatibility.

🪵 Query Logging

DBViewer includes a powerful SQL query logging system that captures and analyzes database queries. You can access this log through the /dbviewer/logs endpoint. The logging system offers two storage backends:

In-Memory Storage (Default)

By default, queries are stored in memory. This provides fast access but queries are lost when the application restarts:

config.query_logging_mode = :memory       # Store queries in memory (default)
config.max_memory_queries = 1000          # Maximum number of queries stored

File-Based Storage

For persistent logging across application restarts, you can use file-based storage:

config.query_logging_mode = :file         # Store queries in a log file
config.query_log_path = "log/dbviewer.log" # Path where query log file will be stored

The file format uses one JSON entry per line, making it easy to analyze with standard tools.

🔒 Security Features

DBViewer includes several security features to protect your database:

  • Read-only Mode: Only SELECT queries are allowed; all data modification operations are blocked
  • SQL Validation: Prevents potentially harmful operations with comprehensive validation
  • Query Limits: Automatic LIMIT clause added to prevent excessive data retrieval
  • Pattern Detection: Detection of SQL injection patterns and suspicious constructs
  • Error Handling: Informative error messages without exposing sensitive information

By default, DBViewer only runs in development or test environments for security reasons. If you need to access it in production (not recommended):

  1. Set an environment variable with a secure random key:
   DBVIEWER_PRODUCTION_ACCESS_KEY=your_secure_random_key
  1. Add an additional constraint in your routes:
   if Rails.env.production?
     constraints ->(req) { req.params[:access_key] == ENV["DBVIEWER_PRODUCTION_ACCESS_KEY"] } do
       mount Dbviewer::Engine, at: "/dbviewer"
     end
   else
     mount Dbviewer::Engine, at: "/dbviewer"
   end
  1. Access the tool with the override parameter: https://yourdomain.com/dbviewer?override_env_check=your_secure_random_key

📝 Security Note

⚠️ Warning: This engine is designed for development purposes. It's not recommended to use it in production as it provides direct access to your database contents. If you must use it in production, ensure it's protected behind authentication and use the production access key mechanism with a strong random key.

🤌🏻 Contributing

Bug reports and pull requests are welcome.

📄 License

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