watcher

README

DB Watcher

DB Watcher is a lightweight, zero-dependency schema introspection library for Go. Mount a real-time, interactive database schema dashboard directly onto your existing web application in seconds.

Dashboard	Relationship Highlighting	Light & Dark Mode

---

Features

• Multi-driver support — SQLite (go-sqlite3, modernc.org/sqlite), PostgreSQL (lib/pq, pgx/v5), MySQL/MariaDB (go-sql-driver/mysql). Instrumented wrappers (e.g. otelsql) are automatically unwrapped.
• Rich schema introspection — tables, columns with full constraint metadata (NOT NULL, DEFAULT, UNIQUE), foreign keys, and indexes (unique, composite, partial).
• Schema diffing — take snapshots over time and compute diffs (added/dropped tables, type changes, index changes).
• Real-time dashboard — the browser polls the backend and updates the ERD without a page reload.
• Interactive ERD — drag tables, click to highlight relationships with animated flow paths, double-click to collapse cards.
• Global search — floating search bar (⌘K or /) to instantly filter tables by name.
• Persistent layout — drag positions and collapsed card states survive page reloads via localStorage.
• Light & Dark mode — follows OS preference, togglable, persisted in localStorage.
• Polymorphic handler — same endpoint serves the HTML dashboard or raw JSON (?format=json / Accept: application/json).

---

Getting Started

Install

go get github.com/esrid/watcher

Quick example

package main

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

    "github.com/esrid/watcher"
    _ "github.com/mattn/go-sqlite3"
)

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

    inspector, err := watcher.NewInspector(db)
    if err != nil {
        panic(err)
    }

    http.HandleFunc("/_debug/schema", watcher.HTTPHandler(inspector))

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

Schema diffing example

d := watcher.NewDiffer(inspector)

// Seed the first snapshot at startup.
if _, err := d.Snapshot(context.Background()); err != nil {
    panic(err)
}

http.HandleFunc("/_debug/schema",         watcher.HTTPHandler(inspector))
http.HandleFunc("/_debug/schema/changes", watcher.ChangesHandler(d))

// POST /_debug/schema/changes  →  take a new snapshot, return the diff
// GET  /_debug/schema/changes  →  return the latest diff (no snapshot)

---

API Reference

NewInspector

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

Detects the underlying driver and returns the matching Inspector implementation. Supported drivers:

Driver	Package
*sqlite3.SQLiteDriver	github.com/mattn/go-sqlite3
*sqlite.Driver	modernc.org/sqlite
*pq.Driver	github.com/lib/pq
*stdlib.Driver	github.com/jackc/pgx/v5/stdlib
*mysql.MySQLDriver	github.com/go-sql-driver/mysql

Instrumented wrappers that implement Unwrap() driver.Driver are resolved automatically. Unknown wrappers fall back to dialect probing.

Inspector

type Inspector interface {
    Tables(ctx context.Context) ([]string, error)
    Columns(ctx context.Context, tableName string) ([]string, error)
    Relations(ctx context.Context, tableName string) ([]string, error)
    Indexes(ctx context.Context, tableName string) ([]Index, error)
    ColumnMeta(ctx context.Context, tableName string) ([]ColumnMeta, error)
}

• Tables — returns all user-defined table names.
• Columns — returns "name|type|pk" descriptors (legacy; prefer ColumnMeta).
• Relations — returns "fromCol -> targetTable.targetCol" descriptors.
• Indexes — returns all non-primary indexes for a table.
• ColumnMeta — returns full column metadata including constraints.

Index

type Index struct {
    Name    string   `json:"name"`
    Unique  bool     `json:"unique"`
    Columns []string `json:"columns"` // in key order
    Partial bool     `json:"partial"` // true when a WHERE predicate exists
}

ColumnMeta

type ColumnMeta struct {
    Name    string `json:"name"`
    Type    string `json:"type"`
    PK      bool   `json:"pk"`
    FK      bool   `json:"fk"`      // set by HTTPHandler from Relations()
    NotNull bool   `json:"notNull"`
    Default string `json:"default"` // empty = no explicit default
    Unique  bool   `json:"unique"`
}

HTTPHandler

func HTTPHandler(inspector Inspector) http.HandlerFunc

Serves the interactive HTML dashboard or, when requested with ?format=json or Accept: application/json, the raw JSON payload:

{
  "tables": [
    {
      "name": "users",
      "cols": [
        {"name": "id",    "type": "int",     "pk": true,  "fk": false, "notNull": true,  "default": "",      "unique": false},
        {"name": "email", "type": "varchar",  "pk": false, "fk": false, "notNull": true,  "default": "",      "unique": true},
        {"name": "bio",   "type": "text",     "pk": false, "fk": false, "notNull": false, "default": "",      "unique": false},
        {"name": "score", "type": "numeric",  "pk": false, "fk": false, "notNull": true,  "default": "0.00",  "unique": false}
      ],
      "indexes": [
        {"name": "idx_users_email",   "unique": true,  "columns": ["email"],      "partial": false},
        {"name": "idx_active_users",  "unique": false, "columns": ["created_at"], "partial": true}
      ]
    }
  ],
  "relations": [
    {"src": "orders", "srcCol": "user_id", "tgt": "users", "tgtCol": "id"}
  ]
}

