Skip to content

sonani-pankaj/data-dictionary-java

BranchesTags

Repository files navigation

Data Dictionary

An interactive database data dictionary application built with Java 21 and Spring Boot 3.4. It connects to PostgreSQL and SQL Server databases, introspects their schemas, and presents a browsable catalog of tables, columns, indexes, constraints, foreign keys, and ER diagrams.

Tech Stack

Layer Technology
Language Java 21
Framework Spring Boot 3.4.1 (Web, Data JPA, Security)
Templates Thymeleaf 3.1 + Layout Dialect 3.4
App Database H2 (file-based, ./data/dictionary)
Build Gradle Kotlin DSL (wrapper included)
Diagrams Mermaid.js v10 (CDN)
Containers Docker multi-stage build, Docker Compose
Target DBs PostgreSQL 16, SQL Server 2022

Features

  • Schema Sync — CLI command to introspect target databases and store metadata in H2
  • Dashboard — Lists all synced databases with table counts and health indicators
  • Database Detail — Tables grouped by schema with row counts, PCI/PII flags, and incoming references
  • Table Detail — Columns, indexes, constraints, foreign keys, and inline ER diagrams
  • ER Diagrams — Full database and per-table diagrams rendered with Mermaid.js
  • Search — Full-text search across tables and columns
  • PCI/PII Tracking — Auto-detected and manual classification with override support
  • Admin Panel — Inline editing of descriptions, notes, glossary, and PCI/PII tags
  • Change Log — Tracks schema changes between sync runs
  • Export — Markdown export for tables and full databases

Prerequisites

  • Java 21 (JDK)
  • Docker & Docker Compose (for target databases)

Quick Start

1. Start Target Databases

docker compose up -d postgres mssql mssql-init

This starts PostgreSQL 16 (port 5432) and SQL Server 2022 (port 1433) with sample schemas pre-loaded.

2. Run the Application

# Windows
.\gradlew.bat bootRun

# Linux / macOS
./gradlew bootRun

The app starts on http://localhost:3000.

3. Sync Databases

# Windows
.\gradlew.bat bootRun --args="--sync"

# Linux / macOS
./gradlew bootRun --args="--sync"

This introspects all configured target databases and stores metadata in the H2 database.

4. Admin Login

Navigate to http://localhost:3000/admin/login and enter password: admin

Once logged in you can edit table/column descriptions, set PCI/PII classifications, and update database notes and glossary entries inline.

Running with Docker Compose (Full Stack)

# Build and start everything (app + databases)
docker compose up -d --build

# View logs
docker compose logs -f app

# Stop all services
docker compose down

The app runs at http://localhost:3000 inside the container. Data is persisted in Docker volumes (app-data, pg-data, mssql-data).

Configuration

All settings are in src/main/resources/application.properties. Key environment variables:

Variable Default Description
PORT 3000 Application port
ADMIN_PASSWORD admin Admin password (plaintext or BCrypt hash)
DB_ENGINE_1 postgres Engine type (postgres or mssql)
DB_HOST_1 localhost Target database host
DB_PORT_1 5432 Target database port
DB_NAME_1 sampledb Target database name
DB_USER_1 postgres Target database user
DB_PASS_1 postgres Target database password
SCHEMA_INCLUDE (empty = all) Comma-separated schemas to include
SCHEMA_EXCLUDE (empty = none) Comma-separated schemas to exclude

Add more connections by incrementing the suffix: DB_ENGINE_2, DB_HOST_2, etc.

Project Structure

src/main/java/com/datadictionary/
├── DataDictionaryApplication.java   # Entry point + CLI sync runner
├── config/
│   └── SecurityConfig.java          # Spring Security (permit all, CSRF config)
├── controller/
│   ├── AdminController.java         # Admin login, API endpoints for editing
│   ├── DatabaseController.java      # Dashboard, DB detail, diagrams, PCI/PII pages
│   ├── DiagramController.java       # ER diagram JSON API
│   ├── ExportController.java        # Markdown export endpoints
│   └── SearchController.java        # Search page
├── entity/                          # JPA entities (10 tables)
├── repository/                      # Spring Data JPA repositories
└── service/
    ├── DatabaseService.java         # DB introspection & sync logic
    └── SearchService.java           # JDBC-based search

