How to Extend Chat2DB's SQL Completion Engine: A Complete Plugin Development Guide

To extend Chat2DB's SQL completion engine, implement the ISqlCompletionProvider interface, define a dialect-specific ISqlCompletionDialect, and expose the provider through your database's ISqlSyntaxPlugin implementation, allowing the DefaultSqlSyntaxHandler to automatically route requests to your custom logic.

Chat2DB (OtterMind/Chat2DB) provides a modular, plugin-based architecture for SQL completion that enables developers to add support for new databases or customize existing completion behavior without modifying core platform code. This guide explains how to leverage the Service Provider Interface (SPI) to extend the SQL completion engine with custom dialects and logic.

Understanding the SQL Completion Architecture

The completion system relies on three core contracts defined in the chat2db-community-spi module. Understanding these interfaces is essential before extending the engine.

The Provider Interface (ISqlCompletionProvider)

Located at chat2db-community-server/chat2db-community-spi/src/main/java/ai/chat2db/spi/ISqlCompletionProvider.java, this interface defines the entry point for all completion logic. It requires a single method:

SqlCompletionResponse complete(DbSqlCompletionRequest request);

Implementations receive a request containing the raw SQL, cursor position, and database metadata, and return a SqlCompletionResponse with candidate suggestions.

The Dispatcher (DefaultSqlSyntaxHandler)

The DefaultSqlSyntaxHandler class in chat2db-community-server/chat2db-community-spi/src/main/java/ai/chat2db/spi/DefaultSqlSyntaxHandler.java acts as the central router. It resolves the active database dialect, retrieves the corresponding ISqlSyntaxPlugin, and delegates completion requests to the plugin's ISqlCompletionProvider. This abstraction ensures the web layer remains agnostic to specific database implementations.

Data Transfer Objects

Requests and responses travel through standardized DTOs:

• SqlCompletionRequest (chat2db-community-server/chat2db-community-web/src/main/java/ai/chat2db/community/web/api/model/request/db/SqlCompletionRequest.java): Carries SQL text, cursor offset, and connection context.
• SqlCompletionResponse (chat2db-community-server/chat2db-community-web/src/main/java/ai/chat2db/community/web/api/model/response/db/SqlCompletionResponse.java): Contains the list of completion candidates, suggestion types, and optional metadata.

Step-by-Step Implementation Guide

Follow these steps to extend Chat2DB's SQL completion engine with custom logic.

1. Implement the ISqlCompletionProvider Interface

Create a new class that implements ISqlCompletionProvider. Most implementations delegate to a SqlCompletionPipeline initialized with a dialect-specific handler.

public class CustomSqlCompletionProvider implements ISqlCompletionProvider {
    private final SqlCompletionPipeline pipeline;
    
    public CustomSqlCompletionProvider() {
        this.pipeline = new SqlCompletionPipeline(new CustomSqlCompletionDialect());
    }
    
    @Override
    public SqlCompletionResponse complete(DbSqlCompletionRequest request) {
        return pipeline.complete(request);
    }
}

2. Define the Dialect Rules

Implement ISqlCompletionDialect to specify tokenizer behavior, keyword sets, and rule-based evidence collectors for your database. This class determines how the pipeline parses SQL and what completion candidates to generate based on syntax context.

3. Register via the Syntax Plugin

Every database plugin must implement ISqlSyntaxPlugin. Override the getSqlCompletionProvider() method to return your provider instance:

public class CustomSyntaxPlugin implements ISqlSyntaxPlugin {
    @Override
    public ISqlCompletionProvider getSqlCompletionProvider() {
        return new CustomSqlCompletionProvider();
    }
    
    // ... other plugin methods
}

Spring's component scan automatically discovers plugins on the classpath. The DefaultSqlSyntaxHandler then routes requests to your provider when users connect to your database type.

4. Testing Your Implementation

Create unit tests similar to MysqlSqlCompletionProviderTest located in chat2db-community-server/chat2db-community-plugins/chat2db-community-mysql/src/test/java/ai/chat2db/plugin/mysql/completion/. Test the provider directly or test the full pipeline to ensure correct candidate generation for various SQL contexts.

