SQLite Dashboard

A beautiful, feature-rich SQLite database browser and query interface for Rails applications. Mount it as an engine in your Rails app to inspect and query your SQLite databases through a clean, modern interface.

• 🎨 Modern UI with Bootstrap 5 and responsive design
• 🔍 Multiple Database Support - Configure and switch between multiple SQLite databases
• ✨ SQL Syntax Highlighting - CodeMirror editor with SQL syntax highlighting and autocomplete
• 📊 Interactive Query Results - Paginated, sortable results with horizontal scrolling
• 🎯 Quick Table Browse - Click any table name to instantly query it
• ⚡ Fast & Lightweight - No build tools required, works with Rails importmap
• 🔐 Safe for Development - Read-only access to prevent accidental data modification
• ⌨️ Keyboard Shortcuts - Ctrl/Cmd + Enter to execute queries

Add this line to your application's Gemfile:

gem 'sqlite_dashboard'

And then execute:

$ bundle install

In your config/routes.rb:

Rails.application.routes.draw do
  mount SqliteDashboard::Engine => "/sqlite_dashboard"

  # Your other routes...
end

Create an initializer config/initializers/sqlite_dashboard.rb:

SqliteDashboard.configure do |config|
  config.db_files = [
    {
      name: "Development",
      path: Rails.root.join("storage", "development.sqlite3").to_s
    },
    {
      name: "Test",
      path: Rails.root.join("storage", "test.sqlite3").to_s
    }
  ]

  # Or add databases dynamically:
  # config.add_database("Custom DB", "/path/to/database.sqlite3")
end

Start your Rails server and navigate to:

http://localhost:3000/sqlite_dashboard

1. Select a database from the main page
2. Write your SQL query in the syntax-highlighted editor
3. Press Execute Query or use Ctrl/Cmd + Enter
4. View paginated results below

• Click any table name in the left sidebar to instantly query it
• Tables are automatically limited to 100 rows for performance

• Adjust rows per page (10, 25, 50, 100, 500)
• Navigate through pages with First, Previous, Next, Last buttons
• See current position (e.g., "Showing 1 to 25 of 150 rows")

• Ctrl/Cmd + Enter - Execute current query
• Ctrl + Space - Trigger SQL autocomplete
• Escape - Close autocomplete suggestions

⚠️ Warning: This gem provides direct SQL access to your databases.

1. Development Only - Only mount in development environment:

# config/routes.rb
if Rails.env.development?
  mount SqliteDashboard::Engine => "/sqlite_dashboard"
end

2. Authentication - Add authentication with Devise or similar:

# config/routes.rb
authenticate :user, ->(user) { user.admin? } do
  mount SqliteDashboard::Engine => "/sqlite_dashboard"
end

3. Basic Auth - Quick protection with HTTP Basic Auth:

# config/initializers/sqlite_dashboard.rb
SqliteDashboard::Engine.middleware.use Rack::Auth::Basic do |username, password|
  username == ENV['DASHBOARD_USER'] && password == ENV['DASHBOARD_PASS']
end

4. Read-Only Mode - Configure read-only database connections:

config.db_files = [
  {
    name: "Production (Read-Only)",
    path: Rails.root.join("db/production.sqlite3").to_s,
    readonly: true  # Coming in v2.0
  }
]

Override styles by creating app/assets/stylesheets/sqlite_dashboard_overrides.css:

.sqlite-dashboard-container {
  /* Your custom styles */
}

.CodeMirror {
  /* Customize editor appearance */
  font-family: 'Fira Code', monospace;
  font-size: 14px;
}

The SqliteDashboard.configure block accepts the following configuration options:

Type: Array<Hash> Default: [] (automatically loads from config/database.yml if empty) Required: No

Specifies the SQLite database files to make available in the dashboard. Each database should be defined as a hash with :name and :path keys.

SqliteDashboard.configure do |config|
  config.db_files = [
    {
      name: "Development Database",
      path: Rails.root.join("storage", "development.sqlite3").to_s
    },
    {
      name: "Cache Database",
      path: Rails.root.join("storage", "cache.sqlite3").to_s
    },
    {
      name: "Custom Database",
      path: "/absolute/path/to/database.sqlite3"
    }
  ]
