Skip to content

README.md

Latest commit

 

History

History
264 lines (204 loc) · 8.63 KB

README.md

File metadata and controls

264 lines (204 loc) · 8.63 KB

DB Insight Visualizer

Database schema explorer with ER diagram visualization and health metrics.

CI Python 3.10+ FastAPI License: MIT

Overview

DB Insight Visualizer is a web-based tool for exploring and analyzing database schemas. It provides interactive ER (Entity-Relationship) diagram visualization, detailed table and column metadata inspection, and comprehensive health scoring to help DBAs and developers understand and improve their database designs.

Features

Schema Introspection

  • Automatic discovery of all tables, columns, types, primary keys, foreign keys, and indexes
  • Support for SQLite databases (extensible to other databases)
  • Full metadata extraction with row counts and column positions

ER Diagram Visualization

  • Interactive Entity-Relationship diagrams drawn on HTML5 Canvas
  • Table boxes showing column names, types, PK/FK indicators
  • Bezier curve arrows connecting related tables
  • Cardinality indicators (1:N) on relationship lines
  • Color-coded columns: blue for PKs, purple for FKs, green for indexed
  • Zoom in/out controls for large schemas

Health Scoring

Comprehensive schema health analysis with six key metrics:

Metric Description Weight
PK Coverage Percentage of tables with primary keys 20%
FK Density Average FK relationships per table 15%
Nullable Ratio Percentage of non-nullable columns (lower is healthier) 15%
Index Coverage Percentage of FK columns that are indexed 20%
Naming Convention Consistency of snake_case/camelCase naming 15%
Type Consistency Same column name uses same type across tables 15%

Dashboard

  • Overall health score with visual gauge
  • Radar chart showing all six health dimensions
  • Table count, relationship count, column count statistics
  • Recent scan history with timestamps
  • Actionable recommendations for schema improvement

Database Management

  • Register multiple database profiles
  • Trigger on-demand schema scans
  • View scan history with comparison over time
  • Built-in sample e-commerce database for demo

Tech Stack

Component Technology
Backend Python 3.10+ / FastAPI
Frontend Vanilla JavaScript SPA
Database SQLite (metadata store)
Visualization HTML5 Canvas
Testing pytest + httpx
Deployment Docker / Docker Compose

Quick Start

Prerequisites

  • Python 3.10 or higher
  • SQLite3 (usually pre-installed)

Option 1: Local Development

# Clone the repository
git clone https://github.com/ravikishan/db-insight-visualizer.git
cd db-insight-visualizer

# Run the start script
chmod +x start.sh
./start.sh

Option 2: Manual Setup

# Create virtual environment
python3 -m venv venv
source venv/bin/activate

# Install dependencies
pip install -r requirements.txt

# Create sample database
mkdir -p seed_data instance
sqlite3 seed_data/sample.db < seed_data/sample_db.sql

# Start the server
uvicorn app:app --host 0.0.0.0 --port 8025 --reload

Option 3: Docker

docker-compose up --build

The application will be available at http://localhost:8025.

API Reference

Database Profiles

Method Endpoint Description
GET /api/databases List all database profiles
POST /api/databases Register a new database profile
GET /api/databases/{id} Get a specific database profile
DELETE /api/databases/{id} Delete a database profile

Schema Scanning

Method Endpoint Description
POST /api/databases/{id}/scan Trigger a schema scan
GET /api/databases/{id}/tables Get table metadata from latest scan
GET /api/databases/{id}/relationships Get FK relationships from latest scan
GET /api/databases/{id}/health Get health metrics from latest scan
GET /api/databases/{id}/scans Get scan history

System

Method Endpoint Description
GET /api/health Application health check

Frontend Routes

Hash Route View Description
#/ Dashboard Health gauges, stats, radar chart
#/databases Database List Manage database profiles
#/schema/:id ER Diagram Interactive schema visualization
#/table/:id/:name Table Detail Column list with metadata
#/health/:id Health Report Detailed health metrics and recommendations

Sample Database

The included sample database models a typical e-commerce system with 8 tables:

users          - User accounts
categories     - Product categories (self-referencing)
products       - Product catalog
addresses      - User shipping addresses
orders         - Customer orders
order_items    - Line items in orders
reviews        - Product reviews
payments       - Payment records

All tables have proper primary keys, foreign keys, and indexes to demonstrate a well-structured relational database schema.

Project Structure

db-insight-visualizer/
|-- app.py                          # FastAPI application entry point
|-- config.py                       # Application configuration
|-- requirements.txt                # Python dependencies
|-- Dockerfile                      # Container image definition
|-- docker-compose.yml              # Container orchestration
|-- start.sh                        # Quick start script
|-- models/
|   |-- __init__.py                 # Model exports
|   |-- database.py                 # SQLAlchemy engine and session
|   |-- schemas.py                  # ORM models (DatabaseProfile, TableMeta, etc.)
|-- routes/
|   |-- __init__.py                 # Route package
|   |-- api.py                      # REST API endpoints
|   |-- views.py                    # SPA page serving
|-- services/
|   |-- __init__.py                 # Service exports
|   |-- introspector.py             # Schema introspection engine
|   |-- health_scorer.py            # Health scoring calculations
|-- frontend/
|   |-- index.html                  # SPA entry page
|   |-- static/
|       |-- css/app.css             # Application styles
|       |-- js/app.js               # SPA JavaScript application
|-- tests/
|   |-- conftest.py                 # Test fixtures
|   |-- test_api.py                 # API endpoint tests
|   |-- test_models.py              # Model tests
|   |-- test_services.py            # Service logic tests
|-- seed_data/
|   |-- sample_db.sql               # Sample database schema and data
|-- .github/
|   |-- workflows/
|       |-- ci.yml                  # CI pipeline

Health Score Interpretation

Score Range Grade Description
80-100 Excellent Schema follows best practices
60-79 Good Minor improvements recommended
40-59 Fair Several areas need attention
0-39 Poor Significant schema issues detected

Testing

# Run all tests
pytest tests/ -v

# Run with coverage
pytest tests/ -v --cov=. --cov-report=term-missing

# Run specific test file
pytest tests/test_services.py -v

Development

Adding Support for New Databases

  1. Extend SchemaIntrospector with a new connection method
  2. Implement the same interface (get_tables, get_columns, get_foreign_keys, etc.)
  3. Add the database type to config.py

Adding New Health Metrics

  1. Add the scoring method to HealthScorer
  2. Update full_report() to include the new metric
  3. Update the weight configuration in config.py
  4. Update the frontend radar chart labels

Contributing

  1. Fork the repository
  2. Create a 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

License

This project is licensed under the MIT License - see the LICENSE file for details.

Acknowledgments

Built with care for database administrators and developers who want to understand and improve their database schemas.