NewDiffer / ChangesHandler

func NewDiffer(inspector Inspector) *Differ
func (d *Differ) Snapshot(ctx context.Context) (SchemaSnapshot, error)
func (d *Differ) Diff() (SchemaDiff, bool)
func ChangesHandler(d *Differ) http.HandlerFunc

Differ stores the two most recent snapshots and computes the diff between them.

ChangesHandler exposes the diff over HTTP:

• POST — takes a new snapshot, then returns the resulting SchemaDiff as JSON.
• GET — returns the latest SchemaDiff without taking a snapshot. Returns {} if fewer than two snapshots exist.

SchemaDiff shape:

{
  "before": "2024-01-01T00:00:00Z",
  "after":  "2024-01-02T00:00:00Z",
  "addedTables":   ["audit_logs"],
  "droppedTables": [],
  "modified": [
    {
      "table":          "users",
      "addedColumns":   ["deleted_at"],
      "droppedColumns": [],
      "typeChanges":    [{"column": "score", "before": "int", "after": "numeric"}],
      "addedIndexes":   ["idx_users_deleted_at"],
      "droppedIndexes": []
    }
  ]
}

---

Testing

DB Watcher uses Testcontainers for Go for integration tests against real PostgreSQL and MySQL instances.

# Unit tests only
go test ./...

# Full integration tests (requires Docker)
go test -tags=integration -v ./...

---

License

MIT — see LICENSE.

Documentation

Overview

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

Index

• func ChangesHandler(d *Differ) http.HandlerFunc
• func HTTPHandler(inspector Inspector) http.HandlerFunc
• func InspectDatabase(ctx context.Context, inspector Inspector) error
• type ColumnMeta
• type Differ
  • func NewDiffer(inspector Inspector) *Differ
  • func (d *Differ) Diff() (SchemaDiff, bool)
  • func (d *Differ) Snapshot(ctx context.Context) (SchemaSnapshot, error)
• type Index
• type Inspector
  • func NewInspector(db *sql.DB) (Inspector, error)
• type SchemaDiff
• type SchemaSnapshot
• type TableDiff
• type TypeChange

Functions

func ChangesHandler

func ChangesHandler(d *Differ) http.HandlerFunc

ChangesHandler serves schema diffs.

GET → returns the latest SchemaDiff as JSON (empty diff if < 2 snapshots). POST → takes a new snapshot, then returns the resulting SchemaDiff as JSON.

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 ColumnMeta

type ColumnMeta = model.ColumnMeta

ColumnMeta extends raw column data with constraint information.

type Differ

type Differ struct {
	// contains filtered or unexported fields
}

Differ takes schema snapshots and computes diffs between consecutive ones.

func NewDiffer

func NewDiffer(inspector Inspector) *Differ

NewDiffer creates a new Differ.

func (*Differ) Diff

func (d *Differ) Diff() (SchemaDiff, bool)

Diff returns the diff between the two most recent snapshots. Returns zero SchemaDiff and false when fewer than two snapshots exist.

func (*Differ) Snapshot

func (d *Differ) Snapshot(ctx context.Context) (SchemaSnapshot, error)

Snapshot captures the current schema state. Thread-safe. The new snapshot becomes "curr"; the previous "curr" becomes "prev".

type Index

type Index = model.Index

Index describes one database index on a table.

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)
	// Indexes returns a list of all indexes defined on a table (except primary keys).
	Indexes(ctx context.Context, tableName string) ([]Index, error)
	// ColumnMeta returns column descriptors with extra constraints.
	ColumnMeta(ctx context.Context, tableName string) ([]ColumnMeta, 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.

type SchemaDiff

type SchemaDiff struct {
	Before        time.Time   `json:"before"`
	After         time.Time   `json:"after"`
	AddedTables   []string    `json:"addedTables"`
	DroppedTables []string    `json:"droppedTables"`
	Modified      []TableDiff `json:"modified"`
}

SchemaDiff describes what changed between two snapshots.

type SchemaSnapshot

type SchemaSnapshot struct {
	At     time.Time
	Tables map[string]tableSnapshot // keyed by table name
}

SchemaSnapshot is a point-in-time capture of the full schema.

type TableDiff

type TableDiff struct {
	Table          string       `json:"table"`
	AddedColumns   []string     `json:"addedColumns"`
	DroppedColumns []string     `json:"droppedColumns"`
	TypeChanges    []TypeChange `json:"typeChanges"`
	AddedIndexes   []string     `json:"addedIndexes"`
	DroppedIndexes []string     `json:"droppedIndexes"`
}

TableDiff describes changes within a single table.

type TypeChange

type TypeChange struct {
	Column string `json:"column"`
	Before string `json:"before"`
	After  string `json:"after"`
}

TypeChange records a column whose type changed between snapshots.