How to Create Custom SQL Builders for Different Database Dialects in Chat2DB

Extend DefaultSqlBuilder and expose it through your plugin's MetaData implementation to add support for new database dialects without modifying Chat2DB's core codebase.

Chat2DB generates DDL and DML statements through a flexible plugin architecture that keeps the core codebase free of database-specific conditionals. To create custom SQL builders for different database dialects in Chat2DB, you implement the ISqlBuilder contract and register your builder through the metadata system. This approach allows you to override only the SQL generation methods that differ from standard syntax while inheriting generic implementations for common operations.

Understanding the ISqlBuilder Architecture

The SQL generation system in Chat2DB revolves around four key components that work together to support multiple database dialects.

ISqlBuilder serves as the unified entry point for all SQL generation. Defined in chat2db-community-spi/src/main/java/ai/chat2db/spi/ISqlBuilder.java, this interface declares methods for building identifiers, DQL statements, DML operations, and DDL commands.

DefaultSqlBuilder provides the base implementation that most dialects can inherit. Located in the SPI module, this class implements standard SQL generation for common operations like CREATE TABLE, ALTER TABLE, and pagination.

Dialect-Specific Builders extend DefaultSqlBuilder and override only the methods where the target database deviates from standard SQL. For example, XUGUDBSqlBuilder in the XU GU DB plugin customizes table creation logic while reusing the base implementation for other operations.

MetaData implementations expose the dialect-specific builder to the runtime. The IDbMetaData interface requires a getSqlBuilder() method that returns your custom builder instance, allowing Chat2DBContext to locate the correct implementation based on the connection's database type.

Step 1: Extend DefaultSqlBuilder for Your Dialect

Create a new class that extends DefaultSqlBuilder and place it in your plugin module under chat2db-community-plugins/chat2db-community-<dialect>. Override only the methods that require dialect-specific syntax.

package ai.chat2db.plugin.mydb.builder;

import ai.chat2db.spi.DefaultSqlBuilder;
import ai.chat2db.spi.model.request.PageLimitRequest;
import ai.chat2db.community.domain.api.model.metadata.Table;
import ai.chat2db.community.domain.api.model.metadata.TableColumn;
import ai.chat2db.community.domain.api.config.TableBuilderConfig;
import static ai.chat2db.plugin.mydb.constant.MyDbSqlBuilderConstants.*;

public class MyDbSqlBuilder extends DefaultSqlBuilder {

    @Override
    public String buildCreateTable(Table table, TableBuilderConfig config) {
        // Custom CREATE TABLE syntax for MyDB
        StringBuilder sb = new StringBuilder();
        sb.append(SQL_CREATE_TABLE).append("\"").append(table.getName()).append("\" (");
        for (TableColumn col : table.getColumnList()) {
            if (col.getName() == null || col.getColumnType() == null) continue;
            sb.append("\"").append(col.getName()).append("\" ")
              .append(mapMyDbType(col.getColumnType()))
              .append(col.getNullable() == 1 ? " NULL" : " NOT NULL")
              .append(", ");
        }
        sb.setLength(sb.length() - 2); // Remove trailing comma
        sb.append(");");
        return sb.toString();
    }

    private String mapMyDbType(String genericType) {
        // Example mapping logic
        return switch (genericType.toUpperCase()) {
            case "VARCHAR" -> "VARCHAR2";
            case "INT"     -> "INTEGER";
            default        -> genericType;
        };
    }

    @Override
    public String buildPageLimit(PageLimitRequest request) {
        // MyDB uses `OFFSET … ROWS FETCH NEXT … ROWS ONLY`
        int offset = request.getOffset();
        int pageSize = request.getPageSize();
        StringBuilder sql = new StringBuilder(request.getSql());
        sql.append(" OFFSET ").append(offset).append(" ROWS FETCH NEXT ")
           .append(pageSize).append(" ROWS ONLY");
        return sql.toString();
    }

    // Override other methods only when MyDB deviates from the default implementation.
}

This example demonstrates overriding buildCreateTable to handle custom type mapping (VARCHAR to VARCHAR2) and buildPageLimit to implement Oracle-style pagination syntax. The class inherits all other SQL generation methods from DefaultSqlBuilder.

Step 2: Expose the Builder via MetaData

Implement the IDbMetaData interface in your plugin to provide the custom builder instance. The getSqlBuilder() method acts as the factory that Chat2DB calls when it needs to generate SQL for your dialect.

package ai.chat2db.plugin.mydb;

import ai.chat2db.spi.IDbMetaData;
import ai.chat2db.spi.ISqlBuilder;
import ai.chat2db.plugin.mydb.builder.MyDbSqlBuilder;
import java.util.List;
import java.util.Arrays;

public class MyDbMetaData implements IDbMetaData {

    @Override
    public ISqlBuilder getSqlBuilder() {
        return new MyDbSqlBuilder();   // <-- custom builder
    }

