Web Database Viewer

A modern, lightweight, privacy-focused web interface to browse, inspect, and manage MySQL, PostgreSQL, and SQLite databases.

Key Features • Architecture • Quick Start • Configuration • API Reference • Contributing • License

---

Overview

Web Database Viewer is an open-source, web-based database management interface designed for developers, database administrators, and data analysts who need a nimble, responsive client without the bloat of heavyweight desktop GUI tools like DBeaver or phpMyAdmin.

Engineered with a modular Flask backend and a modern Vue 3 / Tailwind CSS reactive frontend, it provides an intuitive web interface for managing multi-engine databases on your local machine, in Docker, or across remote cloud instances. All frontend assets (fonts, icons, stylesheets, scripts) are 100% vendored locally, ensuring full functionality in strictly air-gapped or offline development environments.

---

Key Features

Multi-Server Profile Management

• Multi-Engine Connections: Native support for MySQL, PostgreSQL, and SQLite.
• Zero-Restart Profile Switching: Create, modify, and switch between isolated server connection profiles in real time directly from the UI.
• Persistent Configuration: Profile credentials and custom database bookmark lists are safely stored in a local SQLite application database (profiles.db).
• SSL and Advanced Connection Options: Configurable SSL modes (require, prefer, disable), schemas (PostgreSQL search path), ports, and hosts.

Interactive Data Grid and Table Viewer

• High-Performance Pagination: Dynamically paginate through hundreds of thousands of records with configurable page sizes (25, 50, 100, 250 rows).
• Column Sorting and Instant Search: Sort ascending/descending by clicking table headers; execute real-time keyword search across all indexed columns.
• Dedicated Cell Editor Modal: Double-click cell editing in an intuitive modal with "Set to NULL" toggle, character counter, and keyboard shortcuts (Cmd/Ctrl+Enter to save, Esc to cancel).
• Value Inspector Modal (JSON & Text): Hover over cells with JSON or long text to open a dedicated syntax-highlighted code inspector with Formatted/Raw toggle, character/line counts, one-click clipboard copy, and quick edit shortcut.
• Batch Row Deletion (Selection Mode): Toggleable multi-row selection mode that displays checkboxes on demand without cluttering the normal view, complete with "Select All" header toggle, row selection highlights, and a batch "Delete Selected (N)" confirmation action.
• Column Visibility & Reordering: Clever, minimal toolbar dropdown allowing selective column hiding/showing and drag-and-drop reordering with real-time search filtering, quick actions ("Show All", "Only PKs", "Reset"), and per-table localStorage persistence.
• Foreign Key Badges: Automatically inspects schema constraints and highlights relational foreign keys with reference tooltips.

Comprehensive Schema and Relationship Inspector (Visual ER Diagram)

• Interactive Visual Schema Map (ER Diagram): Full interactive canvas rendering table entities and foreign key relationship connectors with smooth cubic Bézier curves and directional arrowhead markers.
• Canvas Pan & Zoom: Infinite canvas with blueprint grid background, smooth mouse wheel zooming (35% to 200%), and background click-and-drag panning.
• Draggable Table Cards: Rearrange and customize table positions freely on canvas with automatic live recalculation of connector lines. Layouts persist in localStorage per database.
• Auto-Arrange Grid: Single-click intelligent multi-column layout organizing all tables and relationships cleanly.
• Entity Details & Navigation: Each card visualizes table name, column count, PK/FK badges, column data types, and a direct shortcut to jump straight to its data grid.
• Relationship Hover Badges: Hover over connector lines or foreign key columns to highlight the link and inspect the exact source.col → target.col relationship in a focused midpoint pill.
• Export Mermaid ERD: Instant one-click clipboard export generating standard Mermaid-compatible erDiagram syntax ready to paste into GitHub READMEs, Notion, or Obsidian.
• Table Search Filter: Quick filter input on the canvas to instantly isolate and locate specific tables in large relational schemas.
• Table and View Breakdown: List physical tables alongside SQL views in categorized sidebars.
• Column Details: Detailed field inspection showing SQL column data types, nullability, default values, primary keys, and auto-increment properties.
• Foreign Key Mappings: Interactive view showing source column, target table, and target column relationships.

