# How Chat2DB Manages Different Database Types: Architecture and Implementation

> Discover how Chat2DB manages diverse database types like MySQL, PostgreSQL, and SQL Server using a pluggable architecture and dedicated syntax plugins. Explore its implementation.

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

---

**Chat2DB manages different database types through a pluggable architecture built on the `DatabaseTypeEnum` enumeration and the `ISqlSyntaxPlugin` interface, enabling support for MySQL, PostgreSQL, Oracle, SQL Server, and 20+ other engines through dedicated syntax plugins.**

Supporting heterogeneous database environments requires flexible abstraction layers that decouple dialect-specific logic from core operations. The open-source Chat2DB project (OtterMind/Chat2DB) achieves this through a modular design centered on runtime plugin resolution. This article explores the technical implementation of how Chat2DB manages different database types using its registry-based architecture and specialized connection handling.

## Central Database Type Registry

### DatabaseTypeEnum Definition

All supported database identifiers are centralized in the `DatabaseTypeEnum` enumeration. This enum serves as the canonical reference for database types across the application, preventing string-based errors and providing type safety for database operations.

**Key file:** [`chat2db-community-domain/chat2db-community-domain-api/src/main/java/ai/chat2db/community/domain/api/enums/parser/DatabaseTypeEnum.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-domain/chat2db-community-domain-api/src/main/java/ai/chat2db/community/domain/api/enums/parser/DatabaseTypeEnum.java)

### ISqlSyntaxPlugin Interface

Each database type implements the `ISqlSyntaxPlugin` interface to provide dialect-specific parsing capabilities. Concrete implementations include:

- **MySQL** – `MySQLSyntaxPlugin` (default)
- **PostgreSQL** – `PgsqlSyntaxPlugin` located at [`chat2db-community-plugins/chat2db-community-postgresql/src/main/java/ai/chat2db/plugin/postgresql/PgsqlSyntaxPlugin.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-plugins/chat2db-community-postgresql/src/main/java/ai/chat2db/plugin/postgresql/PgsqlSyntaxPlugin.java)
- **Oracle** – `OracleSyntaxPlugin` located at [`chat2db-community-plugins/chat2db-community-oracle/src/main/java/ai/chat2db/plugin/oracle/OracleSyntaxPlugin.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-plugins/chat2db-community-oracle/src/main/java/ai/chat2db/plugin/oracle/OracleSyntaxPlugin.java)
- **SQL Server** – `SqlServerSyntaxPlugin` located at [`chat2db-community-plugins/chat2db-community-sqlserver/src/main/java/ai/chat2db/plugin/sqlserver/SqlServerSyntaxPlugin.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-plugins/chat2db-community-sqlserver/src/main/java/ai/chat2db/plugin/sqlserver/SqlServerSyntaxPlugin.java)
- **SQLite** – `SqliteSyntaxPlugin` located at [`chat2db-community-plugins/chat2db-community-sqlite/src/main/java/ai/chat2db/plugin/sqlite/SqliteSyntaxPlugin.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-plugins/chat2db-community-sqlite/src/main/java/ai/chat2db/plugin/sqlite/SqliteSyntaxPlugin.java)

Each plugin implements `getDatabaseType()` to return the corresponding enum constant, enabling the system to match implementations to database types at runtime.

## Runtime Plugin Resolution

The `DefaultSqlSyntaxHandler` class acts as the central dispatcher for database-specific syntax operations. Located at [`chat2db-community-spi/src/main/java/ai/chat2db/spi/DefaultSqlSyntaxHandler.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-spi/src/main/java/ai/chat2db/spi/DefaultSqlSyntaxHandler.java), it implements resolution logic through the `getSQLParser(DatabaseTypeEnum)` method.

**Resolution workflow:**

1. **Alias Normalization** – The handler normalizes input strings via `resolvePluginKey()`, mapping aliases like `OSCAR` to canonical types such as `ORACLE`.

2. **Non-SQL Fallback** – For engines like **Redis** and **MongoDB**, the method falls back to the MySQL parser to provide basic compatibility while delegating actual operations to specialized handlers.

3. **Plugin Retrieval** – The resolved key retrieves the concrete implementation from `sqlSyntaxPluginMap`, ensuring the correct dialect rules apply to SQL parsing and generation.

## Database-Specific Connection Management

