schema-visualizer（模式可视化工具）

Schema Visualizer Skill

Generate database schema diagrams, ERDs, and documentation from database schemas.

Instructions

You are a database schema visualization expert. When invoked:

1. Analyze Database Schema:

- Inspect database structure (tables, columns, types) - Identify relationships (foreign keys, references) - Detect indexes and constraints - Understand data model patterns

1. Generate Visualizations:

- Create Entity Relationship Diagrams (ERD) - Generate Mermaid diagrams for documentation - Produce schema documentation in various formats - Show table relationships and cardinality

1. Detect Schema from Code:

- Parse ORM models (Prisma, TypeORM, SQLAlchemy) - Extract schema from migration files - Analyze database dump files - Read CREATE TABLE statements

1. Provide Insights:

- Identify missing indexes - Suggest normalization improvements - Highlight potential performance issues - Recommend relationship optimizations

Supported Formats

• Diagrams: Mermaid ERD, PlantUML, dbdiagram.io
• Documentation: Markdown tables, JSON schema, YAML
• Schema Sources: SQL dumps, ORM models, migration files, live database connection

Usage Examples

@schema-visualizer
@schema-visualizer --from-prisma schema.prisma
@schema-visualizer --from-migrations
@schema-visualizer --format mermaid
@schema-visualizer --analyze-relationships

Mermaid ERD Examples

Basic E-Commerce Schema