Interactive SQL Console

• Arbitrary Query Execution: Run custom SELECT, INSERT, UPDATE, DELETE, JOIN, or CREATE statements directly from your browser.
• Flexible Docking (Right Sidebar vs. Bottom Drawer): Toggle console placement between a sleek right sidebar and an expansive bottom drawer with smooth CSS slide animations.
• Persistent Preference: Console position preference (right or bottom) and database selection are automatically saved in localStorage.
• Dual-Pane Developer Workbench: When docked at the bottom, the console automatically reorganizes into a two-column workspace: query editor on the left and full-height scrollable results table on the right.
• Dual Output Mode: Automatically formats SELECT queries into an interactive tabular view, or displays affected row counts and execution status for DDL/DML queries.
• Keyboard Shortcuts: Ctrl+Enter / Cmd+Enter to execute query instantly; Escape to close the console.
• Universal Database & SQLite Support: Query MySQL, PostgreSQL, and standalone SQLite files seamlessly.
• Graceful Error Reporting: Descriptive SQL execution error banners highlighting query syntax mistakes without crashing the app.

Standalone SQLite Explorer

• Drag-and-Drop File Upload: Inspect any .db, .sqlite, or .sqlite3 file on demand.
• File Registry: Uploaded files are cataloged with file metadata (size, upload timestamp, last accessed).
• No Database Server Required: Perform instant table browsing and SQL querying on standalone files without spinning up daemon processes.

Import and Export

• CSV Export: Stream filtered or full table datasets directly into standard .csv files for spreadsheets and reporting.
• Database SQL Dump: Export entire databases as .sql scripts using automated mysqldump and pg_dump integration.
• SQL Import: Upload and restore schema/data directly into your target database.

Privacy and Offline-First Architecture

• No External CDN Calls: All fonts (Outfit font family), Phosphor SVG icon sets, Vue 3 runtime, and Tailwind CSS engine are bundled directly inside /static/vendor/.
• Zero Telemetry: No analytics, tracking pixels, or external API pings. Your data never leaves your environment.

---

Architecture

The application is structured into decoupled, single-responsibility modules:

Web-Database-Viewer/
├── adapters/                  # Engine-specific database abstraction layer
│   ├── __init__.py            # Adapter registry
│   ├── mysql.py               # PyMySQL adapter (introspection, queries, mysqldump)
│   ├── postgres.py            # Psycopg adapter (schemas, quoting, pg_dump)
│   └── sqlite.py              # SQLite3 adapter (file-based introspection)
├── routes/                    # Flask blueprints (RESTful endpoints & views)
│   ├── __init__.py            # Blueprint registry
│   ├── profiles.py            # Server profile CRUD and active profile switcher
│   ├── databases.py           # Table inspector, data grid, mutation, SQL console
│   └── sqlite_viewer.py       # Standalone SQLite file viewer & upload registry
├── services/                  # Business logic & persistence services
│   ├── __init__.py            # Service exports
│   ├── profiles.py            # SQLite metadata storage (`profiles.db`) & migrations
│   └── sqlite_storage.py      # Uploaded SQLite file management & file registry
├── static/                    # Vendored static assets (100% offline)
│   ├── fonts/                 # Outfit web fonts (woff2)
│   └── vendor/                # Phosphor icons, Tailwind script, Vue 3 runtime
├── templates/                 # Jinja2 HTML templates
│   ├── base.html              # Master layout, navigation, dark mode, toast alerts
│   ├── profile_selector.html  # Server profile management dashboard
│   ├── index.html             # Profile database list dashboard
│   ├── database.html          # Table data grid, schema viewer, and SQL console
│   └── sqlite_viewer.html     # Dedicated SQLite file viewer interface
├── app.py                     # Application entry point and JSON provider setup
├── requirements.txt           # Python dependency specifications
├── run.sh                     # Launch script
└── LICENSE                    # GNU General Public License v3.0

---

Quick Start

Prerequisites

• Python 3.10+
• (Optional) MySQL Client (mysql, mysqldump) if you plan to import/export MySQL dumps.
• (Optional) PostgreSQL Client (psql, pg_dump) if you plan to import/export PostgreSQL dumps.

1. Clone the Repository

