watcher

README

DB Watcher

DB Watcher is a lightweight Go library for real-time database schema introspection. Mount an interactive ERD dashboard and schema change tracker directly onto your existing HTTP server — no external dependencies, no separate process.

ERD Dashboard	Table Card Detail	Schema Changes

---

Features

• Multi-driver — SQLite, PostgreSQL, MySQL/MariaDB. Instrumented wrappers (otelsql, etc.) unwrapped automatically.
• Rich introspection — columns with NOT NULL, DEFAULT, UNIQUE, PKs, FKs, indexes (unique, composite, partial).
• Interactive ERD — draggable cards, animated FK paths, click to highlight relationships, double-click to collapse.
• Built-in schema changes — the Changes tab is included in the dashboard. No second handler needed — diffs are tracked and delivered by the same polling request as the schema.
• Global search — ⌘K or / filters tables by name; filter stays active after closing the overlay.
• Persistent layout — card positions and collapsed states saved in localStorage.
• Light & Dark mode — follows OS preference, togglable, persisted across reloads.
• JSON API — every endpoint serves the HTML dashboard or raw JSON.

---

Installation

go get github.com/esrid/watcher

---

Setup

One handler is all you need:

package main

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

    "github.com/esrid/watcher"
    _ "github.com/mattn/go-sqlite3" // blank-import your driver
)

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

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

    http.HandleFunc("/_schema", watcher.HTTPHandler(inspector))

    log.Println("dashboard → http://localhost:8080/_schema")
    log.Fatal(http.ListenAndServe(":8080", nil))
}

The dashboard polls itself every 1.5 seconds. Each poll takes a schema snapshot, computes the diff from the previous state, and delivers both in a single response. The ERD updates live. The Changes tab shows diffs as soon as the schema changes — no second handler, no background goroutine, nothing else to configure.

You can mount the handler on any path you want — /, /_debug/schema, /internal/db, anything. The dashboard automatically polls the path it is served from, so no configuration is needed on your side.

---

Dashboard

ERD tab

Interaction	Action
Drag card header	Move the card
Click card header	Highlight FK relationships
Double-click card header	Collapse / expand card
⌘K or /	Open table search
Esc	Close search (filter stays active)
Ctrl/⌘ + scroll	Zoom
Ctrl/⌘ + / - / 0	Zoom in / out / reset

Card positions and collapsed states are saved in localStorage and restored on reload.

Changes tab

Shows the diff between the last two polls: tables added or dropped, columns added or dropped, type changes, indexes added or dropped. The last non-empty diff is persisted — changes stay visible after the schema stabilizes and are only replaced when the next migration triggers a new diff.

---

Supported Drivers

Driver type	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

Wrappers that implement Unwrap() driver.Driver (e.g. otelsql) are resolved automatically. Unknown wrappers fall back to dialect probing.

---

API Reference

NewInspector

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

Detects the driver and returns the matching Inspector. Returns an error if the driver is not recognised and dialect probing fails.

HTTPHandler

func HTTPHandler(inspector Inspector) http.HandlerFunc

Serves the dashboard. On each JSON poll it takes a schema snapshot, computes the diff, and returns everything in one payload. Responds with HTML for browser requests, JSON when ?format=json is set or Accept: application/json is present.

JSON response shape:

{
  "tables": [
    {
      "name": "users",
      "cols": [
        { "name": "id",    "type": "INTEGER", "pk": true,  "fk": false, "notNull": true,  "default": "",    "unique": false },
        { "name": "email", "type": "TEXT",    "pk": false, "fk": false, "notNull": true,  "default": "",    "unique": true  }
      ],
      "indexes": [
        { "name": "idx_active_users", "unique": false, "columns": ["created_at"], "partial": true }
      ]
    }
  ],
  "relations": [
    { "src": "orders", "srcCol": "user_id", "tgt": "users", "tgtCol": "id" }
  ],
  "diff": {
    "before": "2024-01-15T10:00:00Z",
    "after":  "2024-01-15T10:01:30Z",
    "addedTables":   ["audit_log"],
    "droppedTables": [],
    "modified": [
      {
        "table":          "users",
        "addedColumns":   ["deleted_at"],
        "droppedColumns": [],
        "typeChanges":    [],
        "addedIndexes":   ["idx_users_deleted_at"],
        "droppedIndexes": []
      }
    ]
  }
}

diff is omitted from the response until at least two polls have occurred (i.e. there is a before and after to compare). After that it is always present, even when empty.

Advanced: ChangesHandler

func ChangesHandler(d *Differ) http.HandlerFunc

For use-cases that need diff data outside the dashboard — scripts, CI health checks, other clients. Build a Differ manually and mount this handler wherever suits:

differ := watcher.NewDiffer(inspector)
differ.Watch(context.Background(), 10*time.Second, nil)

http.HandleFunc("/api/schema/diff", watcher.ChangesHandler(differ))

• GET — returns the current SchemaDiff as JSON.
• POST — takes a new snapshot, returns the resulting diff.

---

Testing

# Unit tests
go test ./...

# Integration tests — requires Docker (Testcontainers, real Postgres and MySQL)
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)
  • func (d *Differ) Watch(ctx context.Context, interval time.Duration, onErr func(error))
• type Index
• type Inspector
  • func NewInspector(db *sql.DB) (Inspector, error)
• type SchemaDiff
  • func (sd SchemaDiff) Empty() bool
• type SchemaSnapshot
• type TableDiff
• type TableSnapshot
• 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 last non-empty diff, or the latest empty diff if no changes have ever occurred. 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".

func (*Differ) Watch

func (d *Differ) Watch(ctx context.Context, interval time.Duration, onErr func(error))

Watch takes a snapshot on every tick of interval until ctx is cancelled. It calls Snapshot immediately on the first tick, so the first diff is available after two intervals. Snapshot errors are logged and do not stop the loop.

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.

func (SchemaDiff) Empty

func (sd SchemaDiff) Empty() bool

Empty returns true if there are no changes in the diff.

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 TableSnapshot

type TableSnapshot struct {
	Columns []ColumnMeta
	Indexes []Index
}

TableSnapshot describes a single table structure in a snapshot.

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.