# How Chat2DB's ER Diagram Handles Schema Changes: Architecture and Implementation

> Discover how Chat2DB's ER diagram dynamically syncs with live database schemas. Learn about its architecture and implementation for automatic and manual schema change handling.

- Repository: [OtterMind/Chat2DB](https://github.com/OtterMind/Chat2DB)
- Tags: architecture
- Published: 2026-07-26

---

**Chat2DB synchronizes its ER diagrams with live database schemas by dynamically querying metadata through the `IDbErService` interface on every diagram render, automatically refreshing views when DDL operations complete successfully, and providing manual refresh controls in the `ConsoleERModal` frontend component.**

Chat2DB is an open-source database management tool that provides visual ER diagram capabilities for multiple database systems. Understanding how it maintains synchronization between these visual diagrams and evolving database schemas requires examining its layered architecture, from JDBC metadata queries in the storage layer to the React-based rendering engine. This article explores the exact implementation details that ensure Chat2DB's ER diagrams reflect current schema states.

## Backend Metadata Retrieval

The backend architecture relies on a clean separation between API controllers, service interfaces, and storage implementations to fetch schema metadata.

### Controller and Service Interface

The entry point for ER diagram data is the `DbErController` class located at [`chat2db-community-web/src/main/java/ai/chat2db/community/web/api/controller/DbErController.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-web/src/main/java/ai/chat2db/community/web/api/controller/DbErController.java). This controller receives HTTP requests for ER diagram visualization and delegates to the `IDbErService` interface defined in [`chat2db-community-domain-api/src/main/java/ai/chat2db/community/domain/api/service/db/IDbErService.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-domain-api/src/main/java/ai/chat2db/community/domain/api/service/db/IDbErService.java).

The service interface defines the contract for retrieving entity-relationship metadata without specifying implementation details, allowing different storage backends to handle various database protocols.

### Storage Layer Implementation

The default implementation resides in `LocalWorkspaceStorage` at [`chat2db-community-storage/src/main/java/ai/chat2db/community/storage/LocalWorkspaceStorage.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-storage/src/main/java/ai/chat2db/community/storage/LocalWorkspaceStorage.java). The critical method `getErPosition(Long dataSourceId, String databaseName, String schemaName)` executes JDBC queries against the database's metadata catalog (such as `INFORMATION_SCHEMA` or dialect-specific system tables).

This method assembles an `IERTableDetail` structure containing tables, columns, foreign keys, and indexes by directly querying the live database connection, ensuring no stale cached data is returned.

## Web API Conversion

Between the domain layer and the frontend, the `ErWebConverter` class in [`chat2db-community-web/src/main/java/ai/chat2db/community/web/api/converter/er/ErWebConverter.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-web/src/main/java/ai/chat2db/community/web/api/converter/er/ErWebConverter.java) transforms domain models into JSON payloads. The converter maps `ERModelQueryRequest` web parameters to `DbErQueryRequest` domain objects, ultimately producing `IERTableDetail` instances that the React frontend consumes for visualization.

## Frontend Rendering and Refresh Mechanisms

The frontend implementation handles both the initial render and dynamic updates when schemas change.

### Modal Component and User Interface

The primary UI container is `ConsoleERModal`, referenced in [`chat2db-community-client/src/pages/main/workspace/components/WorkspaceTabs/index.tsx`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-client/src/pages/main/workspace/components/WorkspaceTabs/index.tsx). This React component manages the modal window that displays the ER diagram and provides user controls including an explicit refresh button.

### Diagram Rendering Engine

The actual visualization occurs in [`ERDiagram.tsx`](https://github.com/OtterMind/Chat2DB/blob/main/ERDiagram.tsx) located at [`chat2db-community-client/src/blocks/ERModal/ERDiagram.tsx`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-client/src/blocks/ERModal/ERDiagram.tsx). This component utilizes the **AntV G6** graph library to render nodes (representing tables) and edges (representing foreign key relationships) based on the JSON data returned from the backend service.

## Schema Change Detection Strategies

Chat2DB employs multiple strategies to ensure diagrams reflect current database states rather than cached snapshots.

### On-Demand Metadata Refresh

The ER diagram implements a **stateless refresh pattern**. Every time the `ConsoleERModal` opens or receives a refresh signal, the frontend initiates a new API call to `DbErController`. Because `LocalWorkspaceStorage.getErPosition()` queries live database metadata on each invocation, the diagram always reflects the current schema structure without relying on client-side caching.

### Automatic DDL Synchronization

When users execute DDL statements (such as `CREATE TABLE` or `ALTER TABLE`) through Chat2DB's SQL editor, the `SqlExecutionJob` monitors execution results. Upon successful DDL completion, this service automatically triggers a refresh event that propagates to the ER diagram component, forcing a new metadata fetch without requiring manual user intervention.

### Explicit Refresh Controls

Users can manually trigger schema synchronization by clicking the refresh button in the `ConsoleERModal` interface. This action re-invokes the full metadata pipeline from controller to storage layer, providing immediate visual feedback when database administrators apply schema changes outside the application.

## Implementation Examples

The following snippets illustrate the key integration points between layers:

```java
// Storage layer fetching live metadata
public String getErPosition(Long dataSourceId,
                            String databaseName,
                            String schemaName) {
    return storage().getErPosition(dataSourceId, databaseName, schemaName);
}

```

```java
// Web converter transforming web request to domain model
public abstract class ErWebConverter {
    public abstract DbErQueryRequest request2param(ERModelQueryRequest request);
}

```

```tsx
// Frontend modal integration in workspace tabs
const renderERModal = (item: IWorkspaceTab) => {
  return <ConsoleERModal uniqueData={uniqueData!} />;
};

```

```tsx
// Diagram rendering with G6
import G6 from '@antv/g6';

export const ERDiagram = ({ erData }) => {
  const graph = new G6.Graph({ 
    container: 'er-container',
    // configuration options
  });
  graph.data(erData);
  graph.render();
};

```

## Summary

- **Stateless Backend**: The `LocalWorkspaceStorage.getErPosition()` method queries live database metadata via JDBC on every request, eliminating stale data risks.
- **Layered Architecture**: Clear separation between `DbErController`, `IDbErService`, and `ErWebConverter` ensures maintainable data flow from database to frontend.
- **Automatic Synchronization**: The `SqlExecutionJob` detects successful DDL operations and automatically triggers ER diagram refreshes.
- **Manual Controls**: The `ConsoleERModal` component provides explicit refresh functionality for on-demand schema updates.
- **Visualization**: The [`ERDiagram.tsx`](https://github.com/OtterMind/Chat2DB/blob/main/ERDiagram.tsx) component renders dynamic graphs using AntV G6 based on fresh JSON payloads from the backend.

## Frequently Asked Questions

### Does Chat2DB cache ER diagram data on the client side?

No, Chat2DB does not cache ER diagram metadata in the frontend. Every time the diagram loads or refreshes, the application issues a new request to `DbErController`, which fetches current schema data via `LocalWorkspaceStorage.getErPosition()`. This design ensures the visualization always reflects the live database structure.

### How does Chat2DB detect schema changes automatically?

The system monitors SQL execution through `SqlExecutionJob`. When a DDL statement (such as `CREATE`, `ALTER`, or `DROP`) executes successfully, the service emits a refresh event that triggers the ER diagram component to fetch updated metadata from the backend without requiring manual user action.

### What database metadata does Chat2DB query to build the ER diagram?

The `getErPosition()` method queries standard JDBC metadata catalogs including `INFORMATION_SCHEMA` or database-specific system tables to retrieve table definitions, column types, primary keys, foreign key constraints, and index information. This metadata is assembled into `IERTableDetail` objects for visualization.

### Can users manually refresh the ER diagram without reloading the page?

Yes, the `ConsoleERModal` component includes a dedicated refresh button that users can click to force an immediate metadata reload. This action invokes the full backend pipeline from controller to storage layer, updating the diagram with the current schema state within seconds.