How to Implement Custom SQL Parsing for Unsupported Databases in Chat2DB

To implement custom SQL parsing for unsupported databases in Chat2DB, create a new Maven module that provides implementations of the ISqlSyntaxPlugin and ISQLParser interfaces, register the plugin via Spring's @Component annotation or the Java ServiceLoader mechanism using META-INF/services/ai.chat2db.spi.ISqlSyntaxPlugin, and ensure DefaultSqlSyntaxHandler can resolve the database type key.

Chat2DB is an open-source database management tool that uses a pluggable architecture to support multiple SQL dialects. As implemented in the OtterMind/Chat2DB repository, the core parsing logic in DbSqlParserServiceImpl delegates to database-specific plugins, allowing developers to extend support for unsupported databases without modifying the core domain code. This guide walks through the complete process of creating and registering a custom SQL parser plugin.

Understanding the Plugin Architecture

Before writing code, understand how Chat2DB resolves SQL parsers at runtime. The parsing flow begins in chat2db-community-server/chat2db-community-domain/chat2db-community-domain-core/src/main/java/ai/chat2db/community/domain/core/impl/db/DbSqlParserServiceImpl.java, which obtains a parser instance by calling DefaultSqlSyntaxHandler.getSQLParser(dbType). This handler maintains an internal sqlSyntaxPluginMap containing ISqlSyntaxPlugin implementations keyed by normalized database type strings. If no plugin matches the requested dbType, the system falls back to the MySQL parser, making it essential to properly register your custom implementation to override this behavior.

Step-by-Step Implementation Guide

1. Create a New Maven Plugin Module

Create a dedicated Maven module under chat2db-community-plugins/ (e.g., chat2db-community-mydb) to isolate your implementation. Add the required dependencies to your pom.xml:

• chat2db-community-spi (provides ISQLParser and ISqlSyntaxPlugin)
• Your preferred parsing library (e.g., JSqlParser, ANTLR)

2. Implement the ISQLParser Interface

Create a class that implements ai.chat2db.spi.ISQLParser (defined in chat2db-community-server/chat2db-community-spi/src/main/java/ai/chat2db/spi/ISQLParser.java) to define how SQL statements are parsed. The interface requires methods such as parserStatements(String sql), simpleParserStatements(String sql), getAllTokens(String sql), and isSelect(String sql). You can wrap an existing parser like JSqlParser or implement a custom grammar-based solution.

3. Create the ISqlSyntaxPlugin Implementation

Implement ai.chat2db.spi.ISqlSyntaxPlugin to expose your parser to the framework. This acts as a factory that returns your ISQLParser instance from getSQLParser(). Optionally implement getSqlCompletionProvider() to provide IntelliSense support for the SQL editor.

4. Register the Plugin

Chat2DB discovers plugins using Spring's component scanning or the Java ServiceLoader mechanism. For Spring registration, annotate your ISqlSyntaxPlugin implementation with @Component. For ServiceLoader registration, create a file at src/main/resources/META-INF/services/ai.chat2db.spi.ISqlSyntaxPlugin containing the fully qualified class name of your plugin implementation.

5. Declare the Database Type

Ensure your database type is recognized by updating DatabaseTypeEnum in chat2db-community-server/chat2db-community-domain/chat2db-community-domain-api/src/main/java/ai/chat2db/community/domain/api/enums/parser/DatabaseTypeEnum.java. The DefaultSqlSyntaxHandler uses resolvePluginKey() to normalize database type strings (converting to uppercase and mapping aliases like "OSCAR" to "ORACLE"), so your enum constant must match the key used in the plugin map.

Code Examples

Minimal ISQLParser Implementation Using JSqlParser

package com.mydb.plugin;

import ai.chat2db.spi.ISQLParser;
import ai.chat2db.community.domain.api.model.parser.result.SqlParserResponse;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement as JSqlStmt;

import java.util.Collections;
import java.util.List;

public class MyDbSqlParser implements ISQLParser {

    @Override
    public SqlParserResponse parserStatements(String sql) {
        SqlParserResponse resp = new SqlParserResponse();
        try {
            List<JSqlStmt> stmtList = CCJSqlParserUtil.parseStatements(sql).getStatements();
            // Convert JSqlParser statements to Chat2DB's Statement model
            resp.setStatements(convert(stmtList));
        } catch (Exception e) {
            resp.setSyntaxErrors(Collections.emptyList());
        }
        return resp;
    }

