How Chat2DB Manages Different Database Types: Architecture and Implementation

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

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
• Oracle – OracleSyntaxPlugin located at 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
• SQLite – SqliteSyntaxPlugin located at 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, 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 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:

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:

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

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