watcher

README

👁️ DB Watcher

DB Watcher is a lightweight, zero-dependency schema introspection library for Go. It allows developers to mount a beautiful, real-time database schema inspector dashboard directly onto their existing web application in seconds.

---

✨ Features

• Multi-DB & Multi-Driver Support: Out-of-the-box support for SQLite (both CGO go-sqlite3 and pure-Go modernc.org/sqlite), PostgreSQL (lib/pq and pgx/v5), and MySQL (go-sql-driver/mysql).
• Real-time Schema Monitoring: The web dashboard automatically polls the backend and updates the schema layout in real-time when tables, columns, or foreign keys change, with zero page refreshes.
• Persistent Layout Customization: Drag-and-drop tables anywhere to organize your ERD layout. Dragged positions are preserved across updates and page reloads via localStorage.
• Interactive Relationship Mapping: Click on any table header to instantly highlight its incoming and outgoing foreign key relationships.
• Polymorphic HTTP Handler: A single HTTP handler serves both the rich HTML dashboard and a raw JSON API (?format=json), avoiding routing conflicts in your application.

---

🚀 Getting Started

1. Install

go get github.com/esrid/watcher

2. Quick Example

Instantiate your database connection, pass it to NewInspector, and mount the handler at any route you like:

package main

import (
	"database/sql"
	"fmt"
	"net/http"

	"github.com/esrid/watcher"

	_ "github.com/mattn/go-sqlite3" // or pure-Go _ "modernc.org/sqlite"
)

func main() {
	// Connect to your database
	db, err := sql.Open("sqlite3", "app.db")
	if err != nil {
		panic(err)
	}
	defer db.Close()

	// 1. Initialize the schema inspector
	inspector, err := watcher.NewInspector(db)
	if err != nil {
		panic(err)
	}

	// 2. Register the debug dashboard route
	// You can mount this at any custom endpoint (e.g., behind auth middlewares)
	http.HandleFunc("/_debug/schema", watcher.HTTPHandler(inspector))

	// Your business routes
	http.HandleFunc("/", func(w http.ResponseWriter, r *http.Request) {
		w.Write([]byte("Welcome to my application!"))
	})

	fmt.Println("🚀 Dashboard active at http://localhost:8080/_debug/schema")
	http.ListenAndServe(":8080", nil)
}

---

🛠️ API Reference

watcher.NewInspector

func NewInspector(db *sql.DB) (Inspector, error)

Detects the underlying database driver type and returns a concrete Inspector instance. Supported drivers are:

• *sqlite3.SQLiteDriver (github.com/mattn/go-sqlite3)
• *sqlite.Driver (modernc.org/sqlite pure Go SQLite)
• *pq.Driver (github.com/lib/pq PostgreSQL)
• *stdlib.Driver (github.com/jackc/pgx PostgreSQL stdlib)
• *mysql.MySQLDriver (github.com/go-sql-driver/mysql MySQL / MariaDB)

watcher.HTTPHandler

func HTTPHandler(inspector Inspector) http.HandlerFunc

An http.Handler that serves:

• HTML Dashboard: For web browsers and standard GET requests.
• JSON Payload: When requested with ?format=json or Accept: application/json headers. Returns:

  {
    "tables": [
      {
        "name": "users",
        "cols": [
          {"name": "id", "type": "int", "pk": true, "fk": false},
          {"name": "email", "type": "varchar", "pk": false, "fk": false}
        ]
      }
    ],
    "relations": [
      {
        "src": "orders",
        "srcCol": "user_id",
        "tgt": "users",
        "tgtCol": "id"
      }
    ]
  }
  

---

🧪 Development & Testing

DB Watcher uses Testcontainers for Go to run high-fidelity integration tests against real instances of PostgreSQL and MySQL.

Run Unit Tests

go test ./...

Run Real-Database Integration Tests

(Requires Docker to be running locally)

go test -tags=integration -v ./...

---

📄 License

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

watcher

Documentation

Overview

Package watcher provides real-time, interactive database schema introspection and visualization for SQLite, PostgreSQL, and MySQL databases.

Index

• func HTTPHandler(inspector Inspector) http.HandlerFunc
• func InspectDatabase(ctx context.Context, inspector Inspector) error
• type Inspector
  • func NewInspector(db *sql.DB) (Inspector, error)

Functions

func HTTPHandler

func HTTPHandler(inspector Inspector) http.HandlerFunc

HTTPHandler returns an http.HandlerFunc that serves the schema watcher dashboard.

Behavior is polymorphic:

• Web browser / HTML view: Serves the interactive drag-and-drop dashboard.
• Machine / JSON view: Serves the raw schema payload when requested via the "?format=json" query parameter or an "Accept: application/json" header.

func InspectDatabase

func InspectDatabase(ctx context.Context, inspector Inspector) error

Types

type Inspector

type Inspector interface {
	// Tables returns a list of all user-defined table names.
	Tables(ctx context.Context) ([]string, error)
	// Columns returns column descriptors for a table in "name|type|pk" format.
	Columns(ctx context.Context, tableName string) ([]string, error)
	// Relations returns foreign-key descriptors for a table in "fromCol -> targetTable.targetCol" format.
	Relations(ctx context.Context, tableName string) ([]string, error)
}

Inspector defines the contract for database schema introspection. Implementations query database metadata catalog schemas to extract table structures, columns, and foreign key relationships.

func NewInspector

func NewInspector(db *sql.DB) (Inspector, error)

NewInspector automatically detects the driver type of the provided *sql.DB connection and returns the corresponding Inspector implementation.

Supported drivers are:

• SQLite: CGO "*sqlite3.SQLiteDriver" (github.com/mattn/go-sqlite3) and pure Go "*sqlite.Driver" (modernc.org/sqlite)
• PostgreSQL: "*pq.Driver" (github.com/lib/pq) and "*stdlib.Driver" (github.com/jackc/pgx)
• MySQL / MariaDB: "*mysql.MySQLDriver" (github.com/go-sql-driver/mysql)

Instrumented wrappers such as otelsql are also supported: NewInspector first attempts to unwrap the driver via the Unwrap() driver.Driver interface, and if that is not available it falls back to dialect probing (one lightweight query per candidate dialect).

Returns an error if the database driver is unsupported.