Reference Implementation: MySQL Plugin

The MySQL plugin demonstrates the standard pattern for extending the completion engine. Located in chat2db-community-server/chat2db-community-plugins/chat2db-community-mysql, it implements the three-layer architecture:

• Provider: MysqlSqlCompletionProvider.java wraps a SqlCompletionPipeline initialized with MysqlSqlCompletionDialect.
• Dialect: MysqlSqlCompletionDialect.java defines MySQL-specific syntax rules and tokenizers.
• Plugin: MysqlSyntaxPlugin.java exposes the provider through getSqlCompletionProvider().

This structure serves as the template for PostgreSQL, Oracle, or any other database dialect you need to support.

Adding a New Database Dialect

To add support for an entirely new database:

1. Create a new Maven module under chat2db-community-plugins (e.g., chat2db-community-postgresql).
2. Add the chat2db-community-spi dependency to your module's pom.xml.
3. Implement ISqlCompletionProvider with a SqlCompletionPipeline configured for your dialect.
4. Create the dialect class implementing ISqlCompletionDialect with your database's grammar rules.
5. Implement ISqlSyntaxPlugin and override getSqlCompletionProvider() to return your provider.
6. Write unit tests following the pattern in MysqlSqlCompletionProviderTest.java.
7. Build and deploy the JAR to the classpath; Spring automatically registers your plugin.

Summary

• ISqlCompletionProvider is the core interface you must implement to provide custom completion logic.
• DefaultSqlSyntaxHandler automatically routes requests to the correct provider based on the active database connection.
• Implement ISqlCompletionDialect to define syntax-specific tokenization and completion rules.
• Expose your provider through ISqlSyntaxPlugin for automatic discovery via Spring component scanning.
• Reference the MySQL plugin implementation for production-ready examples of each component.

Frequently Asked Questions

What is the ISqlCompletionProvider interface?

The ISqlCompletionProvider interface defines the contract between Chat2DB's completion engine and database-specific implementations. It requires a single method complete(DbSqlCompletionRequest request) that analyzes SQL text at a specific cursor position and returns a SqlCompletionResponse containing suggestion candidates. Located in chat2db-community-server/chat2db-community-spi/src/main/java/ai/chat2db/spi/ISqlCompletionProvider.java, this interface allows any database plugin to inject custom completion logic without modifying core platform code.

How does DefaultSqlSyntaxHandler route completion requests?

DefaultSqlSyntaxHandler acts as the central dispatcher in chat2db-community-server/chat2db-community-spi/src/main/java/ai/chat2db/spi/DefaultSqlSyntaxHandler.java. When the web layer receives a completion request, this handler identifies the target database type, retrieves the corresponding ISqlSyntaxPlugin implementation, calls getSqlCompletionProvider() to obtain the dialect-specific provider, and forwards the request. This architecture decouples the web API from individual database implementations, allowing seamless extension through plugin JARs.

Can I customize completion logic without modifying core code?

Yes. Chat2DB's architecture is designed specifically for non-invasive extension. By creating a new Maven module that implements ISqlCompletionProvider and registering it through ISqlSyntaxPlugin, you can override or extend completion behavior for existing databases or add entirely new dialects. The Spring component scan automatically discovers your plugin on the classpath, and DefaultSqlSyntaxHandler integrates it without requiring changes to the chat2db-community-server core modules.

How do I test my custom SQL completion provider?

Write unit tests that instantiate your ISqlCompletionProvider implementation and invoke the complete() method with various DbSqlCompletionRequest scenarios. The project includes test templates like MysqlSqlCompletionProviderTest.java in chat2db-community-server/chat2db-community-plugins/chat2db-community-mysql/src/test/java/ai/chat2db/plugin/mysql/completion/. These tests verify that your provider returns appropriate SqlCompletionResponse objects containing expected candidates for specific SQL contexts and cursor positions.

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