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

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. 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.

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. 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 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. 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 located at 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:

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

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

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

// 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 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.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}