DB Insight Visualizer

Database schema explorer with ER diagram visualization and health metrics.

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

• FastAPI - Modern Python web framework
• SQLAlchemy - Python SQL toolkit
• SQLite - Embedded relational database

---

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