    @Override
    public SqlParserResponse simpleParserStatements(String sql) {
        return parserStatements(sql);
    }

    @Override
    public List<org.antlr.v4.runtime.Token> getAllTokens(String sql) {
        return Collections.emptyList();
    }

    @Override
    public boolean isSelect(String sql) {
        return sql.trim().toUpperCase().startsWith("SELECT");
    }
    
    // Additional interface methods (getAllTokensOnDefault, getTokenPositionMap, etc.) omitted for brevity
}

ISqlSyntaxPlugin Implementation

package com.mydb.plugin;

import ai.chat2db.spi.ISqlSyntaxPlugin;
import ai.chat2db.spi.ISQLParser;
import org.springframework.stereotype.Component;

@Component
public class MyDbSqlSyntaxPlugin implements ISqlSyntaxPlugin {

    private final ISQLParser parser = new MyDbSqlParser();

    @Override
    public ISQLParser getSQLParser() {
        return parser;
    }

    @Override
    public ai.chat2db.community.domain.api.service.db.ISqlCompletionProvider getSqlCompletionProvider() {
        return null; // Return null if completion is not supported
    }
}

ServiceLoader Registration File

Create src/main/resources/META-INF/services/ai.chat2db.spi.ISqlSyntaxPlugin:

com.mydb.plugin.MyDbSqlSyntaxPlugin

How the Integration Works at Runtime

When Chat2DB starts, DefaultSqlSyntaxHandler iterates over Chat2DBContext.PLUGIN_MAP (populated by Spring beans and ServiceLoader entries) to fill the sqlSyntaxPluginMap. During SQL parsing operations, DbSqlParserServiceImpl.contextParser() retrieves the database type from Chat2DBContext.getDBConfig().getDbType(), passes it to DefaultSqlSyntaxHandler.getSQLParser(databaseType), which normalizes the key via resolvePluginKey() and returns the corresponding parser. If no match exists, it returns the MySQL parser as a fallback. This decoupled architecture allows you to swap parser implementations without touching the core service code in DbSqlParserServiceImpl.

Summary

• Chat2DB uses a plugin architecture centered on ISqlSyntaxPlugin and ISQLParser interfaces to support database-specific SQL parsing.
• Create a new Maven module under chat2db-community-plugins/ with dependencies on chat2db-community-spi and your parsing library.
• Implement ISQLParser to define tokenization and statement parsing logic according to your database's SQL dialect.
• Expose your parser through an ISqlSyntaxPlugin implementation annotated with @Component or registered via META-INF/services/ai.chat2db.spi.ISqlSyntaxPlugin.
• Update DatabaseTypeEnum to ensure DefaultSqlSyntaxHandler can resolve your database type key during lookup.
• The system falls back to MySQL parsing if no custom plugin is found, so proper registration is critical for custom dialect support.

Frequently Asked Questions

What happens if I don't register my custom parser?

If DefaultSqlSyntaxHandler cannot find a registered ISqlSyntaxPlugin for the requested database type, it automatically falls back to the MySQL parser. This fallback is defined in getSQLParser() within chat2db-community-server/chat2db-community-spi/src/main/java/ai/chat2db/spi/DefaultSqlSyntaxHandler.java, which returns the MySQL implementation when the lookup in sqlSyntaxPluginMap returns null.

Can I use a third-party parser like ANTLR instead of writing my own grammar?

Yes, the ISQLParser interface is implementation-agnostic. You can wrap any third-party parser (JSqlParser, ANTLR, or proprietary libraries) by delegating calls from your implementation of parserStatements(), getAllTokens(), and other interface methods to the underlying library's API, as long as you convert the results to Chat2DB's SqlParserResponse model.

Where does Chat2DB look for plugin implementations?

Chat2DB discovers plugins through two mechanisms: Spring's component scanning for beans annotated with @Component (stored in Chat2DBContext.PLUGIN_MAP), and the Java ServiceLoader mechanism which reads fully-qualified class names from META-INF/services/ai.chat2db.spi.ISqlSyntaxPlugin. Both approaches populate the sqlSyntaxPluginMap in DefaultSqlSyntaxHandler at startup.

Do I need to modify the core domain code to add a new database?

No, the architecture is designed to be non-intrusive. You only need to create a new plugin module under chat2db-community-plugins/, implement the SPI interfaces, and optionally extend DatabaseTypeEnum. The core DbSqlParserServiceImpl delegates all parsing operations to the plugin resolved by DefaultSqlSyntaxHandler, keeping your custom code isolated from the community domain core.

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