# How Chat2DB Handles Database Metadata Browsing: SPI Architecture and Implementation

> Discover how Chat2DB uses SPI architecture and plugins to efficiently browse database metadata including schemas tables and columns learn about the implementation and unified endpoints

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

---

**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`](https://github.com/OtterMind/Chat2DB/blob/main/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`](https://github.com/OtterMind/Chat2DB/blob/main/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`](https://github.com/OtterMind/Chat2DB/blob/main/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`](https://github.com/OtterMind/Chat2DB/blob/main/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`](https://github.com/OtterMind/Chat2DB/blob/main/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:

```java
// 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`](https://github.com/OtterMind/Chat2DB/blob/main/DbTableController.java):

```bash

# 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:

```bash

# 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`](https://github.com/OtterMind/Chat2DB/blob/main/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.