git clone https://github.com/ZaidK07/Web-Database-Viewer.git
cd Web-Database-Viewer

2. Create and Activate a Virtual Environment

# On macOS / Linux
python3 -m venv .venv
source .venv/bin/activate

# On Windows (PowerShell)
python -m venv .venv
.venv\Scripts\Activate.ps1

3. Install Dependencies

pip install --upgrade pip
pip install -r requirements.txt

4. Configure Environment Variables (Optional)

Create a .env file in the root directory to customize defaults:

# Default Fallback Connection (optional)
DB_HOST=localhost
DB_PORT=3306
DB_USER=root
DB_PASSWORD=your_password

5. Launch the Server

Using the convenience launcher:

chmod +x run.sh
./run.sh

Or directly via Python:

python3 -m app

6. Access the Web Interface

Open your browser and navigate to:

http://127.0.0.1:10992

---

Configuration

Default Ports and Connection Details

Database Engine	Default Port	Default User	Default Schema
MySQL	3306	root	N/A
PostgreSQL	5432	postgres	public
SQLite	Local file	N/A	N/A

Application Files

• profiles.db: SQLite database created automatically on first run to store profile connection definitions and metadata. Automatically gitignored for security.
• uploads/: Directory where standalone uploaded SQLite databases and registry records are stored.

---

API Reference

The backend exposes a clean JSON REST API used by the frontend:

Server Profiles

Method	Endpoint	Description
GET	/api/profiles	List all saved server profiles and current active profile
POST	/api/profiles	Create a new connection profile
PUT	/api/profiles/<name>	Update an existing profile configuration
DELETE	/api/profiles/<name>	Remove a server profile
PUT	/api/profiles/active	Switch the active server profile

Database and Table Operations

Method	Endpoint	Description
GET	/api/p/<profile>/dbs/list	Get configured databases for a profile
POST	/api/p/<profile>/dbs	Add a database to the profile watchlist
DELETE	/api/p/<profile>/dbs/<dbname>	Remove a database from the watchlist
GET	/api/p/<profile>/dbs/<dbname>/check	Test connectivity to a specific database
GET	/api/p/<profile>/db/<dbname>/schema	Fetch list of tables, views, and column schemas
GET	/api/p/<profile>/db/<dbname>/table/<table\>/data	Paginated rows, sorting, filtering, and foreign keys
POST	/api/p/<profile>/db/<dbname>/table/<table\>/row	Insert a new row
PUT	/api/p/<profile>/db/<dbname>/table/<table\>/row	Update an existing row by primary key
DELETE	/api/p/<profile>/db/<dbname>/table/<table\>/row	Delete a row by primary key
POST	/api/p/<profile>/sql	Execute arbitrary SQL queries
GET	/api/p/<profile>/db/<dbname>/table/<table\>/export/csv	Download table records as CSV
GET	/api/p/<profile>/db/<dbname>/export	Download full SQL dump of database
POST	/api/p/<profile>/dbs/import	Upload and restore a SQL dump file

Standalone SQLite Viewer

Method	Endpoint	Description
GET	/api/sqlite/files	List all uploaded / registered SQLite files
POST	/api/sqlite/upload	Upload a new .sqlite or .db file
DELETE	/api/sqlite/files/<id>	Delete an uploaded SQLite database file

---

Contributing

Contributions make the open-source community an excellent place to learn, build, and share. Any contributions you make are greatly appreciated.

Please see our CONTRIBUTING.md for guidelines on code standards, development setup, and submitting pull requests.

Please also review our CODE_OF_CONDUCT.md before participating in this project.

---

Security and Safe Usage

Web Database Viewer connects directly to database servers and can execute DDL/DML queries. If deploying in team environments:

• Do not expose without authentication: Always place behind a secure reverse proxy (such as Nginx, Caddy, or Cloudflare Tunnel) with HTTP Basic Auth, OAuth2, or VPN access.
• Review our complete SECURITY.md for vulnerability disclosure procedures and configuration recommendations.

---

License

This project is licensed under the terms of the GNU General Public License v3.0 (GPLv3).

You are free to use, modify, study, and share this software under the conditions of the GPLv3. See the LICENSE file for full license text and terms.