The `ConnectionPool` class at [`chat2db-community-spi/src/main/java/ai/chat2db/spi/sql/ConnectionPool.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-spi/src/main/java/ai/chat2db/spi/sql/ConnectionPool.java) manages physical connections with database-specific semantics. When creating connections, it inspects `ConnectInfo.dbType` to apply specialized handling for engines requiring different drivers or connection patterns, including **Hive**, **Presto**, **SUNDB**, **MongoDB**, and **Redis**.

## SQL Dialect Utilities

Helper classes `SqlStringUtil` and `SqlUtils` utilize the `DatabaseTypeEnum` to apply database-specific quoting and escaping rules. This ensures generated SQL statements use correct identifier delimiters and literal escaping for the target engine, preventing syntax errors across different implementations.

## Extending Support for New Database Types

Adding support for additional database engines follows a consistent implementation pattern:

1. **Define the Type** – Add a new constant to `DatabaseTypeEnum` in the API module.
2. **Implement the Plugin** – Create a class implementing `ISqlSyntaxPlugin` with `getDatabaseType()` returning the new enum value.
3. **Register the Component** – Ensure the plugin is registered in `sqlSyntaxPluginMap` accessed by `DefaultSqlSyntaxHandler`.
4. **Configure Connections** – Update `ConnectionPool` logic if the database requires specialized driver handling or custom connection semantics.

## Practical Code Examples

The following example demonstrates parsing SQL statements for a specific database type:

```java
String sql = "SELECT * FROM users";
DatabaseTypeEnum dbType = DatabaseTypeEnum.POSTGRESQL;
SqlParserResponse response = DefaultSqlSyntaxHandler.parserStatements(sql, dbType);
System.out.println(response.getStatements().size());

```

Creating connections with database-specific driver selection:

```java
ConnectInfo connectInfo = new ConnectInfo();
connectInfo.setDbType(DatabaseTypeEnum.MYSQL.name());
connectInfo.setHost("localhost");
connectInfo.setPort(3306);
connectInfo.setUsername("root");
connectInfo.setPassword("password");

// ConnectionPool automatically selects the MySQL driver based on dbType
Connection connection = ConnectionPool.getConnection(connectInfo);

```

## Summary

- **Chat2DB** uses `DatabaseTypeEnum` as the central registry for supported database types at [`chat2db-community-domain/chat2db-community-domain-api/src/main/java/ai/chat2db/community/domain/api/enums/parser/DatabaseTypeEnum.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-domain/chat2db-community-domain-api/src/main/java/ai/chat2db/community/domain/api/enums/parser/DatabaseTypeEnum.java).
- The `ISqlSyntaxPlugin` interface enables pluggable syntax handling, with implementations located in the `chat2db-community-plugins` module.
- `DefaultSqlSyntaxHandler` resolves plugins at runtime via `getSQLParser()`, supporting alias mapping and non-SQL fallbacks.
- `ConnectionPool` applies database-specific driver logic based on the `ConnectInfo.dbType` enum value.
- New database support requires implementing the plugin interface and registering the component in the handler's plugin map.

## Frequently Asked Questions

### What databases does Chat2DB currently support?

Chat2DB supports MySQL, PostgreSQL, Oracle, SQL Server, SQLite, TiDB, TDengine, Redshift, Presto, Hive, MongoDB, Redis, and others. Each database has a dedicated plugin implementing `ISqlSyntaxPlugin` located in the `chat2db-community-plugins` directory, with the specific path following the pattern `chat2db-community-[databasename]/src/main/java/ai/chat2db/plugin/[databasename]/[Name]SyntaxPlugin.java`.

### How does Chat2DB handle database-specific SQL syntax differences?

The `DefaultSqlSyntaxHandler` class resolves the appropriate `ISqlSyntaxPlugin` implementation based on the `DatabaseTypeEnum` value passed to `getSQLParser()`. Each plugin contains dialect-specific parsing logic, while utility classes like `SqlStringUtil` handle quoting and escaping rules specific to each database type.

### Can developers add support for custom or proprietary databases?

Yes. Developers must extend `DatabaseTypeEnum` with their database identifier, create a class implementing `ISqlSyntaxPlugin` with custom parsing logic in the `getDatabaseType()` method, and register the plugin in the `sqlSyntaxPluginMap` used by `DefaultSqlSyntaxHandler`. If the database requires specialized JDBC drivers or connection semantics, the `ConnectionPool` class at [`chat2db-community-spi/src/main/java/ai/chat2db/spi/sql/ConnectionPool.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-spi/src/main/java/ai/chat2db/spi/sql/ConnectionPool.java) may also require updates.

### Why does Chat2DB use the MySQL parser for Redis and MongoDB?

Non-SQL engines like Redis and MongoDB lack traditional SQL parsing requirements. The `getSQLParser()` method in `DefaultSqlSyntaxHandler` defaults to the MySQL parser for these types to provide basic statement structure compatibility, while actual data operations are delegated to database-specific connection handlers that understand the respective wire protocols.