src/main/resources/
├── application.properties           # App configuration
├── schema.sql                       # H2 schema DDL
├── templates/                       # Thymeleaf templates
│   ├── layout.html                  # Base layout
│   ├── dashboard.html               # Home page
│   ├── database.html                # Database detail
│   ├── table-detail.html            # Table detail
│   ├── search.html                  # Search results
│   └── ...                          # Other pages
└── static/
    ├── css/style.css                # Styles
    └── js/
        ├── admin.js                 # Inline editing & PCI/PII modal
        └── diagram.js               # Mermaid ER diagram rendering

Database Backup & Restore

The application stores all its data in an H2 file database at ./data/dictionary. The H2 files are:

  • data/dictionary.mv.db — Main data file
  • data/dictionary.trace.db — Trace/log file (optional, can be deleted)

Backup

Option 1: File Copy (Recommended)

Stop the application first, then copy the database files:

# Stop the app (Ctrl+C if running in terminal)

# Windows
mkdir backup
copy data\dictionary.mv.db backup\dictionary.mv.db

# Linux / macOS
mkdir -p backup
cp data/dictionary.mv.db backup/dictionary.mv.db

Option 2: File Copy While Running (H2 AUTO_SERVER mode)

H2 in AUTO_SERVER mode supports safe file copy while running, but stopping the app first is safer:

# Windows
copy data\dictionary.mv.db backup\dictionary_%date:~-4%%date:~4,2%%date:~7,2%.mv.db

# Linux / macOS
cp data/dictionary.mv.db backup/dictionary_$(date +%Y%m%d).mv.db

Option 3: SQL Dump via H2 Shell

# Windows
java -cp build/libs/*.jar org.h2.tools.Script ^
  -url "jdbc:h2:file:./data/dictionary" -user sa -script backup/dump.sql

# Linux / macOS
java -cp build/libs/*.jar org.h2.tools.Script \
  -url "jdbc:h2:file:./data/dictionary" -user sa -script backup/dump.sql

This creates a full SQL dump that can be used for migration or archival.

Option 4: Docker Volume Backup

If running via Docker Compose, back up the app-data volume:

docker run --rm -v data-dictionary-java_app-data:/data -v $(pwd)/backup:/backup \
  alpine tar czf /backup/dictionary-backup.tar.gz -C /data .

Restore

From File Copy

# Stop the app first

# Windows
copy backup\dictionary.mv.db data\dictionary.mv.db

# Linux / macOS
cp backup/dictionary.mv.db data/dictionary.mv.db

From SQL Dump

Delete the existing database files, then run the dump:

# Remove old data
rm data/dictionary.mv.db

# Windows
java -cp build/libs/*.jar org.h2.tools.RunScript ^
  -url "jdbc:h2:file:./data/dictionary" -user sa -script backup/dump.sql

# Linux / macOS
java -cp build/libs/*.jar org.h2.tools.RunScript \
  -url "jdbc:h2:file:./data/dictionary" -user sa -script backup/dump.sql

Docker Volume Restore

docker compose down
docker run --rm -v data-dictionary-java_app-data:/data -v $(pwd)/backup:/backup \
  alpine sh -c "rm -rf /data/* && tar xzf /backup/dictionary-backup.tar.gz -C /data"
docker compose up -d

Pages

URL Description
/ Dashboard — all synced databases
/db/{id} Database detail — tables by schema
/table/{id} Table detail — columns, FKs, indexes
/diagram/{dbId} Full ER diagram for a database
/search?q=... Search tables and columns
/pci-pii/{dbId} PCI/PII flagged columns
/relationships/{dbId} Foreign key relationships
/changes/{dbName} Schema change log
/glossary/{dbId} Database glossary
/overview/{dbId} Database overview & admin notes
/admin/login Admin login
/admin/connections Manage database connections
/export/md/table/{id} Export table as Markdown
/export/md/db/{id} Export full database as Markdown

About

No description, website, or topics provided.

Resources

Readme
Activity

Stars

0 stars

Watchers

0 watching

Forks

0 forks
Report repository

Releases

No releases published

Packages

 
 
 

Contributors

Languages