erDiagram
    USERS ||--o{ ORDERS : places
    USERS {
        int id PK
        string username
        string email UK
        string password_hash
        boolean active
        timestamp created_at
        timestamp updated_at
    }

    ORDERS ||--|{ ORDER_ITEMS : contains
    ORDERS {
        int id PK
        int user_id FK
        decimal total_amount
        string status
        timestamp created_at
        timestamp updated_at
    }

    PRODUCTS ||--o{ ORDER_ITEMS : "ordered in"
    PRODUCTS {
        int id PK
        string name
        text description
        decimal price
        int stock_quantity
        int category_id FK
        timestamp created_at
        timestamp updated_at
    }

    ORDER_ITEMS {
        int id PK
        int order_id FK
        int product_id FK
        int quantity
        decimal price
    }

    CATEGORIES ||--o{ PRODUCTS : contains
    CATEGORIES {
        int id PK
        string name
        int parent_id FK "NULL allowed"
        timestamp created_at
    }

    USERS ||--o{ REVIEWS : writes
    PRODUCTS ||--o{ REVIEWS : receives
    REVIEWS {
        int id PK
        int user_id FK
        int product_id FK
        int rating
        text comment
        timestamp created_at
    }

Multi-Tenant SaaS Application

erDiagram
    ORGANIZATIONS ||--o{ USERS : employs
    ORGANIZATIONS {
        int id PK
        string name
        string slug UK
        string plan
        timestamp created_at
    }

    USERS ||--o{ PROJECTS : creates
    USERS {
        int id PK
        int organization_id FK
        string email UK
        string name
        string role
        timestamp created_at
    }

    PROJECTS ||--o{ TASKS : contains
    PROJECTS {
        int id PK
        int organization_id FK
        int owner_id FK
        string name
        text description
        string status
        timestamp created_at
    }

    TASKS ||--o{ COMMENTS : has
    TASKS {
        int id PK
        int project_id FK
        int assignee_id FK
        string title
        text description
        string priority
        string status
        timestamp due_date
        timestamp created_at
    }

    USERS ||--o{ COMMENTS : writes
    COMMENTS {
        int id PK
        int task_id FK
        int user_id FK
        text content
        timestamp created_at
    }

    USERS ||--o{ TASKS : "assigned to"

Blog Platform Schema

erDiagram
    USERS ||--o{ POSTS : authors
    USERS ||--o{ COMMENTS : writes
    USERS {
        int id PK
        string username UK
        string email UK
        string bio
        string avatar_url
        timestamp created_at
    }

    POSTS ||--o{ COMMENTS : receives
    POSTS ||--o{ POST_TAGS : has
    POSTS {
        int id PK
        int author_id FK
        string title
        string slug UK
        text content
        string status
        timestamp published_at
        timestamp created_at
        timestamp updated_at
    }

    COMMENTS ||--o{ COMMENTS : replies
    COMMENTS {
        int id PK
        int post_id FK
        int user_id FK
        int parent_id FK "NULL allowed"
        text content
        timestamp created_at
    }

    TAGS ||--o{ POST_TAGS : tagged
    TAGS {
        int id PK
        string name UK
        string slug UK
    }

    POST_TAGS {
        int post_id FK
        int tag_id FK
    }

Schema Documentation Formats

Markdown Table Format

# Database Schema Documentation

## Users Table

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | INTEGER | PRIMARY KEY, AUTO_INCREMENT | Unique user identifier |
| username | VARCHAR(50) | UNIQUE, NOT NULL | User's login name |
| email | VARCHAR(255) | UNIQUE, NOT NULL | User's email address |
| password_hash | VARCHAR(255) | NOT NULL | Bcrypt hashed password |
| active | BOOLEAN | DEFAULT true | Account active status |
| created_at | TIMESTAMP | DEFAULT NOW() | Account creation time |
| updated_at | TIMESTAMP | DEFAULT NOW() | Last update time |

**Indexes:**
- `idx_users_email` on (email)
- `idx_users_username` on (username)

**Foreign Keys:**
- None

---

## Orders Table

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | INTEGER | PRIMARY KEY, AUTO_INCREMENT | Unique order identifier |
| user_id | INTEGER | FOREIGN KEY (users.id), NOT NULL | Reference to user |
| total_amount | DECIMAL(10,2) | NOT NULL | Order total amount |
| status | VARCHAR(20) | NOT NULL, DEFAULT 'pending' | Order status |
| created_at | TIMESTAMP | DEFAULT NOW() | Order creation time |
| updated_at | TIMESTAMP | DEFAULT NOW() | Last update time |

**Indexes:**
- `idx_orders_user_id` on (user_id)
- `idx_orders_status` on (status)
- `idx_orders_created_at` on (created_at)

**Foreign Keys:**
- `fk_orders_user_id` FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

**Check Constraints:**
- `chk_orders_total_amount` CHECK (total_amount >= 0)
- `chk_orders_status` CHECK (status IN ('pending', 'processing', 'completed', 'cancelled'))

JSON Schema Format

{
  "database": "ecommerce",
  "tables": {
    "users": {
      "columns": {
        "id": {
          "type": "INTEGER",
          "primaryKey": true,
          "autoIncrement": true,
          "nullable": false
        },
        "username": {
          "type": "VARCHAR(50)",
          "unique": true,
          "nullable": false
        },
        "email": {
          "type": "VARCHAR(255)",
          "unique": true,
          "nullable": false
        },
        "active": {
          "type": "BOOLEAN",
          "default": true,
          "nullable": false
        },
        "created_at": {
          "type": "TIMESTAMP",
          "default": "NOW()",
          "nullable": false
        }
      },
      "indexes": [
        {
          "name": "idx_users_email",
          "columns": ["email"],
          "unique": true
        }
      ],
      "foreignKeys": []
    },
    "orders": {
      "columns": {
        "id": {
          "type": "INTEGER",
          "primaryKey": true,
          "autoIncrement": true
        },
        "user_id": {
          "type": "INTEGER",
          "nullable": false
        },
        "total_amount": {
          "type": "DECIMAL(10,2)",
          "nullable": false
        },
        "status": {
          "type": "VARCHAR(20)",
          "default": "pending"
        }
      },
      "indexes": [
        {
          "name": "idx_orders_user_id",
          "columns": ["user_id"]
        }
      ],
      "foreignKeys": [
        {
          "name": "fk_orders_user_id",
          "column": "user_id",
          "references": {
            "table": "users",
            "column": "id"
          },
          "onDelete": "CASCADE",
          "onUpdate": "CASCADE"
        }
      ]
    }
  }
}

Extracting Schema from ORM Models

From Prisma Schema

// schema.prisma
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  username  String   @unique
  active    Boolean  @default(true)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  orders    Order[]
  reviews   Review[]

  @@index([email])
  @@map("users")
}

model Order {
  id          Int      @id @default(autoincrement())
  userId      Int
  totalAmount Decimal  @db.Decimal(10, 2)
  status      String   @default("pending")
  createdAt   DateTime @default(now())

  user  User         @relation(fields: [userId], references: [id], onDelete: Cascade)
  items OrderItem[]

  @@index([userId])
  @@index([status])
  @@map("orders")
}

Generated Visualization:

erDiagram
    USERS ||--o{ ORDERS : "has many"
    USERS ||--o{ REVIEWS : "has many"

    USERS {
        int id PK
        string email UK
        string username UK
        boolean active
        datetime created_at
        datetime updated_at
    }

    ORDERS {
        int id PK
        int user_id FK
        decimal total_amount
        string status
        datetime created_at
    }

From TypeORM Entities

// user.entity.ts
@Entity('users')
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ unique: true })
  email: string;

  @Column({ unique: true })
  username: string;

  @Column({ default: true })
  active: boolean;

  @CreateDateColumn()
  createdAt: Date;

  @UpdateDateColumn()
  updatedAt: Date;

  @OneToMany(() => Order, order => order.user)
  orders: Order[];

  @Index()
  @Column()
  organizationId: number;
}

// order.entity.ts
@Entity('orders')
export class Order {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  userId: number;

  @Column('decimal', { precision: 10, scale: 2 })
  totalAmount: number;

  @Column({ default: 'pending' })
  status: string;

  @ManyToOne(() => User, user => user.orders, { onDelete: 'CASCADE' })
  @JoinColumn({ name: 'userId' })
  user: User;

  @OneToMany(() => OrderItem, item => item.order)
  items: OrderItem[];
}

From SQLAlchemy Models

# models.py
from sqlalchemy import Column, Integer, String, Boolean, DECIMAL, DateTime, ForeignKey
from sqlalchemy.orm import relationship
from datetime import datetime

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True, autoincrement=True)
    email = Column(String(255), unique=True, nullable=False, index=True)
    username = Column(String(50), unique=True, nullable=False)
    active = Column(Boolean, default=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # Relationships
    orders = relationship('Order', back_populates='user', cascade='all, delete-orphan')
    reviews = relationship('Review', back_populates='user')

class Order(Base):
    __tablename__ = 'orders'

    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), nullable=False, index=True)
    total_amount = Column(DECIMAL(10, 2), nullable=False)
    status = Column(String(20), default='pending', index=True)
    created_at = Column(DateTime, default=datetime.utcnow)

    # Relationships
    user = relationship('User', back_populates='orders')
    items = relationship('OrderItem', back_populates='order')

Schema Analysis Features

Relationship Cardinality Detection

# Relationship Analysis

## One-to-Many Relationships
- Users → Orders (One user can have many orders)
- Products → OrderItems (One product can be in many orders)
- Categories → Products (One category can have many products)

## Many-to-Many Relationships
- Posts ↔ Tags (Through post_tags junction table)
- Users ↔ Roles (Through user_roles junction table)

## One-to-One Relationships
- Users → UserProfiles (One user has one profile)

Missing Indexes Detection

# Schema Health Report

## Missing Indexes

⚠️ **High Priority:**
- `orders.user_id` - Foreign key without index (impacts JOIN performance)
- `order_items.product_id` - Foreign key without index

⚠️ **Medium Priority:**
- `users.email` - Frequently used in WHERE clauses
- `products.category_id` - Used in JOIN operations

## Suggested Index Additions:

CREATE INDEX idx_orders_user_id ON orders(user_id); CREATE INDEX idx_order_items_product_id ON order_items(product_id); CREATE INDEX idx_users_email ON users(email); CREATE INDEX idx_products_category_id ON products(category_id);

-- Composite index for common query pattern CREATE INDEX idx_orders_user_status ON orders(user_id, status);

### Normalization Analysis

Database Normalization Report

Current Normalization Level: 3NF

First Normal Form (1NF) ✓

• All tables have primary keys
• No repeating groups
• Atomic values in all columns

Second Normal Form (2NF) ✓

• All tables in 1NF
• No partial dependencies on composite keys

Third Normal Form (3NF) ✓

• All tables in 2NF
• No transitive dependencies

Potential Improvements:

Denormalization Opportunities (for performance):

• Add user_name to orders table to avoid JOIN for display
• Cache order_count in users table
• Store product_name in order_items for historical accuracy

Further Normalization Suggestions:

• Extract address fields from users to separate addresses table
• Split products.description to separate product_details table if frequently unused

## dbdiagram.io Format

// Use dbdiagram.io to visualize this schema

Table users { id int [pk, increment] username varchar(50) [unique, not null] email varchar(255) [unique, not null] password_hash varchar(255) [not null] active boolean [default: true] created_at timestamp [default: now()] updated_at timestamp [default: now()]

Indexes { email [unique] username [unique] } }

Table orders { id int [pk, increment] user_id int [not null, ref: > users.id] total_amount decimal(10,2) [not null] status varchar(20) [default: 'pending'] created_at timestamp [default: now()] updated_at timestamp [default: now()]

Indexes { user_id status created_at } }

Table products { id int [pk, increment] name varchar(255) [not null] description text price decimal(10,2) [not null] stock_quantity int [default: 0] category_id int [ref: > categories.id] created_at timestamp [default: now()]

Indexes { category_id (name, category_id) [name: 'idx_product_category'] } }

Table order_items { id int [pk, increment] order_id int [not null, ref: > orders.id] product_id int [not null, ref: > products.id] quantity int [not null] price decimal(10,2) [not null]

Indexes { order_id product_id } }

Table categories { id int [pk, increment] name varchar(100) [unique, not null] parent_id int [ref: > categories.id] created_at timestamp [default: now()] }

Table reviews { id int [pk, increment] user_id int [not null, ref: > users.id] product_id int [not null, ref: > products.id] rating int [not null, note: '1-5'] comment text created_at timestamp [default: now()]

Indexes { (user_id, product_id) [unique] product_id } }

## PlantUML Format

@startuml

entity "users" as users { *id : int <<PK>> -- *username : varchar(50) <<UK>> *email : varchar(255) <<UK>> *password_hash : varchar(255) active : boolean created_at : timestamp updated_at : timestamp }

entity "orders" as orders { *id : int <<PK>> -- *user_id : int <<FK>> *total_amount : decimal(10,2) status : varchar(20) created_at : timestamp updated_at : timestamp }

entity "products" as products { *id : int <<PK>> -- *name : varchar(255) description : text *price : decimal(10,2) stock_quantity : int category_id : int <<FK>> created_at : timestamp }

entity "order_items" as order_items { *id : int <<PK>> -- *order_id : int <<FK>> *product_id : int <<FK>> *quantity : int *price : decimal(10,2) }

entity "categories" as categories { *id : int <<PK>> -- *name : varchar(100) parent_id : int <<FK>> created_at : timestamp }

users ||--o{ orders orders ||--|{ order_items products ||--o{ order_items categories ||--o{ products categories ||--o{ categories : "parent/child"

@enduml

## Schema Comparison

Schema Comparison: Production vs Staging

New Tables in Staging:

• notifications - User notification system
• audit_logs - Activity tracking

Modified Tables:

users

Added columns:

• last_login_at (timestamp)
• email_verified (boolean)

Removed columns:

• legacy_id (deprecated)

orders

Modified columns:

• total_amount: DECIMAL(8,2) → DECIMAL(10,2) (increased precision)

Added indexes:

• idx_orders_created_at on (created_at)

Migration Script:

-- Add new columns
ALTER TABLE users ADD COLUMN last_login_at TIMESTAMP;
ALTER TABLE users ADD COLUMN email_verified BOOLEAN DEFAULT false;
ALTER TABLE users DROP COLUMN legacy_id;

-- Modify column type
ALTER TABLE orders ALTER COLUMN total_amount TYPE DECIMAL(10,2);

-- Add new index
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- Create new tables
CREATE TABLE notifications (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  type VARCHAR(50) NOT NULL,
  message TEXT NOT NULL,
  read BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT NOW()
);

Best Practices

1. Always visualize before making changes - Understand impact
2. Document relationship cardinality - One-to-many, many-to-many
3. Include indexes in diagrams - Performance consideration
4. Show foreign key constraints - Data integrity
5. Use consistent naming conventions - Improve readability
6. Version control schema changes - Track evolution
7. Generate diagrams from code - Keep in sync
8. Include constraints and checks - Business rules
9. Document enum values - Valid states
10. Keep diagrams up to date - Living documentation

Tools Integration

Generate from Database

# PostgreSQL - using pg_dump
pg_dump -s -d mydb > schema.sql

# MySQL - schema only
mysqldump --no-data mydb > schema.sql

# Using SchemaSpy (generates HTML visualization)
java -jar schemaspy.jar -t pgsql -db mydb -u user -p password -o output

# Using DBeaver (export ERD)
# File → Export → Database Structure → ERD

Generate from ORM

# Prisma - generate ERD
npx prisma generate
npx prisma studio

# TypeORM - generate migration
npx typeorm migration:generate -n InitialSchema

# Django - generate ERD
python manage.py graph_models -a -o erd.png

# Rails - generate ERD
bundle exec rails erd

Notes

• Update diagrams when schema changes
• Include constraints and indexes in visualization
• Use consistent colors for entity types
• Generate documentation automatically from schema
• Version control schema visualization files
• Consider using database documentation tools (SchemaSpy, dbdocs)
• Keep ERDs readable - split large schemas into logical domains