    @Override
    public List<String> getSystemSchemas() {
        return Arrays.asList("SYS", "SYSTEM"); // example
    }

    @Override
    public String getMetaDataName(String... names) {
        // MyDB quotes identifiers with back‑ticks
        return Arrays.stream(names)
                     .filter(name -> name != null && !name.isBlank())
                     .map(name -> "`" + name + "`")
                     .reduce((a, b) -> a + "." + b)
                     .orElse("");
    }

    // Implement other metadata methods (type maps, default values, etc.) as needed.
}

The MetaData implementation also defines system schemas, identifier quoting rules, and type mappings that the SQL builder may reference during generation.

Step 3: Plugin Registration and Discovery

Place your plugin module under the chat2db-community-plugins/ directory structure. Ensure your Maven pom.xml declares the chat2db-community-spi dependency to access the base classes and interfaces.

<dependency>
    <groupId>ai.chat2db</groupId>
    <artifactId>chat2db-community-spi</artifactId>
    <version>${project.version}</version>
</dependency>

Chat2DB automatically discovers plugins through the Java SPI mechanism. No additional registration code is required. The framework scans the classpath for IDbMetaData implementations and associates them with the appropriate database type based on your plugin configuration.

Runtime Integration

When a user connects to your database dialect, Chat2DB retrieves the appropriate SQL builder through the context. Higher-level services like DefaultSQLExecutor and DefaultMetaService invoke the builder through the unified ISqlBuilder interface without knowing the specific dialect implementation.

// Inside Chat2DB core (e.g., DefaultSQLExecutor)
ISqlBuilder sqlBuilder = Chat2DBContext.getSqlBuilder(); // Returns MyDbSqlBuilder for MyDB
String createTableSql = sqlBuilder.ddl().buildCreateTable(table, config);

This design maintains clean separation between the core platform and database-specific logic. The java-plugin-contracts.md specification outlines the complete contract that custom builders must respect, including preferred patterns for DDL and DML construction.

Key Source Files

The following files illustrate the complete implementation pattern in the Chat2DB source:

• ISqlBuilder.java: Core interface defining all builder segments. Located at chat2db-community-spi/src/main/java/ai/chat2db/spi/ISqlBuilder.java.
• DefaultSqlBuilder.java: Base implementation providing generic SQL generation. Located at chat2db-community-spi/src/main/java/ai/chat2db/spi/DefaultSqlBuilder.java.
• XUGUDBSqlBuilder.java: Reference dialect-specific builder overriding table creation logic. Located at chat2db-community-plugins/chat2db-community-xugudb/src/main/java/ai/chat2db/plugin/xugudb/builder/XUGUDBSqlBuilder.java.
• XUGUDBMetaData.java: Reference metadata implementation exposing the custom builder. Located at chat2db-community-plugins/chat2db-community-xugudb/src/main/java/ai/chat2db/plugin/xugudb/XUGUDBMetaData.java.

Summary

• Extend DefaultSqlBuilder to create dialect-specific SQL generation while inheriting standard implementations for common operations.
• Implement IDbMetaData and override getSqlBuilder() to expose your custom builder to the Chat2DB runtime.
• Place plugins under chat2db-community-plugins/ to enable automatic discovery via the SPI mechanism.
• Access builders through Chat2DBContext.getSqlBuilder() to ensure the correct dialect-specific implementation is used for each connection.
• Reference XUGUDBSqlBuilder as a working example of dialect customization in the Chat2DB repository.

Frequently Asked Questions

How does Chat2DB determine which SQL builder to use for a specific connection?

Chat2DB uses the Chat2DBContext.getDbMetaData() method to retrieve metadata based on the connection's dbType property. The framework automatically locates the appropriate plugin and calls IDbMetaData.getSqlBuilder(), which returns the dialect-specific builder instance. This lookup occurs transparently when services like DefaultSQLExecutor request a SQL builder through Chat2DBContext.getSqlBuilder().

What methods must I override when extending DefaultSqlBuilder?

You only need to override methods where your target database diverges from standard SQL syntax. Common overrides include buildCreateTable for custom type mappings, buildAlterTable for schema modification quirks, and buildPageLimit for database-specific pagination syntax (such as Oracle's OFFSET/FETCH or MySQL's LIMIT). All other SQL generation methods inherit behavior from DefaultSqlBuilder.

Can I use a completely custom implementation instead of extending DefaultSqlBuilder?

Yes, you can implement the ISqlBuilder interface directly if your database requires radically different SQL syntax. However, extending DefaultSqlBuilder is recommended because it provides working implementations for dozens of common operations, reducing the code you must write and maintain. The XUGUDBSqlBuilder example in the repository demonstrates the preferred extension approach.

Where should I place my custom SQL builder code in the project structure?

Create a new Maven module under chat2db-community-plugins/ with the naming convention chat2db-community-<dialect>. Place your builder class in the builder package and your metadata implementation in the root plugin package. Ensure your module depends on chat2db-community-spi to access the base classes and interfaces required for integration.

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