DB Metadata

A web application for automatic extraction, storage, and analysis of MySQL database metadata. The system connects to one or more MySQL databases, imports their schema into a dedicated metadata repository, computes structural statistics, and stores historical snapshots in InfluxDB for time-series analysis.

---

Overview

DB Metadata is a client-server application designed to automate the collection and management of relational database metadata.

The application connects to arbitrary MySQL databases, extracts metadata from the INFORMATION_SCHEMA system catalog, and stores it in a dedicated metadata repository. Users can browse imported database schemas, view structural statistics, and save snapshots of these statistics to InfluxDB, enabling historical analysis of database evolution.

The project consists of three main components:

• Backend — FastAPI service responsible for metadata extraction, storage, statistics generation, and REST API.

• Frontend — React web application providing an interface for importing databases and viewing metadata.

• Databases

  • MySQL — stores extracted metadata.
  • InfluxDB — stores timestamped statistical snapshots.

---

Features

• Import metadata from arbitrary MySQL databases

• Automatic extraction from INFORMATION_SCHEMA

• Store metadata in a dedicated relational catalog

• Support for:

  • databases
  • tables
  • columns
  • primary keys
  • foreign keys
  • unique constraints
  • composite keys

• Browse imported database schemas

• Generate structural statistics, including:

  • number of tables
  • number of columns
  • number of keys
  • row counts
  • other schema-related metrics

• Save statistics as timestamped snapshots

• View historical statistics stored in InfluxDB

• REST API with interactive documentation

• Docker Compose deployment

---

Architecture

---

Project Structure

db-metadata/
│
├── backend/              # FastAPI application
├── frontend/             # React application
├── sql/
│    ├── metadata/        # SQL scripts initializing metadata schema
│    └── examples/        # SQL scripts initializing example schemas
├── docker-compose.yml
├── Dockerfile
└── README.md

---

Running the Project

Prerequisites

• Docker
• Docker Compose

1. Clone the repository

git clone https://github.com/yrmint/db-metadata.git
cd db-metadata

2. Configure environment variables

Edit the respective part of backend:environment in docker-compose to set MySQL environment variables.

Example:

MYSQL_USER: root
MYSQL_PASSWORD: password

Run metadata schema init scripts from sql/metadata.

3. Start the application

docker compose up --build

4. Open the application

Frontend:

http://localhost:5173

Backend API:

http://localhost:8081

Interactive API documentation:

http://localhost:8081/docs

InfluxDB UI:

http://localhost:8086

---

Usage

Import a database

Enter the name of a MySQL database to import. The backend will:

• connect to the source database,
• read metadata from INFORMATION_SCHEMA,
• populate the metadata catalog.

Generate statistics

Select a database and view structural statistics such as:

• number of tables,
• number of columns,
• number of keys,
• total number of records,
• additional schema metrics.

Save statistics

Store the selected statistics in InfluxDB as timestamped measurements for historical tracking.

View historical snapshots

Browse previously saved statistical snapshots and analyze changes over time.

---

Future Work

• Support for PostgreSQL and MariaDB
• Automatic scheduled metadata synchronization
• Schema change detection
• Metadata comparison between database versions
• Interactive charts for historical statistics
• Export metadata to JSON or CSV
• Authentication and user management