end

Automatic Database Discovery: If db_files is empty (default), the gem will automatically discover SQLite databases from your config/database.yml file for the current Rails environment. This works for both single and multiple database configurations (Rails 6+).

Type: Method Parameters: name (String), path (String)

A convenience method to add databases one at a time instead of assigning the entire array:

SqliteDashboard.configure do |config|
  config.add_database("Development", Rails.root.join("db/development.sqlite3").to_s)
  config.add_database("Test", Rails.root.join("db/test.sqlite3").to_s)
  config.add_database("Analytics", "/var/data/analytics.sqlite3")
end

Type: Boolean Default: false Recommended: false (read-only)

Controls whether Data Manipulation Language (DML) statements are allowed. When false, only SELECT queries are permitted, preventing accidental data modification.

SqliteDashboard.configure do |config|
  # Read-only mode (recommended for production-like environments)
  config.allow_dml = false

  # Or allow write operations (USE WITH CAUTION)
  # config.allow_dml = true

  config.db_files = [
    { name: "My Database", path: "db/production.sqlite3" }
  ]
end

⚠️ Security Warning: Enabling allow_dml = true permits INSERT, UPDATE, DELETE, and other write operations. Only enable this in trusted, development-only environments.

# config/initializers/sqlite_dashboard.rb
SqliteDashboard.configure do |config|
  # Option 1: Explicitly define databases
  config.db_files = [
    {
      name: "Primary Database",
      path: Rails.root.join("storage", "development.sqlite3").to_s
    },
    {
      name: "Background Jobs",
      path: Rails.root.join("storage", "jobs.sqlite3").to_s
    }
  ]

  # Option 2: Or use the helper method
  # config.add_database("Development", Rails.root.join("db/development.sqlite3").to_s)
  # config.add_database("Test", Rails.root.join("db/test.sqlite3").to_s)

  # Option 3: Or leave empty to auto-discover from database.yml
  # config.db_files = [] # (default)

  # Security: Disable write operations (recommended)
  config.allow_dml = false
end

You can conditionally configure databases based on the Rails environment:

SqliteDashboard.configure do |config|
  case Rails.env
  when 'development'
    config.db_files = [
      { name: "Dev DB", path: Rails.root.join("storage/development.sqlite3").to_s },
      { name: "Test DB", path: Rails.root.join("storage/test.sqlite3").to_s }
    ]
    config.allow_dml = true  # Allow writes in development

  when 'staging'
    config.db_files = [
      { name: "Staging DB (Read-Only)", path: Rails.root.join("db/staging.sqlite3").to_s }
    ]
    config.allow_dml = false  # Read-only in staging

  when 'production'
    # Generally not recommended to use in production
    # But if you must, make it read-only
    config.db_files = [
      { name: "Production DB", path: Rails.root.join("db/production.sqlite3").to_s }
    ]
    config.allow_dml = false
  end
end

After checking out the repo, run bin/setup to install dependencies. Then, run rake test to run the tests.

To install this gem onto your local machine, run:

bundle exec rake install

To release a new version:

1. Update the version number in version.rb
2. Update the CHANGELOG.md
3. Run bundle exec rake release

Bug reports and pull requests are welcome on GitHub at https://github.com/yourusername/sqlite_dashboard. This project is intended to be a safe, welcoming space for collaboration.

1. Fork the repository
2. Create your feature branch (git checkout -b feature/amazing-feature)
3. Commit your changes (git commit -m 'Add amazing feature')
4. Push to the branch (git push origin feature/amazing-feature)
5. Open a Pull Request

• v1.1 - Export results to CSV/JSON
• v1.2 - Query history and saved queries
• v1.3 - Database schema visualization
• v2.0 - Read-only mode enforcement
• v2.1 - Dark mode theme
• v2.2 - Multi-query execution
• v2.3 - Query performance analytics

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

Created by Your Name

Special thanks to:

• CodeMirror for the SQL editor
• Bootstrap for the UI framework
• Font Awesome for icons

• 🐛 Report bugs
• 💡 Request features
• 📧 Email support

Made with ❤️ for the Rails community