How Chat2DB Handles Database Metadata Browsing: SPI Architecture and Implementation

Chat2DB handles database metadata browsing through a Service Provider Interface (SPI) layer where dialect-specific plugins implement the IDbMetaData interface, delegating to DefaultMetaService and DefaultSQLExecutor to retrieve schemas, tables, columns, and indexes while exposing unified REST and CLI endpoints.

The OtterMind/Chat2DB open-source project provides comprehensive database metadata browsing capabilities that enable users to discover databases, schemas, tables, columns, indexes, and routines across multiple database systems. At the heart of this functionality lies a clean separation between transport layers (REST and CLI) and dialect-specific implementations through the SPI architecture. This design allows Chat2DB to support diverse databases like MySQL, PostgreSQL, MariaDB, and GaussDB through a pluggable metadata retrieval system.

Core SPI Architecture: The IDbMetaData Interface

The foundation of Chat2DB's metadata browsing capability is the IDbMetaData interface defined in chat2db-community-server/chat2db-community-spi/src/main/java/ai/chat2db/spi/IDbMetaData.java. This contract specifies methods for retrieving all database objects including databases, schemas, tables, columns, indexes, functions, procedures, and triggers.

The DefaultMetaService class provides a generic implementation of this interface in chat2db-community-server/chat2db-community-spi/src/main/java/ai/chat2db/spi/DefaultMetaService.java. Rather than executing SQL directly, it delegates JDBC operations to DefaultSQLExecutor while containing helper methods for building DDL and ALTER statements using the DBStructUtils utility.

Dialect-Specific Plugin Implementations

Each supported database extends the generic implementation through a hierarchical plugin architecture. This inheritance model allows dialects to override only the methods requiring database-specific SQL syntax.

• MySQL: MysqlMetaData extends DefaultMetaService
• MariaDB: MariaDBMetaData extends MysqlMetaData (inheriting MySQL-specific logic)
• PostgreSQL/GaussDB: GaussDBMetaData extends PostgreSQLMetaData which extends DefaultMetaService

For example, the MySQL implementation in chat2db-community-server/chat2db-community-plugins/chat2db-community-mysql/src/main/java/ai/chat2db/plugin/mysql/MysqlMetaData.java overrides specific methods to handle MySQL's information schema queries, while GaussDB specializes PostgreSQL behavior in its respective plugin path.

Metadata Retrieval Flow: From Request to JDBC

Chat2DB processes metadata browsing requests through a layered architecture that cleanly separates concerns:

1. Transport Layer: HTTP requests hit REST controllers (such as DbDatabaseController, DbSchemaController, DbTableController, and DbTriggerController) under /api/*/metadata, while CLI commands route through CliMetadataController at /api/cli/v1/metadata/*.
2. Domain Layer: Controllers delegate to domain services like IDbTableService, which resolve the appropriate dialect implementation.
3. SPI Layer: The system invokes the dialect-specific IDbMetaData implementation (e.g., MysqlMetaData).
4. Execution Layer: Implementations use DefaultSQLExecutor to execute JDBC metadata queries.

The CLI path follows a similar trajectory: CliMetadataController wires into ICliMetadataService, whose implementation CliMetadataServiceImpl (located in chat2db-community-server/chat2db-community-domain/chat2db-community-domain-core/src/main/java/ai/chat2db/community/domain/core/impl/cli/CliMetadataServiceImpl.java) forwards requests directly to the SPI layer and returns JSON payloads.

DDL Generation and Structural Utilities

Beyond simple metadata retrieval, Chat2DB generates CREATE TABLE statements and incremental ALTER scripts through DBStructUtils, located in chat2db-community-server/chat2db-community-spi/src/main/java/ai/chat2db/spi/util/DBStructUtils.java. This static helper class constructs DDL from the metadata objects returned by the SPI layer, powering the "generate DDL" feature in both the UI export functions and CLI commands.

The utility works in conjunction with DefaultMetaService methods like tableDDL() to transform column, index, and constraint metadata into executable SQL statements appropriate for the target database dialect.

Code Examples: Browsing Metadata in Chat2DB

Java Direct Invocation

When working directly with the Chat2DB SPI layer, you can instantiate the appropriate metadata implementation and query database structures:

// Obtain connection from your datasource
Connection conn = dataSource.getConnection();

// Instantiate dialect-specific metadata service
IDbMetaData meta = new MysqlMetaData(); // or PostgreSQLMetaData, etc.

// Retrieve all databases
List<Database> databases = meta.databases(conn);

// List schemas within a specific database
List<Schema> schemas = meta.schemas(conn, "production_db");

// Get column details for a table
TableMetadataRequest request = new TableMetadataRequest();
request.setDatabaseName("production_db");
request.setSchemaName("public");
request.setTableName("users");
List<TableColumn> columns = meta.columns(conn, request);

// Generate DDL
String ddl = meta.tableDDL(conn, request);

HTTP REST API

The web layer exposes metadata through REST endpoints defined in DbTableController.java:

# List tables in a schema

curl -X POST "http://localhost:10825/api/v1/database/tables/list" \
  -H "Content-Type: application/json" \
  -d '{"databaseName":"production_db","schemaName":"public"}'

# Retrieve column metadata

curl -X POST "http://localhost:10825/api/v1/database/tables/columns" \
  -H "Content-Type: application/json" \
  -d '{"databaseName":"production_db","schemaName":"public","tableName":"users"}'

# Get table DDL

curl -X POST "http://localhost:10825/api/v1/database/table/ddl" \
  -H "Content-Type: application/json" \
  -d '{"databaseName":"production_db","schemaName":"public","tableName":"users"}'

CLI Commands

The CLI interface routes through CliMetadataController and supports interactive metadata browsing:

# List all accessible databases

chat2db-cli metadata databases list

# Enumerate tables within a schema

chat2db-cli metadata tables list --database production_db --schema public

# Display DDL for a specific table

chat2db-cli metadata tables ddl --database production_db --schema public --table users

Summary

Chat2DB's database metadata browsing architecture demonstrates several key design principles:

• Plugin-based extensibility: New database support requires only implementing IDbMetaData in a dialect-specific plugin.
• Layered separation: Transport (REST/CLI), domain, and SPI layers remain isolated, allowing the same metadata logic to serve both web interfaces and command-line tools.
• Unified DDL generation: The DBStructUtils utility provides consistent CREATE and ALTER statement generation across all supported dialects.
• Hierarchical inheritance: Dialect implementations leverage inheritance (e.g., MariaDB extending MySQL) to maximize code reuse while accommodating database-specific quirks.

Frequently Asked Questions

What interface defines the contract for metadata browsing in Chat2DB?

The IDbMetaData interface in chat2db-community-server/chat2db-community-spi/src/main/java/ai/chat2db/spi/IDbMetaData.java defines the complete contract. It declares methods for retrieving databases, schemas, tables, columns, indexes, functions, procedures, and triggers, providing a unified API regardless of the underlying database system.

How does Chat2DB support multiple database dialects for metadata retrieval?

Chat2DB uses a plugin architecture where each database dialect provides an implementation of IDbMetaData. These implementations typically extend DefaultMetaService, which handles common JDBC operations. Dialect-specific classes like MysqlMetaData or PostgreSQLMetaData override only the methods requiring custom SQL, while specialized databases like MariaDB extend their parent dialect classes (e.g., MariaDBMetaData extends MysqlMetaData).

Can I retrieve DDL statements through Chat2DB's metadata API?

Yes. The IDbMetaData interface includes methods like tableDDL() that generate CREATE TABLE statements. Under the hood, these methods utilize DBStructUtils to construct DDL from metadata objects. This functionality is exposed through REST endpoints (POST /api/v1/database/table/ddl) and CLI commands (chat2db-cli metadata tables ddl).

What is the difference between web UI and CLI metadata browsing paths?

While both paths ultimately invoke the same SPI layer, they route through different controllers. Web UI requests hit controllers like DbTableController under /api/v1/*, while CLI requests route through CliMetadataController at /api/cli/v1/metadata/*. The CLI path uses CliMetadataServiceImpl as an intermediary adapter, but both converge on the same IDbMetaData dialect implementations and return consistent JSON responses.

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